使用pandas将工作表添加到现有Excel文件

# 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 statistics to my current NHL_STATS_JSB_final.xlsx file, 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.

我不明白为什么,但我正在尝试将工作表播放器统计信息添加到我当前的NHL_STATS_JSB_final.xlsx文件中,但它无法正常工作。我的代码使用当前文件并删除所有以前的工作表来添加新工作表,而不是将工作表添加到文件中。

How could I add Player statistics to my current Excel file with erasing all other worksheets?

如何通过删除所有其他工作表将Player统计信息添加到当前的Excel文件中?

2 个解决方案

#1

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()

#2

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()

#1