Bug 47206 - XSSFSheet autoSizeColumn not considering all rows
Summary: XSSFSheet autoSizeColumn not considering all rows
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.5-dev
Hardware: PC Windows Vista
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2009-05-16 08:23 UTC by ROb
Modified: 2014-08-21 15:57 UTC (History)
0 users

Zip file with 3 attachments (9.76 KB, application/zip)
2009-05-16 08:23 UTC, ROb
hssf screenshot (49.60 KB, image/png)
2009-05-17 09:44 UTC, Yegor Kozlov
xssf screenshot (47.86 KB, image/png)
2009-05-17 09:45 UTC, Yegor Kozlov
Illustrates the incorrect behaviour (3.13 KB, text/java)
2009-05-19 10:47 UTC, ROb

Note You need to log in before you can comment on or make changes to this bug.
Description ROb 2009-05-16 08:23:59 UTC
Created attachment 23670 [details]
Zip file with 3 attachments

Hi there, I am experiencing some strange behaviour with the method XSFFSheet.autoSizeColumn(i)

I have taken a spreadsheet with two rows. I have made two cells on the 2nd row contain more text than the cell directly above that, on row one.

I saved the spreadsheet, one as Office 2003 format: 2003_Report.xls , and the other as 2007 format: 2007_Report.xlsx

I attach a small Java program (Main.java attached). The intention is to adjust the column widths on both sheets, so that all columns are correctly adjusted to show all text as visible.

The application adjusts the 2003 (.xls) file just fine, but the 2007 (.xlsx) file does not show all the text in the 2nd row.

Note this is a really simple example that I am using to illustrate a problem I'm experiencing with poi-dev3.5. I am dealing with spreadsheets with 100's of rows. Dealing with both xlsx and xls files, and the implementation of XSFFSheet.autoSizeColumn(i) does not deal with column widths very well.

- autoSizeColumn.zip
- - 2003_Report.xls
- - 2007_Report.xlsx
- - Main.java

Comment 1 Yegor Kozlov 2009-05-17 09:44:55 UTC
Created attachment 23672 [details]
hssf screenshot
Comment 2 Yegor Kozlov 2009-05-17 09:45:53 UTC
Created attachment 23673 [details]
xssf screenshot
Comment 3 Yegor Kozlov 2009-05-17 09:51:30 UTC
I can't reproduce the problem. Both HSSF and XSSF outputs look OK to me. See attached screenshots. I'm using the latest trunk. 

The Bug report says that the platform is "Windows Vista". Did you run the Java code on Windows or evaluated results only? If you run Java on *nix, make sure that the fonts are available. You may need to copy them into $JAVA_HOME/jre/lib/fonts

Comment 4 ROb 2009-05-18 08:24:22 UTC

I am happy to give you additional information, as I have pinpointed why the problem occurs. It is to do with the fact that I have applied a  XSSFCellStyle to each cell before calling the autoSizeColumn.

I am creating a spreadsheet with multiple tabs (sheets). I am creating each sheet as such:
String[] columns = new String[]{"Column_1","Column_2", "Column_3"};
createSheet("Part Number Comparisons", columns);


 public void createSheet(String sheetName, String[] columns){
         XSSFCellStyle style = wb.createCellStyle();
        style.setFillForegroundColor(new XSSFColor(Color.lightGray));

         XSSFSheet sheet = wb.createSheet(sheetName);
         sheet.createFreezePane( 0, 1, 0, 1 );
         XSSFRow row = sheet.createRow((short) 0);

         for(int i=0;i<columns.length;i++){
             XSSFCell cell = row.createCell(i);

         XSSFRow row2 = sheet.createRow((short) 1);
         XSSFCellStyle style2 = wb.createCellStyle();
         style2.setFillForegroundColor(new XSSFColor(Color.WHITE));


FINALLY, the resizeXLSXColumns(String FILENAME) method is called AFTER the above has taken place.

Simply, the autoSizeColumns() for XSSFSheet does not deal with XSSFCellSytle very well, where as HSSFSheet seems to handle HSSFCellStyle Ok.

Hope that helps.

Rob Stewart
Comment 5 Yegor Kozlov 2009-05-18 11:32:06 UTC
Still can't get it. I combined createSheet() with Main.java and the output looks OK. All columns are resized correctly. 

Can you post an isolated example which demonstrates the problem? Perhaps, a version of Main.java including additional code. 

Comment 6 ROb 2009-05-19 10:47:10 UTC
Created attachment 23693 [details]
Illustrates the incorrect behaviour
Comment 7 ROb 2009-05-19 10:47:25 UTC
Hi there,

I'm adding an attachment, this time only the Main.java as this is all that is necessary.

Apologies for the red herrings in the previous email.

The Java code attached illustrates exactly the problem I'm having.

The code does:
1. Make a new XSSF workbook
2. Adds data to it
3. Tries to resize the columns, but fails

Thoughts ?


Comment 8 Yegor Kozlov 2009-05-23 01:22:50 UTC
Fixed in r777834. 
Excel JDBC Driver is the culprit. It inserts strings in a funny way and POI couldn't read them. 

Comment 9 Shubham 2014-08-21 15:57:51 UTC
Hi Yegor ,

I am using 3.10 beta , but still i am having this issue. Please guide us how we can get it worked.

Previously i was using 3.7 HSSF, which was not having facility to pass number of rows.

// keep some rows in
memory, exceeding rows will be flushed to disk.

But when i changed it to SXSSF , speed was superb to write excel.

With ur previous comment , i am understanding that autoresizecolumn is not possible in this way.  So i m looking for a way to use this with nice column width.