cellStyles: true export is incompatible with LibreOffice #3099

New Issue

myocytebd · 2024-04-02T06:22:30Z

myocytebd commented

2024-04-02 06:22:30 +00:00

LibreOffice has lower constraint on column count, while export with cellStyles: true could always spam 16384 columns instead of min-max range.

Make a sheet containing an infinite horizontal border.
sheet1.xml now contains a last column like this, for both Excel (max=16384) and LibreOffice (max=1024);
<col min="11" max="16384" width="9" style="4" />
Let sheetjs import and export file with cellStyles: true, now columns are spammed:

<col min="11" max="11" />
<col min="12" max="12" />
...
<col min="16384" max="16384" />

LibreOffice rejects the new export due to too many columns.
(LibreOffice accepts max=16384, but not 16384 col entries)

LibreOffice has lower constraint on column count, while export with `cellStyles: true` could always spam 16384 columns instead of min-max range. - Make a sheet containing an infinite horizontal border. `sheet1.xml` now contains a last column like this, for both Excel (max=16384) and LibreOffice (max=1024); `<col min="11" max="16384" width="9" style="4" />` - Let sheetjs import and export file with `cellStyles: true`, now columns are spammed: ``` <col min="11" max="11" /> <col min="12" max="12" /> ... <col min="16384" max="16384" /> ``` - LibreOffice rejects the new export due to too many columns. (LibreOffice accepts max=16384, but not 16384 col entries)

sheetjs commented

2024-04-02 06:53:53 +00:00

A teammate raised a similar issue with LibreOffice 5 years ago: https://bugs.documentfoundation.org/show_bug.cgi?id=125380

To be absolutely sure, the following file with custom column widths is valid in Excel:

var data = Array.from({length:16384}, (_, i)=> i);
var ws = XLSX.utils.aoa_to_sheet([data])
ws["!cols"] = data.map(n => ({wch:n}));
var wb = XLSX.utils.book_new(ws, "Sheet1");
XLSX.writeFile(wb, "i3099.xlsx", {cellStyles: true});

It contains 16384 column entries:

  <cols>
    <col min="1" max="1" width="0.83203125" customWidth="1"/>
    <col min="2" max="2" width="1.83203125" customWidth="1"/>
    <!-- ... more entries ... -->
    <col min="16384" max="16384" width="16383.83203125" customWidth="1"/>
  </cols>

Since it is valid in Excel, it's almost certainly a bug in LibreOffice.

What version of LibreOffice are you using? Do you see the same error if you remove the last column entry?

A teammate raised a similar issue with LibreOffice 5 years ago: https://bugs.documentfoundation.org/show_bug.cgi?id=125380 To be absolutely sure, the following file with custom column widths is valid in Excel: ```js var data = Array.from({length:16384}, (_, i)=> i); var ws = XLSX.utils.aoa_to_sheet([data]) ws["!cols"] = data.map(n => ({wch:n})); var wb = XLSX.utils.book_new(ws, "Sheet1"); XLSX.writeFile(wb, "i3099.xlsx", {cellStyles: true}); ``` It contains 16384 column entries: ```xml <cols> <col min="1" max="1" width="0.83203125" customWidth="1"/> <col min="2" max="2" width="1.83203125" customWidth="1"/>  <col min="16384" max="16384" width="16383.83203125" customWidth="1"/> </cols> ``` Since it is valid in Excel, it's almost certainly a bug in LibreOffice. What version of LibreOffice are you using? Do you see the same error if you remove the last column entry?

myocytebd commented

2024-04-02 07:42:32 +00:00

A teammate raised a similar issue with LibreOffice 5 years ago: https://bugs.documentfoundation.org/show_bug.cgi?id=125380

What version of LibreOffice are you using? Do you see the same error if you remove the last column entry?

7.3.7.2 from Ubuntu 22.04 (release at 08-Dec-2022 11:08 according to LibreOffice archive)
A simple modification to !cols data (crop 1025-16384 col) before sheejs export does avoid error from LibreOffice.

The LibreOffice issue is not exactly relevant and seems to be fixed:

According to the issue, old LibreOffice writes xlsx file that itself cannot handle, which no longer happens.
LibreOffice now accepts Excel save with <col min="11" max="16384" width="9" style="4" />, but rejects 16384 individual cols. (not sure if the latter is intended or bug)

> A teammate raised a similar issue with LibreOffice 5 years ago: https://bugs.documentfoundation.org/show_bug.cgi?id=125380 > > What version of LibreOffice are you using? Do you see the same error if you remove the last column entry? 7.3.7.2 from Ubuntu 22.04 (release at `08-Dec-2022 11:08` according to LibreOffice archive) A simple modification to `!cols` data (crop 1025-16384 col) before sheejs export does avoid error from LibreOffice. The LibreOffice issue is not exactly relevant and seems to be fixed: - According to the issue, old LibreOffice writes xlsx file that itself cannot handle, which no longer happens. - LibreOffice now accepts Excel save with `<col min="11" max="16384" width="9" style="4" />`, but rejects 16384 individual cols. (not sure if the latter is intended or bug)

sheetjs commented

2024-04-02 17:54:20 +00:00

Testing locally against 7.3.2.2, the spreadsheet opens properly. https://jsfiddle.net/27hpxvyL/ is a live demo to generate the spreadsheet.

LibreOffice shows a warning since it does not currently support 16384 columns:

The data could not be loaded completely because the maximum number of columns per sheet was exceeded.

After clicking "OK", a truncated version of the file opens.

You are likely encountering a bug in LibreOffice. Please raise an issue with them.

That said, we could add a special "LibreOffice" mode that enforces 1024 columns and tries to work around other LibreOffice bugs. Can you confirm that the software can handle 1024 col entries?

Testing locally against 7.3.2.2, the spreadsheet opens properly. https://jsfiddle.net/27hpxvyL/ is a live demo to generate the spreadsheet. LibreOffice shows a warning since it does not currently support 16384 columns: > The data could not be loaded completely because the maximum number of columns per sheet was exceeded. ![i3099-LO-warning.png](/attachments/576ec81f-5f27-4536-9977-c6f39c3e618c) After clicking "OK", a truncated version of the file opens. ![i3099-LO.png](/attachments/8c3ef3fc-1bf5-492f-abfd-544a97800506) You are likely encountering a bug in LibreOffice. Please raise an issue with them. --- That said, we could add a special "LibreOffice" mode that enforces 1024 columns and tries to work around other LibreOffice bugs. Can you confirm that the software can handle 1024 col entries?

i3099-LO-warning.png

171 KiB

i3099-LO.png

418 KiB

Sign in to join this conversation.