Bug 64294

Summary: Incompatibility with dataValidation tag in Excel, LibreOffice newest versions
Product: POI Reporter: Adrianna <adrianna.dev>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal    
Priority: P2    
Version: 4.1.2-FINAL   
Target Milestone: ---   
Hardware: Macintosh   
OS: All   
Bug Depends on: 56205    
Bug Blocks:    
Attachments: The XML is not quite the same...

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...) 👩🏻‍💻

BTW LibreOffice 6.2.8.2 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 😸