Bug 16756

Summary: Multiple getFormat(String) calls blows document
Product: POI Reporter: Adrian Preston <APreston>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major    
Priority: P3    
Version: 2.0-pre3   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Output XLS generated by sample code.

Description Adrian Preston 2003-02-04 12:07:33 UTC
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.
Comment 1 Adrian Preston 2003-02-04 12:09:34 UTC
Created attachment 4718 [details]
Output XLS generated by sample code.
Comment 2 Andy Oliver 2003-07-24 15:36:30 UTC
if this is still true...thats bad
Comment 3 Andy Oliver 2003-07-24 15:37:09 UTC
oddly this file works in Excel v.X
Comment 4 Grigorios Merenidis 2003-07-24 17:36:04 UTC
Look at Bug 19638.
This will solve this problem
Comment 5 Shawn Laubach 2003-07-24 18:29:52 UTC
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.
Comment 6 Shawn Laubach 2003-07-24 19:26:36 UTC
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.