Xlsxwriter writer编写自己的表并删除现有的表

Question

我写数据帧excel。也许我做得不对，

当我使用这个代码时:

from datetime import datetime
import numpy as np
import pandas as pd
from openpyxl import load_workbook
start = datetime.now()

df = pd.read_excel(r"C:UsersharshGoogle DriveOddsportalFilesOddsportal "
r"DataHistorical Worksheetdata.xlsx", sheet_name='x1')
df['run_time'] = start
df1 = pd.read_csv(r"C:UsersharshGoogle DriveOddsportalFilesOddsportal "
r"DataPre-processedoddsportal_upcoming_matches.csv")
df1['run_time'] = start
concat = [df, df1]
df_c = pd.concat(concat)
path = r"C:UsersharshGoogle DriveOddsportalFilesOddsportal DataHistorical Worksheetdata.xlsx"
writer = pd.ExcelWriter(path, engine='xlsxwriter')
df.to_excel(writer, sheet_name='x1')
df1.to_excel(writer, sheet_name='x2')
df_c.to_excel(writer, sheet_name='upcoming_archive')
writer.save()
writer.close()
print(df_c.head())

数据帧被写入各自的工作表，所有其他现有的工作表被删除。

我怎样才能只写到各自的页，而不打扰其他现有的?

Answer 1

xlsxwriter不意味着修改现有的xlsx文件。唯一的救星是openpyxl，它可以完成这项工作，但很难学习。我甚至写了一个简单的python脚本来填补空白，在一个表中编写一堆行或列- openpyxl_writers.py

Answer 2

您只需要使用附加模式并将if_sheet_exists设置为replace并使用openpyxl作为引擎。

替换:

writer = pd.ExcelWriter('test.xlsx')

由:

writer = pd.ExcelWriter('test.xlsx', mode='a', engine='openpyxl',
if_sheet_exists='replace')  # <- HERE

来自文档:

模式{' w ', ' '},默认"w">

Answer 3

在写入新的(或覆盖现有的工作表)时，您需要读取和写入先前存在的工作表以及您希望持久化的工作表。

workbook = load_workbook(path)    # Load the workbook
writer = pd.ExcelWriter(path, engine='xlsxwriter')
writer.book = workbook            # Assign workbook to writer's book
writer.sheets = dict((ws.title, ws) for ws in workbook.worksheets)  #Read existing sheets
# Now write the new sheets (or overwrite the existing sheets)
df.to_excel(writer, sheet_name='x1')
df1.to_excel(writer, sheet_name='x2')
df_c.to_excel(writer, sheet_name='upcoming_archive')
writer.save()
writer.close()

在将现有工作表分配给writer.sheets时，您始终可以排除将要覆盖的工作表。