Invalid decimal format in xl\charts\chart1.xml working with Italian Culture

[MarcoBettio]

Generating a workbook with data a series containing decimal values and a line chart and saving it to xlsx, under the Italian CultureInfo, NPOI save in xl\charts\Chart1.xml all double values with comma instead of dot as decimal separator.
Opening the generated file with excel give an error and the option to recover the workbook, then it will fix the number format and the file finally opens.

I tried exporting to xlsx the same file but setting in my application the CurrentCulture to InvariantCulture and NPOI write the file in the correct way.

[PBrunot]

Could you please provide reproduction code for investigation?

[MarcoBettio]

Just use the example code on your repository:
https://github.com/nissl-lab/npoi-examples/tree/main/xssf/LineChart

and change line 60 of Program.cs from this:
cell.SetCellValue(colIndex * (rowIndex + 1));
to this to have decimal values:
cell.SetCellValue((colIndex * (rowIndex + 1)) + 0.4);

In my case executing it generate an xlsx file that is corrupted (Italian Culture has comma for decimal separator).

Adding
using System.Globalization;
and at the beginning of the code
CultureInfo.DefaultThreadCurrentCulture = CultureInfo.InvariantCulture;

Make it just work as expected.

[PBrunot]

I suspect the issue is in this section of the generate document with Italian culture has XML code:

Same file generated with Invariant Culture has this XML code:

I am opening the files with Italian Excel version and reproduce the issue.

analysis

The different XML values are serialized CTNumVal.v properties, and this string is a double casted to string without culture, and then put in the XML file as it is.

Code filling the .v property:

*XSSFChartUtil:

 private static void FillNumCache<T>(CT_NumData cache, IChartDataSource<T> dataSource)
        {
            int numOfPoints = dataSource.PointCount;
            cache.AddNewPtCount().val = (uint)(numOfPoints);
            for (int i = 0; i < numOfPoints; ++i)
            {
                double value = Convert.ToDouble(dataSource.GetPointAt(i));
                if (!double.IsNaN(value))
                {
                    CT_NumVal ctNumVal = cache.AddNewPt();
                    ctNumVal.idx = (uint)(i);
                    ctNumVal.v = (value.ToString());
                }
            }
        }

XML fragment generation code:

*Chart.cs

  internal void Write(StreamWriter sw, string nodeName)
        {
            sw.Write(string.Format("<c:{0}", nodeName));
            XmlHelper.WriteAttribute(sw, "idx", this.idx, true);
            XmlHelper.WriteAttribute(sw, "formatCode", this.formatCode);
            sw.Write(">");
            if (this.v != null)
                sw.Write(string.Format("<c:v>{0}</c:v>", this.v));
            sw.Write(string.Format("</c:{0}>", nodeName));
        }

fixes?

One option can be to specify InvariantCulture in value.ToString() call, so that it gets serialized.
Or to reparse the string at the moment of serialization into a double, and serialize it as a string with invariant culture specified.
This second option is less clean but may be more compatible with code assuming CTNumVal.v is always current-culture formatted.

@tonyqus : any advice on your side to prepare the patch ?

Concept:

*Chart.cs

internal void Write(StreamWriter sw, string nodeName)
        {
            sw.Write(string.Format("<c:{0}", nodeName));
            XmlHelper.WriteAttribute(sw, "idx", this.idx, true);
            XmlHelper.WriteAttribute(sw, "formatCode", this.formatCode);
            sw.Write(">");
            if (this.v != null)
            {
                // v value has been formatted as string as per current culture, but XML requires number to be formatted as invariant culture
                // so we need to convert it back to number and then to string
                double dblValue = 0.0;
                if (double.TryParse(this.v, out dblValue))
                {
                    sw.Write(string.Format("<c:v>{0}</c:v>", dblValue.ToString(CultureInfo.InvariantCulture));
                }
                else
                {
                    // Fall back to string
                    sw.Write(string.Format("<c:v>{0}</c:v>", this.v));
                }
                
            }
                
            sw.Write(string.Format("</c:{0}>", nodeName));
        }```



Test files attached:
[test-italian.xlsx](https://github.com/nissl-lab/npoi/files/9768626/test-italian.xlsx)
[test-invariant.xlsx](https://github.com/nissl-lab/npoi/files/9768627/test-invariant.xlsx)

[Bykiev]

I had the same issue with charts and decimal separator, but it was closed

[tonyqus]

Option one (to specify InvariantCulture in value.ToString() call) looks safe. Please go ahead and create a PR.

[MarcoBettio]

Hi all, I write again because I was following the thread...
From the analisys of @PBrunot and the response of @tonyqus I was sure that the patch should be applied in this piece of code:

*XSSFChartUtil:

 private static void FillNumCache<T>(CT_NumData cache, IChartDataSource<T> dataSource)
        {
            int numOfPoints = dataSource.PointCount;
            cache.AddNewPtCount().val = (uint)(numOfPoints);
            for (int i = 0; i < numOfPoints; ++i)
            {
                double value = Convert.ToDouble(dataSource.GetPointAt(i));
                if (!double.IsNaN(value))
                {
                    CT_NumVal ctNumVal = cache.AddNewPt();
                    ctNumVal.idx = (uint)(i);
  HERE----> ctNumVal.v = (value.ToString());
                }
            }
        }

where there is already an ToString(), but the patch created touch the other file where the value was not converted anymore (and at this point I don't know if the conversion with InvariantCulture makes some difference...

[PBrunot]

You're right, the bug is still here, I messed up.

I'm now writing a reproduction case starting from LineChart.cs. as follows:

foreach(var culture in CultureInfo.GetCultures(CultureTypes.NeutralCultures)) { CultureInfo.DefaultThreadCurrentCulture = culture; Console.WriteLine($"Testing with {culture.Name}..."); GenerateFile(); }
Then my test procedure is to open the generated Excel will with my italian-language Excel 365.
Problem is there in many, many cultures.

I'll prepare a fix to rollback the previous change which did not help.