pandas 如何将数据框附加到现有的 Excel 工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39068124/
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
How to append a dataframe to existing excel sheet?
提问by el323
The function gets called several times. I have kept a count so that if its called for the first time, A workbook is created. Then I write to that workbook using pd.ExcelWrite(). Next time else:gets executed and the same workbook is opened. Its first sheet is selected, its last row is found. And DataFrame is written on that row.
This is my code:
该函数被多次调用。我保留了一个计数，以便如果第一次调用它，就会创建一个工作簿。然后我使用pd.ExcelWrite(). 下次else:执行并打开相同的工作簿。它的第一张纸被选中，它的最后一行被找到。并且 DataFrame 写在该行上。这是我的代码：
def WriteFile (df):
if count1 == 1:
workbook = xlsxwriter.Workbook('pandas_simple.xlsx')
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
df.to_excel(writer, index=False )
workbook.close()
else:
book = open_workbook('pandas_simple.xlsx')
sheet_names = book.sheet_names()
sheet = book.sheet_by_name(sheet_names[0])
row = sheet.nrows
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
df.to_excel(writer, index=False, header=False, startrow = row )
I get this exception:
我得到这个例外：
Exception Exception: Exception('Exception caught in workbook destructor. Explicit close()
may be required for workbook.',) in <bound method Workbook.__del__ of <xlsxwriter.workbook.Workbook
object at 0x000000000A143860>> ignored Exception
And my pandas_simple.xlsx is also empty after execution of code. What am I doing wrong?
并且我的pandas_simple.xlsx 在代码执行后也是空的。我究竟做错了什么？
回答by Shijo
Thanks to @ski.
感谢@ski。
Please refer his ans on the same question
请参考他对同一问题的回答
How to write to an existing excel file without overwriting data (using pandas)?
如何在不覆盖数据的情况下写入现有的 excel 文件（使用 Pandas）？
import pandas
from openpyxl import load_workbook
book = load_workbook('Masterfile.xlsx')
writer = pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])
writer.save()
回答by MaxU
you can do it this way:
你可以这样做：
df = pd.DataFrame(np.arange(1, 31), columns=['val'])
fn = 'd:/temp/test.xlsx'
count = 0
writer = pd.ExcelWriter(fn)
for chunk in np.split(df, 3):
chunk.to_excel(writer, index=False, header=(count==0), startrow=count+(count!=0))
count += len(chunk)
writer.save()
Result:
结果：
