Unplanned
Last Updated: 03 Nov 2020 14:19 by ADMIN
Philip
Created on: 28 Oct 2020 15:22
Category: Grid
Type: Feature Request
2
Use culture aware format for DateTime in Excel export (the ones that show up as Date in the Format Cell, and with an asterisk). If that's not possible, use "yyyy-mm-dd hh:mm:ss"

This will let Excel mark the field as a Date and act according to the current culture on the machine that opens the file.

---

ADMIN EDIT:

This feature would let you define custom formats, so you may want to Vote for it and Follow it too: Custom Format for Excel Export per column. It is important to keep in mind that the Excel formats are completely different from the .NET formats.

If this is of high importance for you right now, you could create your own Excel file with the desired settings by following the example from this thread.

---

5 comments
ADMIN
Marin Bratanov
Posted on: 03 Nov 2020 14:19

I am adding here the summary of a series of discussions we had privately with Philip.

The ultimate goal would be to generate the Excel file so that it recognizes the field as a Date and offers to format the date in the current culture of the Excel application (not of the Blazor app). Since these can differ, and since the Excel formats are wildly different from Excel formats, that might not be possible (pending a deeper investigation and review). If that's not possible, the default format string should become "yyyy-mm-dd hh:mm:ss" which is culture-neutral and seems to be the industry standard in many solutions that offer excel export for data.

 

 

Regards,
Marin Bratanov
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Philip
Posted on: 30 Oct 2020 00:57

Marin

I am NOT suggesting to use a "dd-mm-yy" format. I am suggesting to use the "Date" format which will adjust automatically with the culture.

So, if "dd-mm-yy" is not the format you want, what format do you want _in the code_?
The answer to this is DATE format - because it is a date.

Marin you are confusing STRING format in C#/Web Development with Excel CELL format - it is not the same.

Further to this, the other issue is that if you have a C# DATE type, no matter what, it is getting exported as a DATETIME, instead of just a DATE.

From your video, you are not understanding how Excel works - all you are doing there is CREATING new custom formats which are specific/culture bound - excel does this automatically.

 

Also both your screenshots are partially incorrect;

This is NOT a default format. That is why the format is sitting under the "Custom" section in Excel.

There IS a time portion still within the data. It is only the cell format that is obscuring it. You can test this by reformatting the cell back to custom, and the time will appear again.

 

Have a read of this article from Microsoft:

https://docs.microsoft.com/en-us/visualstudio/vsto/globalization-and-localization-of-excel-solutions?view=vs-2019

 

With note to this paragraph;

Even though you use the English (United States) format for data that is passed or manipulated by managed code, Excel interprets and displays the data correctly according to the end user's locale setting. Excel can format the data correctly because the managed code passes locale ID 1033 along with the data, which indicates that the data is in English (United States) format and therefore must be reformatted to match the user's locale setting.

 

There are 2 things to deal with for excel export and display; The Data type of cell contents and the cell format (not string format).

 

The data type being passed to the Excel is working fine - no issues with this part.

 

The cell format however is the issue because it is a HARD-CODED custom US date format. You are not allowing Excel to work its in-built magic to automatically format the date based on culture.

 

The fact remains, the format is hard-coded to US format in the Excel export functionality which means the date format will be incorrect for 95% of the world who uses the Metric system. Which means most people will need to create and maintain an entire custom "Excel Export" routine for ALL columns to fix this? (I'm unsure why you would want to impose this either?)

Yes - we can fix this using a custom format, but this isn't a solution, its a FIX and requires maintenance every time a database column is added/removed to every Telerik blazor project deployed with Export functionality.

 

The fix is to use a standard Date format and let Excel deal with the culture conversion.
If you want to show the time with DATETIME types, then you should determine the UI culture first, before applying the custom cell format (dynamic) or use a culture agnostic format.

-Then it will work for the whole world ;)

 

 

 

 

ADMIN
Marin Bratanov
Posted on: 29 Oct 2020 13:40

Hello Philip,

As I confirmed in your private ticket and in the note I added when opening this thread on your behalf - in Blazor we do use a custom format, which is documented. Using the dd-mm-yy format will destroy the time portion, and so "correcting" this now will result in data loss and a breaking change.

Thus, this can only be implemented after a feature for setting custom formats is available.

I am also attaching here a short video that shows how excel recognizes dates and for me it seems like the dd-mm-yy format is what it recognizes. If not, please correct me with a concrete example, because the exact format you will input by writing differs from what you should set in code - the input format depends on the current culture, while a coded format uses a dash or a slash to denote the delimiter character.

I am also attaching two screenshots from the current behavior with the custom format string, and a screenshot of the same data with the "dd-mm-yy" format changed in our source code for the test itself - it results in Excel recognizing the date as expected for me.

So, if "dd-mm-yy" is not the format you want, what format do you want _in the code_?

 

Regards,
Marin Bratanov
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Philip
Posted on: 29 Oct 2020 01:00

Further clarification to this, the data itself is being exported fine, it is the "cell style/format" that is incorrect but the fix is simple;

 

Just change the style.Numberformat.Format to Date for Dates

And for DateTime you can still create a custom date format based on CultureInfo

Or, if you wanted to do the short-cut way, at least select a custom format that is non-culture specific such as YYYY-MM-DD AM/PM - Telerik UI is providing generic UI components (should not have hard-coded US culture).

 

 

 

Philip
Posted on: 29 Oct 2020 00:48

Sorry - the thread title is not accurate (created by admin).
Should just be "Use standard date format when exporting Dates in Excel export"

 

ie. Export dates in standard date format (not a custom Excel format).

 

To understand the issue, you must understand Excel cell formats.

Steps to understand the issue;

1. Open a blank Excel file
2. Input a date
3. Right click format the cell and note that the cell is in DATE format
4. Adjust your regional settings and notice that the cell will automatically adjust according to your region

NOW try this

5. Format the same cell as Custom and select a hard-coded date format such as "d-mmm-yy" or "mmm-d-yy"
6. Now try adjusting your regional settings

You'll notice that when a custom cell format is applied, regional settings will no longer take effect.


With the Blazor export to Excel, dates are exported as a CUSTOM format, not as a DATE format, hence will not adjust to regional settings.

Also note that this issue is not present in the AJAX, Kendo or MVC "Export to Excel". This issue is only present in the Blazor "Export to Excel".

 

Proposing to create an entire custom export for something which is essentially being exported incorrectly, is a very big job, especially for "big data" applications where columns are being added/removed/modified on a regular basis.

 

Happy to be corrected?

 

Attached Files: