Bug 64294 - Incompatibility with dataValidation tag in Excel, LibreOffice newest versions
Summary: Incompatibility with dataValidation tag in Excel, LibreOffice newest versions
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 4.1.2-FINAL
Hardware: Macintosh All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on: 56205
  Show dependency tree
Reported: 2020-03-31 14:40 UTC by Adrianna
Modified: 2020-03-31 16:10 UTC (History)
0 users

The XML is not quite the same... (110.40 KB, image/png)
2020-03-31 14:40 UTC, Adrianna

Note You need to log in before you can comment on or make changes to this bug.
Description Adrianna 2020-03-31 14:40:40 UTC
Created attachment 37140 [details]
The XML is not quite the same...

I am generating a dynamic spreadsheet, with dropdown data validation lists. I have discovered an incompatibility with Microsoft Office 16.35 (the newest version) I do not have any other versions available to test this issue.

The issue is (I was using Apache POI 3.7 and then I updated to 4.1.2 so this issue is on BOTH latest major versions)

this Java code 

generates Excel XML code that reads 

allowBlank="true" showDropDown="true"

 whereas a dropdown list validation added manually on MSFT Excel will add the following XML code

allowBlank="1" showDropDown="1"

This has stumped me for numerous hours (I was trying different things on Java...) 👩🏻‍💻

BTW LibreOffice was NOT able to cope with this either 😿 so I don't want to be TOO harsh on "poor MSFT"

but Apple Numbers 6.2.1 has always been able to open.

My workaround is that I removed these offending Java lines which luckily are by default anyways so I obtained the desired behaviour. 

Without these lines the XLSS files are compatible across the three spreadsheets programs without a hitch. 

Just putting it here so the next dev is spared the pain I went through 😸