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.
5 Answers, 1 is accepted
Rank 2
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.
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
Rank 2
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.
Rank 1
Wallace, could you please clarify that you are exporting to Xslx format?
Also, make sure there are no JavaScript errors on the page - Troubleshooting JavaScript errors.
In case there is Ajax on the page, please try to disable it temporarily and test the application while monitoring for JavaScript errors again - Get more descriptive errors by disabling AJAX
If these are not helping, we would need a repro of the problem in order to help you further. A runnable sample, or the markup declaration of the RadGrid along with the relevant code-behind logic could help us in troubleshooting.
Rank 1
I'm glad the provided directions have proven helpful and thank you for sharing your findings and approach with our community.
Regards,
Eyup
Telerik