This is a migrated thread and some comments may be shown as answers.

Export grid date columns to Excel as dates

7 Answers 134 Views
GridView
This is a migrated thread and some comments may be shown as answers.
Richard
Top achievements
Rank 2
Veteran
Iron
Iron
Richard asked on 13 Jan 2011, 11:56 AM

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

7 Answers, 1 is accepted

Sort by
0
Vlad
Telerik team
answered on 13 Jan 2011, 12:37 PM
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
Let us know about your Windows Phone 7 application built with RadControls and we will help you promote it. Learn more>>
0
Richard
Top achievements
Rank 2
Veteran
Iron
Iron
answered on 13 Jan 2011, 01:15 PM

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

0
Vlad
Telerik team
answered on 14 Jan 2011, 11:14 AM
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
Let us know about your Windows Phone 7 application built with RadControls and we will help you promote it. Learn more>>
0
JC
Top achievements
Rank 1
answered on 03 Feb 2011, 06:14 PM
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
0
Vlad
Telerik team
answered on 04 Feb 2011, 07:23 AM
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
Let us know about your Windows Phone 7 application built with RadControls and we will help you promote it. Learn more>>
0
JC
Top achievements
Rank 1
answered on 04 Feb 2011, 04:58 PM
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

0
Anne Lyon
Top achievements
Rank 1
answered on 24 Aug 2012, 10:01 AM
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?
Tags
GridView
Asked by
Richard
Top achievements
Rank 2
Veteran
Iron
Iron
Answers by
Vlad
Telerik team
Richard
Top achievements
Rank 2
Veteran
Iron
Iron
JC
Top achievements
Rank 1
Anne Lyon
Top achievements
Rank 1
Share this question
or