Phpspreadsheet: Writing to a Template xlsx file with a Pivot Table

[onachassi]

This is:

- [X] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Load existing template with a pivot table on the first worksheet, input raw data on the second sheet, and have the pivot table update with the new data.

What is the current behavior?

Data is being inputted on the second sheet but the pivot table on the first sheet is not longer a pivot table, and is now text.

What are the steps to reproduce?

1) create excel (xlsx) template with pivot table on first page generated from raw data on second.
2) delete the raw data and refresh the pivot table
3) load new data into the raw data worksheet with PhpSpreadsheet
4) download and view xlsx file

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:
This is very simplified version of the code I'm trying to run. I cannot provide the issue without providing an external excel file, so I'm trusting that you can do that on your own.

<?php
//receive payload as a json post parameter
$payload = isset($_POST['PAYLOAD']) ? json_decode($_POST['PAYLOAD'], true) : null;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

require_once __DIR__ . './vendor/phpoffice/phpspreadsheet/src/Bootstrap.php';

// Create new Spreadsheet object
$spreadsheet = IOFactory::load('./templates/PivotTemplate.xlsx');

$spreadsheet->getProperties()->setCreator('Ori Nachassi')
        ->setTitle('Pivot Demo')
        ->setSubject('Office 2007 XLSX');


// ============================ START Raw Data SHEET =============================

$spreadsheet->setActiveSheetIndex(1);
$spreadsheet->getActiveSheet()->setTitle('Raw Data');

if ($payload != null) {
    $rowCount = '2';
    foreach ($payload as $row) {

               $spreadsheet->setActiveSheetIndex(1)
          ->setCellValue('A'.$rowCount, utf8_encode($row[0]));

        $spreadsheet->setActiveSheetIndex(1)
          ->setCellValue('B'.$rowCount, utf8_encode($row[1]));

                $spreadsheet->setActiveSheetIndex(1)
          ->setCellValue('C'.$rowCount, utf8_encode($row[2]));

                $rowCount++;
    }       
}   

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(1);
$filename = 'Pivot Table ('.date("d-m-y").').xlsx';
// Redirect output to a client’s web browser (Xlsx)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;

?>

Which versions of PhpSpreadsheet and PHP are affected?

Php Version 7.1.8
PHPSpreadsheet Version 1.0.0

Thank you for this great tool, your help is greatly appreciated!

[lbernardxmp]

I have the same need.
An other solution to get around the problem?

[alfongoma]

I have the same need.

For the moment I'm using this https://github.com/svrnm/exceldatatables for the pivot tables. I've tested with 700.000 rows and It works fine.

For sheets without pivot tables I keep using PhpSpreadsheet, it's amazing

[onachassi]

Ok so here is my workaround.

I created an excel macro that generated a pivot table and save it to the worksheet as an xlsm file with a hot key startup and a little note on the first page as to how to active the macro.

I then went into the IOFactory.php file in the src/PhpSpreadsheet folder and added

'Xlsm' => Reader\Xlsx::class
To the $readers array and
'Xlsm' => Reader\Xlsx::class
to the $writers array.

I then emptied the file and placed the file with the macro as a template. I brought in the file like i would bring in any other xlsx template through PhpSpreadsheets and added raw data then exported it as an 'Xlsx' file with an 'xlsm' extension.

`
// import the spreadsheet
$spreadsheet = IOFactory::load('./templates/PivotTemplate.xlsm');
// ... this is where you fill your raw data tab ...
// export the file as a xlsm file
$filename = 'Pivot Table ('.date("d-m-y").').xlsm';

// But specify the IO factory writer as an xlsx file
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;`

Seems to work for me. Hope this helps.

[alfongoma]

Thanks, I'm going to try it

[lbernardxmp]

I created a macro that generated a pivot table too.

[alfongoma]

Hi onachassi, I've tried your solution, but It doesn't' work. Here It is my code:

`
$spreadsheet = IOFactory::load('../template.xlsm');
/* Fill Raw Data Tab */

$filename = 'Pivot Table ('.date("d-m-y").').xlsm';
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($filename);
`
Do you know what my problem is?

I am generating the excel in command line, and I send it by mail.

Thanks

[alfongoma]

Finally I did it with python, faster than php. I've used python 3 with pandas.

`
import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
from openpyxl import load_workbook

df = pd.read_csv(data_csv)
book = load_workbook(template_pivot_table)

writer = pd.ExcelWriter(path_xlsx, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, sheet_name = 'Data',startcol=0,startrow=1, header=None, index=False)
writer.save()

`

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[ekosynth]

This should be reopened. Still a bug. Formatted tables are turned into text.

[molerat619]

Almost 1 year later - still a bug.

[wernerkotze]

Finally I did it with python, faster than php. I've used python 3 with pandas.

`
import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
from openpyxl import load_workbook

df = pd.read_csv(data_csv)
book = load_workbook(template_pivot_table)

writer = pd.ExcelWriter(path_xlsx, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, sheet_name = 'Data',startcol=0,startrow=1, header=None, index=False)
writer.save()

`

@alfongoma What is the "data_csv" that you referring to?

[hongjieq]

Same needs, but it seems still cannot work. Could anyone help it? Thanks!