如何使用python在excel文件的现有工作表中追加dataframe

Question

您可以在下面找到我迄今为止尝试过的内容：

import pandas
from openpyxl import load_workbook

book = load_workbook('C:/Users/Abhijeet/Downloads/New Project/Masterfil.xlsx')
writer = pandas.ExcelWriter('C:/Users/Abhijeet/Downloads/New Project/Masterfiles.xlsx', engine='openpyxl',mode='a',if_sheet_exists='replace') 
df.to_excel(writer,'b2b')

writer.save()
writer.close()

Answer 1

生成样本数据

import pandas as pd

# dataframe Name and Age columns
df = pd.DataFrame({'Col1': ['A', 'B', 'C', 'D'],
                   'Col2': [10, 0, 30, 50]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('sample.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1', index=False)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

此代码将添加两列Col1和Col2，并将数据添加到sample.xlsx的Sheet1中

将数据附加到现有excel的步骤

import pandas as pd
from openpyxl import load_workbook
# new dataframe with same columns
df = pd.DataFrame({'Col1': ['E','F','G','H'],
                   'Col2': [100,70,40,60]})
writer = pd.ExcelWriter('sample.xlsx', engine='openpyxl')
# try to open an existing workbook
writer.book = load_workbook('sample.xlsx')
# copy existing sheets
writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)
# read existing file
reader = pd.read_excel(r'sample.xlsx')
# write out the new sheet
df.to_excel(writer,index=False,header=False,startrow=len(reader)+1)

writer.close()

此代码将在excel的末尾追加数据

检查一下这些

how to append data using openpyxl python to excel file from a specified row?

Answer 2

假设您有excel文件abc.xlsx。 您有一个数据帧要附加为“df1”

1.使用熊猫读取文件

import pandas as pd
df = pd.read_csv("abc.xlsx")

2.连接两个数据帧并写入“abc.xlsx”

finaldf = pd.concat(df,df1)
# write finaldf to abc.xlsx and you are done