Calling the HSSFDataFormat.getFormat(String) method more than once with the same argument blows the formatting of the document so that, when opened in Excel, incorrect styles/fonts/formats are applied to the cells in the document. Fails with 1.8.0-dev-20020919 and 1.9.0-dev-20030131. Sample code: HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet("Test"); HSSFRow row = sheet.createRow(0); HSSFCell c1 = row.createCell((short)0); HSSFCell c2 = row.createCell((short)1); HSSFCell c3 = row.createCell((short)2); HSSFDataFormat f1 = book.createDataFormat(); short i1 = f1.getFormat("dd/mm/yyyy"); HSSFDataFormat f2 = book.createDataFormat(); short i2 = f2.getFormat("dd/mm/yyyy"); HSSFDataFormat f3 = book.createDataFormat(); short i3 = f3.getFormat("dd/mm/yyyy"); HSSFCellStyle s1 = book.createCellStyle(); s1.setDataFormat(i1); HSSFCellStyle s2 = book.createCellStyle(); s2.setDataFormat(i2); HSSFCellStyle s3 = book.createCellStyle(); s3.setDataFormat(i3); c1.setCellStyle(s1); c2.setCellStyle(s2); c3.setCellStyle(s3); c1.setCellValue(new Date(System.currentTimeMillis() + (1L * 24L * 60L * 60L * 1000L))); c2.setCellValue(new Date(System.currentTimeMillis() + (2L * 24L * 60L * 60L * 1000L))); c3.setCellValue(new Date(System.currentTimeMillis() + (4L * 24L * 60L * 60L * 1000L))); System.out.println("Cell 1: " + i1 + " - " + c1.getCellStyle ().getDataFormat()); System.out.println("Cell 2: " + i2 + " - " + c2.getCellStyle ().getDataFormat()); System.out.println("Cell 3: " + i3 + " - " + c3.getCellStyle ().getDataFormat()); try { book.write(new FileOutputStream("TestFormat.xls")); } catch (Exception e) { e.printStackTrace(); } Sample output: Cell 1: 164 - 164 Cell 2: 214 - 214 Cell 3: 214 - 214 In the created xls file, only the first date is formatted as a date. In more elaborate instances, this causes styles/fonts to be applied to the wrong cells.
Created attachment 4718 [details] Output XLS generated by sample code.
if this is still true...thats bad
oddly this file works in Excel v.X
Look at Bug 19638. This will solve this problem
But not properly. It is using the user model in the low level model. Plus, if you look, HSSFDataFormat already keeps a hash. Finally, most users go in through the HSSFWorkbook and would not have access to the Workbook object to call the validate on.
Commited fix that ensured the same HSSFDataFormat object is returned for each createDataFormat. This ensures that the hash table is kept up to date without the need for sharing.