如何使用Pandas将新表保存到现有的Excel文件中？

Question

我想使用Excel文件来存储用Python处理的数据。我的问题是，我无法向现有的Excel文件中添加工作表。在这里，我提供了一段示例代码，以便解决此问题。

import pandas as pd
import numpy as np

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)

x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
df1.to_excel(writer, sheet_name = 'x1')
df2.to_excel(writer, sheet_name = 'x2')
writer.save()
writer.close()

这段代码将两个DataFrame保存到两个不同的工作表中，分别命名为“x1”和“x2”。如果我创建了两个新的DataFrame并尝试使用相同的代码添加两个新工作表“x3”和“x4”，那么原始数据将会丢失。

import pandas as pd
import numpy as np

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
df3.to_excel(writer, sheet_name = 'x3')
df4.to_excel(writer, sheet_name = 'x4')
writer.save()
writer.close()

我希望得到一个包含四个工作表的Excel文件：'x1'、'x2'、'x3'和'x4'。

我知道 'xlsxwriter' 不是唯一的“引擎”，还有 'openpyxl'。我也看到已经有其他人写过这个问题，但我仍然不明白该怎么做。

这里有一个来自这个链接的代码示例：

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

他们说它有效，但很难弄清楚如何操作。我不理解这个文本中的“ws.title”、“ws”和“dict”是什么意思。

最好的方法是保存“x1”和“x2”，然后关闭文件，再次打开并添加“x3”和“x4”？

- Stefano Fedele

Answer 1

谢谢。我相信一个完整的示例对于其他遇到同样问题的人也会很有帮助：

import pandas as pd
import numpy as np

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)

x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
df1.to_excel(writer, sheet_name = 'x1')
df2.to_excel(writer, sheet_name = 'x2')
writer.close()

我在这里生成一个Excel文件。据我理解，无论是通过"xlsxwriter"还是"openpyxl"引擎生成，都不会影响结果。

当我想要在不丢失原始数据的情况下进行写入时

import pandas as pd
import numpy as np
from openpyxl import load_workbook

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

book = load_workbook(path)
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

df3.to_excel(writer, sheet_name = 'x3')
df4.to_excel(writer, sheet_name = 'x4')
writer.close()

这段代码完成了任务！

- Stefano Fedele

---

是的，这将在不清除现有工作表的情况下将工作表添加到Excel中。谢谢！ - Nikhil VJ

保存Excel文件时，我该如何保留现有的Excel表格格式？ - Vineesh TP

如果有人阅读此文并想知道如何覆盖同名的现有工作表而不是重命名新工作表：请在writer.book = book之后添加以下代码行：writer.sheets = dict((ws.title, ws) for ws in book.worksheets) - Harm

@Stefano Fedele，您能否使用“xlsxwriter”而不是“openpyxl”来更新现有的Excel？ - Ronnie

嗨@Stefano Fedele，我在Google Colab上尝试了您的解决方案，但我只提供了“ExcelFile.xlsx”而不是完整路径，并且我尝试使用和不使用“r”。代码可以运行，但是文件后来却损坏了。我不知道为什么。 - Andrea Ciufo

Answer 2

创建新文件

x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)
with pd.ExcelWriter('sample.xlsx') as writer:  
    df1.to_excel(writer, sheet_name='x1')

要将内容追加到文件中，请在pd.ExcelWriter中使用参数mode='a'。

x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)
with pd.ExcelWriter('sample.xlsx', engine='openpyxl', mode='a') as writer:  
    df2.to_excel(writer, sheet_name='x2')

默认值为 mode ='w'。 请参见文档。

- Pulkit Khandelwal

---

这个解决方案没有覆盖我以前的表格。 - Sameen

我认为这比被接受的答案更好。因为.book不是公共API。 - Crispy13

请确保使用pip安装'openpyxl'，请参考以下链接进行安装。 - undefined

Answer 3

在您分享的示例中，将现有文件加载到book中，并将writer.book值设置为book。在writer.sheets = dict((ws.title, ws) for ws in book.worksheets)这一行中，您正在将工作簿中的每个工作表都视为ws进行访问。然后，工作表标题是ws，因此您正在创建一个字典，其中包含{sheet_titles：sheet}键值对。然后将此字典设置为writer.sheets。 实质上，这些步骤只是从'Masterfile.xlsx'加载现有数据并将其填充到您的写入器中。 现在假设您已经有一个包含x1和x2工作表的文件。您可以使用示例代码加载文件，然后执行以下操作来添加x3和x4。

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"
writer = pd.ExcelWriter(path, engine='openpyxl')
df3.to_excel(writer, 'x3', index=False)
df4.to_excel(writer, 'x4', index=False)
writer.save()

那应该可以做你想要的事情。

- Grr

---

你有什么想法，为什么我尝试这个时会得到以下错误：ValueError: No Excel writer 'Sales Leads Calculations.xlsx'? - bernando_vialli

这是擦除现有的纸张。 - Nikhil VJ

Answer 4

一次性将多个数据写入Excel的简单示例。当您想要将数据附加到已经关闭的Excel文件中的工作表时也适用。

如果这是您第一次写入Excel（将“df1”和“df2”写入“1st_sheet”和“2nd_sheet”）

import pandas as pd 
from openpyxl import load_workbook

df1 = pd.DataFrame([[1],[1]], columns=['a'])
df2 = pd.DataFrame([[2],[2]], columns=['b'])
df3 = pd.DataFrame([[3],[3]], columns=['c'])

excel_dir = "my/excel/dir"

with pd.ExcelWriter(excel_dir, engine='xlsxwriter') as writer:    
    df1.to_excel(writer, '1st_sheet')   
    df2.to_excel(writer, '2nd_sheet')   
    writer.save()    

当你关闭Excel后，但是你希望在同一个Excel文件的另一个工作表上“追加”数据，比如说把“df3”添加到名为“3rd_sheet”的工作表中。

book = load_workbook(excel_dir)
with pd.ExcelWriter(excel_dir, engine='openpyxl') as writer:
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)    

    ## Your dataframe to append. 
    df3.to_excel(writer, '3rd_sheet')  

    writer.save()     

请注意，Excel 格式不能为 xls，您可以使用 xlsx 格式。

- Wong Tat Yau

---

我不认为这个答案有什么帮助。事实上，反复使用像这样的上下文管理器将涉及更多的I/O操作。 - Charlie Clark

Answer 5

当你想把 Pandas DataFrame 保存为 Excel 时，你可以调用这个函数：

import os

def save_excel_sheet(df, filepath, sheetname, index=False):
    # Create file if it does not exist
    if not os.path.exists(filepath):
        df.to_excel(filepath, sheet_name=sheetname, index=index)

    # Otherwise, add a sheet. Overwrite if there exists one with the same name.
    else:
        with pd.ExcelWriter(filepath, engine='openpyxl', if_sheet_exists='replace', mode='a') as writer:
            df.to_excel(writer, sheet_name=sheetname, index=index)

- Vishal Gupta

Answer 6

我强烈建议您直接使用openpyxl，因为它支持Pandas DataFrames。。这使您可以集中精力处理与Excel和Pandas相关的代码。

- Charlie Clark

---

如果您能添加更多类似于此处的“Pandas”示例（http://xlsxwriter.readthedocs.io/working_with_pandas.html），那将非常有帮助。 - MaxU - stand with Ukraine

我自己并没有太多与Pandas相关的工作经验，因此无法提供太多示例，但欢迎对文档进行改进。 - Charlie Clark

Answer 7

可以使用 openpyxl 中的工具，而不必使用 ExcelWriter 来完成。使用 openpyxl.styles 可以更轻松地将字体添加到新工作表中。

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

#Location of original excel sheet
fileLocation =r'C:\workspace\data.xlsx'

#Location of new file which can be the same as original file
writeLocation=r'C:\workspace\dataNew.xlsx'

data = {'Name':['Tom','Paul','Jeremy'],'Age':[32,43,34],'Salary':[20000,34000,32000]}

#The dataframe you want to add
df = pd.DataFrame(data)

#Load existing sheet as it is
book = load_workbook(fileLocation)
#create a new sheet
sheet = book.create_sheet("Sheet Name")

#Load dataframe into new sheet
for row in dataframe_to_rows(df, index=False, header=True):
    sheet.append(row)

#Save the modified excel at desired location    
book.save(writeLocation)

- Jis Mathew

---

这是一个不错的解决方案，但我不确定它是否也是一种暗示。你的意思是说你不能使用ExcelWriter吗？还是你只是不需要使用它？ - MattSom

你可以使用Excelwriter来完成，但我发现只使用openpyxl更容易。 - Jis Mathew

Answer 8

你可以将你感兴趣的现有表格，例如“x1”、“x2”，读入内存并在添加更多新表格之前进行“写回”（请记住文件中的表格和内存中的表格是两个不同的东西，如果不读取它们，它们将丢失）。此方法仅使用“xlsxwriter”，不涉及openpyxl。"最初的回答"

import pandas as pd
import numpy as np

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

# begin <== read selected sheets and write them back
df1 = pd.read_excel(path, sheet_name='x1', index_col=0) # or sheet_name=0
df2 = pd.read_excel(path, sheet_name='x2', index_col=0) # or sheet_name=1
writer = pd.ExcelWriter(path, engine='xlsxwriter')
df1.to_excel(writer, sheet_name='x1')
df2.to_excel(writer, sheet_name='x2')
# end ==>

# now create more new sheets
x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

df3.to_excel(writer, sheet_name='x3')
df4.to_excel(writer, sheet_name='x4')
writer.save()
writer.close()

如果您想保留所有现有的工作表，您可以将上面的代码替换为以下代码（在Begin和End之间）： "如果您想保留所有现有的工作表，您可以在开始和结束之间将上述代码替换为以下代码："。

# read all existing sheets and write them back
writer = pd.ExcelWriter(path, engine='xlsxwriter')
xlsx = pd.ExcelFile(path)
for sheet in xlsx.sheet_names:
    df = xlsx.parse(sheet_name=sheet, index_col=0)
    df.to_excel(writer, sheet_name=sheet)

- Jonathan L

Answer 9

另一种相当简单的方法是创建一个像这样的方法:

def _write_frame_to_new_sheet(path_to_file=None, sheet_name='sheet', data_frame=None):
    book = None
    try:
        book = load_workbook(path_to_file)
    except Exception:
        logging.debug('Creating new workbook at %s', path_to_file)
    with pd.ExcelWriter(path_to_file, engine='openpyxl') as writer:
        if book is not None:
            writer.book = book
        data_frame.to_excel(writer, sheet_name, index=False)

这里的想法是，如果存在 path_to_file 的工作簿，则加载该工作簿，然后将 data_frame 作为新工作表附加到 sheet_name。如果工作簿不存在，则创建它。似乎 openpyxl 或 xlsxwriter 都不支持追加，因此如上面 @Stefano 的示例所示，您确实需要加载并重写以进行追加。

- MrMajestyk

---

导入日志记录 导入pandas作为pd 导入openpyxldef write_frame_to_new_sheet(path_to_file=None, sheet_name='sheet', data_frame=None): book = None try: book = openpyxl.load_workbook(path_to_file) except Exception: logging.debug('在%s处创建新工作簿', path_to_file) with pd.ExcelWriter(path_to_file, engine='openpyxl') as writer: if book is not None: writer.book = book data_frame.to_excel(writer, sheet_name, index=False) - BrianBeing

Answer 10

如果你想添加空白工作表

xw = pd.ExcelWriter(file_path, engine='xlsxwriter')    
pd.DataFrame().to_excel(xw, 'sheet11')

如果你得到了空白的表格

sheet = xw.sheets['sheet11']

- Myeongsik Joo