How to understand if cell is Date?

Hello,

In Excel OLE I got Date as string from Excel automatically. Now I try to do it with FlexCel:

        vCell := XLApp.GetCellValue(I + 1, J + 1);
        if not vCell.IsEmpty then
          if vCell.ValueType = TCellValueType.DateTime  then
            vS := DateToStr(vCell.AsDateTime)
          else
            vS := vCell.ToString;


In fact  vCell.ValueType = TCellValueType.DateTime   is always false and I get vCell.ToString values like: 42584
I tried also: 
if vCell.IsDateTime
- it is also always False. 
Please help how to do it right.
Thank you.

Similar question: 

how to understand that cell has currency or finance format?

Hi,
If you want the cell value as a string, you can use


vS := XLApp.GetStringFromCell(i + 1, J + 1);



It will correctly return dates as dates, and formatted cells formatted (like for example the string '$1.234' instead of the number 1234)

Now, to go more in depth: GetCellValue returns the value of the cell with the data type it is stored in.
It would be equivalent to doing:
    MsgBox ActiveCell.Formula
In Excel OLE.

GetCellFromString is more like:
    MsgBox ActiveCell.Value

Excel doesn't really have "DateTime" data types, dates are just stored as numbers with a date format. The number is the number of days since jan 1, 1900 (In your case, 42584 is Aug 2, 2016).

So, as the dates are just numbers with a special format, you can't know if it has a date by looking at the data: It will always be a number. TCellValueType will always be Number for dates, and the only reason it exists is to allow you to enter dates into cells.
When writing:
xls.SetCellValue(1, 1, now) will enter a Datetime into the cell, by converting the date into a number.
But when reading, since dates are stored as numbers, TCellValueType will never be DateTime.

If you want to know if a cell contains a DateTime, you must look at the format, not the cell value type. There is an example on how to do it in the "Reading files" demo:


function TFReadingFiles.FormatValue(const v: TCellValue; const Row, Col: integer): String;
var
  CellValue: String;
  Formatted: string;
  HasDate, HasTime: boolean;
  CellColor: TUIColor;
begin

  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;

  TCellValueType.DateTime:  //FlexCel won't currently return DateTime values, as dates are numbers.
  begin
    exit('a DateTime value: ' + DateTimeToStr(v.AsDateTime));
  end;

  TCellValueType.StringValue:
    begin
      if v.AsString.RTFRunCount > 0 then Formatted := ' FORMATTED ' else Formatted := ' ';

      Result := 'a' + Formatted +'string: ' + v.AsString.ToString
      + #10 + 'In html: ' + v.AsString.ToHtml(xls, xls.GetCellVisibleFormatDef(Row, Col), THtmlVersion.Html_32, THtmlStyle.Simple, TEncoding.UTF8);
      exit;
    end;
  end;

  raise Exception.Create('Unexpected value on cell');
end;



The FormatValue call in red above returns a "HasDate" and "HasTime" parameters for the format, which you can use to know if the number is formatted as a date.

But this is all just to clarify a little more what is going on. In your case, I think that just doing:
vS := XLApp.GetStringFromCell(i + 1, J + 1);
Will fix your problem. Internally, GetStringFromCell does all the looking at the format, etc, and returns you the formatted cell.

About the second question, currency or finance formats are just predefined format strings. What you need to look is a the FlxFormat.Format string and see how the format string is defined. Say for example
"$0,000" is a currency format, but so is "$  0,###". We provide TFlxNumberFormat.FormatValue to format a number according to an Excel format string, but as said in my first post, I think that this is not needed in your case. If you want the formatted string for a cell, just call GetStringFromCell. It will cover dates, currencies, fractions and almost anything you can throw to it. In fact, GetCellFromString is what the FlexCelPreviewer and the PDF exporter use to render Excel files.

Thank you for the answer!

Excel OLE always returns date like: 
21.09.2016
even if it is shown in Excel as 
23 sep 16

XLApp.GetStringFromCell(i + 1, J + 1) 
returns 
23 sep 16
This is a problem here:
Delphi TryStrToDateTime does not work with this string.
It works with: 
21.09.2016


THE SAME problem with money. 
Excel OLE always return digits without sign, even if currency label is shown.
and again:
TryStrToCurr does not work with "$45" it wants just "45". 
maybe this is not about Excel OLE but Variant works this way.

So my problems are the same. I need to understand if the cell is Date or Finance / Currency.  I hope that TFlxNumberFormat.FormatValue will help me. But with currency/finance i still have no idea. 
any help is really appreciated. Thank you

is any way to do this ?:

var
  Money: Double;
  Time: TDateTime;
begin
if Cell.IsCurrency then
 Money := Cell.AsMoney;
if Cell.IsDateTime then
 Time := Cell.AsDateTime;

Excel OLE returns different stuff depending in what you use: You can use Text, Value, Value2 and Formula.( http://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 ) What is what you are using?

Now, whatever you use, if you want the date (21.09.2016) and not the string, then you should use GetCellValue, not get the string and convert it to a date. Try to avoid converting stuff as much as you can.

I am not really sure on what you are trying to do: I thought you wanted the string that is displayed in the cell. But if you want the real values, then you need to look at both the value (with GetCellValue) and the format (with GetCellFormat) of the cell.

As said, you can look at the code in "Reading Files" demo for an example of how to know if a format contains a date. To know if a format contains a currency is trickier, because currencies might be in different languages. For example, in europe it might be € 2000 and in england £ 2000, while in USA is $ 2000. (or USD 2000)

You can still look at the format of the cell (TFlxFormat.Format) and see if for example it contains a $ or an € but I don't think there is a correct way to get all currencies. What is your code doing in Excel? Is it looking for the $ sign? If you are doing so, you can do the same looking at the format string for the cell. GetCellValue will always return the number, you can see how it is displayed by looking at the format.

As said you can easily find if a format is a date. You can use some code like this:
  XF: integer;
  cellvalue: TCellValue;
  fmt: string;
begin
  xls := TXlsFile.Create(1, true);
  XF := -1;
  cellvalue:= xls.GetCellValue(1, 1, XF);
  fmt := xls.GetFormatString(XF);
  if TFlxNumberFormat.HasDateOrTime(fmt) then Time := TFlxDateTime.FromOADate(cellvalue.AsNumber, xls.OptionsDates1904);

But to know if a format is a currency is more complex. One simple way could be to see if the fmt variable contains a "$" but as explained in the previous mail this isn't foolproof.
What code are you using in Excel to know if a cell has a currrency?

>What code are you using in Excel to know if a cell has a currrency?
Vartype
https://msdn.microsoft.com/en-us/library/office/gg278470.aspx

code in Delphi:

  if VarType(Range.Cells[I + 1, J + 1].Value) in [5, 6] then
            FMoneyIndex := J;

Adrian,

 Thank you very much for the great support!
You try to help fast even with very difficult questions. 

I implemented  a way to distinguish time, money and other cell types due to your help. Time is made like you wrote while with money (currency and finance formats in Excel) I used your idea vice versa:
// the following is idea, not working code:
if Cell.IsNumber then 
  if not Cell.IsTime then
    if not TryStrToFloat(Cell.AsNumber) then
      ThisIsMoneyFormatHere!

I think that TMS is wrong in writing that Excel do not have time format and returning strange days digits by default. Maybe Excel really stores these digits but this is a private undocumented Excel trick, there is no need to use it "as is". Much more convenient is to make it like in OLE Variant with date types and return it always in normal datetime format, not just number. And of course, Cell.IsDateTime must be DateTime if it DateTime in Excel :)
Similar situation with Currency and Finance. I believe it is not very difficult to implement all Excel Types and give it to developer as real output. MSDN help about Excel cell types could be used here for similar development.

Thank you very much again for your efforts!

Hi,
Dates being numbers are not a private undocumented Excel trick, it is there in public view. Just enter a date in a cell, then change the format to "currency" and you will see the strange digits that FlexCel is reporting:



Or the reverse, if you write the number 42376 in Excel and format it as date, you will see the corresponding date.
You might also use those numbers in formulas, for example if you write the date 1/1/2017 in A1 and in A2 write = A1+ 1 then you will see 1/2/2017, or the serial number 42377.

Excel even allows you to change the start of the date: 1900 or 1904 (even if you will normally will want to keep it in 1900):



And in fact, Excel OLE will also return you the number if you use "Cell.Value2" instead of "Cell.Value" as you are using.

But now, the reason why we return you "what is on the cell" instead of "what you see on the cell" is because in many cases, you don't care about it being it a date or not, and we in those cases it would be much slower for you if we returned a date. As said, what FlexCel (and Excel) has stored is a double, so we would have to look at the format of the cell every time and see if it is a "date format" which isn't a trivial thing to do either: "dd/mmm/yyyy" is a date format but also "yyyy-mm" or "hh:mm: are date formats. It takes time to process the format in order to return you a date, and in so many cases you don't care. I guess this is why Excel OLE provides you with "Value2" which should be faster too for the same reasons (it doesn't have to look at the format), and normally it is the recommended way to read cell values with OLE.

If we give you what is in the cell, you can easily get the date with the code in my last post. But if we provided you with the date that you see due to the format, you wouldn't have a way to see the raw number which might be the only thing you are interested in, and it would be slower for no reason.

I guess we could have a GetCellValue2 in the same way that Excel OLE has Value and Value2, but I think it is more confusing than anything else. It isn't that hard to get a date value.

You can always have a method "GetValueAsIWantIt" which will see if the format is a date or a currency and return different stuff. But what we have to provide is the basics, so you can build from there. If we provided you the already processed (in a slow way) result, then you wouldn't have a way to speed it up it you didn't need it.

Anyway, I'll see to add some method to TCellValue where you can get the value as a date if the format is a date: It should make stuff a little simpler.