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

4 posts, 1 answers
  1. Julian
    Julian avatar
    61 posts
    Member since:
    Apr 2015

    Posted 07 Jul 2015 Link to this post

    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

     

  2. Answer
    Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 10 Jul 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. Julian
    Julian avatar
    61 posts
    Member since:
    Apr 2015

    Posted 14 Jul 2015 in reply to Kostadin Link to this post

    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 !

  4. Kostadin
    Admin
    Kostadin avatar
    1733 posts

    Posted 17 Jul 2015 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top