为Excel生成CSV文件，如何在值中包含换行符 [英] Generating CSV file for Excel, how to have a newline inside a value

问题描述

我需要为Excel生成一个文件，该文件中的一些值包含多行。

I need to generate a file for Excel, some of the values in this file contain multiple lines.

还有非英文文本，因此文件必须是Unicode。

there's also non-English text in there, so the file has to be Unicode.

我现在生成的文件现在看起来像这样：（在UTF8中，非英语文本混合并且有很多行） p>

The file I'm generating now looks like this: (in UTF8, with non English text mixed in and with a lot of lines)

Header1,Header2,Header3
Value1,Value2,"Value3 Line1
Value3 Line2"

请注意，多行值用双引号括起来，每天都有一个正常的换行符。

Note the multi-line value is enclosed in double quotes, with a normal everyday newline in it.

根据我在网上发现这应该是工作，但它不，至少不赢得Excel 2007和UTF8文件，Excel将第三行作为第二行数据不作为第一个数据行的第二行。

According to what I found on the web this supposed to work, but it doesn't, at least not win Excel 2007 and UTF8 files, Excel treats the 3rd line as the second row of data not as the second line of the first data row.

这必须在我的客户机器上运行，我无法控制他们的Excel版本，所以我需要一个解决方案

This has to run on my customer's machines and I have no control over their version of Excel, so I need a solution that will work with Excel 2000 and later.

感谢

编辑：我解决了我的问题， CSV选项，一个用于Excel（Unicode，制表符分隔，字段中没有换行符）和一个用于世界其他地方（UTF8，标准CSV）。

I "solved" my problem by having two CSV options, one for Excel (Unicode, tab separated, no newlines in fields) and one for the rest of the world (UTF8, standard CSV).

正在寻找，但至少它的工作（到目前为止）

Not what I was looking for but at least it works (so far)

推荐答案

你应该在字段的开始只有空格字符空格字符是数据的一部分。 Excel不会去除前导空格。您将在标题和数据字段中收到不必要的空格。更糟糕的是，应该保护第三列中的换行符的将被忽略，因为它不在字段的开头。

You should have space characters at the start of fields ONLY where the space characters are part of the data. Excel will not strip off leading spaces. You will get unwanted spaces in your headings and data fields. Worse, the " that should be "protecting" that line-break in the third column will be ignored because it is not at the start of the field.

如果文件中有非ASCII字符（以UTF-8编码），则应该有一个UTF-8 BOM（3字节， hex EF BB BF ），否则Excel将根据您的语言环境的默认编码（例如cp1252）而不是utf-8解释数据，并且您的非ASCII字符将被删除。

If you have non-ASCII characters (encoded in UTF-8) in the file, you should have a UTF-8 BOM (3 bytes, hex EF BB BF) at the start of the file. Otherwise Excel will interpret the data according to your locale's default encoding (e.g. cp1252) instead of utf-8, and your non-ASCII characters will be trashed.

以下注释适用于Excel 2003,2007和2013;未在Excel 2000上测试

如果您在Windows资源管理器中通过双击其名称打开文件，一切都可以正常。

如果从Excel中打开文件，结果不同：

If you open it from within Excel, the results vary:

1. 您在文件中只有ASCII字符（且没有BOM）：工作。


2. 您在文件中具有非ASCII字符（以UTF-8编码），开头为UTF-8 BOM：它识别您的数据以UTF-8编码，但它忽略csv扩展名并丢弃进入文本导入不是向导，不幸的是，你得到了换行问题。

1. You have only ASCII characters in the file (and no BOM): works.
2. You have non-ASCII characters (encoded in UTF-8) in the file, with a UTF-8 BOM at the start: it recognises that your data is encoded in UTF-8 but it ignores the csv extension and drops you into the Text Import not-a-Wizard, unfortunately with the result that you get the line-break problem.

选项包括：

1. 训练用户不要在Excel中打开文件： - （


2. 考虑直接编写XLS文件...有可用于在Python / Perl / PHP / .NET / etc中执行的包/库

这篇关于为Excel生成CSV文件，如何在值中包含换行符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！