Bug 50211

Summary: autoSizeColumn calculates wrong column size of formula cells
Product: POI Reporter: gerd.gotthard
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: clive
Priority: P2    
Version: 3.7-FINAL   
Target Milestone: ---   
Hardware: Sun   
OS: Solaris   
Attachments: Screencopy of AutosizeError

Description gerd.gotthard 2010-11-04 06:49:05 UTC
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
}
Comment 1 Nick Burch 2010-11-05 14:14:40 UTC
Setting the formula doesn't re-calculate the value. You need to use the formula evaluator to do that, before you size
Comment 2 Yegor Kozlov 2010-11-05 17:18:04 UTC
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
Comment 3 Yegor Kozlov 2010-11-09 10:17:23 UTC
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
Comment 4 gerd.gotthard 2010-11-10 04:14:20 UTC
Created attachment 26277 [details]
Screencopy of AutosizeError
Comment 5 gerd.gotthard 2010-11-10 04:16:33 UTC
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
Comment 6 Yegor Kozlov 2010-11-10 04:57:49 UTC
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
Comment 7 Yegor Kozlov 2010-12-06 02:35:18 UTC
*** Bug 50415 has been marked as a duplicate of this bug. ***