Bug 56579 - Cell Value - String 32,767 Character Limit
Summary: Cell Value - String 32,767 Character Limit
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.10-FINAL
Hardware: Macintosh All
: P2 minor (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-05-30 04:09 UTC by smashew
Modified: 2015-04-03 15:57 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
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.
Comment 5 Dominik Stadler 2015-04-03 15:57:51 UTC
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.