C# Response.Write CSV file not splits the cells

Question

I have this code:

    context.Response.ClearHeaders();
    context.Response.AddHeader("content-disposition", "attachment; filename=Clients.csv");
    context.Response.ClearContent();
    context.Response.ContentType = "application/ms-excel";
    context.Response.ContentEncoding = System.Text.Encoding.Unicode;
    context.Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
    context.Response.BufferOutput = false;
    context.Response.Buffer = false;

    foreach (var c in clients)
    {
        context.Response.Output.WriteLine(string.Format("{0},{1}", c.FirstName, c.LastName));
    }

The downloaded file looks fine, except the fact that all row cells is merged into one cell. I must use the Response as BinaryWrite because of Hebrew and Japan characters in the csv/xls content. How to write splitted cells csv/xls file with binary write?

Answer 1

To quote Wikipedia:

Microsoft Excel will open .csv files, but depending on the system's regional settings, it may expect a semicolon as a separator instead of a comma, since in some languages the comma is used as the decimal separator.

So although it is called Comma Separated Values, I do think you should give it a try using semicolons instead of commas.

I.e. use:

context.Response.Output.WriteLine(
    string.Format("{0};{1}", c.FirstName, c.LastName));

Answer 2

Best way is to sanitize your strings by wrapping the string with double quotes when there is any comma, the way excel does it while saving your csv file.