How to check a double value is of type Date in java 6

Question

I am trying to parse an excel sheet which have date in its cell.But when my code is parsing it is converting the date to a double i.e 42921,43098 , i have to parse back that double to a date so that i can get the proper output.Also i have to maintain the code compatible to java 6 only.I am using poi 3.6.So what i have tried is below ,

private String formatDoubleNumberToString(double number) {
    String formattedNumber = NumberToTextConverter.toText(number);


    // replace E notation format
    if (formattedNumber.contains("E")) {
        formattedNumber = String.format("%." + DECIMAL_PLACES + "f", number);
        formattedNumber = formattedNumber.indexOf(DECIMAL_SEPARATOR) < 0 ? formattedNumber
                : formattedNumber.replaceAll("0*$", "").replaceAll("\\" + DECIMAL_SEPARATOR + "$", "");
    }
    else if(DateUtil.isValidExcelDate(double number)){ //here i am trying to check the double is of type Date 

           // convert the date using simpleDateFormat

    }

    return formattedNumber;
}

I am trying to check the number is of type date using isValidExcelDate(double value) , but it is also considering other double value as date and this cheking is not working .Can anyone have any idea about this ? Please help.

To determine whether a number in an Excel cell is a date, it is necessary to get the format of that cell. If the cell is date formatted, then it is a date, if the cell is not date formatted, then not. There is not another possibility. Using `DataFormatter` as shown in https://stackoverflow.com/questions/48843618/how-to-check-a-number-in-a-string-contains-a-date-and-exponential-numbers-while/48889186#48889186 already is the best solution for this. — Axel Richter, Feb 23 '18 at 07:17
Variant of [How to check a number in a string contains a date and exponential numbers while parsing excel file using apache event model in java](https://stackoverflow.com/questions/48843618/how-to-check-a-number-in-a-string-contains-a-date-and-exponential-numbers-while). — Ole V.V., Feb 23 '18 at 09:33

Jim Garrison · Answer 1 · 2018-02-23T07:41:09.593

It is not "converting a date to double".

Dates in Excel are stored as a double value natively, being the number of days since 12/31/1899 (or as Excel calls it January 0, 1900). The time of day is encoded as the fractional part.

If you want to access the date as a string, read the string value of the cell, not the numeric value. But then you'll get the value formatted however the user specified, which could be quite a range of different formats.

//here i am trying to check the double is of type Date

All positive double values are "valid dates" in that Excel will convert them to a date based on the number of days since Excel's "epoch" (the starting point). Here are some examples:

  Number    Date
--------    ------------
       0    1/0/1900
     365    12/30/1900
   10000    5/18/1927
  100000    10/14/2173
 1000000    11/26/4637
10000000    ############

So 1 million days from 1900 is valid, but 10 million is out of range. Note also that Excel cannot deal with date values with negative values, i.e. before "January 0, 1900".

As to whether the cell you are looking at contains a date, you cannot really tell for sure other than to examine the cell's stored format. If the cell's format is "Date", then it's likely the user intended the value to be a date, but this is not 100% certain. To answer the question as asked, you cannot examine the double value and tell if it's a date.

The method in your code has only one double argument, so it cannot determine the original format of the source cell. You need access to both the double value and the original cell's format. One way to do this is to modify the method's signature to add a "format" parameter, and have the caller pass the cell format.

can you tell me how i will determine that in my else condition ? — Mandrek, Feb 23 '18 at 07:34
You cannot do that in a method that has only a `double` as a parameter. You must examine the actual cell format, which means you need access to the `Cell` object. Refer to the POI Javadoc for `Cell` to determine the format. — Jim Garrison, Feb 23 '18 at 07:38

score 0 · Answer 2 · edited Mar 12 '18 at 00:12

0

Date date = DateUtil.getJavaDate(Double.parseDouble(formattedValue));
String dateTime =  new SimpleDateFormat("dd.MM.yyyy HH:mm:ss").format(date);`

edited Mar 12 '18 at 00:12

Xcoder

1,433
3
17
37

answered Mar 11 '18 at 23:15

hamza kissi

1

How to check a double value is of type Date in java 6

2 Answers2