Export to spreadsheet creates an xlsx file with errors when exporting empty tables/layers

[kobusburger]

Describe the bug
The "Export to spreadsheet" algorithm creates an xlsx file with errors depending in the layers that are selected. Some layers do not create the error and others do. The errors can be verified the attached files:
Export test.qgz
Export test.gpkg
error107520_04.xml
Export test.xlsx

How to Reproduce

1. Run "Export to spreadsheet"
2. Select layers, select output file and run (accept the defaults for the check boxes)
3. The algorithm completes without errors
4. Excel gives an error when the xlsx file is opened

QGIS and OS versions

QGIS version	3.18.2-Zürich	QGIS code revision	515138c
Compiled against Qt	5.11.2	Running against Qt	5.11.2
Compiled against GDAL/OGR	3.1.4	Running against GDAL/OGR	3.1.4
Compiled against GEOS	3.8.1-CAPI-1.13.3	Running against GEOS	3.8.1-CAPI-1.13.3
Compiled against SQLite	3.29.0	Running against SQLite	3.29.0
PostgreSQL Client Version	11.5	SpatiaLite Version	4.3.0
QWT Version	6.1.3	QScintilla2 Version	2.10.8
Compiled against PROJ	6.3.2	Running against PROJ	Rel. 6.3.2, May 1st, 2020
OS Version	Windows 10 (10.0)
Active python plugins	firstaid; kmltools; NNJoin; plugin_reloader; refFunctions; SpreadsheetLayers; db_manager; MetaSearch; processing

Additional context
Export error test.zip

[gioman]

Additional context
Export error test.zip

@kobusburger gpkg datasource is missing in your zip

[kobusburger]

Apologies Kobus Burger +27 83 228 9674

…

On Mon, 26 Apr 2021 at 11:44, Giovanni Manghi ***@***.***> wrote: *Additional context* Export error test.zip <https://github.com/qgis/QGIS/files/6375043/Export.error.test.zip> @kobusburger <https://github.com/kobusburger> gpkg datasource is missing in your zip [image: Screenshot_20210426_104254] <https://user-images.githubusercontent.com/1951107/116062879-3c22c800-a67c-11eb-8acc-ad684967a00e.png> — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#42945 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABPZJ7BWAQI6TUZAZI33MCDTKUYV7ANCNFSM43SJOQGA> .

[kobusburger]

Export error test.zip
gpkg is now included in the zip

[gioman]

Export error test.zip
gpkg is now included in the zip

@kobusburger output opens fine in LibreOffice Calc, cannot test on Excel as I don't own any copy of MS Office.

[kobusburger]

Also note that "Export to spreadsheet" sometimes does not export all the selected layers (depending on the selection). This can confirmed by opening the xlsx in 7-Zip.

[FelisPimeja]

I can confirm the bug with test dataset in Excel 365 on Windows 10 x64.

The bug seems somehow related with exporting empty tables.

• If you export one empty table - it's OK.
• When you export several tables including empty one and that empty table is in the end of queue (- > and last spreadsheet in excel file) - it's OK.
• When you export several tables including empty one and that empty table is NOT in the end of queue - resulting XLSX is corrupted.

If I add a row to the empty table the bug also dissapears.
Have no skills to investigate further ).

[Pedro-Murteira]

Still valid on QGIS 3.22.4 and 3.24.0. Tested with both "MS Office Excel" and "LibreOffice Calc".

[alexbruy]

@rouault does this ring a bell for you?

[rouault]

Could someone confirm that test.xlsx can be opened fine with Excel 365 / MS office ?

[alexbruy]

@rouault I tried to open it using Office 2016 and it opens fine.