pandas python：将数据框更新到现有的Excel工作表而不覆盖同一工作表和其他工作表上的内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39049148/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
python: update dataframe to existing excel sheet without overwriting contents on the same sheet and other sheets
提问by Lisa
Struggling for this for hours so I decided to ask for help from experts here:
为此苦苦挣扎了几个小时，所以我决定在这里向专家寻求帮助：
I want to modify existing excel sheet without overwriting content. I have other sheets in this excel file and I don't want to impact other sheets.
我想在不覆盖内容的情况下修改现有的 Excel 工作表。我在这个 excel 文件中有其他工作表，我不想影响其他工作表。
I've created sample code, not sure how to add the second sheet that I want to keep though.
我已经创建了示例代码，但不确定如何添加我想保留的第二张纸。
t=pd.date_range('2004-01-31', freq='M', periods=4)
first=pd.DataFrame({'A':[1,1,1,1],
'B':[2,2,2,2]}, index=t)
first.index=first.index.strftime('%Y-%m-%d')
writer=pd.ExcelWriter('test.xlsx')
first.to_excel(writer, sheet_name='Here')
first.to_excel(writer, sheet_name='Keep')
#how to update the sheet'Here', cell A5:C6 with following without overwriting the rest?
#I want to keep the sheet "Keep"
update=pd.DataFrame({'A':[3,4],
'B':[4,5]}, index=pd.date_range('2004-04-30',
periods=2,
freq='M'))
I've researched SO. But not sure how to write a dataframe into the sheet.
我研究过SO。但不确定如何将数据框写入工作表。
Example I've tried:
我试过的例子：
import openpyxl
xfile = openpyxl.load_workbook('test.xlsx')
sheet = xfile.get_sheet_by_name('test')
sheet['B5']='wrote!!'
xfile.save('test2.xlsx')
回答by Lisa
Figured it out by myself:
自己想出来的：
#Prepare the excel we want to write to
t=pd.date_range('2004-01-31', freq='M', periods=4)
first=pd.DataFrame({'A':[1,1,1,1],
'B':[2,2,2,2]}, index=t)
first.index=first.index.strftime('%Y-%m-%d')
writer=pd.ExcelWriter('test.xlsx')
first.to_excel(writer, sheet_name='Here')
first.to_excel(writer, sheet_name='Keep')
#read the existing sheets so that openpyxl won't create a new one later
book = load_workbook('test.xlsx')
writer = pandas.ExcelWriter('test.xlsx', engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
#update without overwrites
update=pd.DataFrame({'A':[3,4],
'B':[4,5]}, index=(pd.date_range('2004-04-30',
periods=2,
freq='M').strftime('%Y-%m-%d')))
update.to_excel(writer, "Here", startrow=1, startcol=2)
writer.save()
回答by Charlie Clark
I'd suggest you update to the 2.4 (either the beta or a checkout) of openpyxl and use the built in support fro dataframes. These can now easily be converted by openypxl into rows that you do what you want with.
我建议您更新到 openpyxl 的 2.4（测试版或结帐版）并使用数据帧的内置支持。这些现在可以通过 openypxl 轻松转换为您想要的行。
See http://openpyxl.readthedocs.io/en/latest/pandas.htmlfor details.
有关详细信息，请参阅http://openpyxl.readthedocs.io/en/latest/pandas.html。
