Bug 54662

Summary: HSSFDataValidation message size limit
Product: POI Reporter: idelvall <igdevaal>
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.9-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description idelvall 2013-03-10 22:26:43 UTC
When a message with a size longer than 255 is added to a cell data validation, the workbook is corrupted.
This limit comes from an excel limitation.

CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
DataValidation dataValidation = new HSSFDataValidation(addressList, DVConstraint.createCustomFormulaConstraint("1=1"));dataValidation.createPromptBox(title, message);

createPromptBox() should throw an IllegalArgumentException() in this case explaining the reason

Similary a limit of 33 characters is imposed to "title" argument in ME
Comment 1 Michael van der Gulik 2019-07-01 04:01:28 UTC
Just been caught out by this bug. My opinion is that it's a bug in Excel and that Apache POI needs to work around it...

It also applies to XSSFDataValidation.

The symptom is (for people Googling this) when Excel opens the spreadsheet:

"We found a problem with some content in <spreadsheet>. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

and then:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error104200_01.xml</logFileName><summary>Errors were detected in file 'C:\Users\<snip>.xlsm'</summary><repairedRecords><repairedRecord>Repaired Records: Data validation from /xl/worksheets/sheet2.xml part</repairedRecord></repairedRecords></recoveryLog>

Thanks, Excel. That's a useless error message and I spent an hour hunting this down.
Comment 2 Dominik Stadler 2020-02-09 19:29:23 UTC
This seems to have been fixed quite a while ago via 59200 already, see r1771254 for details.

*** This bug has been marked as a duplicate of bug 59200 ***