Can't decide between Date and Number cells

[2knu]

How can I decide if its a Date cell or just a number cell?
I want to read different excel sheets, where I don't know which one is a date cell.

There is only a CellType Number and not a Date type. So I could try to read every number as Date, but than numbers will be read wrong for me, because they arent dates mostly.

So what I would expect is:

if (cell.getType() == CellType.Date)
{ cell.asDate(); }
else if(cell.getType() == CellType.Number)
{ cell.asNumber(); }
else 
{ cell.getText(); }

Would be nice when smt. like this will work :)

Originally posted by @2knu in #91 (comment)

[ochedru]

To achieve that, we would have to read the cell format (as replied in issue #91, date type does not exist) and apply complex checks (see DateUtil.isADateFormat()) to verify it is actually a date format.
This is a lot of work.

[sebastian-raubach]

I think it is an essential feature to know whether a cell actually contains a date or a number. I only just realised this was an issue and am now seriously considering using a different library, because without this ability, I cannot use fastexcel anymore.

[lpyfm]

dates are stored internally in Excel as double values, so I don't think adding something like CellType.Date is possible.

With Apache POI, there's also no Date type, but there's the method org.apache.poi.ss.usermodel.isCellDateFormatted, which tells you if a cell is formatted as date. It would be great if fastexcel provides something similar.

[PGWelch]

I think it is an essential feature to know whether a cell actually contains a date or a number. I only just realised this was an issue and am now seriously considering using a different library, because without this ability, I cannot use fastexcel anymore.

Agreed. I was going to use this library for reading excels as its more lightweight / less dependencies than POI, but given this issue I don't see how we could use it (which is a shame).

[perNyfelt]

If I define ReadingOptions as follows to get data format ids for each cell:
static final ReadingOptions OPTIONS = new ReadingOptions(true, true)
and pass it to the ReadableWorkbook constructor:
try (ReadableWorkbook workbook = new ReadableWorkbook(excelFile, OPTIONS))

I found that the datFormatId is not reliable for resolving e.g. 165 can be both custom date formats and customer money formats so you need to look at the content of the format string to determine if its a date or not. The POI DateUtil does just that and can easily be copied to a small utility class used to determine if its a date or not.

[MartinHaeusler]

Just wanted to add my voice to this one. While the FastExcel reader is a lot faster than Apache POI and a lot more memory efficient, not being able to differentiate user-facing cell formats and data types is a rather harsh limitation. In fact, FastExcel often just gives null when asking for the dataFormatString or the dataFormatId, while using Apache POI (ExcelNumberFormat.from(cell, null)) produces a non-null result for the same input cell. This would be the bare minimum requirement; we could take it from there and differentiate further, but we can't infer anything from a null value for the format string.

[MartinHaeusler]

I did some debugging and I've got some new insight:

Here's where the crucial information seems to get lost. The reader has already determined (correctly) that the formatId of the cell is 165 and the formatString is mm/dd/yy (all of which is correct!), but since the type of the cell is "s" (I presume this means "String"), it drops the information and calls into a parseString constructor which receives nothing but the cell address addr.

Why is the format information not being preserved here since the library already went through the trouble of calculating it? I'm not saying that this 100% solves the issue but it seems like a big part of it.