Pandas.to_excel数据覆盖及应对方法

Posted by Weitu Blog on July 24, 2019

使用Pandas保存数据到excel中时，会强制新建文档。在某些场景下，你可能会为此抓狂，比如你基于输出的数据，已经在excel里面做好了完备的透视表，你已经定义好了完备的数据分析逻辑，只待刷新一下数据，更新结果即可。这时，如果旧文件被覆盖，你在excel上做的所有工作，都被冲洗掉了。

pandas.to_excel

  1. df.to_excel 
    DataFrame.to_excel(self, excel_writer, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None, inf_rep='inf', verbose=True, freeze_panes=None)[source]
  2. 使用ExcelWriter一次性写入多个sheet

这个只是在to_excel的基础上实现了一次性写入多个sheet的功能而已，还是会覆盖已有的excel文档

替代方法

使用openpyxl引擎，可以优雅的实现sheet的追加写入

使用xlrd可以实现同一个sheet里面，内容的追加，xlrd可以对cell进行操作

综合场景

如果我们既需要覆盖excel文档中的某一个sheet，又要保留其他的sheet，当然可以使用xlrd去重新写，但是个人总感觉xlrd不优雅，更新的方法如下

import pandas as pd
from openpyxl import load_workbook

df = pd.DataFrame([['a', 'b'], ['c', 'd']],
                    index=['row 1', 'row 2'],
                    columns=['col 1', 'col 2'])

writer = pd.ExcelWriter(r"C:\Users\hp\Documents\output.xlsx")
df.to_excel(writer, sheet_name="to_excel_sheet")
df.to_excel(writer, sheet_name="writer_sheet")
writer.close()


writer_pyxl = pd.ExcelWriter(r"C:\Users\hp\Documents\output.xlsx", engine="openpyxl")
exl = load_workbook(writer_pyxl.path)

idx = exl.sheetnames.index("to_excel_sheet") # 获得待移除的sheet的索引
exl.remove(exl.worksheets[idx])               # 移除该sheet

writer_pyxl.book = exl
df.to_excel(writer_pyxl, "to_excel_sheet", index=None)
writer_pyxl.close()

code

import pandas as pd


# pandas.to_excel

df = pd.DataFrame([['a', 'b'], ['c', 'd']],
                    index=['row 1', 'row 2'],
                    columns=['col 1', 'col 2'])
df.to_excel(r"C:\Users\hp\Documents\output.xlsx", sheet_name="to_excel_sheet")



# 同样的，ExcelWriter仅仅可以实现一次性写入多个sheet，依然会将已经存在的文件覆盖
df2 = df
df3 = df
writer = pd.ExcelWriter(r"C:\Users\hp\Documents\output.xlsx")
df2.to_excel(writer, sheet_name="writer_sheet_df2")
df3.to_excel(writer, sheet_name="writer_sheet_df3")
writer.close()


# openpyxl, 可以实现旧文件不被覆盖
from openpyxl import load_workbook

writer_pyxl = pd.ExcelWriter(r"C:\Users\hp\Documents\output.xlsx", engine="openpyxl")

df4 = df
exl = load_workbook(writer_pyxl.path)
writer_pyxl.book = exl
df4.to_excel(writer_pyxl, sheet_name="writer_pyxl", index=None)
writer_pyxl.close()


# xlrd\xlwt
import xlwt
import xlrd
from xlutils.copy import copy

df5 = df

# xlwt， 覆盖， 不支持 xlsx
ww = xlwt.Workbook()

ns1 = ww.add_sheet("xlwt_sheet")
ns1.write(0, 0, "sample_Var")

ww.save(r"C:\Users\hp\Documents\output.xls")


# xlrd， 读入更新， 不支持 xlsx
wrd = xlrd.open_workbook(r"C:\Users\hp\Documents\output.xls")

# copy，这一步是实现append的关键
nwrd = copy(wrd)

nsheet = nwrd.add_sheet("xlrd_sheet")
nsheet.write(0, 0, "sample_Var")

# 将DataFrame写入excel
nsheet2 = nwrd.add_sheet("xlrd_sheet_df")
for i in range(df5.shape[0]):
    for j in range(0, df5.shape[1]):
        
        # 写入column
        if i==0:
            nsheet2.write(i, j+1, df5.columns[j])
            
        # 写入index
        if j==0:
            nsheet2.write(i+1, j, df5.index[i])
        
        nsheet2.write(i+1, j+1, df5.iloc[i, j])



# 使用xlrd还可以实现在sheet尾部append内容
sht = nwrd.get_sheet("xlrd_sheet") # 根据sheet名，获取sheet

row = len(sht.rows) # 获取原有sheet的最后一行行数
sht.write(row, 0, "add value1")
sht.write(row, 1, "add value2")

nwrd.save(r"C:\Users\hp\Documents\output.xls")
FEATURED TAGS
SAS
FRIENDS