Bug 58607 - DateTime formatting
Summary: DateTime formatting
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.13-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-11-13 12:58 UTC by Usman
Modified: 2017-06-16 11:03 UTC (History)
0 users



Attachments
Normal and Wrong output (10.21 KB, image/png)
2015-11-13 12:58 UTC, Usman
Details
Normal and Wrong output (10.21 KB, image/png)
2015-11-13 12:59 UTC, Usman
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Usman 2015-11-13 12:58:06 UTC
Created attachment 33273 [details]
Normal and Wrong output

Hi !

This code formats dateTime cell perfectly in Office 2010:

style.setDataFormat(wb.createDataFormat().getFormat("dd/MM/yyyy HH:mm:ss"));
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(style);
cell.setCellValue((java.util.Date)data);

but in Office 2013 i get float numbers.
Whats wrong ?

with best regards,
Usman
Comment 1 Usman 2015-11-13 12:59:28 UTC
Created attachment 33274 [details]
Normal and Wrong output

Output
Comment 2 Dominik Stadler 2015-12-04 15:57:45 UTC
Are these the same machines with the two versions of Excel or two different ones? 

Which locales are they running with? Sometimes Excel and/or POI provide date formats depending on your current locale so it might be that one machine has a locale where Excel supports this locale and the other runs a different one.
Comment 3 Usman 2015-12-06 15:31:07 UTC
Hi Dominik !

Thank you for reply

(In reply to Dominik Stadler from comment #2)
> Are these the same machines with the two versions of Excel or two different
> ones? 

Two different machines. On the developer workstation - MSOffice 2010, client - 2013

> 
> Which locales are they running with? Sometimes Excel and/or POI provide date
> formats depending on your current locale so it might be that one machine has
> a locale where Excel supports this locale and the other runs a different one.

Locales maybe different (RUS/KAZ)... 
But as client says, the problem was only in the 2013 version. 
And he also checked this *.xls file on another machine with 2010 - it's ok
O_o

With best regards, Usman
Comment 4 Dominik Stadler 2016-03-27 09:45:28 UTC
I tried with my version of Outlook which is something like Office 365 Plus, 15.0.4797.1003, there it worked without a problem.

I think we need some more information from you in order to find out more, please attach the sample file that is showing this problem.

Also please try to upgrade to the latest version 3.14 to make sure this is not something that got fixed in the meantime.
Comment 5 Dominik Stadler 2016-03-29 18:45:23 UTC
Testcase which I used with manual verification in Excel:

@Test
    public void test58607() throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle style = wb.createCellStyle();

        style.setDataFormat(wb.createDataFormat().getFormat("dd/MM/yyyy HH:mm:ss"));

        final Cell cell = wb.createSheet("test").createRow(0).createCell(0);
        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(style);
        cell.setCellValue(new Date());

        OutputStream out = new FileOutputStream("C:\\temp\\58607.xls");
        try {
            wb.write(out);
        } finally {
            out.close();
        }
    }
Comment 6 Dominik Stadler 2017-06-16 11:03:52 UTC
Could not reproduce this and no response for more than a year.