Python 如何使用 ExcelWriter 写入现有工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34744863/
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 How to use ExcelWriter to write into an existing worksheet
提问by Hamed
I am trying to use ExcelWriter to write/add some information into a workbook that contains multiple sheets. First time when I use the function, I am creating the workbook with some data. In the second call, I would like to add some information into the workbook in different locations into all sheets.
我正在尝试使用 ExcelWriter 将一些信息写入/添加到包含多个工作表的工作簿中。第一次使用该函数时，我正在使用一些数据创建工作簿。在第二次调用中，我想将一些信息添加到所有工作表中不同位置的工作簿中。
def Out_Excel(file_name,C,col):
writer = pd.ExcelWriter(file_name,engine='xlsxwriter')
for tab in tabs: # tabs here is provided from a different function that I did not write here to keep it simple and clean
df = DataFrame(C) # the data is different for different sheets but I keep it simple in this case
df.to_excel(writer,sheet_name = tab, startcol = 0 + col, startrow = 0)
writer.save()
In the main code I call this function twice with different col to print out my data in different locations.
在主代码中，我用不同的 col 调用了这个函数两次，以在不同的位置打印出我的数据。
Out_Excel('test.xlsx',C,0)
Out_Excel('test.xlsx',D,10)
But the problem is that doing so the output is just the second call of the function as if the function overwrites the entire workbook. I guess I need to load the workbook that already exists in this case? Any help?
但问题是，这样做输出只是函数的第二次调用，就好像函数覆盖了整个工作簿一样。我想我需要加载在这种情况下已经存在的工作簿？有什么帮助吗？
回答by Stefan
Use load_bookfrom openpyxl- see xlsxwriterand openpyxldocs:
使用load_book来自openpyxl- 请参阅xlsxwriter和openpyxl文档：
import pandas as pd
from openpyxl import load_workbook
book = load_workbook('test.xlsx')
writer = pd.ExcelWriter('test.xlsx', engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(writer, sheet_name='tab_name', other_params)
writer.save()
回答by Naufal
You could also try using the following method to create your Excel spreadsheet:
您还可以尝试使用以下方法来创建 Excel 电子表格：
import pandas as pd
def generate_excel(csv_file, excel_loc, sheet_):
writer = pd.ExcelWriter(excel_loc)
data = pd.read_csv(csv_file, header=0, index_col=False)
data.to_excel(writer, sheet_name=sheet_, index=False)
writer.save()
return(writer.close())
Give this a try and let me know what you think.
试试这个，让我知道你的想法。
回答by T.C. Proctor
Pandas version 0.24.0added the modekeyword, which allows you to append to excel workbooks without jumping through the hoops that we used to have to do. Just use mode='a'to append sheets to an existing workbook.
Pandas 0.24.0 版本添加了mode关键字，它允许您附加到 excel 工作簿，而无需跳过我们过去必须做的工作。仅用于mode='a'将工作表附加到现有工作簿。
From the documentation:
从文档：
with ExcelWriter('path_to_file.xlsx', mode='a') as writer:
df.to_excel(writer, sheet_name='Sheet3')
