Excel date problem

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

Moderator: Alex Zencovich

Excel date problem

Postby vcastro » Thu Jun 22, 2006 11:50 pm

hi i'm trying the trial of Pivotcube VCL, but when i export data to Excel, it changes day by month, and if day > 12 then it leave the cell value as text

in mexico we use dd/MM/yyyy for day format,
but usa and other countries use MM/dd/yyyy

so january 7, 2006 is 7/01/2006 but after export excel shows it like
date 1/7/2006

january 13 shows it correctly ( 13/1/2006 ) but as if it was text, not a date formated cel
------------------------------------------------------------------------
on another issue i have when i activate row percent and col percent on a measure, the total by row column shows me the exact same value for both col and row percent, i think row should by 100% always

thanks
vcastro
Guest
 
Posts: 2
Joined: Thu Jun 22, 2006 11:39 pm

Postby AlexZencovich » Fri Jun 23, 2006 6:54 am

Hello,

1. PivotCube (PivotGrid.ExportToExcel) does not recognize dimension items caption type (date, time, money etc). It always treated as string, no special format performing.

But you may to knwo, Excel try to recognize cell data and transform it to specifed format. On pity it not always work correcly because Excel may not have embedded format template like ShortDate fromat in your locale.
For example my Excel 2000 does not have(and do not recognize it as date) dd/mm/yyyy format at all, but only have
dd/mm/yy.

Also, you may need understand, when cube saved to stream(xlm) item captions also saved as strings, so if you will change systme locale after cube saved and restore it back, string caption of date's still unchanged. Key (date values) will restored correctly so sortings by key(date) value will work correctly.

2. Row total % works as expected. For row totals it show % value relatied to common cube total, so if you will remove all dimension from columns, you will see correct values anyway.
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

Postby vcastro » Fri Jun 23, 2006 3:33 pm

thanks for fast response
found a workaround for #1, if anybody have the same problem
saving it like a .csv file, excel opens it correctly

Code: Select all
 
  PivotGrid1.ExportToStringGrid(true,true,true,stringgrid1);
  memo1.Clear;
  for i:=0 to StringGrid1.RowCount do
      memo1.lines.add(StringGrid1.Rows[i].CommaText);
  memo1.Lines.SaveToFile('.\open_me_with_excel.csv');
vcastro
Guest
 
Posts: 2
Joined: Thu Jun 22, 2006 11:39 pm

Postby AlexZencovich » Fri Jun 23, 2006 4:24 pm

Intresting.....Same text is recognozable in csv and locked in OLE (or XLS - I try to export in XLS with same problem)

Thank you very much for workaround!
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


Return to PivotCube VCL

Who is online

Users browsing this forum: No registered users and 2 guests

cron