Getting error using POI HSSF

Question

I'm getting an error when trying to open an Excel sheet with MS office 2003. This Excel sheet is created using HSSFWorkbook, implementing the usermodel scope org.apache.poi.hssf.usermodel

In Microsoft Excel 2003: "Too many different cell formats". In Microsoft Excel 2007/2010, files may produce the following error message: "Excel found unreadable content in the file". This is regarding cell formats, please refer to the below page:

http://support.microsoft.com/kb/213904

So how can I fix this issue in code?

Answer 1

Excel has a limit on the number of different cells styles you can have, and it's surprisingly low. A common problem for people new to working with POI is that they skip over the bit about cell styles being workbook wide, and instead they create one cell style per cell. This quickly pushes them over the limit in Excel...

Where you code might previously have looked something like

Sheet s = wb.createSheet();
for (int rn=0; rn<=10; rn++) {
    Row r = s.createRow(rn);
    for (int cn=0; cn<=4; cn++) {
        Cell c = r.createCell(c);
        c.setCellValue( getMyCellValue(rn,cn) );

        CellStyle cs = wb.createCellStyle();
        cs.setBold(true);
        if (cn == 2) { 
            cs.setDataFormat( DataFormat.getFormat(yyyy/mm/dd) );
        }
        c.setCellStyle(cs);
    }
}

You instead need to pull your cell style creation out to the start, something like

CellStyle bold = wb.createCellStyle();
bold.setBold(true);

CellStyle boldDate = wb.createCellStyle();
boldDate.setBold(true);
boldDate.setDataFormat( DataFormat.getFormat(yyyy/mm/dd) );

Sheet s = wb.createSheet();
for (int rn=0; rn<=10; rn++) {
    Row r = s.createRow(rn);
    for (int cn=0; cn<=4; cn++) {
        Cell c = r.createCell(c);
        c.setCellValue( getMyCellValue(rn,cn) );

        CellStyle cs = bold;
        if (cn == 2) { 
            cs = boldDate;
        }
        c.setCellStyle(cs);
    }
}