Total Pageviews

2016/12/07

[Apache POI] java.lang.IllegalArgumentException: The maximum length of cell contents (text) is 32,767 characters

Problem
I am using Apache POI to write data into Microsoft Excel, but it throw this exception during the process:
java.lang.IllegalArgumentException: The maximum length of cell contents (text) is 32,767 characters

Here is the code snippet, this exception result from line 12:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    private void writeIssueDataForEachRow(Issue issue, Row row, CellStyle style,
                                          List<ColumnIndex> customFieldDefinitions) {
        Cell cell = row.createCell(0);
        cell.setCellValue(issue.getId()); // 編號
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue(issue.getSubject()); // 主旨
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue(issue.getDescription()); // 敘述
        cell.setCellStyle(style);

    }



Solution
According to Apache POI's source code, it will check the maximum length of each cell cannot be larger than 32,767 characters.
https://github.com/apache/poi/blob/31f2b5fdd10c7442576cbed5d37507cb2cdf11cc/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java#L574-L576    

And it is also XLS's imitation, so we need to do substring or use XLSX format instead. We choose the former option. Therefore, my source code should be modified as following:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    private void writeIssueDataForEachRow(Issue issue, Row row, CellStyle style,
                                          List<ColumnIndex> customFieldDefinitions) {
        Cell cell = row.createCell(0);
        cell.setCellValue(issue.getId()); // 編號
        cell.setCellStyle(style);

        cell = row.createCell(1);
        cell.setCellValue(issue.getSubject()); // 主旨
        cell.setCellStyle(style);

        // substring 的原因是要避開 The maximum length of cell contents (text) is 32,767 characters
        cell = row.createCell(2);
        cell.setCellValue(StringUtils.substring(issue.getDescription(), 0, 32767)); // 敘述
        cell.setCellStyle(style);

    }


Reference
[1] https://support.office.com/en-us/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa?CorrelationId=c8dcbafe-51ff-447e-bd62-3c1ce0e1d05e&ui=en-US&rs=en-US&ad=US&ocmsassetID=HP010073849

No comments: