创建csv文件时，Excel显示双引号

I've created a .csv file using C# and when I open this file in Excel, it shows the double quotes in the cells.

我使用c#创建了一个.csv文件，当我在Excel中打开这个文件时，它会显示单元格中的双引号。

When I open the csv in Notepad++ I see that are quotes are there and everything looks right to me. Here's a part of my csv:

当我在Notepad++中打开csv时，我看到里面有引号，所有的东西看起来都很对。这是我的csv文件的一部分:

"CertificaatNummer", "Data", "Inleg", "Getekend", "Ingangsdatum", "Betaalmethode", "Status", "Bonus", "Aankoopkoers", "Korting", "Garantiewaarde", "Betaalde termijnen", "Inleg nominaal", "Tweede naam", "Recht op bonus maand", "Begunstigde", "Product", "Incasserekening", "Uitbetaalrekening"
"126136", "some data with, a comma", "118.34", "True", "28-1-1999 00:00:00", "Cash", "C02", "0.00", "531,940", "0,000", "0.00", "0", "0.00", "", "False", "P.H. Bloemink", "Cash-Click", "", "260952095"
"137190", "other data", "0.00", "True", "23-12-1999 00:00:00", "Cash", "C02", "0.00", "660,620", "0,000", "0,00"

When I open this file in Excel it treats the comma in some data with, a comma as a new column. So I get "Some data with in one cell and a comma" in antother cell. As you see, Excel doesn't care about the double quotes.

当我在Excel中打开这个文件时，它会用逗号作为新列。所以我在antother单元格中得到了一些数据，一个逗号。如你所见，Excel不关心双引号。

Here's what my code looks like (simplified):

下面是我的代码看起来(简化):

var content = new List<string>();

// Add headers
content.Add("\"Header 1\", \"Header 2\", \"Header 3\", \"Header 4\"");

// Add content
content.Add("\"123456\", \"some data with, a comma\", \"118.34\", \"True\"");
// etc..

// I let a function create my file content
using (var stream = new MemoryStream())
using (var writer = new StreamWriter(stream))
{
    foreach (var line in this.content)
    {
        writer.WriteLine(line);
    }

    writer.Flush();
    return stream.ToArray();
}

// finally I return the file content to the browser with 
// HttpContext.Response.AddHeader("content-disposition", "file;name=" + fileName);

The question is, which part of my code do I have to edit so Excel will display my file properly? I rather want to edit my code then executing some tricks in Excel.

问题是，我需要编辑代码的哪一部分才能让Excel正确显示我的文件?我宁愿编辑我的代码，然后在Excel中执行一些技巧。

3 个解决方案

#1

The problem seems to be spaces after the comma separators.
Instead of

问题似乎是逗号分隔符后面的空格。而不是

"some, text","Other text"

your code is generating:

你的代码生成:

"some, text",<space>"Other text"

So the Excel CSV importer is getting flummoxed. Remove those extra spaces and you should be OK.

Excel CSV导入器变得混乱了。去掉那些多余的空格，你应该没问题。

#2

Some time ago, I found out that Excel likes its CSV files UTF-16LE encoded, with tabs (ASCII code 9) as delimiters. It even accepts (and removes) double quotes around the values.

不久前，我发现Excel喜欢用UTF-16LE编码的CSV文件，标签(ASCII码9)作为分隔符。它甚至接受(并删除)值周围的双引号。

#3

-1

Just reflect comma with "\" symbol.

只要用“\”符号表示逗号就可以了。

Or if you doing it programmatically just fire Replace(",","\,") method

或者，如果您以编程的方式执行此操作，只需触发Replace(“，”，“\，”)方法

#1