Apache POI Excel 单元格格式在工作表中超出 32748 个单元格时不起作用 [英] Apache POI Excel cell formatting is not working beyond 32748 cell in a sheet

问题描述

我有一个要求，我需要为给定期间的所有交易生成报告，并且我需要相应地应用单元格格式.在这种情况下，单元格格式在工作表中创建 32748 个单元格后尤其不适用于日期.这似乎是 API 中的错误，如果有人已经遇到此问题并找到任何修复程序，请提供一些输入.

I have a requirement where I need to generate a report for all transaction for a given period, and I need to apply the cell format accordingly. In this case cell formatting is not working especially for Date after creating 32748 cells in the sheet. This seems to be bug in the API, please provide some inputs if anybody already faced this issue and found any fix.

作为参考，这里是示例代码:

For reference, here is the sample code :

public class TestFormat {

public static void main(String args[]){
    try {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Excel Sheet");

    HSSFDataFormat format = wb.createDataFormat();


    for(int i = 1; i<65535;i++ ) {

            HSSFRow row = sheet.createRow(i);
            HSSFCell cell = row.createCell(1);
            HSSFCellStyle style = wb.createCellStyle();
            cell.setCellValue((Date) new Date());
            style.setDataFormat(format.getFormat("MM/dd/yyyy HH:mm:ss"));
            cell.setCellStyle(style);

    }
    FileOutputStream fileOut;

        fileOut = new FileOutputStream("c:\\test\\excelFile.xls");
         wb.write(fileOut);
            fileOut.close();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    System.out.println("Data is saved in excel file.");
}

}

推荐答案

您正在循环内部创建单元格样式.不要！

You're creating your cell style inside the loop. Don't!

Excel 对文件格式中允许的单元格样式数量有限制.您需要做的是将单元格样式的创建/设置移到循环之外，因此它只创建一次，应该没问题

Excel has a limit on the number of cell styles that it allows in the file format. What you need to do is to move the creation/setup of your cell style outside of your loop, so it's only created once, and it should be fine

您的代码的核心部分将如下所示:

The core part of your code would then look something like:

HSSFDataFormat format = wb.createDataFormat();
HSSFCellStyle style = wb.createCellStyle();
cell.setCellValue((Date) new Date());
style.setDataFormat(format.getFormat("MM/dd/yyyy HH:mm:ss"));

for(int i = 1; i<65535;i++ ) {
        HSSFRow row = sheet.createRow(i);
        HSSFCell cell = row.createCell(1);
        cell.setCellStyle(style);
}

这篇关于Apache POI Excel 单元格格式在工作表中超出 32748 个单元格时不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！