python另存为excel_python-如何使用Pandas将新工作表保存在现有的excel文件中？

python-如何使用Pandas将新工作表保存在现有的excel文件中？

我想使用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”。如果我创建了两个新的DataFrames并尝试使用相同的代码添加两个新的床单，“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”的最佳方法是什么？

7个解决方案

66 votes

谢谢。 我相信，一个完整的示例可能会对遇到相同问题的其他人有所帮助：

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

根据我的理解，在这里我生成了一个excel文件，无论它是通过“ xslxwriter”还是“ 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.save()

writer.close()

此代码可以完成工作！

Stefano Fedele answered 2020-02-12T03:22:23Z

14 votes

在您共享的示例中，您将现有文件加载到book中，并将writer.book值设置为x1。在x2行中，您将工作簿中的每个工作表都访问为x3。工作表标题为x4，因此您将创建一个字典 {sheet_titles: sheet}键，值对。 然后将此词典设置为writer.sheets。 本质上，这些步骤只是从x1加载现有数据，并使用它们填充您的编写器。

现在，假设您已经有一个包含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 answered 2020-02-12T03:22:53Z

9 votes

一个简单的示例，一次可以写入多个数据以使其表现出色。 而且，当您要将数据附加到书面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文件中但又插入到另一张工作表后，让工作表名称“ 3rd_sheet”说“ df3”。

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 answered 2020-02-12T03:23:27Z

7 votes

我强烈建议您直接使用openpyxl，因为它现在支持Pandas DataFrames。

这使您可以专注于相关的Excel和Pandas代码。

Charlie Clark answered 2020-02-12T03:23:51Z

0 votes

您可以将自己感兴趣的现有工作表（例如“ 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()

如果要保留所有现有工作表，则可以在开头和结尾之间替换以下代码：

# 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 answered 2020-02-12T03:24:17Z

0 votes

#This program is to read from excel workbook to fetch only the URL domain names and write to the existing excel workbook in a different sheet..

#Developer - Nilesh K

import pandas as pd

from openpyxl import load_workbook #for writting to the existing workbook

df = pd.read_excel("urlsearch_test.xlsx")

#You can use the below for the relative path.

# r"C:\Users\xyz\Desktop\Python\

l = [] #To make a list in for loop

#begin

#loop starts here for fetching http from a string and iterate thru the entire sheet. You can have your own logic here.

for index, row in df.iterrows():

try:

str = (row['TEXT']) #string to read and iterate

y = (index)

str_pos = str.index('http') #fetched the index position for http

str_pos1 = str.index('/', str.index('/')+2) #fetched the second 3rd position of / starting from http

str_op = str[str_pos:str_pos1] #Substring the domain name

l.append(str_op) #append the list with domain names

#Error handling to skip the error rows and continue.

except ValueError:

print('Error!')

print(l)

l = list(dict.fromkeys(l)) #Keep distinct values, you can comment this line to get all the values

df1 = pd.DataFrame(l,columns=['URL']) #Create dataframe using the list

#end

#Write using openpyxl so it can be written to same workbook

book = load_workbook('urlsearch_test.xlsx')

writer = pd.ExcelWriter('urlsearch_test.xlsx',engine = 'openpyxl')

writer.book = book

df1.to_excel(writer,sheet_name = 'Sheet3')

writer.save()

writer.close()

#The below can be used to write to a different workbook without using openpyxl

#df1.to_excel(r"C:\Users\xyz\Desktop\Python\urlsearch1_test.xlsx",index='false',sheet_name='sheet1')

nileshk611 answered 2020-02-12T03:24:41Z

0 votes

实现此目的的另一种相当简单的方法是制作如下方法：

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 answered 2020-02-12T03:25:08Z