python - 在不覆盖数据的情况下向现有 excel 文件添加新行(Python)
问题描述
所以我从How to write to an existing excel file without overwritten data (using pandas)中复制了这段代码?
def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None,
truncate_sheet=False,
**to_excel_kwargs):
"""
Append a DataFrame [df] to existing Excel file [filename]
into [sheet_name] Sheet.
If [filename] doesn't exist, then this function will create it.
Parameters:
filename : File path or existing ExcelWriter
(Example: '/path/to/file.xlsx')
df : dataframe to save to workbook
sheet_name : Name of sheet which will contain DataFrame.
(default: 'Sheet1')
startrow : upper left cell row to dump data frame.
Per default (startrow=None) calculate the last row
in the existing DF and write to the next row...
truncate_sheet : truncate (remove and recreate) [sheet_name]
before writing DataFrame to Excel file
to_excel_kwargs : arguments which will be passed to `DataFrame.to_excel()`
[can be dictionary]
Returns: None
"""
from openpyxl import load_workbook
import pandas as pd
# ignore [engine] parameter if it was passed
if 'engine' in to_excel_kwargs:
to_excel_kwargs.pop('engine')
writer = pd.ExcelWriter(filename, engine='openpyxl')
# Python 2.x: define [FileNotFoundError] exception if it doesn't exist
try:
FileNotFoundError
except NameError:
FileNotFoundError = IOError
try:
# try to open an existing workbook
writer.book = load_workbook(filename)
# get the last row in the existing Excel sheet
# if it was not specified explicitly
if startrow is None and sheet_name in writer.book.sheetnames:
startrow = writer.book[sheet_name].max_row
# truncate sheet
if truncate_sheet and sheet_name in writer.book.sheetnames:
# index of [sheet_name] sheet
idx = writer.book.sheetnames.index(sheet_name)
# remove [sheet_name]
writer.book.remove(writer.book.worksheets[idx])
# create an empty sheet [sheet_name] using old index
writer.book.create_sheet(sheet_name, idx)
# copy existing sheets
writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
except FileNotFoundError:
# file does not exist yet, we will create it
pass
if startrow is None:
startrow = 0
# write out the new sheet
df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)
# save the workbook
writer.save()
该函数的作用是:它将数据框(行)添加到现有的 excel 文件中。但是,此功能正在替换原始 excel 文件的行,用于数据框的行。如何更改/添加到此函数,以便它首先插入行而不是替换行,从而维护原始数据并添加新数据?
解决方案
后# try to open an existing workbook
writer.book = load_workbook(filename)
添加ws= writer.book[sheet_name]
ws.insert_rows("number of the row you want to insert")
推荐阅读
- sql-server - 根据条件在SQL中放置where子句条件?
- c# - 从 Axios 请求返回 ASP.NET Core API 中的下载文件
- python - 在 Python 中使用有限线程进行异步、多线程抓取
- batch-file - bat读取文本获取变量
- .net - 如何在 VSTO Excel 中创建自定义任务窗格?
- android - 在 Android Studio 中看不到 Activity
- javascript - 未经授权的 API Arcgis 到 laravel 中的谷歌地图
- c# - 将 SQL 查询转换为 LINQ - 不起作用
- java - 使用 java 流设置联合和交集
- android - 在android的交错网格布局中添加水平全屏卡