none
Creating Excel xml fails on localization issue RRS feed

  • Question

  • In my Silverlight control there is a grid. Users can filter and sort data until they have a grid they like then click an "Export" button to export the data to Excel. As long as the selected (default) culture is English all is well. But if they have chosen to work in French there is a problem.

    Here is the flow of the Export button Click handler:

    • show SaveFileDialog
    • open StreamWriter for SaveDialog.OpenFile
    • open template (text file) in StreamReader
    • loop through grid data
    • at some point write this line:  <Cell><Data ss:Type=\"Number\">" + data.Salary + "</Data></Cell>

    Let's say data.Salary is 12.34. If the current culture is "en", the xml file written has

    <Cell><Data ss:Type="Number">12.34</Data></Cell>

    and the Excel file shows 12.34 or $12.34 if the currency style is applied. Great so far.

    But if the current culture is "fr" then I get

    <Cell><Data ss:Type="Number">12,34</Data></Cell>

    the Excel file shows 1234 or 1234.00$. I have tried a number of different ways to get the xml right in this case, including temporarily setting a new culture (en) or parsing the value like this 

    Decimal.TryParse(data.Salary, NumberStyles.Number, CultureInfo.CreateSpecificCulture("en-CA"), out newnumber)

    and if I break on that line it indicates 12.34 is the value being used, but the xml file somehow still gets 12,34.

    How can I make the value that is written to the output stream go in the correct format?

    • Moved by Jamles Hez Wednesday, April 1, 2015 9:01 AM
    Tuesday, March 31, 2015 7:05 PM

Answers

  • In the whole of the procedure I would have to switch the thread culture for two lines, then switch back for three then switch again for one then back to the original again before exiting the method. It seemed verbose and I didn't try it.

    In the beginning, I had this

       string temp = string.Empty;
       . . . 
       temp = (lang == "en") ? fr.Salary.ToString() : fr.Salary.ToString().Replace(",", ".");
       writer.WriteLine("<Cell><Data ss:Type=\"Number\">" + temp + "</Data></Cell>");
    
    but despite the "ss:Type" specification it was rendered in the spreadsheet as text: left justified and not summable. I just changed back to this thinking I could better use my time fiddling with Excel formatting. Of course, with no explanation, this now works. So data.Salary=25,36 in code now becomes 25.36 $ in Excel, which I can live with.

      I still don't understand why the parse attempts I made did not change the format of the output string. As interesting as it is academically I am far behind on the project and have to leave this unsolved.

    Monday, April 20, 2015 6:52 PM

All replies

  • Hello ARM15

    Thanks for contacting Microsoft Support. A support engineer will be in touch to assist further.

    Regards


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Saturday, April 4, 2015 2:41 AM
  • Hi ARM15,

    I'm guessing (without having tested this) that you are hitting a similar problem to this thread:

    https://social.msdn.microsoft.com/Forums/en-US/d98a3fc8-e3de-41ac-9ddb-8558a28f5b6a/problem-with-streamwriterwrite-method-and-double?forum=csharplanguage

    Have you tried:

    System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;

    Best regards,
    Tom Jebo
    Microsoft Open Specifications

    Saturday, April 4, 2015 7:02 AM
    Moderator
  • And here is the reference for the CultureInfo.InvariantCulture property with example code on how to use it.

    https://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo.invariantculture(v=vs.110).aspx

    Tom

    Saturday, April 4, 2015 7:13 AM
    Moderator
  • I'm very sorry for such a late response. There was a catastrophe that kept me out of the office for three weeks.

    Invariant culture isn't working either. Here is what I have more specifically:

     
    decimal tempdec = 0.0M;
      . . . 
      tempdec = Decimal.Parse(data.Salary.ToString(), NumberStyles.Number, CultureInfo.InvariantCulture);
      writer.WriteLine("<Cell><Data ss:Type=\"Number\">" + tempdec + "</Data></Cell>");


    While debugging, data.Salary (a decimal field) is 25.36; tempdec gets the value 2536, so the Excel xml shows it as 2536 and the spreadsheet has, with formatting applied, 2536.00$. I have tried other settings for NumberStyles and for CultureInfo and either it is the same result or there is some parsing exception.

    As mentioned before if I don't do anything to the data and just write

      writer.WriteLine("<Cell><Data ss:Type=\"Number\">" + data.Salary + "</Data></Cell>");
     

    the value written to the xml file is "25,36" which shows up in the spreadsheet as 2536.00$, which is really confusing.

    Surely I'm not the first person to encounter this?

    Monday, April 20, 2015 3:16 PM
  • Hi ARM15,

    Thanks for the extra information. I'll take a closer look and get back to you but it may not be until tomorrow. Thanks for your patience.

    Tom

    Monday, April 20, 2015 5:46 PM
    Moderator
  • Actually, did you try setting the culture on the thread instead of using it in the TryParse?

    System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;

    Tom

    Monday, April 20, 2015 5:52 PM
    Moderator
  • In the whole of the procedure I would have to switch the thread culture for two lines, then switch back for three then switch again for one then back to the original again before exiting the method. It seemed verbose and I didn't try it.

    In the beginning, I had this

       string temp = string.Empty;
       . . . 
       temp = (lang == "en") ? fr.Salary.ToString() : fr.Salary.ToString().Replace(",", ".");
       writer.WriteLine("<Cell><Data ss:Type=\"Number\">" + temp + "</Data></Cell>");
    
    but despite the "ss:Type" specification it was rendered in the spreadsheet as text: left justified and not summable. I just changed back to this thinking I could better use my time fiddling with Excel formatting. Of course, with no explanation, this now works. So data.Salary=25,36 in code now becomes 25.36 $ in Excel, which I can live with.

      I still don't understand why the parse attempts I made did not change the format of the output string. As interesting as it is academically I am far behind on the project and have to leave this unsolved.

    Monday, April 20, 2015 6:52 PM
  • Ok, well, I'm glad you have something that works.

    Tom

    Tuesday, April 21, 2015 4:08 AM
    Moderator