Style with POI "too many different cell format" when trying to open excel

Question

I'm generating an Excel file from Java with POI. The Excel spreadsheet contains too many tables. When I generate the file and try to open it with Excel I got this error: "too many different cell formats".

This is how I create a style:

private XSSFCellStyle crearEstilo(String formato, Color colorLetra, Color colorFondo, Double size, Boolean bold, String bordes, String alineacion) {

    XSSFCellStyle estilo = wb.createCellStyle();
    // Formato
    if (formato != null) {
        estilo.setDataFormat(wb.createDataFormat().getFormat(formato));
    }

    // Letra
    XSSFFont letra = wb.createFont();
    letra.setFontName("Arial");
    if (size != null) {
        letra.setFontHeight(size);
    }

    // Bold
    if (bold != null) {
        letra.setBold(bold);
    }

    // Color de letra
    if (colorLetra != null) {
        XSSFColor color = new XSSFColor(colorLetra);
        letra.setColor(color);
    }
    estilo.setFont(letra);

    // Bordes
    if (bordes != null) {
        // Borde izquierdo        
        if (bordes.charAt(0) == '1') {
            estilo.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        }
        // Borde superior
        if (bordes.charAt(1) == '1') {
            estilo.setBorderTop(XSSFCellStyle.BORDER_THIN);
        }
        // Borde derecho
        if (bordes.charAt(2) == '1') {
            estilo.setBorderRight(XSSFCellStyle.BORDER_THIN);
        }
        // Borde inferior
        if (bordes.charAt(3) == '1') {
            estilo.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        }
    }
    // alineación
    if (alineacion != null) {
        char horizontalAlign = alineacion.charAt(0);
        if (horizontalAlign == '1') {
            estilo.setAlignment(HorizontalAlignment.LEFT);
        } else if (horizontalAlign == '2') {
            estilo.setAlignment(HorizontalAlignment.CENTER);
        } else if (horizontalAlign == '3') {
            estilo.setAlignment(HorizontalAlignment.RIGHT);
        }

        char verticalAlign = alineacion.charAt(1);
        if (verticalAlign == '1') {
            estilo.setVerticalAlignment(VerticalAlignment.TOP);
        } else if (verticalAlign == '2') {
            estilo.setVerticalAlignment(VerticalAlignment.CENTER);
        } else if (verticalAlign == '3') {
            estilo.setVerticalAlignment(VerticalAlignment.BOTTOM);
        }

        if (alineacion.length() > 2) {
            char ajustarTexto = alineacion.charAt(2);
            if (ajustarTexto == '1') {
                estilo.setWrapText(true);
            } else if (ajustarTexto == '0') {
                estilo.setWrapText(false);
            }
        }
    }

    if (colorFondo != null) {
        estilo.setFillForegroundColor(new XSSFColor(colorFondo));
        estilo.setFillPattern(CellStyle.SOLID_FOREGROUND);
    }

    return estilo;
}

And this is how I use it in a specific cell:

XSSFSheet hojaVariables = wb.createSheet("nombreHoja");

    XSSFRow row_2 = hojaVariables.createRow(1);
    setCellValue(row_2, 1, "Data", crearEstilo(null, null, new Color(204, 255, 255), 8d, false, "1101", "12"));


 private void setCellValue(XSSFRow row, int colNum, String value, XSSFCellStyle estilo) {
        XSSFCell celda = row.createCell(colNum);
        celda.setCellType(Cell.CELL_TYPE_STRING);
        celda.setCellValue(value);
        celda.setCellStyle(estilo);
    }

Ok, i didnt saw it. Thanks! – user3241039 Aug 12 '15 at 17:52 — user3241039, Aug 12 '15 at 17:52

score 2 · Accepted Answer · answered Aug 12 '15 at 17:14

As indicated in the Apache POI Quick Guide, there is a limit to the number of CellStyles that a Workbook can hold.

This problem comes from the fact that you are creating a CellStyle for every Cell you create, even if the CellStyles are identical.

Create the CellStyle once, and reuse it for multiple Cells.

CellStyle myCellStyle = crearEstilo(null, null, new Color(204, 255, 255), 8d, false, "1101", "12");

And later, when creating the cells, you can reuse the CellStyle:

setCellValue(row_2, 1, "Data", myCellStyle);

Ok, i will change my code thanks. – user3241039 Aug 12 '15 at 17:50 — user3241039, Aug 12 '15 at 17:50

Style with POI "too many different cell format" when trying to open excel

1 Answers1