Bug 67510

Summary: No POI-generated Charts can be opened by Excel for Mac v. 16.77
Product: POI Reporter: Adam <adam>
Component: XWPFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major CC: effort0819, lynda.p.douglas
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Generated XLS from POI Example
original bar chart from documentation
Re-saved original bar chart from documentation

Description Adam 2023-09-25 14:31:10 UTC
Created attachment 39069 [details]
Generated XLS from POI Example

Upon an (automatic) upgrade of Excel for Mac to v. 16.77 I have observed that all Charts created via POI / XSSF appear to be detected as corruption on-open. 


The repair result is this: 
Repair Result to ooxml-bar-chart2.xmlErrors were detected in file '/tmp/poi/ooxml-bar-chart.xlsx'Removed Part: /xl/drawings/drawing1.xml part. (Drawing shape)



This was originally detected w/ some filed I made with POI, and to investigated I cloned src and ran a bunch of examples, with same result. 

Example: 
https://github.com/apache/poi/blob/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/BarChart.java
Comment 1 ihor 2023-10-05 11:47:34 UTC
Can confirm issue appears for updated Excel on Mac. 

Encountering an error exclusively on Excel for Mac 16.77.1, while it works fine on version 16.66.1

Error reproduces using minimal setup from the official BarChart Example. Opening and resaving the file on a different Excel version is a temporary fix.
Comment 2 Dominik Stadler 2023-10-05 16:45:04 UTC
Can you attach the same file BEFORE and AFTER it was "fixed" by opening/saving in Excel?
Comment 3 ihor 2023-10-05 17:04:21 UTC
Created attachment 39099 [details]
original bar chart from documentation
Comment 4 ihor 2023-10-05 17:04:47 UTC
Created attachment 39100 [details]
Re-saved original bar chart from documentation
Comment 5 ihor 2023-10-05 17:06:29 UTC
Added two files, the original generated from https://svn.apache.org/repos/asf/poi/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/BarChart.java

and re-saved file which starts to be working.
Comment 6 Adam 2023-10-05 17:14:18 UTC
thank you @ihor + @Dominik for taking a look!
👏 👏 😃
Comment 7 opy.hartmann 2023-10-10 12:52:51 UTC
Hello,
I can confirm that bug with the new Excel version 16.77 on MacOs and Windows, too.

Even if this lib is fixed for new generated files, this bug is a real problem for retro-compatibility of old generated files. That's why I have contacted Microsoft support that has transferred my request to the tech team. Waiting now...

Do you have any more information on your side ?
Thank you,
Olivier
Comment 8 PJ Fanning 2023-10-10 13:06:12 UTC
My view is that this a Microsoft problem. They even get paid to fix problems. Apache POI is a free volunteer project.

If your Excel install has a bug, then try loading the Excel files into something else - examples
* hosted Excel support (eg on outlook.com)
* load the xlsx with Google Spreadsheets
* try it with LibreOffice or OpenOffice
Comment 9 PJ Fanning 2023-10-10 21:03:22 UTC
A number of stackoverflow.com questions appear to have been created about this or similar issues recently and all of them appear to link to this answer.

https://stackoverflow.com/questions/77131316/create-a-chart-in-a-powerpoint-using-apache-poi-and-java-from-scratch/77135432#77135432
Comment 10 ihor 2023-10-11 07:26:58 UTC
This works well, thank you PJ Fanning!
Fix is here:

if (bottomAxis.hasNumberFormat()) bottomAxis.setNumberFormat("@");
if (rightAxis.hasNumberFormat()) rightAxis.setNumberFormat("#,##0.00");
Comment 11 opy.hartmann 2023-10-11 09:33:55 UTC
Hello,
Thank you very much, that fix the thing. More, the generated files could never been opened with Mac's Numbers and never found why, and it solved it, too !
Thanks !
Comment 12 ihor 2023-10-11 09:36:36 UTC
Why would we resolve this ticket? Should be the fix applied for POI lib?
Comment 13 PJ Fanning 2023-10-11 10:46:12 UTC
We can leave this open. We should make a POI change but I'm not sure when this will happen. If someone wants to try creating a patch with a test then we will certainly consider it.
Comment 14 PJ Fanning 2023-10-11 10:53:50 UTC
I tried XDDFCategoryAxis

private void initializeAxis(CTPlotArea plotArea, AxisPosition position)

to do this:

        ctCatAx.addNewNumFmt().setSourceLinked(true);
        ctCatAx.getNumFmt().setFormatCode("@");

I don't have Microsoft products installed any more and my outlook.com Word online access just seems to hang if I load a POI generated docx file with or without this change.

I don't have much time on this but I can review any submissions.
Comment 15 PJ Fanning 2023-10-13 08:43:15 UTC
*** Bug 67726 has been marked as a duplicate of this bug. ***
Comment 16 PJ Fanning 2023-10-14 14:55:16 UTC
*** Bug 67638 has been marked as a duplicate of this bug. ***
Comment 17 PJ Fanning 2023-11-08 12:36:08 UTC
I added r1913672. I tested this using POI's BarChart example and it seems to fix that. Excel on outlook.com shows the chart after I apply this change locally and run the sample (and did not before I made this change).