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;

• current-beahvior.png

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

• dd-MM-yy-format.png

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 ;)

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).

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?