使用Python创建与Excel兼容的CSV文件？

Question

我正在尝试使用Python创建一个真正与Excel兼容的CSV文件（如果有区别，我正在使用Excel 2007）。

这就是我想做的事情的样子

import csv

data = [ ['9-1', '9-2', '9-3'] ]

fd = open('test.csv', 'wb')
try:
    writer = csv.writer(fd, dialect='excel', quotechar='"', quoting=csv.QUOTE_ALL)
    writer.writerows(data)

finally:
    fd.close()

这将创建一个包含以下内容的csv文件：

"9-1","9-2","9-3"

当我在Excel中加载它时，我会得到这些列：

09-Jan  09-Feb  09-Mar

[Grrr，谢谢Excel。说真的，引号有什么意义？]

现在，我已经在其他地方阅读到，为了让Excel将字符串视为文字，必须在前面加上等号符号="像这样"。因此，我真正想要生成的是：

="9-1",="9-2",="9-3"

有没有办法使用Python的csv模块来实现这个？我猜想需要创建一个自定义方言，但是我找不到任何可以给我想要结果的变量。

- Lee Netherton

Answer 1

如果您的目标是在Excel上编写一个列表，您可以尝试下面的示例，其中";"和方言='excel-tab'属性使我们能够在列之间切换。

import csv

RESULTS = [
    ['val_col1;','val_col2;','val_col3']
]
resultFile = open("testExcel.csv",'wb')
resultWriter= csv.writer(resultFile, dialect='excel-tab')
resultWriter.writerows(RESULTS)

- kara

Answer 2

值得一提的是，Excel 2007版本甚至不兼容自己。如果您在一个空的电子表格中输入9-1，当您离开该单元格时，它会立即将其转换为1-Sep日期格式。如果您将单元格格式从常规更改为文本，则可以输入字符串文字而不改变，并将其保存为.csv文件，但是当您将同一文件读入带有常规格式的空白电子表格时，它会重新转换为日期格式。

Excel将文件保存为

9-1,9-2,9-3

去掉引号和等号符号。 即使在读取csv文件之前将单元格格式设置为文本也对我无效 - 它会导入日期（显示值为1-Sep，单元格值为9/1/2011），其中3个单元格处于自定义格式; 其余单元格转换为常规。

编辑：包含以下内容的csv文件：

"9-1",'9-2',='9-3',=''9-4''

显示在电子表格中的内容为：

1-Sep  '9-2'  ='9-3'  ="9-4"

所以=没有任何作用，但至少单引号将得到一个字符串字面量，并保留引号。在处理掉引号方面，需要更加熟悉Excel的人来帮助；“粘贴值”不会去除它们。

- Dave

---

是的，Excel很糟糕。我甚至不想使用它，但我的一些同事坚持使用它来查看数据。 - Lee Netherton

Answer 3

问题在于您正在使用Excel的CSV导入器将CSV文件导入Excel。这似乎有些违反直觉，但您不应该以此方式导入CSV文件。相反，将它们作为文本文件导入（即，使用 txt 扩展名）。然后，您将有机会指定每个列的类型，并可以正确地选择Excel中看起来像日期的列的文本。

如果您正在寻找一种即插即用的“这是一个可在Excel中打开的文件”方法，请尝试xlwt（而不是我最初建议的pyXLWriter），避免完全使用CSV。

- kindall

---

谢谢。我将文件扩展名更改为.txt，这强制Excel打开了文本导入向导。然而，我必须逐个选择每个列并选择将其导入为文本（没有办法选择多个列）。对于有数百列的表格来说，这对我来说并不是一个可用的解决方案 :-( - Lee Netherton

在第三步中，您可以使用Shift-Click选择多个列并同时更改它们。 - kindall

你也可以考虑使用xlwt作为另一种直接从Python编写Excel文件的方式，它可以在http://www.python-excel.org/上获得。在我的几个项目中，它表现得非常出色。 - TimothyAWiseman

-1代表pyXLWriter，就像挪威蓝鹦鹉一样。尽管sourceforge本周显示有12次下载，但你是不是造成了这种情况？？它已经不再维护（最后一次更新：6年前），只能写Excel 5/7 XLS文件，而且存在缺陷。 - John Machin

数百列？有多少个数百？如果超过256列，您需要使用类似pyopenxl的工具；否则请使用xlwt。 - John Machin

Answer 4

好的，谢谢你的所有帮助。我已经想出了一个大概的解决方案，所以我想在这里分享一下。

虽然不是最漂亮的解决方案，但对我的目的来说可以工作。不过要警告一下：如果字符串中有引号，它将会出错！

import csv
import re

data = [ ['9-1', '9-2', '9-3'] ]


def quoteCsvData(data):
    for x, row in enumerate(data):
        for y, item in enumerate(row):
             if data[x][y] != '':
                 data[x][y] = r'="' + data[x][y] + r'"'
    return data

def unquoteCsvData(data):
    for x, row in enumerate(data):
        for y, item in enumerate(row):
            if data[x][y] != '':
                m = re.match(r'="([^"]*)"',data[x][y])
                if m:
                    data[x][y] =  m.group(1)
    return data


fd = open('test2.csv', 'wb')
data = quoteCsvData(data)
try:
    writer = csv.writer(fd, delimiter=',',
                            quotechar='|', 
                            quoting=csv.QUOTE_NONE, 
                            escapechar='\\')
    writer.writerows(data)

finally:
    fd.close()

fd = open('test2.csv', 'rb')
try:
    reader = csv.reader(fd, delimiter=',', 
                            quotechar='|', 
                            quoting=csv.QUOTE_NONE, 
                            escapechar='\\')
    data = []
    for row in reader:
        data.append(row)

finally:
    fd.close()

data = unquoteCsvData(data)

print data

更新：删除了大量的 = "" 空单元格的垃圾邮件。

- Lee Netherton

Answer 5

data = [ ['9-1', '9-2', '9-3'] ]
fd = open('test.csv', 'wb')
for row in data:
    for x in row:
        fd.write('="{}",'.format(x))
    fd.write('\n')

- totowtwo

---

问题不在于让Python写文件，而是让Excel读取.csv文件时不会破坏数据。 - Dave

Answer 6

尝试：

dialect=csv.excel

就像

import csv
csv.excel 

• lineterminator '\r\n' 换行符为'\r\n'
• skipinitialspace False 不忽略字段前的空格
• quoting 0 不引用任何字段
• delimiter ',' 分隔符为逗号
• quotechar '"' 引用字符为双引号
• doublequote True 双引号表示字段内部的引用字符

对我来说，这是一种黑魔法，从未让我去阅读代码，但我做的一些小实验总是能够使Excel（2003）以以上格式良好地运行。

- lifeisstillgood

---

这与 dialect='excel' 做的事情完全相同，即不是 OP 想要的。 - Wooble