.Net web api download csv file (Excel separator)

user_13531053 0
Viewed 222

I have a controller endpoint from which I want to generate a csv file and download it.

Currently I am using nuget CsvHelper and my code is like this:

var cc = new CsvConfiguration(new System.Globalization.CultureInfo("sl-SI"));
using (var ms = new MemoryStream())
{
using (var sw = new StreamWriter(stream: ms, encoding: new UTF8Encoding(true)))
{
    using (var cw = new CsvWriter(sw, cc))
    {
        cw.WriteRecords(ListOfReports);
    }// The stream gets flushed here.
    return File(ms.ToArray(), "text/csv", $"{docNumber.Trim()}_{docType}.csv");
}
}

It generated csv pretty nice, but the problem was, if I opened it in Excel, whole row was in the first column and was not splitted.

I added this part:

cw.WriteField("sep=,", false);
cw.NextRecord();

Before cw.WriteRecords(ListOfReports);, which made it work in Excel, but if I open it in Notepad, there is a sep=, in my first row.

I noticed there is a difference in CultureInfo, If I set "sl-SI" it will work properly on Slovenian windows (separator will be ;), if I set "en-US" it will work on English Windows (separator ,). But what do i need to do to work on any Culture?

Does anyone has any idea how to fix this so it will work properly in Excel and any other text editor?

webapi .net-core api .net c#
1 Answers

This is effectively the same as this SuperUser question, but it appears you want a programming-oriented solution rather than a user-oriented one.

The problem is fundamentally that Excel is really bad at dealing with CSV files, especially when taking non-US cultures into account. My suggestion would be to allow users to download a real Excel file using a library like DocumentFormat.OpenXml.

If you have two separate use cases for your downloads (i.e. some users who open the file in notepad or consume it with software that reads CSV, and others who open the file in Excel), give the users separate options to download in CSV or Excel.

user_1209550
Related