Search code examples
asp.net-core.net-coreepplus

EPPlus: CSV to Excel - Result all in one line

I am experiencing a weird problem, when exporting an excel file on Linux using EPPlus version 4.5.3.3.

The problem is, that when generating CSV content and using that content to generate the excel file, the whole result ends up in the first row of the excel-sheet (using Libre-Office on Ubuntu). This does only happen on Linux (Ubuntu and Debian tested), but not on Windows. Another weird thing is, when I don't use CSV content to generate the excel file, but a collection, it does work with no problems. Sadly, that way, there is no way to use custom headers.

Code for generating the CSV:

private static string GenerateCsv()
{
    var header = $"{nameof(Menu.Main)}{Delimiter}{nameof(Menu.Desert)}";
    var builder = new StringBuilder();
    builder.AppendLine(header);

    foreach (var menu in Menus)
    {
        var row = $"{menu.Main}{Delimiter}{menu.Desert}";
        builder.AppendLine(row);
    }

    return builder.ToString();
}

Code for generating the excel file:

var csv = GenerateCsv();

using var pck = new ExcelPackage();

var ws = pck.Workbook.Worksheets.Add("Sheet1");

var format = new ExcelTextFormat
{
    DataTypes = new[]
    {
        eDataTypes.String, eDataTypes.String
    },
    Delimiter = Delimiter.First(),
    Encoding = new UTF8Encoding(),
};

using var range = ws.Cells[1, 1];

range.LoadFromText(csv, format);

var bytes = pck.GetAsByteArray();

return this.File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "menus.xlsx", true);

The full sample can be cloned from this github-repository. If you are executing it on windows, you will see that it works just fine.

I really tried a lot of things and also tried changing the delimiter from ; to ,, introduced a text-qualifier and what not, but can't seem to make it work.

Any help would be greatly appreciated!

Solution

  • The method StringBuilder.AppendLine in CreateCsv terminates each line
    with the default line terminator of the OS which is on:

    • Windows \r\n
    • Unix \n

    EPPlus doesn't respect the default line terminator of the OS and uses \r\n as a default value.
    That's why it works on Windows.

    So you can set the ExcelTextFormat.EOL property to Environment.NewLine to use the default line terminator of the OS.

    var format = new ExcelTextFormat
{
    DataTypes = new[]
    {
        eDataTypes.String, eDataTypes.String
    },
    Delimiter = Delimiter.First(),
    Encoding = new UTF8Encoding(),
    EOL = Environment.NewLine
};