There is a 32,767 max character limit for strings in Excel. If one writes an XML file that exceeds this limit, the file is subject to inspection and correction by Excel on the first load (which is really annoying). Microsoft Documentation On Limit http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx?CTT=5&origin=HP005199291 Workaround (Caller - Example) cell.setCellValue(toWrite.length() > 32767 ? toWrite.substring(0, 32767 - 3) + "..." : toWrite); Proposed Solution Simply truncate the string the same way Excel would truncate the string upon first inspection and load and provide sufficient documentation alerting users of the what a cell value can hold. or... Either throw a RuntimeException alerting the user of the impending error I'll open for discussion if you would like a pull request, please, just let me know and I'd be happy to put one together, with a test for the team. Thank You!
In HSSFCell, we have the following logic: throw new IllegalArgumentException("The maximum length of cell contents (text) is 32,767 characters"); I'd suggest we just apply the same in XSSF too, if you could do a patch and a unit test that'd be great!
Nick, Thank you very much for the reply. I will create a patch this weekend after I get done with my work. :o) Great work team, doing a lot of work over at Apache Streams (Incubating) and this will make a great addition.
If you spot any issues with the docs, or areas where extra examples would be helpful, whilst doing your Streams work we'd be delighted to have updates :)
Hi, I've run into the same problem while working with apache POI 3.9-20121203. One thing that I'd like to note here is that apache poi doesn't have any problems reading back the content of the cell and the conent is exactly what is stored before. The file though is unusable by Microsoft Excel and what I've observed is that once we decide to let Excel recover the contents then the cell contents are truncated.
We now enforce this limit via changes in r1671096, as the resulting document is not valid in Excel I don't think there is much use in creating those, better to alert the developer early that the document will not be good.