Increase the maximum length of HSSFCell in java

Question

Actually I tried to store some data in HSSFCell using java but i got an error like

java.lang.IllegalArgumentException: The maximum length of cell contents (text) i
s 32,767 characters
        at org.apache.poi.hssf.usermodel.HSSFCell.setCellValue(HSSFCell.java:559
)
        at org.apache.poi.hssf.usermodel.HSSFCell.setCellValue(HSSFCell.java:533
)
        at application.ExtractUI.datatoexcel(ExtractUI.java:272)
        at application.ExtractUI$3.getData(ExtractUI.java:208)
        at application.ExtractUI$3.handle(ExtractUI.java:198)
        at application.ExtractUI$3.handle(ExtractUI.java:1)

can anyone suggest me a method to increase the cell length ie more than 32767 characters???

I used the following code for which I got the above error

 public void datatoexcel(ResultSet rs) {
        try {
            int iter = 0;
            ResultSetMetaData rmeta = rs.getMetaData();
            int col = rmeta.getColumnCount();
            HSSFWorkbook workbook = new HSSFWorkbook();

            Date date = new Date();

            SimpleDateFormat sdf = new SimpleDateFormat("ddMMyyyy HHmmss");
            String pa = pth + "\\" + sdf.format(date) + ".xlsx";
            System.out.println(pa);
            FileOutputStream out = new FileOutputStream(new File(pa));
            HSSFSheet sheet = workbook.createSheet();
            HSSFRow myRow = null;
            HSSFCell myCell = null;
            // Font style for headers
            HSSFFont boldFont = workbook.createFont();
            boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            boldFont.setColor(HSSFFont.COLOR_RED);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFont(boldFont);
            while (rs.next()) {
                // limit the data to 1000 anad create a new sheet
                if (iter == 1000) {
                    sheet = workbook.createSheet();
                    iter = 0;
                }
                // Adding header to the first row
                if (iter == 0) {
                    myRow = sheet.createRow(iter);
                    for (int k = 1, j = 0; k <= col && j < col; k++) {
                        myCell = myRow.createCell( j);

                        myCell.setCellValue(rmeta.getColumnName(k));
                        // set style to font
                        myCell.setCellStyle(cellStyle);

                        j++;
                    }
                    iter++;
                }
                // Adding data from 2nd Row
                myRow = sheet.createRow(iter);
                for (int k = 1, j = 0; k <= col && j < col; k++) {

                    myRow.createCell( j).setCellValue(
                            rs.getString(rmeta.getColumnName(k)));
                    j++;
                }
                iter++;
            }

            workbook.write(out);

            out.close();


        } catch (Exception e) {
            e.printStackTrace();
        }

    }

any suggestions??

Could you add your code? – Till Rohrmann Aug 11 '15 at 07:12 — Till Rohrmann, Aug 11 '15 at 07:12

score 10 · Accepted Answer · answered Aug 11 '15 at 09:07

Your only option is to switch file formats. There's a hard limit in both the .xls and .xlsx file formats of 32,767 characters. Apache POI is simply enforcing the file format + Excel limit. You can see details of those limits in the Microsoft documentation, and also captured nicely in this Apache POI javadoc page

If you really need text that long, you'll need to switch to another file format such as CSV

score 0 · Answer 2 · answered Aug 26 '21 at 11:30

0

Or you can truncate your string length to the specified maximum length minus one

answered Aug 26 '21 at 11:30

Gokuw

1
1
1

score -1 · Answer 3 · edited Jun 18 '20 at 12:45

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);

}

Increase the maximum length of HSSFCell in java

3 Answers3