Urgent: Exporting to Excel / Display data in user's locale

[Julian]

I'm a bit unsure of how to handle localization in regards to the numbers and dates within a radGrid. 

This is our scenario. I have created the reports and tested them in the U.S. When the user runs the report on his workstation in Germany and exports the data to Excel the numerical data is displaying in English format. 

 

ie. 1,344.00

 As opposed to the German format which would be: 1.344,00

 Currently my data is displaying on a German server as 1,344.00. If I go to the German server, open up the excel file that was exported and click on the cell that currently displays 1,344.00. I can then set that cells format to number and check the checkbox  for: "use 1000 separator (.)". Then I have to re-type the number into the field. So I type in 134400 and hit enter. Then the value is displayed as 1.344,00. Which is what I want.

So the million dollar question. How do I export my data such that the formatting is like this?

 This is an urgent issue so any help is greatly appreciated.

 

Thanks,

Julian

 

[Kostadin]

Hello Julian,

I tried to replicate the issue on my side by setting a German culture to the page but when exporting the grid the correct format is applied in the Excel document. I attached the test sample to this thread so could you please give it a try and let me know how it differs from your real setup. Additionally, you can manually set a format to the cells but I am not sure which export format you are using, since the approach differs for the different format. Could you please let me which format you are using in order to provide you with a possible solution?

Regards,
Kostadin
Telerik

[Julian]

Kostadin,

 Sorry I didn't get back to you sooner!

 I am exporting excel as "HTML". What I found was that Excel will automatically format the data in the correct local culture if the value in the cell is a plain old number without commas. For some reason the comma being in the cell was causing an issue. Just inspecting the format of the CELL from within Excel you could see what formatting it was trying to apply. In my case the formula excel was applying was correct but the comma somehow prevented that formula from working. I found this out be changing the exported value in code during runtime in C#. I actually took a value that was "1,540.00" and changed it to "1540.00" and presto excel converted this value in the cell to "1.540,00". Which is what I wanted. 

 In the "OnExportCellFormatting" event i use this code to get the desired result.

 

 e.Cell.Text = e.Cell.Text.Replace(",", "");
e.Cell.Style["mso-number-format"] = @"\" + "#" + @"\," + @"\" + "#" + @"\" + "#0" + @"\" + ".00";

 

Thanks !

[Kostadin]

Hi Julian,

You can try forcing the excel to display the German format by using the following approach.

e.Cell.Style["mso-number-format"] = "###.###,00";

Regards,
Kostadin
Telerik