Export grid date columns to Excel as dates

8 posts, 0 answers
  1. Richard
    Richard avatar
    72 posts
    Member since:
    Feb 2009

    Posted 13 Jan 2011 Link to this post

    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

  2. Vlad
    Admin
    Vlad avatar
    11100 posts

    Posted 13 Jan 2011 Link to this post

    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>>
  3. DevCraft banner
  4. Richard
    Richard avatar
    72 posts
    Member since:
    Feb 2009

    Posted 13 Jan 2011 Link to this post

    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

  5. Vlad
    Admin
    Vlad avatar
    11100 posts

    Posted 14 Jan 2011 Link to this post

    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>>
  6. JC
    JC avatar
    32 posts
    Member since:
    Sep 2010

    Posted 03 Feb 2011 Link to this post

    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
  7. Vlad
    Admin
    Vlad avatar
    11100 posts

    Posted 04 Feb 2011 Link to this post

    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>>
  8. JC
    JC avatar
    32 posts
    Member since:
    Sep 2010

    Posted 04 Feb 2011 Link to this post

    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

  9. Anne Lyon
    Anne Lyon avatar
    87 posts
    Member since:
    Jan 2010

    Posted 24 Aug 2012 Link to this post

    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?
Back to Top
DevCraft banner