0

I want to get the date value from all the cells of a xlsx file using APACHE POI library.

Setting: The file contains strings,small integers like 12-15 (working hours) and dates in the dd/mm/yyyy format. I want to get all the dates from the current file. 

switch (cell.getCellType()) {
    case STRING:
        break;
    case NUMERIC:
        return cell.getLocalDateTimeValue();
    case BOOLEAN:
        break;
    default:
        break;
    }

Iterating through the cells and using the above code does not work because it converts the numeric 5 to 1/12/1900(not the exact numbers). Plus i don't want to hardcode something like:

if(date.isAfter(someDate)) {accept it }

The only solution i found was:

  1. Get the numeric value
  2. Convert it to string
  3. Use regex to match a specified format
  4. If it is accepted convert it back to date

I did not implement it yet. It seems like it is going to work but i think it's a bit messy.

Note:I am at junior level so i do not know if this is a good practice to convert it back and forth.

This answer throws me the error shown in the image as i iterate though cellsenter image description here

Olaf Kock
  • 46,930
  • 8
  • 59
  • 90
Kostas Thanasis
  • 368
  • 4
  • 11
  • I don't see why matching to a specified format is going to do anything better than checking if the date is after some date. Isn't `1/12/1900` going to match your format? Since Excel doesn't have a date/time datatype, you just can't tell them apart, and need to resort to heuristics. – RealSkeptic Feb 10 '20 at 17:38
  • I thought of a senario where i might find a large integer like 100000 or something and when i read it as a date it "lands" on something like 12/06/2021 and break my code. I know this is next to impossible to happen but it is a case. Plus when someone reads my code it will be clear as what i am doing than checking if the read date is after some arbitrary date. – Kostas Thanasis Feb 10 '20 at 17:43
  • Please re open the question. The link that you provided for me was not an answer. I replaced all my checks and just add the check mentioned while iterating through cells.It throws me this error: Cannot get a NUMERIC value from a STRING cell. – Kostas Thanasis Feb 10 '20 at 18:09
  • 1
    You need to check if it's numeric before you check it is date. The only way for you to tell if a number is a date or a number is to look at its style. But you have to make sure it's a number first. – RealSkeptic Feb 10 '20 at 18:12

0 Answers0