HSSFSheet.autoSizeColumn calculates the size of a cell (which contains a formula) depending on the size of the formula, not on the size of the result of the formula. To reproduce this error, see listing below. Cell 0 and Cell 1 should have the same column size, because they both evaluate to '1'. In the case below, Cell 1 is much wider than Cell 0. import org.apache.poi.hssf.usermodel.*; import java.io.*; public class AutosizeError { public static void main(String args[]) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Autosize error"); HSSFRow row = sheet.createRow(0); HSSFCell cell0 = row.createCell(0); HSSFCell cell1 = row.createCell(1); cell0.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell0.setCellValue(1); sheet.autoSizeColumn(0); cell1.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell1.setCellFormula("A1+2*A1-2*A1"); sheet.autoSizeColumn(1); try { FileOutputStream out = new FileOutputStream("Autosize_Error.xls"); workbook.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } } // main }
Setting the formula doesn't re-calculate the value. You need to use the formula evaluator to do that, before you size
It is actually a bug. Column sizing relies on DataFormatter#formatCell(Cell cell) which returns formula string for formula cells. It should format the cached formula result instead. I also see that auto-sizing logic is duplicated between HSSF and XSSF, I'm going to consolidate it in a common class. Something to work for me. The fix is coming soon. Yegor
Should be fixed in r1033005. Sheet.autoSizeColumn does not evaluate formula cells, instead it sizes the formula cells based on their cached formula results. If a formula was never evaluated (like in your case), the cached result is undefined the result of autoSizeColumn can be 'off'. This means that it is a good idea to evaluate the entire workbook before auto-sizing. The simplest way to evaluate all cells is as follows: HSSFFormulaEvaluator.evaluateAllFormulaCells(workbbok); Just insert this line before calling Sheet.autoSizeColumn. Yegor
Created attachment 26277 [details] Screencopy of AutosizeError
I inserted the line into my example code. Now it looks like: cell1.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell1.setCellFormula("A1+2*A1-2*A1"); HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook); sheet.autoSizeColumn(1); This does not resolve the problem. Column #1 is still much wider then column #0 and it scales with the size of the formula (see attachment). Does it work correctly in your environment? Am I missing something else? Gerd
Make sure you are using the latest build from trunk. The version where the problem is fixed in 3.8-dev, not 3.7. On my environment both columns are equally sized. BTW, you don't need to call cell1.setCellType(HSSFCell.CELL_TYPE_NUMERIC) before setting formula. A call of HSSFCell#setCellFormula automatically changes the cell type to HSSFCell.CELL_TYPE_FORMULA. Yegor
*** Bug 50415 has been marked as a duplicate of this bug. ***