Apache POI autoSizeColumn() 行为怪异

问题描述

在标记为重复之前，请阅读全文.我已经看过所有相关/类似的问题，我想我在这里发生了一些奇怪的事情.

Please read full before marking as duplicate. I have seen all the related/similar questions already and I guess I have something weird happening here.

我使用 Apache POI 3.9 版生成 4 页工作簿.每次填写完表格后，我都会自动调整列的大小.代码如下:

I'm using Apache POI version 3.9 to generate a 4 sheet workbook. Every time I'm done filling the sheet, I auto-size the columns. Here's the code:

for (String alias : map.keySet()) {
    Sheet sheet = workbook.createSheet(getSheetNameForAlias(alias));
    List<Object[]> resultList = (List<Object[]>) map.get(alias);
    Collections.sort(resultList.subList(1, resultList.size()), getComparator(alias));
    int rownum = 0;
    for (Object[] array : resultList) {
        rownum = populateRow(cellStyles, sheet, rownum, array, false);
    }

    for(int j=0; j< resultList.get(0).length; j++){
        sheet.autoSizeColumn(j, false);
    }
}

populateRow 函数是:

the populateRow function is:

private int populateRow(CellStyle[] cellStyles, Sheet sheet, int rownum, Object[] dataArr, boolean doAutoSizing) {

    if (logger.isDebugEnabled()) {
        logger.debug("Populating row ... @{} : {} ", sheet.getSheetName(), rownum);
    }

    int cellnum;
    Row row = sheet.createRow(rownum++);
    cellnum = 0;
    if (ArrayUtils.isEmpty(dataArr)) {
        logger.error("No data found for row ... @{} : {} ", sheet.getSheetName(), rownum);
        return rownum;
    }
    for (Object obj : dataArr) {
        Cell cell = row.createCell(cellnum++);

        // get the type of the data inserted in the cell.
        // Accordingly set the cell value
        String data = String.valueOf(obj);

        if (StringUtils.isBlank(data) || "null".equals(data)) {
            cell.setCellValue(StringUtils.EMPTY);
        } else {
            if (NumberUtils.isNumber(data)) {

                Double dbVal = NumberUtils.createDouble(data);

                // Millions separator for Numeric fields
                if (dbVal == Math.ceil(dbVal)) {
                    cell.setCellStyle(cellStyles[0]);
                } else {
                    cell.setCellStyle(cellStyles[1]);
                }
                cell.setCellType(Cell.CELL_TYPE_NUMERIC); // for numeric , set cell type as numeric
                cell.setCellValue(dbVal);
            } else {
                if(obj instanceof java.sql.Timestamp || obj instanceof java.sql.Date){
                  cell.setCellStyle(cellStyles[2]);
             }
                cell.setCellValue(data);
            }
            if (doAutoSizing) {
                sheet.autoSizeColumn(cellnum - 1);
            }
            data = null;
        }
        obj = null;
    }
    return rownum;
}

奇怪的是，四张纸中有一张的列被错误地调整了大小.

The weird part is, columns of one out of the four sheets are getting resized incorrectly.

我尝试在脑海中用不同的方法重写 auto size 逻辑，并发现如果我尝试 sheet.getRow，我会得到这种错误行为的工作表(0)，它给出 null ，它指定该行没有数据，但生成的 excel 不适用，所有四张表都有适当的数据.并且在任何情况下，第一行始终是标题，因此即使其下方没有数据，它也不能为空.

I tried re-writing the auto size logic with different approaches in my mind and discovered that the sheet, for which I'm getting this incorrect behaviour, if I try sheet.getRow(0), it gives out null which specifies that there is no data for that row but the excel being generated does not hold good with that, all four sheets have appropriate data. And in every case, the first row is always the headings so It couldn't be null even if there is no data beneath it.

我不知道我错过了什么，但我尝试发现这不是 JVM 的字体问题.

I don't know what I'm missing but I tried and found that it is not a Font issue with the JVM.

如果您需要其他信息，请告诉我.

Please let me know in case you need some other info.

观察到自动调整大小对于超过 100 行的工作表工作不正确.我不知道如何将它与某些逻辑联系起来.这真的很奇怪.

Observed that auto-sizing is working incorrectly for the sheet which has more than 100 rows. I don't know how to relate it to some logic. This is real WEIRD.

EDIT 2:我在寻找其他东西时找到了 Axel 给出的原因的官方参考.这足以解决我的困惑.阅读以下段落链接

EDIT 2: I found the official reference for the reason given by Axel while looking for something else. This does suffice my confusion. Read the para on the followinglink

推荐答案

既然你说它最多可以工作 100 行，那么看起来你正在使用 SXSSFWorkbook，而不是 XSSFWorkbook.那么这种行为是可以预料的，因为只有固定数量的行保存在内存中，并且只会考虑这些行.因此，如果我是对的，您将不得不改用 XSSFWorkbook(前提是在处理大量行时不会导致 OOM).

Since you say it works up to 100 rows, it looks like you are using SXSSFWorkbook, not XSSFWorkbook. Then that behaviour has to be expected, since only a fixed number of rows are kept in memory, and only these rows will be considered. So, if I am right, you will have to switch to using XSSFWorkbook (provided this doesn't lead to OOMs when dealing with a large number of rows).

这篇关于Apache POI autoSizeColumn() 行为怪异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！