Phpspreadsheet: Excel from template didn't copy charts.

[Bust3D]

This is:

- [ ] a bug report
- [x] 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?

Should copy Charts if there exists.

What is the current behavior?

Using "Generating Excel files from templates (read, modify, write)" on a file where created by Excel and if it contains Charts, the Charts are not saved. The name of worksheet is copied but the Chart not.

What are the steps to reproduce?

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:

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = IOFactory::load("template.xlsx");
$spreadsheet->setActiveSheetIndexByName('score');
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->fromArray($score_v);

for ($i=1; $i <= count($score_v); $i++) { 
    $worksheet->setCellValue('E'.$i, '50');
}

// Save Excel 2007 file
$filename = 'ok.xlsx';
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($filename);
// add code that show the issue here...

Which versions of PhpSpreadsheet and PHP are affected?

i'm using the current version.

[Bust3D]

Here is my template, contains a simple empty chart created with Microsoft Excel.

https://ufile.io/51mnm

Please use this for Debug or Test.

Regards.

[Bust3D]

I just look into code and also printed out the objects after template.xlsx loaded and there is no charts, so this is a feature request not a bug.

Also on load function didn't found any case where the charts are copied. (in template.xlsx archive the structure and everything is there for chart).

Regards.

[PowerKiKi]

The file contains links to other files, and I cannot see any chart when opening it with LibreOffice. Clearly there is something unusual about that file. Please reproduce the case without depending on an external file.

[Bust3D]

That file is a simple empty chart created with Microsoft Excel 2016. Nothing to do with LibreOffice.
The you can reproduce it alone.
You have to just create one chart with Excel and see if it's copies when you save (but won't) and that's the problem.

[lubosdz]

Though closed, I can also confirm, that Excel templates ignore charts - won't load, thus won't save.

Scenario:

• create any XLSX with values in cells A1 - A3 [1,2,3]
• insert e.g. bar chart and save the file as template.xlsx.
• Following code will load template.xlsx, modify A1, save as template2.xlsx - but without the chart.

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('template.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->getCell('A1')->setValue(123);
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true); // useless since no charts were loaded
$writer->save('template2.xlsx'); // cell A1 has value 123, but the chart is gone

[PowerKiKi]

are you able to reproduce it purely with code ? creating the template by code and then demonstrate the bug ?

[lubosdz]

Sure, here's full code:

// create template with chart
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 1);
$sheet->setCellValue('A2', 2);
$sheet->setCellValue('A3', 3);

$area = 'Worksheet!$A$1:$A$3';
$dataSeriesValues = $dataseriesLabels = $xAxisTickValues = [
    new \PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues('Number', $area),
];

$series = new \PhpOffice\PhpSpreadsheet\Chart\DataSeries(
    \PhpOffice\PhpSpreadsheet\Chart\DataSeries::TYPE_BARCHART,
    \PhpOffice\PhpSpreadsheet\Chart\DataSeries::GROUPING_STANDARD,
    range(0, count($dataSeriesValues) - 1),
    $dataseriesLabels,
    $xAxisTickValues,
    $dataSeriesValues
);

$plotarea = new \PhpOffice\PhpSpreadsheet\Chart\PlotArea(null, [$series]);
$chart = new \PhpOffice\PhpSpreadsheet\Chart(
  'chart1',   // name
  null,       // title
  null,       // legend
  $plotarea,  // plotArea
  true,       // plotVisibleOnly
  0,          // displayBlanksAs
  NULL,       // xAxisLabel
  NULL        // yAxisLabel
);

$chart->setTopLeftPosition('A5');
$chart->setBottomRightPosition('H20');
$sheet->addChart($chart);

// save XLSX
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->setIncludeCharts(true);
$writer->save('template.xlsx'); // saved with the chart

// re-use template
$spreadsheetTpl = \PhpOffice\PhpSpreadsheet\IOFactory::load('template.xlsx');
$worksheetTpl = $spreadsheetTpl->getActiveSheet();
$worksheetTpl->getCell('A1')->setValue(123);
$writerTpl = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheetTpl, 'Xlsx');
$writerTpl->setIncludeCharts(true); // useless since no charts were loaded
$writerTpl->save('template2.xlsx'); // cell A1 has value 123, but the chart is gone

[nic86]

to make the charts work it is necessary to call the function "setIncludeCharts" also in the reader:

$inputFileType = 'Xlsx';
$inputFileName = 'test.xlsx';
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load($inputFileName);

or better, for all types of files(but it works only if this static function is set public):

$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::getReaderTypeFromExtension($inputFileName);
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load($inputFileName);

[lubosdz]

@nic86
That really works, thanx a lot!

Though it's counter intuitive - one would expect that opening Excel file automatically include charts too.
There are so many undocumented things in PhpSpreadSheet and it takes lots of time to figure out by reverse engineering or debugging :-(

[saurid]

I know this issue is closed, but I'm still having the same problem as above.

When I run lubosdz code from 28 may together with nic86's addition on reader, both files is indeed created/read/saved correctly.

However, if I open and save the created template.xlsx in MS Office (without changing anything), only use the "re-use template"-part to read/save, then I get an error in template2.xlsx. MS Excel returns an error when the file is opened, tries to correct the problem and responds that the chart (/xl/drawings/drawing1.xml) is deleted.

This leads me to the conclusion that files saved from MS Office Excel with charts is still not loaded/saved correctly.

I use Microsoft Office 365 on Windows 10.

[SalvadorBR]

I have the same problem that saurid, I use MS EXCEL 2016,

please surid did you found the solution?

In my server I have:

php 5.6.38
phpspreadsheet v1.3.0 (also I test this issue witth v1.4.0)
PHP extension php_zip enabled
PHP extension php_xml enabled
PHP extension php_gd2 enabled

I also I test this isue wirh a file .ods and I have the same problem

Thanks.

[saurid]

@SalvadorBordon
No, I didn't find any solution. I ended up creating the chart from scratch with code instead. It's not perfect but I had to come up with something.

I hope I don't have to do this with a more complicated project.

[SalvadorBR]

Thanks saurid for your answer, but I need a solution for my project.

Please If samebody know a solution, my code (it's a apiRest service) to test is:

$ParametrosApl = new ric_areaAdminParametros();

$pAnyo = $token = $app->request->get("anyo");
$fechaHoraFichero = gmdate('Y_m_d_H_i_s');

$pathTemplateXLSX = $ParametrosApl->rutaFichControlComedor."CONTROL_COMEDOR_2018_SOLO_VALORES.xlsx";
$pathTempXLSX = $ParametrosApl->rutaFichTempXLS."InformeAnualDetallado_".$fechaHoraFichero.".xlsx";

$inputFileType = 'Xlsx';
$inputFileName = $pathTemplateXLSX;
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load($inputFileName);
$worksheet = $spreadsheet->getActiveSheet();

// here the code for data in Excel file......

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$fileData = $writer->save('php://output');

$res = $app->response();

$res['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
$res['Content-Transfer-Encoding: binary'];
$res['Content-Disposition'] = 'attachment; filename=resumenmensual_'.$fechaHoraFichero.'.xlsx';
$res['Cache-Control'] = 'max-age=0';
$res['Cache-Control'] = 'max-age=1';
$res['Expires'] ='Mon, 26 Jul 1997 05:00:00 GMT';
$res['Last-Modified'] = gmdate('D, d M Y H:i:s') . ' GMT';
$res['Cache-Control'] = 'cache, must-revalidate';
$res['Pragma'] = 'public';
$resultQuery = array("result" => "753");

echo $fileData;

[lubosdz]

@SalvadorBordon Your writer should also set $writer->setIncludeCharts(true);.

[SalvadorBR]

THANKS A LOT, it's works

Today it's a great friday.... ;-)

[97WaterPolo]

Not sure about anyone else, but I am still having issues loading a graph from an excel document.

` $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

    $reader->setIncludeCharts(TRUE);

    $workbook = $reader->load($xlsx);

    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($workbook);

    $writer->setIncludeCharts(TRUE);

    $writer->save($xlsxTarget);

    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($xls);

    $reader->setIncludeCharts(TRUE);

    $spreadsheet = $reader->load($xls);

    $writer =  \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');

    $writer->setIncludeCharts(TRUE);

    $writer->save($xlsTarget2);

    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($xlsx);

    $reader->setIncludeCharts(TRUE);

    $spreadsheet = $reader->load($xlsx);

    $writer =  \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');

    $writer->setIncludeCharts(TRUE);

    $writer->save($xlsxTarget2);`

None of these methods have worked. The file is succesfully copied but when I open it I get a message about it being corrupted and it needs to be repaired to launch. I repair it and then the graph is gone. Any help?

[Jam1eP]

I also have the same issue with charts being very inconsistent. It seems quite random if the Xlsx will be corrupted or not. I have 2 template files, both created with Excel2016 the same way. 1 works the other is corrupt.

Take the output from PhpSpreadsheet with a working chart, change an axis in excel and resave as the template and it is now corrupt

I have now found that the same file that reports as corrupt in Excel, opens ok in LibreOffice but looks terrible. I'll try creating the charts in an older version of Excel to see if that helps

[Jam1eP]

@97WaterPolo

After 100s of attempts I may have found why charts in templates are a problem.

Created an Excel spreadsheet containing data with a bar chart. Used this as a template and all is ok. Delete the data but leave the empty chart to use as a template and the Excel becomes corrupt.

Put a space in the first cell of the data range for the chart and it works perfectly. Looks like the empty cell is causing a problem

[97WaterPolo]

@Jam1eP

So you are saying have all the data ranged for the chart filed with a default value? I have 6 different bar graphs plotted on my chart, so I would need to add something in all the spaces that the chart spans?

[Jam1eP]

@97WaterPolo I only had to add a space in the 1st cell. Which for me was x axis labels. Try populating the template with default values and cutting them back until it doesn’t work again. I haven’t had a chance to check the source to see if it is the reader or writer that was causing the issue

[97WaterPolo]

@Jam1eP

I did what you said, and I succesfully got the graph to copy over, but it is completely wrong. The data is there but the graph itself gets corrupted, the line graphs, bar graphs, etc are all incorrect after conversion.

[Jam1eP]

@97WaterPolo Have you got setPreCalculateFormulas set to true? Thats all I can think of to help you. All the charts I have (once the template copies) work ok. It may look a little different from the the template due to the way the engine makes the graphs

[97WaterPolo]

@Jam1eP I set it to true, it made some progress with displaying the other two stacked bar graphs, but the colors are not kepy and my line graphs are completely incorrect. I really wish there could be a sheet for sheet copy and only modify a specific sheet.

[97WaterPolo]

I ended up giving up with PHPSpreadsheet, it works very well for simple data exporting into Excel format but to get a graph template to work was a pain. I ended up manually editing the Excel file.

I had two sheets, one for the data and one for the graph.

So what I did was change .xlsx to .zip, unzip and go to xl/worksheets/sheet1.xml and made a template for that. I then just used that template to add my rows and values, put it back in the folder, zipped it back up, and then renamed it to xlsx and it works perfectly. Slight issue with strings, but the repair automatically assigns it to the sharedStrings.xml so I don't even need to worry about it.

I feel I am an outlier for needing a excel graph template so I guess I solved this issue on my own by manually editing the XML files that make up an excel file.

[sgurlt]

I have also faced this issue and realized that it seems to be chart type related.
Pie charts are working without any issue, but it is not working with Line diagrams.

[97WaterPolo]

To add on to what @sgurlt said, I believe I am having the issues with bar and line graphs as well!