Built in number formats are different for differen languages

[ihorbach]

If a cell uses some built in number format, this will not be specified explicitelly. It is just the index of the format that is given. A parsing tool (like EPPlus) have to be aware of the built in formats. But the problem is, these are different for different cultures. Did you know?

I miss german built in formats, so I will create a pull request to add them. But generally you'd better consider supporting all other cultrues, that excel supports

Here is one example, how you can reproduce the problem

var testThread = new Thread(() =>
{
	Assert.AreEqual("#,##0 _€;-#,##0 _€", ExcelNumberFormat.GetFromBuildInFromID(37));
});

testThread.CurrentCulture = new CultureInfo("de-DE");

testThread.Start();

[ihorbach]

created a pull request

#775

[ihorbach]

Hello Jan,

you wrote:

EPPlus used the formats as described in 18.8.30 in ecma-376 - https://www.ecma-international.org/publications-and-standards/standards/ecma-376/ (part 1), so this is by design. These formats are not localized for all I can see.

Yes, you are right. According to ECMA specification these formats should not be localizable. But Microsoft seems not to hold 100% on ECMA specification and makes them localizable.

At the end of the day those excel files, that we create or change with EPPlus are thought to be opened with Microsoft Excel. So we need to solve the the dilemma "either correct due to specification, or practically usefull".

What if I change this pull request in the following way:

1. We introduce a new setting in the ExcelWorkbook named UseLocalizedBuiltInNumberFormats with default value of false
2. When this setting is true ExcelNumberFormat.GetFromBuildInFromID() will return a localized setting

How do you find this?

[JanKallman]

Hello Ivan,
I can't see that Excel add localized formats for these build-in styles. Swiching to german regional settings and using this tests does not give a localized format...

            using (var p = OpenPackage("BuildinStylesRegional.xlsx", true))
            {
                var ws = p.Workbook.Worksheets.Add("Sheet1");
                ws.Cells["A1:A4"].FillNumber(1);
                ws.Cells["A1"].Style.Numberformat.Format = "#,##0 ;(#,##0)"; //Build-in 37
                ws.Cells["A2"].Style.Numberformat.Format = "#,##0 ;[Red](#,##0)";  //Build-in 38
                ws.Cells["A3"].Style.Numberformat.Format = "#,##0.00;(#,##0.00)"; //Build-in 39
                ws.Cells["A4"].Style.Numberformat.Format = "#,##0.00;[Red](#,##0.00)"; //Build-in 40
                SaveAndCleanup(p);
            }

Can you provide a test case so I can understand the issue?

[ashahabov]

Not sure if this is related to this issue, but check out this code:

format.xlsx

var book = new ExcelPackage(new FileInfo(@"C:\temp\format.xlsx"));
var sheet = book.Workbook.Worksheets[0];


var format = sheet.Cells[1, 1].Style.Numberformat.Format; // #,##0.00;(#,##0.00)

So, format is #,##0.00;(#,##0.00), but Excel returns #.##0,00;-#.##0,00 for this cell

This is a bug, right? EPPlus should also return #.##0,00;-#.##0,00.

[ihorbach]

Hello Ivan, I can't see that Excel add localized formats for these build-in styles. Swiching to german regional settings and using this tests does not give a localized format...

Can you provide a test case so I can understand the issue?

Hi Jan, sorry for coming back only now. There are some formats so called formats with asterisk. They always depend on localization

Just compare these two functions from my pull request:

It might be done in some more elegant way, but I am not sure how