Apache POI autoSizeColumn resizes to minimum width

Question

When working with values that are formulas, I am having difficulty getting the columns to autoresize properly.

I have "solved" this by making a hidden row that has maximum values as constant strings values, but that is far from elegant and often requires evaluating the formulas in each cell to get the largest strings that are generated. While this kind of works for such a small spreadsheet, it becomes very impractical for sheets that are ~16 columns x ~6000 rows.

The following code renders as in OpenOffice.

package com.shagie.poipoc;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;

public class SimpleBug {
    public static void main(String[] args) {
        try {

            Workbook wb = new HSSFWorkbook();
            FileOutputStream fileOut = new FileOutputStream("workbook.xls");
            Sheet sheet = wb.createSheet("new sheet");

            Row row = sheet.createRow(0);
            CellStyle style = wb.createCellStyle();
            style.setDataFormat(wb.createDataFormat().getFormat("[h]:mm"));
            Cell cell = row.createCell(0);
            cell.setCellValue(123.12);
            cell.setCellStyle(style);

            row.createCell(1).setCellFormula("A1");

            row.createCell(2)
              .setCellFormula("TRUNC(A1) & \"d \" & TRUNC(24 * MOD(A1,1))" +
              " & \"h \" & TRUNC(MOD(60 * 24 * MOD(A1,1),60)) & \"m\"");

            row.createCell(3).setCellValue("foo");

            for(int i = 0; i < 4; i++) {
                sheet.autoSizeColumn(i);
            }

            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

Related and tried:

Apache POI autoSizeColumn Resizes Incorrectly I've tried the font in the style. I got a list of all the fonts Java was aware of with

GraphicsEnvironment e = GraphicsEnvironment.getLocalGraphicsEnvironment();
for(String font:e.getAvailableFontFamilyNames()) {
    System.out.println(font);
}

I tried several of the fonts listed with that loop, and while OpenOffice changed the font, the columns where still sized incorrectly.

score 12 · Accepted Answer · edited Nov 09 '17 at 17:58

Assuming you're looking to have the correct size of the column based on the formula results, just insert the following line right before you do the autoSizeColumn, in this case before your for loop:

HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

The reason is autoSizeColumn sizes your cell based on the cached formula evaluated results and if the formula was never evaluated, it would not know what size to set for it.

Code:

...

row.createCell(3).setCellValue("foo");
HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

for (int i = 0; i < 4; i++) {
    sheet.autoSizeColumn(i);
}

...

Output (in OpenOffice)

Apache POI autoSizeColumn resizes to minimum width

1 Answers1