Bug 62394 - Excel loses styles and formating after writing certain rows. Even if Cellstyles are not exhausted
Summary: Excel loses styles and formating after writing certain rows. Even if Cellstyl...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC All
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-05-21 09:51 UTC by Nilesh
Modified: 2018-08-09 10:34 UTC (History)
1 user (show)



Attachments
The output .xls file generated by the above code. (125.00 KB, application/vnd.ms-excel)
2018-05-21 09:51 UTC, Nilesh
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Nilesh 2018-05-21 09:51:31 UTC
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.
Comment 1 Mark Murphy 2018-05-21 12:21:33 UTC
I have tested XLSX and it does not fail. I tested up to 50,000 styles with POI 3.14 and Java 8. This test does show the failure with HSSF though.