Bug 52421 - autoSizeColumn for large numbers
Summary: autoSizeColumn for large numbers
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: unspecified
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-01-04 15:33 UTC by Rob van Harrevelt
Modified: 2012-01-14 17:46 UTC (History)
0 users



Attachments
Program that shows that autoSizeColumn produces too wide columns for large numbers. (950 bytes, application/octet-stream)
2012-01-04 15:33 UTC, Rob van Harrevelt
Details

Note You need to log in before you can comment on or make changes to this bug.
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.

Hello,

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.

Thanks,

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");
         style.setDataFormat((short)idx);

         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);
             cell.setCellValue(values[c]);
             cell.setCellStyle(style);
             sheet.autoSizeColumn(c);
             System.out.println("column width /256 = "
                     + sheet.getColumnWidth(c) / 256);
         }

Yegor