使用 Pandas 将工作表添加到现有 Excel 文件中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46672207/
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
Add worksheet to existing Excel file with pandas
提问by Brad Campbell
# Set the working folder to the same folder as the script
os.chdir(os.path.dirname(os.path.abspath(__file__)))
test = send_request().content
df = pd.read_csv(io.StringIO(test.decode('utf-8')))
writer = pd.ExcelWriter('NHL_STATS_JSB_final.xlsx', \
engine = 'xlsxwriter')
df.to_excel(writer, 'Player statistics', index=False)
writer.save()
I don't understand why, but I am trying to add the worksheet Player statisticsto my current NHL_STATS_JSB_final.xlsxfile, but it is not working. Instead of adding the worksheet to the file, my code use the current file and erase all previous worksheet to add the new one.
我不明白为什么，但我试图将工作表添加Player statistics到我当前的NHL_STATS_JSB_final.xlsx文件中，但它不起作用。我的代码没有将工作表添加到文件中，而是使用当前文件并删除所有以前的工作表以添加新的工作表。
How could I add Player statisticsto my current Excel file with erasing all other worksheets?
如何Player statistics通过擦除所有其他工作表添加到我当前的 Excel 文件中？
回答by Brad Campbell
Here is a snippet of code from one of my projects. This should do exactly what you want. You need to use openpyxl rather than xlsxwriter to allow you to update an existing file.
这是我的一个项目中的一段代码。这应该完全符合您的要求。您需要使用 openpyxl 而不是 xlsxwriter 来允许您更新现有文件。
writer = pd.ExcelWriter(file_name, engine='openpyxl')
if os.path.exists(file_name):
book = openpyxl.load_workbook(file_name)
writer.book = book
df.to_excel(writer, sheet_name=key)
writer.save()
writer.close()
回答by patrickjlong1
As the OP mentioned, xlsxwriter will overwrite your existing workbook. Xlsxwriter is for writing original .xlsx files. Openpyxl, on the other hand, can modify existing .xlsx files.
正如 OP 所提到的，xlsxwriter 将覆盖您现有的工作簿。Xlsxwriter 用于编写原始 .xlsx 文件。另一方面，Openpyxl 可以修改现有的 .xlsx 文件。
@Brad Campbell answer using openpyxl is the best way to do this. Since the OP was using the xlsxwriter engine, I wanted to demonstrate that it is possible to read in your existing .xlsx file and then create a new workbook (of the same name) containing that data from the original sheets and the new sheet that you'd like to add on.
@Brad Campbell 使用 openpyxl 回答是最好的方法。由于 OP 使用的是 xlsxwriter 引擎，我想证明可以读取现有的 .xlsx 文件，然后创建一个新的工作簿（同名），其中包含来自原始工作表和新工作表的数据想补充。
import pandas as pd
import os
xl = pd.ExcelFile('NHL_STATS_JSB_final.xlsx')
sheet_names = xl.sheet_names # a list of existing sheet names
#the next three lines are OPs original code
os.chdir(os.path.dirname(os.path.abspath(__file__)))
test = send_request().content
df = pd.read_csv(io.StringIO(test.decode('utf-8')))
#beginning the process of creating new workbook with the same name
writer = pd.ExcelWriter('NHL_STATS_JSB_final.xlsx', engine = 'xlsxwriter')
d = {} #creating an empty dictionary
for i in range (0, len(sheet_names)):
current_sheet_name = sheet_names[i]
d[current_sheet_name] = pd.read_excel('NHL_STATS_JSB_final.xlsx', sheetname = i)
d[current_sheet_name].to_excel(writer, '%s' % (current_sheet_name), index=False)
# adding in the new worksheet
df.to_excel(writer, 'Player statistics', index=False)
writer.save()
