Steve,
The unit1.pas in your example looks empty, could it be the "zip before saving in rad studio" effect? (effect that happened more than once to me too).
But anyway, even if I can't see the exact code, this is probably because Excel doesn't have any "DateTime" data type. Excel has numbers, strings, booleans, errors and formulas, but no datetimes.
A date in Excel is just a number formatted as "date". If you right click the xlsx file you sent me in the cell A2, select format and choose "general" instead of "Date", you'll see that it actually holds the number 40087
As a side note, this number 40087 should be the number of days since january 1, 1900 (same as a TDateTime in delphi), but things are more complex than that because:
1)Excel considers 1900 was a leap year, and it wasn't. This was done for compatibility with lotus 123, which probably considered a leap year so the algorithm was simpler and could fit in those 512kb machines. This makes that dates before feb 29, 1900 are different numbers in delphi TDateTime and Excel. If you want to read more about this, please take a look at:
2)Excel has 2 "date modes" 1900 and 1904 dates. The 1904 dates were introduced for the macs, but you can save any file in either 1900 or 1904 by changing the Excel options, in Windows or mac. So you can't really trust that the number is the days from 1/1/1900, they could be the days from 1/1/1904. (FlexCel has full support for 1904 dates btw, by using Options1904 property, and all calculations take the 1904 mode into account)
So well, what we have until now is a number, which might or not might mean the number of days since 1900, with the fractional part being the number of hours in the day. (so 40087.5 would mean 1/10/2009 at 12:00, with time going from 0 to 24).
As the conversion isn't clear, in order to read a date you need to do 2 things:
1)See if the cell is formatted as a date , or a time or as a datetime. If it isn't, it will show as a number.
2)Convert that numeric value into the real date. As you need to account for 1900 or 1904 dates, my advise would be to always use FlxConvert.Convert methods.
There is an example of this in the "Reading Files" demo:
case v.ValueType of
TCellValueType.Empty: exit('empty');
TCellValueType.Boolean: exit('a boolean: ' + BoolToStr(v.AsBoolean, true));
TCellValueType.Error: exit('an error: ' + TFormulaMessages.ErrString(v.AsError));
TCellValueType.Number:
begin //Remember, dates are doubles with date format. Also, all numbers are returned as doubles, even if they are integers.
CellColor := TUIColor.Empty;
CellValue := TFlxNumberFormat.FormatValue(v, xls.GetCellVisibleFormatDef(Row, Col).Format, CellColor, xls, HasDate, HasTime).ToString;
if HasDate or HasTime then
begin
Result := 'a DateTime value: ' + DateTimeToStr(v.ToDateTime(xls.OptionsDates1904)) + #10 + 'The value is displayed as: ' + CellValue;
end
else
begin
Result := 'a double: ' + FloatToStr(v.AsNumber) + #10 + 'The value is displayed as: ' + CellValue + #10;
end;
exit;
end;
Note that the next line comments about the "DateTime" CellValueType:
TCellValueType.DateTime: //FlexCel won't currently return DateTime values, as dates are numbers.
Now, if you are wondering why a TCellValueType.DateTime exists at all (When Excel won't return dates) it is because it was designed for entering values, not for reading them. Thanks to this DateTime type you can write:
xls.SetCellValue(1,1,Now);
And it will enter the correct date into the sheet, taking in account 1900/1904 dates. This is done because "Now" in the line above gets converted to a TCellValue with data type "TDateTime", and then entered into the cell. If we converted this TDateTime int a numeric TCellValue, then we would have no way to know it was a date, and FlexCel wouldn't be able to account for 1900/1904 dates.
And about why we decided to do it this way, instead of faking a datetime ourselves and returning a datetime if the cell was formatted as date, well, this is mostly historical, this was one of the first decisions we had to do along time ago, and at that time I cared about 2 things:
1)Performance: Analyzing the format string to see if the cell has a date or a number takes time, and in many cases it doesn't matter. By returning datetimes if the cell was formatting as date, we would be introducing delays every time we read a cell: We also now need to read the format, parse it, and decide if that format is a date or not. FlexCel was designed as a low level API (with reports being the high level API), so it made sense to keep it as fast as possible.
2)I like to keep FlexCel "honest". If it is reading a number, I want it to return a number, not to be too "smart" and decide it will return a date because of the format of the cell. You should be able to change the format of the cell and the value shouldn't change, they should be orthogonal concepts. The main issue is the way Excel handled it, by making the datatype depend on the display format, but I didn't want to add the extra coupling datatype/data format just because of that bad decision.
If I had to rethink it today I am not sure on what I would choose, reasons 1) and 2) are still valid, but I also feel that the whole datetime thing is more confusing than it should be. So I am not really sure on what I would decide today, but it is likely it might be the same. But I would think more about it :)