Created attachment 31498 [details] Snippet * 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
Created attachment 31499 [details] output xls file
Created attachment 31500 [details] Generated width
Created attachment 31501 [details] Width after doubleclicking the column header
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 http://poi.apache.org/spreadsheet/quick-guide.html#Autofit
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.
(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.
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()); cell.setCellValue(GMT_CALENDAR); 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.