|Summary:||Cell Value - String 32,767 Character Limit|
|Component:||XSSF||Assignee:||POI Developers List <dev>|
Description smashew 2014-05-30 04:09:56 UTC
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!
Comment 1 Nick Burch 2014-05-30 10:24:35 UTC
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!
Comment 2 smashew 2014-05-30 14:47:28 UTC
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.
Comment 3 Nick Burch 2014-05-30 16:10:18 UTC
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 :)
Comment 4 Mirjan Merruko 2014-08-27 12:57:21 UTC
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.