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:

  1. 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 is 0X002D, 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) {
    ...
    
  2. 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)?
  3. This is the file in question.POI5.2.3.xlsx

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 16

Most upvoted comments

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.

Hi & thank you for the report. This sounds like an odd problem that I can only explain happening from a mix of data from different zip formats. Considering that Excel has no problem opening archives created by OpenXLSX, I would suggest the best way forward for me to address this would be to make sure that all “metadata” generated by opczip is wiped when saving in OpenXLSX. Would you see any issues with that approach?

It is also possible to erase all the “metadata” generated by opczip when saving, but for larger xlsx files, saving and reading will be more time-consuming due to the need to parse the XML.

Well - there is zero metadata in the actual XML as far as I could tell from your example. To be honest, without having looked too closely into zippy functionality, I am surprised that it is not re-creating the zip archive from scratch upon saving anyways - and that consequently, you get different behavior on xlsx-files created with OpenXLSX, and those modified with it. Just to repeat - is it correct that you can open the files created with OpenXLSX in MS Excel on your system without problems? E.g. the Demo0?.xlsx files? From your last comment, it appears that maybe it is enough to “downgrade” the version number if zippy finds it “too high”? May I ask what the “correct value” is for the Data Descriptor?

Yes, xlsx files created with OpenXLSX can be opened by MS Excel.

The “correct value” of the data descriptor is the size of each XML file.

As you know, if the XML file in the zip file has a Data Descriptor, the CRC of the XML file before compression, the size before compression, and the size after compression are saved in the Data Descriptor.

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:

// origin xml's Data Descriptor
 50 4B 07 08 73 B9 D9 10 06 66 30 21 00 00 00 00 9A 90 DC 15 00 00 00 00
|-----------|-----------|-----------------------|-----------------------|
 'PK\07\08'    CRC-32    compressed size          uncompressed size


// new xml's Data Descriptor
 50 4B 07 08 73 B9 D9 10 06 66 30 21 00 00 00 00
|-----------|-----------|-----------|-----------|
 'PK\07\08'    CRC-32    compressed 	uncompressed
 				        size                   size

// The correct one should be as follows (That is the bytes I modified manually)
 50 4B 07 08 73 B9 D9 10 06 66 30 21 9A 90 DC 15
|-----------|-----------|-----------|-----------|
 'PK\07\08'    CRC-32    compressed 	uncompressed
 				        size                   size