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
URL:
Keywords:
Depends on: 56205
Blocks:
  Show dependency tree
 
Reported: 2020-03-31 14:40 UTC by Adrianna
Modified: 2020-03-31 16:10 UTC (History)
0 users


Attachments
The XML is not quite the same... (110.40 KB, image/png)
2020-03-31 14:40 UTC, Adrianna 		Details
View All Add an attachment (proposed patch, testcase, etc.)

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 
	    dataValidation.setSuppressDropDownArrow(false);
	    dataValidation.setEmptyCellAllowed(true);

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...) ﷐[U+1F469]﷑﷐[U+1F3FB]﷑‍﷐[U+1F4BB]﷑

BTW LibreOffice 6.2.8.2 was NOT able to cope with this either ﷐[U+1F63F]﷑ 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 ﷐[U+1F638]﷑