使用pd将生成的数据保存到已存在的xlsx文件时,原始状态下会将xlsx数据表全部覆盖,造成之前的数据全部被删除。实际工作中,我们常常需要把不同的数据存放在同一个xlsx文件中。以下方法可以实现需求。上代码:
import pandas as pd
from openpyxl import load_workbook
path = 'pdtest.xlsx'
mydata = pd.read_excel(path,sheet_name='sheet1',index_col='ID')
mydata['销售价格'] = mydata['库存价格']*mydata['调价系数']*0.9
#ndata = mydata['销售价格']
# 写入excel对应表
book = load_workbook(path)
writer=pd.ExcelWriter(path,engine='openpyxl')
writer.book = book
mydata.to_excel(excel_writer=writer,sheet_name='nsht')
writer.close()
有用户反映运行以上代码时,会出现警告提示
pdtoec.py:21: FutureWarning: Setting the `book` attribute is not part of the public API, usage can give unexpected or corrupted results and will be removed in a future version
writer.book = book
出现上述警告提示时,可以引入warnings库中的simplefilter 方法即可解决。