Bug 56377 - Autosize columns with custom date format (yyyy-MM-dd HH:mm:ss GMT) gets wrong width
Summary: Autosize columns with custom date format (yyyy-MM-dd HH:mm:ss GMT) gets wrong...
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.10-FINAL
Hardware: PC All
: P2 normal with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2014-04-09 14:15 UTC by spuyo84
Modified: 2014-06-26 15:22 UTC (History)
1 user (show)

Snippet (1.96 KB, text/plain)
2014-04-09 14:15 UTC, spuyo84
output xls file (8.62 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-04-09 14:16 UTC, spuyo84
Generated width (38.30 KB, image/png)
2014-04-09 14:18 UTC, spuyo84
Width after doubleclicking the column header (37.32 KB, image/png)
2014-04-09 14:20 UTC, spuyo84

Note You need to log in before you can comment on or make changes to this bug.
Description spuyo84 2014-04-09 14:15:39 UTC
Created attachment 31498 [details]

* Quick overview:

When you try to add a date into a cell, and you use a date with customized format. 
For instance: 

"yyyy-MM-dd HH:mm:ss \"GMT\"" (this adds always a fixed timezone value to the date) 

It means that you want to keep the cell as a date cell (no string cell), but only display in it the extra "GMT". When you want to autosize this column, the output width is shorter than the content, and then '#' characters are displayed. (see the attached xls file)

* How to reproduce the issue: 

To reproduce it just execute the "generate()" method of the attached code snippet, and create the xls file with the byte array returned value.

* Note: I reproduced the issue with org.apache.poi.xssf.usermodel.XSSFSheet and org.apache.poi.ss.usermodel.Sheet
Comment 1 spuyo84 2014-04-09 14:16:34 UTC
Created attachment 31499 [details]
output xls file
Comment 2 spuyo84 2014-04-09 14:18:35 UTC
Created attachment 31500 [details]
Generated width
Comment 3 spuyo84 2014-04-09 14:20:32 UTC
Created attachment 31501 [details]
Width after doubleclicking the column header
Comment 4 Nick Burch 2014-06-12 09:27:47 UTC
I notice that your problematic cell is using the Calibri font

Have you made sure that you have the Calibri font installed on your machine, *AND* available to Java? 

POI uses the Java font code to work out how big each letter needs to be, when calculating the sizing, and that requires the right fonts to be present to get a precise value. Without the right font, Java picks a "nearby" one which doesn't always size out the same. See the bottom of the warning at
Comment 5 Holger 2014-06-12 10:22:51 UTC
I'm using the Calibri font in a similar sample. I've checked that the font is available to Java.

However, columns with a date format or with an accounting format are not sufficiently resized using Sheet.autoSizeColumn().

I also noticed the following: If I manually change the cell format to "General" in the resulting xls, the column width is sufficient. Hence I would guess that the autoSizeColumn() method does only consider the unformatted value of a cell.
Comment 6 Nick Burch 2014-06-26 14:53:22 UTC
(In reply to Holger from comment #5)
> Hence I
> would guess that the autoSizeColumn() method does only consider the
> unformatted value of a cell.

That's incorrect - it very much does consider the formatted value of the cell. See line 156 of SheetUtil for it in action: http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/util/SheetUtil.java

If you call DataFormatter.formatCell directly, do you get the correct string value back? If there was a problem with that, it might explain it.
Comment 7 spuyo84 2014-06-26 15:22:57 UTC
I have debugged and I found the following:

This is the stack trace after calling the autoSizeColumn method:

1. org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(int, boolean): line 386 --> calls the getColumnWidth
2. org.apache.poi.ss.util.SheetUtil.getColumnWidth(Sheet, int, boolean): line 203 --> Creates a default DataFormatter and then gets the cell.
3. org.apache.poi.xssf.usermodel.XSSFRow.getCell(int, MissingCellPolicy): line 204 --> Gets a cell with the following string value 41816.59016799769

This value is generated here: 

3.1: when we set the date of the date like this.		
GMT_CALENDAR.setTime(new Date());
3.2: org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(Calendar): line 630 --> converts to numeric excel date
3.3: org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(double): line 238 --> gets the string value of this number

4. org.apache.poi.ss.util.SheetUtil.getCellWidth(Cell, int, DataFormatter, boolean): line 156 --> For the CellType numeric gets the string value 41816.59016799769
5. org.apache.poi.ss.util.SheetUtil.getCellWidth(Cell, int, DataFormatter, boolean): line 164 --> Add "0" to this value
6. org.apache.poi.ss.util.SheetUtil.getCellWidth(Cell, int, DataFormatter, boolean): line 183 --> Calculates the width value: (layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention(). 
((96.7158203125 / 1)/5) + 0 = 19.34

If I put this numeric value (41816.590167997690) in excel and we make it fit with the content and then I check the column width, I get this 19.34 value.
The expected width should be 22px, for the formatted data: '2014-06-26 14:41:12 GMT'.

So it's not using the same format I set to the date, It's using a numeric default format for date and then gets the width for that.