excel - 使用 apache poi 库写入某些行后 Excel 会丢失样式和格式

Question

我正在尝试创建一个 excel 文件（.xls），同时为总共 9000 行编写一个 excel，在行号 3000 之后，所有样式和格式都丢失了。在使用workbook.getNumCellStyles（）编写完整的工作簿后，我尝试获取单元格样式的数量，它返回2091。很明显，单元格样式的数量没有超过（即小于4000）仍然样式丢失。请帮助我理解为什么会发生这种情况？

我创建了一个示例代码，即使我正在创建 75 个 CellStyle 对象，它也会在下面失败。

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

}

标签： excelapache-poiexcel-2010excel-2007