Export grid date columns to Excel as dates

[Richard]

Hi,
I’m doing a grid export to excel using the ExeclML export type.

If I do not apply a DateFormatString to my date columns it displays in the grid as “09/04/2010 00:00” and exports to Excel as a date/time with the hh:mm included (i.e. “09/04/2010 00:00”) . Importantly it is a DATE in excel. Here is a snippet from the generated xml.

<Cell ss:StyleID="GeneralDate" ss:Index="37"><Data
ss:Type="DateTime">2010-07-15T00:00:00.000</Data></Cell>

However, if I apply a DateFormatString of "{0:d}" it displays in the grid how I want it i.e.  “09/04/2010” but exports to excel as a “String” data type which is no good to me.

<Data ss:Type="String">16/03/2010</Data></Cell>

I need the grid to display it as “09/04/2010” and export it to excel as “09/04/2010” with a data type of DATE

Any ideas ?

Thanks,
Richard

[Vlad]

Hi,

 When you apply string.Format() (DataFormatString case) to any type (no matter DateTime or not) actually you will convert the original data to string.

Best wishes,
Vlad
the Telerik team

[Richard]

Ah ok  see, that makes sense.  I have a follow up question.

 I now tried seting the DateFormatString  to null before the export and reset it to "{0:d}" after. That fixes the issue with displaying it correctly on the Grid and it exports to Excel as a date, however it sill exports with the hh:mm at the end.

Is there anyway to trim the hh:mm from the excel export and keep the date data type?

 Richard

[Vlad]

Hello Richard,

 If you remove the time part most probably Excel will unable to display these values as valid DateTime.

Greetings,
Vlad
the Telerik team

[JC]

Hello,

So, I'm in the same isue and I think this is a bug.
Because when you switch on data type on RenderDataCells method the type must be "Date" even if you apply a ToString() to render the date ! You must find the good type in the dataContext and not the value in the TextBlock
I follow your code with reflector and found a suspect method in "CreateMemberAccessFunc" : ApplyBindingConverterAndStringFormat
I suggess you to use the real binding mecanism such as this exemple why not : 
http://forums.silverlight.net/forums/p/116374/262454.aspx

Do you do something for this ?
Bye
JC

[Vlad]

Hi,

 The export will work as expected without the format. Any type formated with string.Format will produce string value. 

Best wishes,
Vlad
the Telerik team

[JC]

Hi,

In my case, when a column bound to a date without DataFormatString, you write ToString("yyyy-MM-ddTHH:mm:sss.fff") in exel cell and when I open it, the type is not "Date" but "Custom" with m/d/yyyy h:mm format : BAD !

So, I take a look in the generated ExcelML file and i found : 

<Style ss:ID="GeneralDate">
   <NumberFormat ss:Format="General Date"/>
</Style>

I was wondering where did this "General Date" and I found this reference un MSDN : 
http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx#odc_xmlss_ss:numberformat
And I think I understood the problem, it's for Office XP (office10) and I use 2007 & 2010 (office12)
So I tried the same documentation for 2010 but not way ... so I create a file and I put the 17 types of date here :

<Styles>
  <Style ss:ID="s62">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="Short Date"/>
   <Protection/>
  </Style>
  <Style ss:ID="s63">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[$-F800]dddd\,\ mmmm\ dd\,\ yyyy"/>
   <Protection/>
  </Style>
  <Style ss:ID="s64">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="m/d;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s65">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="m/d/yy;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s66">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="mm/dd/yy;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s67">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[ENG][$-409]d\-mmm;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s68">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[ENG][$-409]d\-mmm\-yy;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s69">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[ENG][$-409]dd\-mmm\-yy;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s70">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[ENG][$-409]mmm\-yy;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s71">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[ENG][$-409]mmmm\-yy;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s72">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[ENG][$-409]mmmm\ d\,\ yyyy;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s73">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[$-409]m/d/yy\ h:mm\ AM/PM;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s74">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="m/d/yy\ h:mm;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s75">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[$-409]mmmmm;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s76">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[$-409]mmmmm\-yy;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s77">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="m/d/yyyy;@"/>
   <Protection/>
  </Style>
  <Style ss:ID="s78">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat ss:Format="[ENG][$-409]d\-mmm\-yyyy;@"/>
   <Protection/>
  </Style>
 </Styles>

Has you can see, "General Date" seems not existe in 2010 !!
Can you adapt the export for 2007+ users please ?

Sincerly
JC

[Anne Lyon]

Hi,
Re-opening an old thread, but I have a similar issue with export of dates to Excel. Ref your answer: "The export will work as expected without the format". Yes it works if I set the DataFormatString to String.Empty before the export, then the date column exports to Excel as a column behaving like a date (although the cell format is "Custom" and not "Date" as I would expect it to be).

BUT: if you either do a sort or filter on this date column in the RadGridView prior to setting the DataFormatString to Empty and then exporting it, it does not matter if you make sure the DataFormatString is empty, the date will appear in Excel as a text field with type "General". I can not find anything in the GridViewDataColumn properties for my date column that I can temporarily switch off in order for the export to be OK.

Do you have an explanation or a workaround to this problem?