0

I want to load Excel files with exactly the same formatting as displayed in Excel. However I am struggling to get the Date correctly 100% of time.

I have tried to use DataFormatter mentioned here: how to read exact cell content of excel file in apache POI but with no luck.

My code is:

switch (cell.getCellType()) {
(...)
   case NUMERIC:
      if (DateUtil.isCellDateFormatted(cell)) {
         DataFormatter df = new DataFormatter()                                 
         data.get(i).add(df.formatCellValue(cell));
      } else {
         data.get(i).add(String.valueOf(cell));
      }
      break;
(...)

Examples:

  1. In Excel date is formatted using the pattern dd.MM.yyyy (28.12.2018), but application reads it as MM/dd/yy (12/28/18) which is wrong;

  2. In Excel date is formatted using the pattern yyyy-MM-dd (2018-12-28) and application reads it as yyyy-MM-dd (2019-12-28) which is fine.

How to make it read the Date as it appears in Excel no matter which formatting is chosen?

Michal Jeruzal
  • 89
  • 1
  • 1
  • 6
  • 1
    Possible duplicate of [How to read Excel cell having Date with Apache POI?](https://stackoverflow.com/questions/3148535/how-to-read-excel-cell-having-date-with-apache-poi) – XtremeBaumer May 29 '19 at 14:28
  • 2
    See https://stackoverflow.com/questions/53228302/poi-dataformatter-returns-2-digits-year-instead-of-4-digits-year-for-date-cells/53236531#53236531 – Axel Richter May 29 '19 at 14:30
  • @XtremeBaumer I believe it is not. I can read the date, but it has wrong formatting. – Michal Jeruzal May 29 '19 at 14:30
  • @AxelRichter it may be the solution for my problem, but I have to look into this. Thanks! – Michal Jeruzal May 29 '19 at 14:42
  • actually,[not the accepted answer](https://stackoverflow.com/a/42392277/7109162) is what you are looking for – XtremeBaumer May 29 '19 at 14:47
  • @XtremeBaumer if you look at my code again it looks identical as the one linked by you, but still, it does not work the way I expect it to work. I think that Alex Richter may be right about locale settings. – Michal Jeruzal May 29 '19 at 14:55
  • What locale / date format is the machine running excel set to? And if you change that to US-English, does it then render the same in Excel as in Apache POI? – Gagravarr May 29 '19 at 15:27
  • 1
    https://poi.apache.org/apidocs/dev/org/apache/poi/util/LocaleUtil.html#setUserLocale-java.util.Locale- -- not sure it will help but it might be worth playing a few locale settings – PJ Fanning May 29 '19 at 18:19

0 Answers0