Pandas写入Excel函数——to_excel 技术总结

Pandas作为Python数据分析的一个常用包，经常会与Excel交互。
最近经常使用pandas的to_excel函数，发现坑还不少。经常报错，覆盖写入，让人烦躁。甚至Run一遍后，excel文件里只剩一个sheet或者文件根本打不开。
经过两周使用，总算有点门道了。

下面是我对to_excel函数一些技术总结。

一、单个sheet写入：

import pandas as pd

df1 = pd.DataFrame({'One': [1, 2, 3]})
df1.to_excel('excel1.xlsx', sheet_name='Sheet1', index=False) # index false为不写入索引

excel1.xlsx 不存在的话，则会新建文件，再写入 Sheet1。 
excel1.xlsx 已存在的话，则会新建，写入，再覆盖。
所以无论 excel1.xlsx 是否存在，上述代码的结果是一样的。它的作用就是新建 excel1.xlsx（文件已存在则覆盖），写入 Sheet1。excel1.xlsx 中最后只有一个表 Sheet1。
 

当Pandas要写入多个sheet时，to_excel第一个参数excel_writer要选择ExcelWriter对象，不能是文件的路径。否则，就会覆盖写入。
ExcelWriter可以通过上下文管理器来执行，省去save()，优雅。

二、多个sheet写入到同一个Excel

import pandas as pd

df1 = pd.DataFrame({'One': [1, 2, 3]})
df2 = pd.DataFrame({'Two': [4, 5, 6]})

with pd.ExcelWriter('excel1.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)

Once a workbook has been saved it is not possible write further data without rewriting the whole workbook.
to_excel的Doc中有上面一句话，所以，ExcelWriter可以看作一个容器，一次性提交所有to_excel语句后再保存，从而避免覆盖写入。

三、新增sheet，不覆盖已存在的sheet

沿用上面的代码，excel1.xlsx 已存在，增加一个新的表 Sheet3。这里，ExcelWriter的参数mode='a'，模式改为新增，非写入（'w'）。
注意：这里模式的新增指的是sheet，不是对sheet的内容进行新增。

import pandas as pd

df3 = pd.DataFrame({'Three': [7, 8, 9]})
with pd.ExcelWriter('excel1.xlsx', mode='a') as writer:
    df3.to_excel(writer, sheet_name='Sheet3', index=False)

同样，新增两个sheet

import pandas as pd

df4 = pd.DataFrame({'Four': [11, 22, 33]})
df5 = pd.DataFrame({'Five': [55, 66, 77]})

with pd.ExcelWriter('excel1.xlsx', mode='a') as writer:
    df4.to_excel(writer, sheet_name='Sheet4', index=False)
    df5.to_excel(writer, sheet_name='Sheet5', index=False)

 

四、修改sheet中的内容，不覆盖已存在的sheet

沿用上面的代码，修改Sheet4、Sheet5。

import pandas as pd
from openpyxl import load_workbook

df41 = pd.DataFrame({'Four': [44, 55, 66]})
df51 = pd.DataFrame({'Five': [77, 88, 99]})

with pd.ExcelWriter('excel1.xlsx') as writer:
    book = load_workbook('excel1.xlsx')
    writer.book = book # 读取excel
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets) # 复制excel的所有表
    df41.to_excel(writer, sheet_name='Sheet4', index=False)
    df51.to_excel(writer, sheet_name='Sheet5', index=False)

  其实，这是一个折中的方案，先用openpyxl 读取到了excel1，载入excel1的内容到ExcelWriter中，再对Sheet4、Sheet5进行覆盖写入。
 

  Sheet5中追加数据。 to_excel的参数startrow、startcol为写入的起始行列。header为是否写入列名。

import pandas as pd
from openpyxl import load_workbook

df6 = pd.DataFrame({'Six': [11, 22, 33]})
df7 = pd.DataFrame({'SEVEN': ['AA', 'BB', 'CC']})

with pd.ExcelWriter('excel1.xlsx') as writer:
    book = load_workbook('excel1.xlsx')
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    df6.to_excel(writer, sheet_name='Sheet5', index=False, startrow=0, startcol=3)
    df7.to_excel(writer, sheet_name='Sheet5', index=False, header=False, startrow=4, startcol=0)

利用startrow、startcol两个参数，我们不仅可以追加数据，还可以用覆盖写入的方式去修改sheet的部分内容。比如将"ARE YOU OK?"写入Sheet5的Six列

import pandas as pd
from openpyxl import load_workbook

df8 = pd.DataFrame({'EIGHT': ['ARE', 'YOU', 'OK？']})
with pd.ExcelWriter('excel1.xlsx') as writer:
    book = load_workbook('excel1.xlsx')
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    df8.to_excel(writer, sheet_name='Sheet5', index=False, header=False, startrow=2, startcol=3)

总结：
to_excel是将pandas数据保存到Excel文件中的一个函数，从字面上看，它的功能是格式转化存储。它操作的基本单位是一个个的sheet以及sheet组成的excel文件。
虽然，通过先读取，再写入的方法可以实现单个sheet的内容追加、修改功能，但这是一个“笨方法”，不优雅。
当有这类对单个sheet的内容追加、修改功能需求时，可以直接使用openpyxl、xlwings等第三方包。to_excel的底层引擎也是这些包。

参考：
ExcelWrite Doc
to_excel Doc
Openpyxl Doc with Pandas