Apache POI autoSizeColumn() behaving weird

Question

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.

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

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.

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.

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.

EDIT: 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: 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

Answer 1

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).