Bug 60832 - export xlsx file sometimes can not open using ms office
Summary: export xlsx file sometimes can not open using ms office
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2017-03-08 04:48 UTC by IceWee
Modified: 2019-07-01 15:17 UTC (History)
0 users

ok and error excel files (477.00 KB, application/zip)
2017-03-08 04:48 UTC, IceWee

Note You need to log in before you can comment on or make changes to this bug.
Description IceWee 2017-03-08 04:48:05 UTC
Created attachment 34807 [details]
ok and error excel files

when I use SXSSF export excel, sometimes can not open, I don't know why.
There is nothing exceptions or errors print.
The broken file's size lager than the file which can open.
The broken file has no author, the right file has author named Apache POI.
The bottom is the OK.xlsx and error.xlsx files.
Help me to find the reason, Thanks!
Comment 1 Javen O'Neal 2017-03-08 05:47:33 UTC
> sometimes
For the same input file and code, do you always get a corrupt file?

Can you include the code you used that resulted in corrupt files?
Comment 2 IceWee 2017-03-08 07:35:55 UTC
Thans for your reply!

Using the same code, some data export correctly, open is ok, but some data failed.

I think whether some special word/sign/char make excel-cell wrong, xml wrong...

This is Microsoft Office Excel's error tips:
Excel file level has been completed verification and repair.This workbook parts may have been repaired or discarded.
Replaced parts: XML errors/xl/worksheets/sheet1.xml.The attribute value cannot contain the '<'.Line 1880, 370.

I have checked the OK.xlsx and Error.xlsx, it's the same,/xl/worksheets/sheet1.xml the same.

Whether compress zip error? when I changed xlsx to zip, overrides Error file's sheet1.xml with the OK's, WINRAR tip "Compressed file damage"
Comment 3 IceWee 2017-03-08 08:09:44 UTC
(In reply to Javen O'Neal from comment #1)
> > sometimes
> For the same input file and code, do you always get a corrupt file?
> Can you include the code you used that resulted in corrupt files?

below codes:
cell = row.createCell(cellnum);

the value is from database, if I change the value to common string such as "hello", export file is OK. But sometimes(debugging) change nothing it's OK.

Comment 4 Javen O'Neal 2017-03-08 09:08:27 UTC
(In reply to IceWee from comment #3)
> below codes:
> cell = row.createCell(cellnum);
> cell.setCellValue(value);

This isn't sufficient for me to reproduce this problem. See http://sscce.org
Comment 5 IceWee 2017-03-08 10:31:31 UTC
I am sorry. I really don't know how to desc.

Maybe the data caused.
Comment 6 Nick Burch 2017-03-08 11:57:50 UTC
Based on your description, it sounds like there might be a bug in how we're escaping one of the more complicated/less-common values, when writing to the cell

For your problem file, if I open it in Excel 2010 I get the same error, with the same line/column. However, reading the unzipped .xlsx file I can't see anything wrong with the XML at the position indicated. (Does Excel count differently?)

xmllint is happy with the file, as is libreoffice

For the problematic file, would you be able to change your export logic to write a new file every 10 lines instead, and then share with us the smaller problematic file with just 10 lines in that fails the same way?
Comment 7 IceWee 2017-03-08 13:36:59 UTC
I've no idea.
If I export part data it's OK, for example, 100 rows, 10 rows.

When I export top 714 rows it's failed. So I think 714 row is problem data.
Then I export only 714 row, it's OK...Crazy....

It's work well export other Tables. SO I give up...
Comment 8 Dominik Stadler 2019-07-01 15:17:33 UTC
I tried to take a look at the ERROR.xlsx, but the file is an invalid ZIP file, so it seems the process which created this file somehow corrupted the whole zip. 

Exception in thread "main" java.io.IOException: central directory is empty, can't expand corrupt archive.
	at org.apache.commons.compress.archivers.zip.ZipFile.populateFromCentralDirectory(ZipFile.java:528)
	at org.apache.commons.compress.archivers.zip.ZipFile.<init>(ZipFile.java:216)
	at org.apache.commons.compress.archivers.zip.ZipFile.<init>(ZipFile.java:192)
	at org.apache.commons.compress.archivers.zip.ZipFile.<init>(ZipFile.java:153)
	at org.apache.poi.openxml4j.util.ZipSecureFile.<init>(ZipSecureFile.java:122)
	at org.apache.poi.openxml4j.opc.internal.ZipHelper.openZipFile(ZipHelper.java:205)
	at org.apache.poi.ooxml.dev.OOXMLPrettyPrint.handleFile(OOXMLPrettyPrint.java:90)
	at org.apache.poi.ooxml.dev.OOXMLPrettyPrint.main(OOXMLPrettyPrint.java:81)

So I don't think we can do much more here unless we know more about how the file is produced.

If you still have this problem and can provide a more isolated test-case then please reopen this issue and attach the test-case.