Excel Cell Style issue

Question

I am using below code to get date value from XLSX file. This is working absolutely fine for some XLSX files, but it is not giving exact date format which is in XLSX file. This issue is for some files.

For example, I have date like this 21/01/2016 (dd/mm/yyyy), but after reading, it gives date as 01/21/16(mm/dd/yy)

Is there any other way to get cellstyle?

Is it issue of XLSX file?

String dateFmt = cell.getCellStyle().getDataFormatString();
                
if (DateUtil.isCellDateFormatted(cell)) {
    double val = cell.getNumericCellValue();
    Date date = DateUtil.getJavaDate(val);

    String dateFmt = cell.getCellStyle().getDataFormatString();
                                    
    System.out.println("dateFmt "+dateFmt);

    value = new CellDateFormatter(dateFmt).format(date);
                                    
    System.out.println("Date "+value);
                                
}

Axel Richter · Accepted Answer · 2018-11-10T06:30:35.917

12

If the cell, which is containing the date, is formatted as the default date format (Short Date), then only the format id 0xE (14) is stored in the file. See https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html. The *.xlsx file contains only

<xf numFmtId="14" ... applyNumberFormat="1"/>

in styles.xml. There is no special formatCode saved for this numFmtId.

So how this will be displayed in Excel depends on the locale settings of the system.

For example with my German Windows system the numFmtId="14" will be displayed as TT.MM.JJJJ as is set in Region and Language settings:

In English Great Britain Windows systems this will be DD/MM/YYYY per default.

But if you change the setting for Short Date in the system settings, to JJJJ-MM-TT for example, then this format will also displayed in Excel with the numFmtId="14".

So to know how exactly Excel will display the date with the numFmtId="14", one needs to know the exact Windows system settings in Region and Language.

So also apache POI can't know how this should be displayed without knowing the locale settings of the system, since the file contains no infomation about this. So it will assume en-us locale. This leads to m/d/yy for date.

You could check if the format id 14 is used and if so define your own default date format.

   if (DateUtil.isCellDateFormatted(cell)) {
    Date date = cell.getDateCellValue();

    System.out.println(date);

    String dateFmt = "";

    if (cell.getCellStyle().getDataFormat() == 14) { //default short date without explicit formatting
     dateFmt = "dd/mm/yyyy"; //default date format for this
    } else { //other data formats with explicit formatting
     dateFmt = cell.getCellStyle().getDataFormatString();
    }

    System.out.println("dateFmt " + dateFmt);

    String value = new CellDateFormatter(dateFmt).format(date);

    System.out.println("Date " + value);

   }

To be clear: This all is only with numFmtId="14" cell.getCellStyle().getDataFormat() == 14. All other date formats will have explicit data format strings cell.getCellStyle().getDataFormatString() and so POI can display them exactly like in Excel.

See POI DataFormatter returns 2 digits year instead of 4 digits year for date cells for how to use DataFormatter to work around this issue.

edited Nov 10 '18 at 06:30

answered Jan 20 '16 at 14:03

Axel Richter

56,077
6
60
87

@AxelRitcher Thanks for this useful information. I was searching for it, but did not get any explaination. – Sonal Jan 21 '16 at 03:55
@AxelRitcher Your solution worked. Thank you. but one more question. is there any way to get existing format or reading date as it is from excel? – Sonal Jan 21 '16 at 04:04
"Is there any way to get existing format or reading date as it is from excel?" No, not if the date is formatted with `numFmtId="14"` and we do not know the `Region and Language` settings of the Windows system the Excel is running on. – Axel Richter Jan 21 '16 at 09:09
@AxelRitcher plz check this [http://stackoverflow.com/questions/34918775/xlsx-issue-cell-getcellstyle-getdataformat-changing-value-after-adding-or-r] – Sonal Jan 21 '16 at 09:12
@AxelRitcher: I understood completely. but i need to fetch date from excel as it is so i edited my code as per values returned by cell.getCellStyle().getDataFormat() for various date types – Sonal Jan 21 '16 at 09:18
1

All other date formats except 14 will have explicit data format strings `cell.getCellStyle().getDataFormatString()`. So no need reading `cell.getCellStyle().getDataFormat()` for others than 14. – Axel Richter Jan 21 '16 at 09:28
@AlexRitcher Thank you for complete answer and information. :) – Sonal Jan 21 '16 at 09:44
@AxelRichter I've got a feeling that there's actually a handful of "localised" formats. You can use [DateFormatConverter](https://poi.apache.org/apidocs/org/apache/poi/ss/util/DateFormatConverter.html) to do the localisation, or force excel into a locale with something like `"[$-0411]dd MMMM, yyyy;@"` – Gagravarr Jan 21 '16 at 11:17
@Gagravarr can you plz elaborate more on you comment? – Sonal Jan 21 '16 at 11:42
@Gagravarr: Yes there are "localised" formats. But thats another thing. The `numFmtId="14"` is precisely *not* localised but system dependent. – Axel Richter Jan 21 '16 at 13:05
The DataFormatter() may be created with some Locale however I tried several different locales but date format wasn't changed. Looks like it doesn't work with numFmtId="14" – Lazy Nov 09 '18 at 18:36
1

See https://stackoverflow.com/questions/53228302/poi-dataformatter-returns-2-digits-year-instead-of-4-digits-year-for-date-cells/53236531#53236531 for how to use `DataFormatter` to work around this issue. – Axel Richter Nov 10 '18 at 06:29

Excel Cell Style issue

1 Answers1

Linked

Related