Phpspreadsheet: Creating file from template, formatting changed

[ajaxovic]

I have simple template with cell and conditional formatting (see attachment). Cells E4 and E5 are formatted as percentage, cells F4 and E5 are currency. Now, I run this code:

$sheet = \PhpOffice\PhpSpreadsheet\IOFactory::load(__DIR__ . '/template.xltx');
$sheet->setActiveSheetIndex(0);

$activeSheet = $sheet->getActiveSheet();
$activeSheet->setCellValueExplicit('B4', number_format(126852.36, 2, '.', ''), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC);
$activeSheet->setCellValueExplicit('B5', number_format(33525555.2, 2, '.', ''), \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC);

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($sheet);
$writer->save(__DIR__ . '/output.xlsx');

When I open output.xlsx, Cells E4:F5 are not formatted as in template and I can't change them to right format I don't know why. Tested in Excel 2010. Libre office are OK.

files.zip

[PowerKiKi]

Can you reproduce without template.xltx ?

[ajaxovic]

I don't know what you mean... Problem is that formatting is not transferred from template to new document filled with PHP. Or what I'm missing?

[PowerKiKi]

Then can you read the format of the template's cell with PhpSpreadsheet ? what does it gives you ?

[ajaxovic]

If you mean format string, so, PhpSpreadsheet's answer for both files:
E4: 0%
F4: #,##0.00\ "Kč"

But as I said, Excel 2000 does not see it. I did some more tests and problem is conditional formatting. If I create new template without conditional formatting, everything works...

[PowerKiKi]

Can you find a difference when reading the template with conditional formatting and the one without it ? Are the format string the same ? Also did you check/compare $cell->getDataType() ?

[ajaxovic]

DataType is "f" for all files. Formatting string is same in all files. I think, problem is not reading, problem is writing.

I put all three files into attachement
files.zip

[ajaxovic]

I tried to dig into XML files in documents. Maybe, problem will be in linking cell style with cell definition.

I unzipped output file from phpSpreadsheet. If I understand it right, in file /xl/worksheet/sheet1.xml is reference to style 1 and style 2:

<c r="E4" s="2">
    <f>D4/C4</f>
    <v>-2799.1164263769</v>
</c>
<c r="F4" s="1">
    <f>D4/C4</f>
    <v>-2799.1164263769</v>
</c>

but, in file /xl/styles.xml is only one definition of styles:

<cellStyles count="1">
    <cellStyle name="Normal" xfId="0" builtinId="0"/>
</cellStyles>

Same it is with tag <cellStyleXfs> in same file. Also I noticed, that tag <dxfs> in template has 8 children, but in file from your library, there is only 4 children.

I hope it helps, I really need to fix this.. Thanks!

[PowerKiKi]

Did you try loading template and writing to disk without any modifications ? Then is still OK in Excel ?

Also why do you use setCellValueExplicit() instead of setCellValue() ?

[ajaxovic]

Nice idea, even without modifications of cell values, phpSpreadsheet broke formatting. I had this code:

$sheet = \PhpOffice\PhpSpreadsheet\IOFactory::load(__DIR__ . '/template_format.xltx');
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($sheet);
$writer->save(__DIR__ . '/output.xlsx');

And cell formatting is wrong in output file. Using setCellValue() did not change anything.

[PowerKiKi]

Can you try to re-create your template by code only with PhpSpreadsheet and see how that new template differs from your original template ? Maybe your original is corrupted, or PhpSpreadsheet is not able to write it, or not able to read it...

[ajaxovic]

That is not a point of this issue. File is not corrupted, I took Excel 2000, create new document, fill 4 columns with format and formula and save as template. Many times with different settings. Also, problem is more complex, because I have to fill huge table delivered from customer. I can't create template, customer creats template. I just simplified data for this issue. And also, I sent files to you for this tests. I think it will be much faster and much easier if some of developers debug this problem with my files.

[PowerKiKi]

Unfortunately we don't have much time to debug each issues. The fastest way to solve it is for you to submit a PR.

[ajaxovic]

I did what I can, Now is the developer time. You have a bug in code that I can't solve. Please, consider to test it.

[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.

[masfranzhuo]

Anyone still got this as an issue? I tried to read the file and save it again without changing anything, but the file size reduce and style format change.

[albertStaalburger]

Having same issue here...
Frustrating if you spend time styling everything in you r template to have it butchered by the export!
Have been at this issue for 2 days now.

[thanhkhanhcntt]

Any update, why close this issue?

[sykorax]

Hi,

I'm facing the same issue, just with charts instead of conditional formatting.

Expected looking (this is inside the skeleton / template file)

The result the package provides based on the previous template file.

Here is the code which produces the result. I'm really not familiar with xlsx formats and standards. Isn't there any kind of workaround to simply copy an existing sheet from a source file and inject it into the destination file? Maybe an immutable sheet feature should be considered so complex and continuously evolving features such as charts won't affect the package all the time, since we'd have the possibility to extract them into another sheets away from de-facto data sources.

// Create report from skeleton.
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setIncludeCharts(true);
$reader->setLoadSheetsOnly(['CHARTS', '%']);

$excel = $reader
    ->load(__DIR__.'/../../static/files/Task_Charts_Skeleton.xlsx');

$excel
    ->getProperties()
    ->setTitle($excelTitle = 'Tickets_weekly_export_'.date('Y-m-d'));

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($excel,'Xlsx');
$writer->setIncludeCharts(true);