Generated extra line breaks

[H0LIXI]

Hello, I found that after fix this issue [https://github.com//issues/1655], another issue appeared.

When the newline character in the previous file was x000D\r\n, opening it in Excel would show only one line break.
After saving with version Epp.7.5.0, x000D\r\n becomes \r\r\n, and opening it in Excel results in two line breaks. After saving in Excel, it becomes \r\n\r\n.

If it's a newly created file, there might not be an issue, but if it's a file that was handled by an older version, upgrading the plugin might cause an increase in newline characters.

I'm not sure if directly removing \r when saving the text, or adjusting \r...\r\n to \r\n, is a feasible solution.

You can use this file to test. Observe the changes in cell A3.
Test-rrn.xlsx

// Read any cell and then save.
using (var excel = new ExcelPackage(newPath))
{
    var sheet = excel.Workbook.Worksheets.FirstOrDefault();
    var excelName = sheet.Name;

    for (var i = 1; i <= 2; i++)
    {
        for (var j = 1; j <= sheet.Dimension.Columns; j++)
        {
            var cellVal = sheet.Cells[i, j].Text;
        }
    }

    excel.Save();
}

[JanKallman]

Thanks for reporting. I'll have a look at it.

[JanKallman]

I'm not sure why Excel makes a difference on x000D\r\n and \r\r\n. This seems weird.

[H0LIXI]

I tried using Microsoft's Microsoft.Office.Interop.Excel library to read the cell, and x000D\r\n was read as \r\n. It seems that x000D was ignored.

[MatthewACollier]

I am having a similar issue. When I Save the file using EPPlus, Some cells are getting extra returns. These are not cells that I am editing with EPPlus at all. They are in a different sheet. I am using version 7.2.2.

[lxxxl]

having same problem, untouched cell after save will be changed. Line break in cell value is some how not the same as previous. I'm making a excel merging tool, so it is matter to preserve cell value exactly the same when untouched

[JanKallman]

I investigated this further and it seems that Excel converts and single CR character, CFLF or LF to LF on when loading the shared strings. However if you encode the CR as x000D, Excel will not replace the CR with LF.
The test illustrates the issue:

        [TestMethod]
	public void i1709()
	{
            using (var p = OpenPackage("i1709-2.xlsx",true))
            {
                var ws = p.Workbook.Worksheets.Add("Sheet1");
		ws.Cells["A1"].Value = "row 1_x000d__x000d_col 1";
                ws.Cells["A2"].Value = "row 2\r\rcol 1";
                ws.Cells["A3"].Value = "row 3\r\n\r\ncol 1";
                ws.Cells["A4"].Value = "row 4\n\ncol 1";
                ws.Cells["A5"].Value = "row 5_x000d_\ncol 1";
                ws.Cells["A6"].Value = "row 6_x000d__x000a_col 1";

                ws.Cells["A1:A6"].Style.WrapText = true;
		ws.Cells["B1:B6"].Formula = "=CODE(MID(A1,5,1))";
                ws.Cells["C1:C6"].Formula = "=CODE(MID(A1,6,1))";
                ws.Cells["D1:D6"].Formula = "=CODE(MID(A1,7,1))";
                ws.Cells["E1:E6"].Formula = "=CODE(MID(A1,8,1))";
                SaveAndCleanup(p);
            }
        }

[provegard]

I just encountered a possibly related problem after upgrading to 7.5.2 from 6.2.8. I have a failing test that outputs:

Step #5 - "build-and-test":   Error Message:
Step #5 - "build-and-test":      Expected string length 13 but was 12. Strings differ at index 5.
Step #5 - "build-and-test":   Expected: "hello\r\ndetail"
Step #5 - "build-and-test":   But was:  "hello\ndetail"
Step #5 - "build-and-test":   -----------------^

I looked at the tests in b883ee8, but it's not clear to me what they test, so I don't know if that commit fixes this problem.

Maybe it's a different problem that should go into a new issue?