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

Discussion forum for PivotCube VCL users. Common questions. Bug and problem reports

Moderator: Alex Zencovich

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

Postby DaviWall » Fri Jul 18, 2008 12:15 pm

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.
DaviWall
User
 
Posts: 5
Joined: Wed Jun 13, 2007 6:07 pm

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

Postby AlexZencovich » Fri Jul 18, 2008 7:24 pm

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
Best regards,

Alex Zencovich
-------------------------------------------------------------------------------------------------------------------------------------------
www.pivotcube.com - OLAP solution for Delphi, C++ Builder and ActiveX environment
AlexZencovich
Site Admin
 
Posts: 580
Joined: Sun Jun 18, 2006 10:09 am

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

Postby DaviWall » Wed Jul 23, 2008 6:56 pm

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.
DaviWall
User
 
Posts: 5
Joined: Wed Jun 13, 2007 6:07 pm

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

Postby AlexZencovich » Wed Jul 23, 2008 8:35 pm

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!!
Best regards,

Alex Zencovich
-------------------------------------------------------------------------------------------------------------------------------------------
www.pivotcube.com - OLAP solution for Delphi, C++ Builder and ActiveX environment
AlexZencovich
Site Admin
 
Posts: 580
Joined: Sun Jun 18, 2006 10:09 am

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

Postby anaves » Fri Mar 19, 2010 10:33 pm

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

Regards
anaves
Developer
 
Posts: 25
Joined: Mon Oct 30, 2006 2:06 pm

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

Postby AlexZencovich » Sun Mar 21, 2010 11:53 pm

Yes, of course.
Best regards,

Alex Zencovich
-------------------------------------------------------------------------------------------------------------------------------------------
www.pivotcube.com - OLAP solution for Delphi, C++ Builder and ActiveX environment
AlexZencovich
Site Admin
 
Posts: 580
Joined: Sun Jun 18, 2006 10:09 am

Re: Exporting Cube to Excel with Date Values formated DD/MM/

Postby Nadya » Wed Feb 04, 2015 7:59 am

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
Nadya
Guest
 
Posts: 4
Joined: Wed Jan 24, 2007 4:55 am


Return to PivotCube VCL

Who is online

Users browsing this forum: No registered users and 2 guests

cron