Bug 62394

Created attachment 35941 [details]
The output .xls file generated by the above code.

I am trying to create a excel file(.xls) while writing a excel for tolal of 9000 rows, after row number 3000 all the styles and formatting are lost. I have tried to get the number of cellstyle after writing the complete workbook by using workbook.getNumCellStyles(), it returns 2091. It is clear that the number of cellstyle are not exceeded(i.e are less than 4000) Still the styling is lost. Please help me understand why would this happen?

I have created a Sample code, below this fails even if i am creating 75 CellStyle Objects.

public class ExcelSheet {
    static Workbook workbook;
    static Sheet spreadsheet;
    static CellStyle tempCellStyle;
    static Map<Integer, CellStyle> styles = new HashMap<Integer, CellStyle>();
    static boolean flag = false;

    public static void main(String[] args) throws IOException {
        workbook = new HSSFWorkbook();
        spreadsheet = workbook.createSheet(" Employee Info ");
        Row row;
        System.out.println(" Writing data to excel...");
        for (int i = 1; i < 500; i++) {
            row = spreadsheet.createRow(i);
            createCells(row, i);
        }
        for (int i = 0; i < 10; i++) {
            spreadsheet.setColumnWidth(i, 4500);
        }
        FileOutputStream out = new FileOutputStream(new File("E:\\Dan\\Writesheet.xls"));
        System.out.println(" No. of cell styles : " + workbook.getNumCellStyles());
        System.out.println(" No. of font styles : " + workbook.getNumberOfFonts());
        workbook.write(out);
        out.close();
    }

    private static void createCells(Row row, int rowNo) {
        for (int i = 0; i < 10; i++) {
            Cell cell = row.createCell(i);
            try {
                writeData(cell, i, rowNo);
            } catch (Exception e) {
                System.out.println("Exception : " + e);
            }
        }
    }

    private static void writeData(Cell cell, int i, int rowNo) throws Exception {
        CellStyle style;
        if (workbook.getNumCellStyles() < 75) {
            style = createStyleObject();
        } else {
            style = null;
        }

        switch (i) {
        case 0:
            if (style == null) {
                style = styles.get(0);
            } else {
                styles.put(0, style);
            }
            cell.setCellValue(rowNo);
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(style);
            break;
        case 1:
            if (style == null) {
                style = styles.get(1);
            } else {
                styles.put(1, style);
            }
            cell.setCellValue("Test Data " + rowNo);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellStyle(style);
            styles.put(0, style);
            break;
        case 2:
            if (style == null) {
                style = styles.get(2);
            } else {
                styles.put(2, style);
            }
            DataFormat poiFormat = spreadsheet.getWorkbook().createDataFormat();
            String excelFormatPattern = DateFormatConverter.convert(Locale.US, "yyyy-MM-dd");
            style.setDataFormat(poiFormat.getFormat(excelFormatPattern));
            cell.setCellValue(new Date(rowNo % 10, rowNo % 12, rowNo % 28, rowNo % 12, rowNo % 60, rowNo % 60));
            cell.setCellStyle(style);
            break;
        case 3:
            if (style == null) {
                style = styles.get(3);
            } else {
                styles.put(3, style);
            }
            cell.setCellValue(rowNo + rowNo);
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(style);
            break;
        case 4:
            if (style == null) {
                style = styles.get(4);
            } else {
                styles.put(4, style);
            }
            cell.setCellValue("Sample Text " + rowNo);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellStyle(style);
            break;
        case 5:
            if (style == null) {
                style = styles.get(5);
            } else {
                styles.put(5, style);
            }
            DataFormat poiFormat1 = spreadsheet.getWorkbook().createDataFormat();
            String excelFormatPattern1 = DateFormatConverter.convert(Locale.US, "yyyy-MM-dd");
            style.setDataFormat(poiFormat1.getFormat(excelFormatPattern1));
            cell.setCellValue(new Date(rowNo % 18, rowNo % 12, rowNo % 28, rowNo % 12, rowNo % 60, rowNo % 60));
            cell.setCellStyle(style);
            break;
        case 6:
            if (style == null) {
                style = styles.get(6);
            } else {
                styles.put(6, style);
            }
            cell.setCellValue(rowNo + i);
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellStyle(style);
            break;
        case 7:
            if (style == null) {
                style = styles.get(7);
            } else {
                styles.put(7, style);
            }
            cell.setCellValue("Exports Data " + rowNo);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellStyle(style);
            break;
        case 8:
            if (style == null) {
                style = styles.get(8);
            } else {
                styles.put(8, style);
            }
            DataFormat poiFormat2 = spreadsheet.getWorkbook().createDataFormat();
            String excelFormatPattern2 = DateFormatConverter.convert(Locale.US, "yyyy-MM-dd");
            style.setDataFormat(poiFormat2.getFormat(excelFormatPattern2));
            cell.setCellValue(new Date(rowNo % 25, rowNo % 12, rowNo % 28, rowNo % 12, rowNo % 60, rowNo % 60));
            cell.setCellStyle(style);
            break;
        case 9:
            if (style == null) {
                style = styles.get(9);
            } else {
                styles.put(9, style);
            }
            DataFormat poiFormat3 = spreadsheet.getWorkbook().createDataFormat();
            String excelFormatPattern3 = DateFormatConverter.convert(Locale.US, "yyyy-MM-dd");
            style.setDataFormat(poiFormat3.getFormat(excelFormatPattern3));
            cell.setCellValue(new Date());
            cell.setCellStyle(style);
            break;

        default:
            if (style == null) {
                style = styles.get(7);
            } else {
                styles.put(7, style);
            }
            cell.setCellValue("Default Value " + rowNo);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellStyle(style);
            break;
        }
    }

    private static CellStyle createStyleObject() {
        Workbook workbook = spreadsheet.getWorkbook();
        CellStyle style = (HSSFCellStyle) workbook.createCellStyle();
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor((short) 67);
        style.setWrapText(true);
        return style;
    }

}


why should it lose style even if i use one unique styles or more , unless the number is less than 4000. This is just a example code i have put in here to demonstrate the problem, my actual code generates only unique styles.

This also Happens for .xlsx but after a larger number of cellstyle objects.
The POI version here is 3.8 but i have also tried it with latest 3.17 it gives the same issue.