Hi Adrian,
I'm trying to find the best way to retrieve a cell formatted as date into a variant, but correctly flagged as a varDate and not a varDouble .
I'd initially tried xls.GetCellValue(Row,Col).AsVariant but this returns it as a varDouble
Now I understand that Excel stores dates as doubles, and I need to check the format, so I've tried something like
var CV:TCellValue; XF:integer;
CV:=xls.GetCellValue(Row,Col,XF);
if TFlxNumberFormat.HasDateOrTime(xls.GetFormatString(XF)) then
V:=TDateTime(CV.AsVariant)
else
V:=CV.AsVariant;
which works fine
So my questions are:
Firstly, is this the best approach, or is there already a different function already present in FlexCel to achieve what I need [I'm fairly new to FlexCel, so still discovering it's multitude of methods!)
Secondly, the function GetCellValue(Row,Col).AsDateTime gives an error, and GetCellValue(Row,Col).IsDateTime returns FALSE for cells formatted as dates, would there ever be a situation when these would return TRUE and a valid TDateTime? And if not, what is the purpose of these methods?
Best regards
Steve Collins
Hi,
The method you are using seems fine. We have actually considered adding some methods to TCellValue so you could do directly something like CV.TryAsDate(..) or similar, but the problem is that this would make the build process of the packages much slower because TCellValue is at the very bottom of the uses list (everything uses TCellValue), and so TCellValue should be using the least possible units (even in implementation). To provide a method in TCellValue that parses the format would generate a big cycle which slows down compilation times a lot. (We have actually spent a lot of time breaking compilation cycles in FlexCel so it can install faster: it used to be way slower to install)
So I think that instead of a method in TCellValue (which I agree would be nicer), it is best to keep the method separated as TFlxNumberFormat.HasDateOrTime
About the second question: I know it is confusing but it is sadly the best we can do. The IsDateTime property is indeed false for all values, because TCellValue doesn't know about formats, and you could actually use the TCellValue with the date to set it into a different cell which has a number format, and the TCellValue wouldn't be a date anymore.
So why do we have the option to store DateTimes in TCellValue at all if we never return DateTimes on it? The option to store datetimes is there so you can set values, not read them.
Say for example you write:
xls.SetCellValue(1, 1, now);
Under the hood, what happens here is that there is a TCellValue created implicitly, so you are actually calling:
xls.SetCellValue(1, 1, TCellValue.Create(now));
But the constructor TCellValue.Create(date: TDateTime) can't return a TCellValue with the number that corresponds to the date. Because the constructor of TCellValue doesn't know where the date will be used, and Excel has 2 date systems: One that starts in 1900 and the other in 1904. And of course, the number for a date is different depending in the date system you are using.
So the constructor for TCellValue that takes a date, must return a TCellValue with a type of TDateTime, where IsDateTime will be true. Later on, SetCellValue will read this date and enter the correct number depending in the date system (1900 or 1904) of your file.
Well, I don't know if the explanation explained anything or make things more confused, but what you need to rememeber is that IsDateTime is there for the values you set, so FlexCel can convert them correctly to numbers. When reading files, IsDateTime will always be false, because those values are numbers, and TCellValue doesn't know or care about how those numbers are formatted.
Thanks for the prompt and detailed explanation.
I've already created a descendant of TXlsFile that all our code uses, so I'll just add a new function to that to return a cell as a 'correct' variant!