Format Date with SetCellFromString

FLX TMS FlexCel for VCL & FMX
1
Hi, could you provide a code sample of how to use SetCellFromString method to set in cell a string to a formatted date (I am using C++ Builder XE2) :

void __fastcall SetCellFromString(const int row, const int col, const System::UnicodeString value, const int XF, const System::DynamicArray<System::UnicodeString> dateFormats);

How do you fill the date format (like specified in http://msdn.microsoft.com/en-us/library/8kb3ddd4(v=vs.110).aspx) ?
What do you put for XF ?


2

Hi, 

Just in case, first thing to mention is that you might not need to specify any of those parameters. If you just do:
xls->SetCellFromString(1,1, "02/01/1996");

It will enter a date in the file, using whatever settings you have in your machine, or those settings you set by using TFlexCelFormatSettings.

About what those parameters do, and why you would want to use them:
1)XF: This stands for "eXtra Formatting" (this name comes from Excel). In short, it is an identifier that has the formatting of the cell (bold, red, borders, etc). It is a shortcut so you don't have to write SetCellValue(...) and then SetCellFormat(....), you do both at once and it is a little faster.

But in your case, you set it to -1, which means "keep the format in the cell". For more information about XF, please read the "API Developers guide" (Start menu->TMS FlexCel->Documentation)

2)dateFormats:  By default, if you don't specify this parameter, FlexCel will try to match the string with any possible valid date for your OS. For example, it will understand that "02/01/1996" is a date, but it will also understand that "1996-01-02" is a date. In fact Windows is kind of lax understanding what can be a date, and sometimes it can be problematic. For example, if you used "1.2.2" this can be understood as a german formatted date (jan 2 of 2002), and maybe it was a version number instead. 

So, by explicitly specifying dateFormats, you can explicitly say which strings are valid dates. This parameter is an array, so you could specify that both "dd-MM-yyyy" and "yyyy-MM-dd" are valid formats. But in general, you will only have one string in the array, the one that identifies how your input string is formatted.

Here is a little C++ example on how to use SetCellFromString with and without specifying dateFormats. Note that the last 2 lines change the formatting from dd/MM to MM/dd so the value entered in the generated file is different. Also note that we use uppercase "M" for month, because lowercase "m" is used for minutes. (we are using .NET formatting as per the link you quoted in the original post)

void __fastcall TForm11::Button1Click(TObject Sender)
{
  TXlsFile xls = new TXlsFile(1, true);
  xls->SetCellFromString(1,1, "02/01/1996");

  DynamicArray<String> ExpectedDateFormat;
  ExpectedDateFormat.Length = 1;
  ExpectedDateFormat[0] = "dd/MM/yyyy";
  xls->SetCellFromString(2,1, "02/01/1996", -1, ExpectedDateFormat);


  ExpectedDateFormat[0] = "MM/dd/yyyy";
  xls->SetCellFromString(3,1, "02/01/1996", -1, ExpectedDateFormat);

  xls->Save("r:\test.xlsx");

}

 
3

Thanks a lot Adrian for these explanations.


So "dateFormats" is for expected date format in input.
How do you set date format in output (in cell) ?

The code below is not working, I want to convert the date from 'ddMMyyyy' to 'dd-MM-yy' (without using string manipulations with c++ code, but with your api) :

TFlxFormat viMyFormat = pXls->GetDefaultFormat;

viMyFormat.Format     = "dd-MM-yy";
int viIndFormat       = pXls->AddFormat(viMyFormat);


DynamicArray<String>ExpectedDateFormat;
ExpectedDateFormat.Length = 1;
ExpectedDateFormat[0]     = "ddMMyyyy";


pXls->SetCellFromString(viLig, viCol, "22112013", viIndFormat, ExpectedDateFormat);

4

Hi,

I see 2 issues here:
1) While you are using the correct expected format, the string "22112013" is also a number. SetCellFromString will try to convert the string to the "best" format, and in this case it will just convert it to a number. (as numbers are tried before dates, SetCellFromString will see this is a number, enter it, and the code to test for dates won't even be called).

In order for this to work you would need some "date separator" or something in the string, so it isn't a number. For example "22-112013" will work fine.

2)While vIndFormat specifies most of the format for the cell, in this particular case FlexCel will ignore the formatting string, since it has to change it depending if it is a number, bool, etc. So for this to work, you would have to set the full format again in next line.
This code should get you what you want:
  TXlsFile* pXls = new TXlsFile(1, true);
  TFlxFormat viMyFormat = pXls->GetDefaultFormat;
  viMyFormat.Format     = "dd-MM-yy";
  int viIndFormat       = pXls->AddFormat(viMyFormat);

  DynamicArray<String>ExpectedDateFormat;
  ExpectedDateFormat.Length = 1;
  ExpectedDateFormat[0]     = "dd-MMyyyy";

  pXls->SetCellFromString(1, 1, "22-112013", viIndFormat, ExpectedDateFormat);
  pXls->SetCellFormat(1, 1, viIndFormat);
  pXls->Save("r:\test.xlsx");

Now, I am not sure if this is the best approach. SetCellFromString is designed to deal with cases when you only have a string and don't know what datatype it is. SetCellFromString has to apply heuristics to "guess" what datatype it is, and it might not be correct 10% of the times. As in this case, where it sees the string "22112013" and it can't know if it is supposed to be a number or a date.

But you do know this is a date and not a number, so probably the best is that you set the value as a date directly. No need to have FlexCel guess what datatype it is.

I would personally use some code like this:

#include <__UMiscUtils.hpp>
...
void __fastcall TForm11::Button1Click(TObject Sender)
{
  TXlsFile pXls = new TXlsFile(1, true);
  TFlxFormat viMyFormat = pXls->GetDefaultFormat;
  viMyFormat.Format     = "dd-MM-yy";
  int viIndFormat       = pXls->AddFormat(viMyFormat);

  DynamicArray<String>ExpectedDateFormat;
  ExpectedDateFormat.Length = 1;
  ExpectedDateFormat[0]     = "ddMMyyyy";

  TDateTime Date;
  __Umiscutils::ConvertDateToNumber("22112013", ExpectedDateFormat, Date);

  pXls->SetCellValue(1, 1, TCellValue::Create(Date), viIndFormat);
  pXls->Save("r:\test.xlsx");

}

Note that I am using an "internal" FlexCel method to convert from a string to a date given a format: 
__Umiscutils::ConvertDateToNumber(...)
and this is why I also had to include __UMiscUtils.hpp

I used this because it is what FlexCel uses internally, but you could use standard VCL or C++ methods to convert a string to a date if you prefer. (I am not really sure on what those methods are, I would have to check)

Syntax also get a little funny in C++ builder when dealing with this delphi stuff, so you need to explicitly create a TCellValue, where in delphi it would just be xls.SetCellValue(1,1,date); But sadly implicit operators in delphi don't map to implicit operators in c++ builder.


5

Thanks, then for dates I will do it easy with strings :



AnsiString viDate = "22112013";
viDate    = viDate.SubString(1, 2) + "-" + viDate.SubString(3, 2) + "-" + viDate.SubString(7, 2);
pXls->SetCellValue(1, 1, TCellValue::Create(viDate));
6

With dateformat input checking :



DynamicArray<String>ExpectedDateFormat;
ExpectedDateFormat.Length = 1;
ExpectedDateFormat[0]     = "dd-MM-yy";



AnsiString viDate = "22112013";
viDate    = viDate.SubString(1, 2) + "-" + viDate.SubString(3, 2) + "-" + viDate.SubString(7, 2);


pXls->SetCellFromString(1, 1, viDate, -1, ExpectedDateFormat);