OpenXLSX: For xlsx generated by java POI5, it cannot be opened by MS Excel after modification by OpenXLSX
Background: Apache POI library is specially used to process MS office files. The fifth version of POI(POI5), relies on the zip library Apache Commons Comprese. The zip64 implementation of this library is opczip, which is specially implemented for MS Excel.
Problem: After opening, modifying and saving the xlsx file generated by POI5 through OpenXLSX, MS Excel will not be able to open the modified file. I tried to look at the source code of POI and OpenXLSX and found the problem here. The root cause is that there are slight differences between the zip64 standard implemented by zippy (which looks like miniz) and opczip that OpenXLSX relies on for zip64.
Notes:
- Because opczip is a special implementation of MS Excel (I have tested that the xlsx generated by opczip can be opened by MS Excel), I tried to modify the zippy code. There is a bad way to make it compatible with opczip (that is, MS Excel). When dealing with the optional data descriptor in
mz_zip_writer_add_from_zip_reader, check whether the zip version is0X002D, like this:... mz_bool is_src_zip64 = MZ_FALSE; // a bad way for compatible with POI5 if (MZ_READ_LE16(pLocal_header + MZ_ZIP_LDH_VERSION_NEEDED_OFS) >= 0X002D) { unsigned long len = (sizeof(mz_uint32) * 6) + 2; mz_uint8 tmp[len]; if (pSource_zip->m_pRead(pSource_zip->m_pIO_opaque, cur_src_file_ofs, tmp, len) == len) { n = sizeof(mz_uint32) * ((MZ_READ_LE32(tmp) == MZ_ZIP_DATA_DESCRIPTOR_ID) ? 6 : 5); if (MZ_READ_LE16((mz_uint8*)tmp + n) == 0X4B50) { is_src_zip64 = MZ_TRUE; } } } if ((pSource_zip->m_pState->m_zip64) || (found_zip64_ext_data_in_ldir) || is_src_zip64) { ... - Maybe this isn’t Zippy’s fault, but I found that zippy has many problems with its implementation of zip64. Have you considered updating OpenXLSX’s dependency on zippy (it seems that zippy does not fully support zip64)?
- This is the file in question.POI5.2.3.xlsx
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Comments: 16
One more thing - if you are playing around with the code, please consider working with the development-aral branch that fixes some other issues with “non vanilla” workbooks.
However, the issue you triggered with your modifications could also happen for a workbook that contains files which are currently not supported by OpenXLSX (and therefore ignored, just like in the change that I believe you added).
Okay, so with that knowledge, if I skip a worksheet from being parsed, I can get the Zippy save routine to return false from “IsModified()” for that file. But that creates a whole lot of different problems - worksheets are not supposed to be “skipped” when loading. And your example does not work for me then, because without a single worksheet in the workbook, saving it will throw an exception because the workbook validation fails.
I’ll mark this as invalid and close it for now - when you have a good working suggestion for a loading optimization (at the user’s risk) please open a new issue with a “feature request” 😃
Yes - in my development-aral branch, the “IsModified()” is always true. And I am trying to find out why…
But also, it appears that the bug you encounter is triggered by the files considered “unmodified” in the source archive.
If the Data Descriptor is in zip64 standard, the size before and after compression will be represented by 8 bytes respectively, otherwise it will be represented by 4 bytes.
In the zippy implementation, if an XML file is not modified, the Data Descriptor in the source file will be copied to the new file. During the copying process, since the source file is recognized by zippy as zip32 format, it will be copied incorrectly, as shown below: