这几天在用 Python3 研究一个爬虫,最后一个需求是把爬下来的 20 + 个 csv 文件整合到一个 excel 表里的不同 sheets。

初版的核心代码如下:

1
2
3
4
5
6
7
8
while year <= 2018:
csvPath = sys.path[0] + '/result/%d.csv' % year
excelPath = sys.path[0] + '/result.xlsx'
csvReader = pandas.read\_csv(csvPath, encoding='utf\_8_sig')
excelWriter = pandas.ExcelWriter(excelPath)
print("正在将 %d 年的 %d 条数据转换为 xlsx..." % (year, countThis))
csvReader.to\_excel(excelWriter, sheet\_name=str(year))
year = year + 1

奇怪的是使用这个方法,每次 to_excel 之后,result.xlsx 中都只会存储一年的数据,只会存在一个 sheet,之前的所有数据都会被覆盖。

通过查询官方文档(pandas.DataFrame.to_excel)和一个 github 上跨越了 5 年的 issue(Allow ExcelWriter() to add sheets to existing workbook)得知 pandas 库的 ExcelWriter 缺失了一个 mode='a' 的 append 模式,所以在这种情况下每次 to_excel () 都会直接新建一个文件写入而无视之前的数据。

解决方案是使用 openpyxl engine 来打开 ExcelWriter,用 openpyxl 的 load_workbook 方法将之前已经存在的数据加载进 ExcelWriter.book 里。修改后的核心代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 依赖 openpyxl 库
from openpyxl import load_workbook

while year <= 2018:
csvPath = sys.path[0] + '/result/%d.csv' % year
excelPath = sys.path[0] + '/result.xlsx'
csvReader = pandas.read\_csv(csvPath, encoding='utf\_8_sig')
# 增加 engine='openpyxl' 一栏
excelWriter = pandas.ExcelWriter(excelPath, engine='openpyxl'
# 使用 openpyxl 来把现有数据传递给excelWriter,使其在写入的时候保留原本数据
book = load_workbook(excelPath)
excelWriter.book = book

print("正在将 %d 年的 %d 条数据转换为 xlsx..." % (year, countThis))
csvReader.to\_excel(excelWriter, sheet\_name=str(year))
excelWriter.save()

如此存储的 excel 文件里就会有多个 sheets 了,每个 sheets 里都存储着一个 csv 里的全部数据。

需要注意的是这样做的效率非常低,因为这并不是真正的追加模式,而是在每一次创建 ExcelWriter 对象之后,先将现有的数据全部传入 ExcelWriter,再将新的数据连同旧的数据一同写入一个新的文件并覆盖。这就导致程序作了许多重复而无用的工作,所以我在处理这个任务的时候。最后的几个 10+m 的 csv 文件的平均耗时都在 300s 以上,如果还有后续任务的话,这个数字会一直增长下去。得到一个 80m 的 xlsx 总表耗费了接近一个小时的时间,这对于一些更大的任务来说是难以接受的。所以如果你需要处理的任务比较巨大,你可以脱离 pandas 库而使用 xlrd 和 xlwt 里的方法,会使运行效率优雅不少。(也许直接使用 excel 的 vba 宏也是个不错的选择?)