为 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 中，混合了非英文文本并带有很多行)

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 将第 3 行视为第二行数据而不是第二行第一个数据行.

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 版本，因此我需要一个适用于 Excel 2000 及更高版本的解决方案.

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 扩展名并丢弃您进入Text Import not-a-Wizard，不幸的是，您遇到了换行问题.

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屋！