Bug 52421

Summary: autoSizeColumn for large numbers
Product: POI Reporter: Rob van Harrevelt <R.van.Harrevelt>
Component: POI OverallAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: Program that shows that autoSizeColumn produces too wide columns for large numbers.

Description Rob van Harrevelt 2012-01-04 15:33:33 UTC
Created attachment 28119 [details]
Program that shows that autoSizeColumn produces too wide columns for large numbers.


The method autoSizeColumn of class org.apache.poi.ss.usermodel.Sheet produces columns that are far too wide when the column contains large numbers such as 1e32. 

Attached is a simple Java program that demonstrates the problem for the HSSF format.  

I have tried poi-3.7 and poi-3.8-beta5, with identical results for xls files. 
For poi-3.8-beta5 the problem also occurs for the XSSF format.


Rob van Harrevelt
Comment 1 Yegor Kozlov 2012-01-14 17:46:15 UTC
This is expected behavior.

POI delegates formatting of numbers to java.text.DecimalFormat and this class does not support automatic switching to scientific notation. 

Explicitly set a exponential format and you will be good:

         Workbook wb = new HSSFWorkbook();

         //  create a cell style that formats numbers in scientific notation
         CellStyle style = workbook.createCellStyle();
         int idx = workbook.getCreationHelper().createDataFormat().getFormat("##0.0E+0");

         double[] values = {1,  1e12, 1e32, 1e64};

         Sheet sheet = wb.createSheet();
         Row row = sheet.createRow(0);
         for (int c = 0; c < values.length; c++) {
             Cell cell = row.createCell(c);
             System.out.println("column width /256 = "
                     + sheet.getColumnWidth(c) / 256);