Yet another RadGrid Excel export date problem

6 posts, 0 answers
  1. Kevin
    Kevin avatar
    9 posts
    Member since:
    Nov 2014

    Posted 27 Apr Link to this post

    Hi, I'm difficulty with exported date formats in my mulicultural application.  

    The issue is that users within my application are allowed to change their language (thus, their culture setting within our application) but the radgrid's exports seem to "stick" to whatever their previous culture's date format is.  This "sticking" behavior is regardless of which culture is started with and then changed to.

    To support multiple dates, I'm applying a DataFormatString of "{0:d}" to the GridDateTimeColumn so I can allow .NET to auto-format it with the builtin Globalization features.  So my column is defined like this:

    <telerik:GridDateTimeColumn ItemStyle-Wrap="false" HeaderText="<%$ Resources: OurstringsResource, HeaderTextInvoiceDate %>" DataField="invDate" HeaderStyle-Width="8em" DataFormatString="{0:d}" />

     

    When applying this format, the on-screen date is shown correctly, however it is not when exporting in the same format as shown on the screen.  Again, this is after they've changed their language/culture.  All other aspects of our application regarding the culture change work correctly (e.g. I am debugging the code and can see the correct culture is set) so I'm confident it is not a problem with our application.

    I've found a number of posts on the Telerik site describing similar behavior, but in my case I'm using the Xlsx format, such as this:

    protected void btnExportToExcel_Click(object sender, EventArgs e)

    {

        RadGrid1.ExportSettings.FileName = "MyExportedFile";

        RadGrid1.ExportSettings.ExportOnlyData = true;

        RadGrid1.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx;

        RadGrid1.MasterTableView.ExportToExcel();

    }

      Since we are using the format of Xlsx I don't see many events that I can change the format, per the many suggestions found in the other threads.  I did see that OnItemCreated looked promising and I tried doing something like this:

    if (e.Item is GridDataItem && exporting)

    {

       var item = (GridDataItem)e.Item;

      //also tried manually setting the specific short date as defined by the current culture

      item["invDate"].Text = Convert.ToDateTime(item["invDate"].Text).ToString("d"); 
    }

    ... but this didn't work.  

    I've tried many other things such as changing my column type, setting the appropriate culture in either/both the Page.Culture and RadGrid.Culture, binding the DataFormatString string to a fixed string appropriate for the culture, etc etc etc.  

    I'm really at a loss here and could use some help.  

    Thanks in advance!

     

    BTW, I'm using the Q1 2016 version of UI for ASP.NET AJAX, and .NET 4.5.

     

  2. Kevin
    Kevin avatar
    9 posts
    Member since:
    Nov 2014

    Posted 27 Apr in reply to Kevin Link to this post

    I should add that I did find this information, but when setting the Suppress option to true, this did not change the resultant exported string.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Kevin
    Kevin avatar
    9 posts
    Member since:
    Nov 2014

    Posted 27 Apr Link to this post

    Further thought... if the client machine is configured with a given culture (under Region and Language), how much influence does the RadGrid have over how Excel shows the date?

    Could Excel's interpretation of the value as passed from the RadGrid be the actual problem?

     

    Again, thanks in advance for any clues as to what's going on here.

  5. Eyup
    Admin
    Eyup avatar
    3015 posts

    Posted 02 May Link to this post

    Hi Kevin,

    The last question, which can be the key to this specific behavior, is not related specifically to Telerik but to Microsoft Excel in general. You can test it by changing the regional settings of your machine and opening the same Excel document containing a table with {0:d} time column in different cultures. Here are 2 tests you can try which will confirm this hypothesis:

    1. Export the grid content to PDF and observe the results. If the result is per your described requirement, it means that the Excel changes the data per the user's machine cultural configuration.
    2. Instead of dynamically changing the Localization of the web application, start the project directly using some different culture - changing the Culture and UICulture of both the <%@ Page ... %> and RadGrid alike.
    If again everything is localized correctly, but the date format remains, this will lead to this theory once more.

    You can also try enabling the IgnorePaging property, which will cause an internal Rebind() before exporting the grid.
    I hope this directions will prove helpful.

    Regards,
    Eyup
    Telerik
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
  6. Kevin
    Kevin avatar
    9 posts
    Member since:
    Nov 2014

    Posted 03 May Link to this post

    Thanks for the reply Eyup.

    I followed your suggestions and I agree that it does appear that the fault mainly lies with Excel.  Thanks for the suggestions.

     

    For anybody who has a similar problem in the future...

    I was able to address the short-coming in Excel by forcing the specific ExportInfrastructure.Cell.Format to the culture's specific ShortDatePattern (somewhat similar to this post).  Another challenge for me is that since I'm using GridExcelExportFormat.Xlsx file format, the only event that I found that fires was the OnInfrastructureExporting RadGrid event; this event wasn't immediately obvious but did do the trick.   With this event I was able to set the specific date format before the file was exported.  

     The other exporting events that actually have 'Excel' in the name did not fire for me and appear to be reserved for Biff, ExcelML, and/or Html formats.

  7. Eyup
    Admin
    Eyup avatar
    3015 posts

    Posted 06 May Link to this post

    Hi Kevin,

    I'm glad the provided directions have proven helpful and thank you for sharing your findings and approach with our community.

    Regards,
    Eyup
    Telerik
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017