Phpspreadsheet: Writing to and saving existing xlsx file.

[unknown-opensource]

This is a bug report for 1.4.0

What is the expected behavior?

  Libreoffice (Version: 6.0.3.2 Build ID: 1:6.0.3-0ubuntu) opens file without warning.

What is the current behavior?

  Warning loading document output.xlsx: The data could not be loaded completly because the maximum number of columns per sheet was exceeded.

What are the steps to reproduce?

    $template = "template.xlsx"; 
    // (https://github.com/PHPOffice/PhpSpreadsheet/files/2365395/template.xlsx) - empty file
    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($template);
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save("output.xlsx");

[yasar-luo]

Could you please just provide the template.xlsx？

[MAKS-dev]

@GreatHumorist he supplied a link to it in his ticket:
https://github.com/PHPOffice/PhpSpreadsheet/files/2365395/template.xlsx

[yasar-luo]

@unknown-opensource @MAKS-dev I tried in my computer, there is no error occurred, and the output.xlsx file is successfully generated.

[MAKS-dev]

Sorry ... I'm not conttibuting here on the issue nor (tried to) reproduce it.
Just pointed out where to find the file ;-)

[damijanc]

This is not a library bug. Libre office supports max 1024 columns while Excel support 16384. You can read all about it https://bugs.documentfoundation.org/show_bug.cgi?id=50916.

[unknown-opensource]

@damijanc so my empty file has too many columns?

[damijanc]

No, there is nothing wrong with your file. You will get the warning because excel supports more columns than open office does. As long as you do not generate excel files with more than 256 columns of data your open office users will be fine.

[unknown-opensource]

@damijanc it is interesting how template file (https://github.com/PHPOffice/PhpSpreadsheet/files/2365395/template.xlsx) opens without error before processing with PhpSpreadsheet

[damijanc]

Maybe the file metadata is wrong. It would be interesting to check.

[PowerKiKi]

Not a PhpSpreadsheet bug, but a limitation of LibreOffice

[Yopai]

I don't understand why you say "this is a limitation of LibreOffice"

The file is totally empty, just opened and saved with spreadsheet, and something changed in it (since it doesn't open cleanly anymore in Libreoffice)

I can hear that Libreoffice generates something that Phpspreadsheet don't (or badly) understand, but aren't you interested in knowing WHAT phpspreadsheet doesn't understand ?

[dericlima]

I agree with @Yopai , while exporting with PHPExcel something is changing and causing this warning.

I'm investigating to see if I can find the root cause of this. So far I have discovered that while exporting a excel that already exists, a collection with column dimensions will be created with 1025 objects, each object correspond to one column. Since Calc has a limit of 1024 columns, I'm assuming this collection is creating the issue.

During my tests I realized that generating a excel from scratch the column dimension collection remains empty.

For now I don't have a answer but as soon as I find out what's the issue I will post here....

[CaffeinatedTech]

Yeah I'm seeing this too using a template supplied by a customer.
material_template_paragon.xlsx
material_paragon.xlsx

use PhpOffice\PhpSpreadsheet\IOFactory;
$spreadsheet = IOFactory::load($_SERVER['DOCUMENT_ROOT'].'/companytemplates/material_template_paragon.xlsx');
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->getCell('C9')->setValue(15);
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save($_SERVER['DOCUMENT_ROOT'].'/images/material_paragon.xlsx');

[yellowstonebum]

Ya. I can create a spreadsheet, but it wont save locally. I am trying to save a copy into a folder within Wordpress. Would that need special permission settings?

[lphooge]

This happens as far as i can see with xlsx files that where created by MS office, and than opened and saved by libreoffice.
A fresh file created by libreoffice does not trigger that behavior for me.

The bug seems to be in the reader, not the xlsx-writer: You can output the file in html format, and see that a LOT of empty columns have been generated.
Also, trying to remove them does not work:

    $worksheet->removeColumn('C', 0);
    $worksheet->removeRow(3,0);

this will clear out any data beyond colum C and row 3, but the empty cells are still rendered.

[lphooge]

So, I extracted one original xlsx file from MS Excel, and also the version from openoffice, diffed them and created different combinations of the two to find the exact part in the xml that triggers the bug.

I found that it is the following tag inside the sheet1.xml file:
<col customWidth="false" hidden="false" outlineLevel="0" max="1025" min="66" style="1" width="11.42"/>
In the Excel version of the file it looks like this:

<col min="66" max="16384" width="11.42578125" style="2"/>

The source of the problem is the max="1025" attribute. If I change that to 16384 the problem disappears.

my current best guess is that 16384 is treaded as some kind of magic number inside the Excel-Reader, and if it is something different the column-rendering bugs out.

Edit: Found it, here is the problem:
https://github.com/PHPOffice/PhpSpreadsheet/blob/f734783d826bd84c3d54fcf7b71c37ab9bac4b04/src/PhpSpreadsheet/Reader/Xlsx/ColumnAndRowAttributes.php#L139

[PowerKiKi]

my current best guess is that 16384 is treated as some kind of magic number inside the Excel-Reader,

This is correct. We do have code like:
php if ((int) ($column['max']) == 16384) {

And its origin goes all the way back to the very first commit of this project. So it's hard to figure why it's there without debugging further...

[Yopai]

Reading the code, we can still do an educated guess.
The break has for effect stopping treating columns, probably in order that the last column definition isn't repeated until 16384.

So, the test would probably be better written "are we in the last iteration of the foreach ($worksheetCols->col as $column) loop".