Apache的POI autoSizeColumn（）行为怪异 [英] Apache POI autoSizeColumn() behaving weird

问题描述

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

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片工作簿。每次我做灌片，我自动调整大小的列。这里的code：

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.

我试图重新写自动尺寸在我的脑海里不同的方法的逻辑和发现表，为此我得到这个不正确的行为，如果我尝试 sheet.getRow（0），它给出了空其中明确说明存在该行，但对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.

编辑2：我发现在寻找别的东西由阿克塞尔给出的理由的官方参考。这不就够了我的困惑。阅读下面的对位
链接

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 following link

推荐答案

既然你说这工作到100行，它看起来像你正在使用的 SXSSFWorkbook ，不是的 XSSFWorkbook 。然后该行为已被预期的，因为只有行的固定数目被保存在存储器中，和只有这些行将被考虑。所以，如果我是对的，你将不得不切换到使用 XSSFWorkbook （只要这不导致与大量的行打交道时奥姆斯）。

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（）行为怪异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！