German data formats (dates, decimal separator etc.) are not supported

[KarasKsenia]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows

Epplus version

7.1.3

Spreadsheet application

Excel

Description

Hi everyone,

it seems like this issue has a lot in common with the https://github.com/EPPlusSoftware/EPPlus/issues/1483, but has more different formats, that are not supported yet (such as "dd.mm.yyyy" for German documents instead of "mm-dd-yy" for English ones). I used this test to ensure that the most common German specific data formats are supported:

[DataTestMethod]
[DataRow(2, "General")]
[DataRow(3, "0")]
[DataRow(4, "0.00")]
[DataRow(5, "#,##0")]
[DataRow(6, "#,##0.00")]
[DataRow(7, "#,##0 _€;-#,##0 _€")]
[DataRow(8, "#,##0 _€;[Red]-#,##0 _€")]
[DataRow(9, "#,##0.00 _€;-#,##0.00 _€")]
[DataRow(10, "#,##0.00 _€;[Red]-#,##0.00 _€")]
[DataRow(11, "#,##0\\ \"€\";\\-#,##0\\ \"€\"")]
[DataRow(12, "#,##0\\ \"€\";[Red]\\-#,##0\\ \"€\"")]
[DataRow(13, "#,##0.00\\ \"€\";\\-#,##0.00\\ \"€\"")]
[DataRow(14, "#,##0.00\\ \"€\";[Red]\\-#,##0.00\\ \"€\"")]
[DataRow(15, "0%")]
[DataRow(16, "0.00%")]
[DataRow(17, "0.00E+00")]
[DataRow(18, "##0.0E+0")]
[DataRow(19, "# ?/?")]
[DataRow(20, "# ??/??")]
[DataRow(21, "dd.mm.yyyy")]
[DataRow(22, "dd. mm yy")]
[DataRow(23, "dd. mmm")]
[DataRow(24, "mmm yy")]
[DataRow(25, "h:mm AM/PM")]
[DataRow(26, "h:mm:ss AM/PM")]
[DataRow(27, "hh:mm")]
[DataRow(28, "hh:mm:ss")]
[DataRow(29, "dd.mm.yyyy hh:mm")]
[DataRow(30, "mm:ss")]
[DataRow(31, "mm:ss.0")]
[DataRow(32, "@")]
[DataRow(33, "[h]:mm:ss")]
[DataRow(34, "_-* #,##0\\ \"€\"_-;\\-* #,##0\\ \"€\"_-;_-* \"-\"\\ \"€\"_-;_-@_-")]
[DataRow(35, "_-* #,##0\\ _€_-;\\-* #,##0\\ _€_-;_-* \"-\"\\ _€_-;_-@_-")]
[DataRow(36, "_-* #,##0.00\\ \"€\"_-;\\-* #,##0.00\\ \"€\"_-;_-* \"-\"??\\ \"€\"_-;_-@_-")]
[DataRow(37, "_-* #,##0.00\\ _€_-;\\-* #,##0.00\\ _€_-;_-* \"-\"??\\ _€_-;_-@_-")]
[DataRow(38, "mmm\\ yyyy")]
[DataRow(39, "[$-407]dddd\\,\\ d/\\ mmmm\\ yyyy")]
public void German_built_in_number_format(int cellRow, string expectedFormat)
{
    // Arrange
    Thread.CurrentThread.CurrentCulture = new CultureInfo("de-DE");
    var exlPackage = new ExcelPackage(GetTestStream("GermanBuildInNumberFormat.xlsx"));
    var ws = exlPackage.Workbook.Worksheets[0];

    // Act
    var excelFormatString = ws.Cells[cellRow, 1].Style?.Numberformat?.Format;

    // Assert
    Assert.AreEqual(expectedFormat, excelFormatString);
}

I also created the PR https://github.com/EPPlusSoftware/EPPlus/pull/1491 with the possible fix for this issue. The main changes are new methods to get the culture specific data formats in ExcelNumberFormatXml.cs:

[swmal]

Hi @KarasKsenia The build for #1491 didn't pass since there are failing tests (Error message: "AppVeyor was unable to build non-mergeable pull request"). If you can fix this we will have a look at it to get a better understanding of this PR/Feature request.

[KarasKsenia]

Hello, I resolved all merge conflicts, but AppVeyor build fails, and there is no specific info in the build log, why did it fail. Can you please look into it and overall check the problem I described above?

[oharabati]

Hi @swmal

here is the unit test, which reproduces the error. In the places where the unit test asserts fail, you can see that the wrong cell format is identified. As a result, the values in the cells do not match the original numbers in the attached Excel file, e.g., with time and date formats. The Excel test file is attached below. GermanBuildInNumberFormat.xlsx

[TestMethod]
        public void Amana_issue_german_built_in_number_format()
        {
#if Core
            var dir = AppContext.BaseDirectory;
            dir = Directory.GetParent(dir).Parent.Parent.Parent.FullName;
#else
            var dir = AppDomain.CurrentDomain.BaseDirectory;
#endif
            using var exlPackage = new ExcelPackage(new FileInfo(Path.Combine(dir, "Workbooks", "GermanBuildInNumberFormat.xlsx")));

            SwitchToCulture("de-DE");
            exlPackage.Workbook.Calculate();
            var ws = exlPackage.Workbook.Worksheets[0];

            Assert.AreEqual("123456789,1", ws.Cells["A2"].Text); // actual "123456789,123456"
            Assert.AreEqual("123456789", ws.Cells["A3"].Text);
            Assert.AreEqual("123456789,12", ws.Cells["A4"].Text);
            Assert.AreEqual("123.456.789", ws.Cells["A5"].Text);
            Assert.AreEqual("123.456.789,12", ws.Cells["A6"].Text);
            Assert.AreEqual("123.456.789,12", ws.Cells["A9"].Text);
            Assert.AreEqual("123.456.789,12", ws.Cells["A10"].Text);
            Assert.AreEqual("123.456.789 €", ws.Cells["A11"].Text);
            Assert.AreEqual("123.456.789 €", ws.Cells["A12"].Text);
            Assert.AreEqual("123.456.789,12 €", ws.Cells["A13"].Text);
            Assert.AreEqual("123.456.789,12 €", ws.Cells["A14"].Text);
            Assert.AreEqual("12345678912%", ws.Cells["A15"].Text);
            Assert.AreEqual("12345678912,35%", ws.Cells["A16"].Text); 
            Assert.AreEqual("1,23E+08", ws.Cells["A17"].Text);
            Assert.AreEqual("123,5E+6", ws.Cells["A18"].Text); // actual "123456789,1"
            Assert.AreEqual("123456789 1/8", ws.Cells["A19"].Text);
            Assert.AreEqual("123456789 10/81", ws.Cells["A20"].Text);
            Assert.AreEqual("29.03.2018", ws.Cells["A21"].Text);
            Assert.AreEqual("29. Mrz 18", ws.Cells["A22"].Text); // actual "29-März-18"
            Assert.AreEqual("29. Mrz", ws.Cells["A23"].Text); // actual "29-März"
            Assert.AreEqual("Mrz 18", ws.Cells["A24"].Text); // actual "Mär-18"
            Assert.AreEqual("10:45 AM", ws.Cells["A25"].Text); // actual "10:45"
            Assert.AreEqual("29. Mrz", ws.Cells["A26"].Text); // actual "10:45:00" 
            Assert.AreEqual("10:45", ws.Cells["A27"].Text);
            Assert.AreEqual("10:45:00", ws.Cells["A28"].Text);
            Assert.AreEqual("29.03.2019 10:45", ws.Cells["A29"].Text); // actual "3.29.19 10:45"
            Assert.AreEqual("45:00", ws.Cells["A30"].Text); // actual "03:59"
            Assert.AreEqual("45:00,0", ws.Cells["A31"].Text); // actual "0359.0"
            Assert.AreEqual("43555,48958", ws.Cells["A32"].Text); // actual "43555,4895832755"
            Assert.AreEqual("1045332:45:00", ws.Cells["A33"].Text); // actual "12:03:59"
            Assert.AreEqual("10:45:00", ws.Cells["A35"].Text); // actual "123.456.789"
            Assert.AreEqual("Mrz 2019", ws.Cells["A38"].Text); // actual "Mär 2019"
            Assert.AreEqual("Samstag, 30. März 2019", ws.Cells["A39"].Text);

            Assert.AreEqual("-123.456.789,12", ws.Cells["B9"].Text); // actual "(123.456.789,12)"
            Assert.AreEqual("-123.456.789,12", ws.Cells["B10"].Text); // actual "-123.456.789 €"
            Assert.AreEqual("-1,23E+08", ws.Cells["B17"].Text);
            Assert.AreEqual("-123,5E+6", ws.Cells["B18"].Text); //actual "-123456789,1"
            Assert.AreEqual("-123456789 1/8", ws.Cells["B19"].Text);   //actual: "--123456789 1/" 
            Assert.AreEqual("-123456789 10/81", ws.Cells["B20"].Text); //actual: "--123456789  1/"  

            Assert.AreEqual("0", ws.Cells["C2"].Text);
            Assert.AreEqual("0", ws.Cells["C3"].Text);
            Assert.AreEqual("0,00", ws.Cells["C4"].Text);
            Assert.AreEqual("0,00", ws.Cells["C9"].Text);
            Assert.AreEqual("0,00 €", ws.Cells["C11"].Text);
            Assert.AreEqual("0,00 €", ws.Cells["C13"].Text);
            Assert.AreEqual("0,00 €", ws.Cells["C14"].Text);
            Assert.AreEqual("0%", ws.Cells["C15"].Text);
            Assert.AreEqual("0,00%", ws.Cells["C16"].Text);
            Assert.AreEqual("0,00E+00", ws.Cells["C17"].Text);
            Assert.AreEqual("000,0E+0", ws.Cells["C18"].Text); // actual "0,0"

            Assert.AreEqual("- €", ws.Cells["C34"].Text);
            Assert.AreEqual("- ", ws.Cells["C35"].Text);
            Assert.AreEqual("- €", ws.Cells["C36"].Text);
            Assert.AreEqual("- ", ws.Cells["C37"].Text);
        }

[JanKallman]

Just an update. I am working with this issue and have found several bugs and issues with localizations. I will try to fix the bugs and I will add a new property so you can add your own format codes for the build-in number formats per culture. I will also add a call-back function from the ExcelRangeBase.Text property, so you can manipulate any number format.

[JanKallman]

See update in EPPlus 7.2.1

[oharabati]

See update in EPPlus 7.2.1

@JanKallman thank you very much for fixing !!