Bug - Unable to upload xlsx file to Power BI workspace due to applyAlignment attribute

[stitches]

I use Apache POI to generate xlsx files. Recently I upgraded from Apache POI 4 to Apache POI 5 and my excel files started failing their upload to my workspace (https://app.powerbi.com/).

My investigation led me to generate roughly the same content using Apache POI 4 and Apache POI 5 and narrowed down the issue to the applyAlignment="true" attribute in the excel file's "xl/styles.xml" file. This attribute was added to Apache POI 5 as a bug fix (https://bz.apache.org/bugzilla/show_bug.cgi?id=64494) and seems to be causing an error when uploading to my Power BI workspace.

I removed all instances of the attribute from the styles.xml file, and I was able to successfully upload the file to my Power BI workspace.

Interestingly, the same xlsx file can be opened just fine in other Microsoft products, such as Excel.

[Anonymous]

Hi @stitches 

So your problem is caused by upgrading the version of Apache POI, is it ?If you restore Apache POI to 4.0, does the problem still exist?

Best Regards,
Community Support Team _ Ailsa Tao

[stitches]

Hi @Anonymous 

If I restore to Apache POI v4.x.x, the problem doesn't exist and the xlsx file is able to be uploaded to Power BI workspace.

I was able to narrow down the cause to the "applyAlignment" attribute by generating the same content using Apache POI v4 and Apache POI v5 and finding that the only significant difference between the two xlsx sources to be the "applyAlignment" attribute. I tested the theory by removing the "applyAlignment" attribute from the Apache POI v5-generated xlsx file, and I was able to upload the xlsx file to Power BI.

[chinale]

Hi, were you able to work around this issue somehow?

[stitches]

@chinale 

PowerBI has an implementation of xlsx parser that doesn't work well with specific compression methods. In my case, Apache POI 5 defaults to Zip64 mode, a compression method that PowerBI's xlsx parser struggles with, while Microsoft Excel's xlsx parser seems to be able to handle just fine.

Our workaround is to set Apache POI's compression strategy to Zip64Mode.AsNeeded, as it was in Apache POI 4. We've requested that PowerBI support this compression mode since it's a valid compression method, but the change is not as simple to make on PowerBI's side and is not a priority as of when I talked to them.

[chinale]

Thanks @stitches, set to Zip64Mode.AsNeeded and I'm able to load in PowerBI now.