Exporting Cube to Excel with Date Values formated DD/MM/YYYY

[DaviWall]

Hey PC VCL creators,

We were having a problem when exporting a cube containing date value to excel. Excel insists in treating data values in American format MM/DD/YYYY, even though we use DD/MM/YYYY.
SO, checking on this forum, I found a post from 2006 where it's said that this is an Excel Ole Automation problem and there is no solution for it.

A hard to buy answer! Is that it ? there is no solution for this problem till now ?? do you have any update about this problem ?

Thank you.

Davi Wall.

[AlexZencovich]

Yes, it is quite serious problem, but pity - I have no idea to solve it. The Excel does not recognize other deta formats except native USA format. At least that was true till MS Office XP. Probably latest Excel releases are free from that bug. But according to http://www.joelonsoftware.com/items/2008/02/19.html I do not sure

[DaviWall]

I'm very proud to announce that I've found a solution for your problem ( which is mine also ).

Basically OleObject only understand dates in American format (MM/DD/YYYY), it doesn't take in consideration Computer Regional Settings. When you send a text date to a cell it tries to convert the cell to date format. As the date is invalid the cell remains as text format.

This will produce a TEXT fomatted cell:
Ex: Let's say HC.Text := '23/12/2008' (DD/MM/YYYY)
Sheet.Cells[hc.Range[0] + FFixedRowsExt + 1 + 2 * Ord(Title <> ''), k + 1] := HC.Text;

Now, if you convert the date from DD/MM/YYYY to MM/DD/YYYY the OleObject will be able to succesfully convert this cell to date format as it is a valid date for the Object.

This will produce a DATE fomatted cell:
Ex: Let's say HC.Text := '23/12/2008' (DD/MM/YYYY)
Sheet.Cells[hc.Range[0] + FFixedRowsExt + 1 + 2 * Ord(Title <> ''), k + 1] := FormatDateTime('MM/DD/YYYY',StrToDatetime(HC.Text))

Conclusion, when you open this file in Excel, it understands this cell as date and use the regional settings to format it!
So in the end, I believe that no matter what date format / language the computer is using, you have always to pass the date in American format.

This is the change I did for my own need:
if TryStrToDate(hc.Text, Data) then
Sheet.Cells[hc.Range[0] + FFixedRowsExt + 1 + 2 * Ord(Title <> ''), k + 1] := FormatDateTime('MM/DD/YYYY',StrToDatetime(HC.Text))
else
Sheet.Cells[hc.Range[0] + FFixedRowsExt + 1 + 2 * Ord(Title <> ''), k + 1] := HC.Text;

Why am I telling you this? Cuz I'm looking for a job...(no, just kidding)
It's just that I don't wanna have go over this change everytime I get a new update of PivotCube.
I even hope you find a more elegant solution for this problem.

Thank you for your time.

Davi Wall.

[AlexZencovich]

Hmm..look really you have got a solution.
I look on this idea too, bit it stop me just because I was unsure about "when you open this file in Excel, it understands this cell as date and use the regional settings to format it!"
but if you say it is works, I will implement this for next build.

Thank you very much!!

[anaves]

Hi Alex.
Have you already implemented this modification in PivotCube VCL new releases ?

Regards

[AlexZencovich]

Yes, of course.

[Nadya]

Hi Alex,
Where can I get form a source code for this fixed issue? Unfortunatelly my source code is old (2006) and I still have this problem with exporting wrong format of date.

Or maybe anybody else who have a newer source code can send it to me please? I guess it is in PivotGrid_SRC.pas file should be changes?

Regards,
Nadya