gpt4 book ai didi

python - 如何在现有 Excel 工作表下方写入数据框而不丢失数据透视表工作表中的 Excel 切片器?

转载 作者:行者123 更新时间:2023-12-01 06:47:48 26 4
gpt4 key购买 nike

我正在使用 openpyxl 模块将 pandas 数据框附加到现有 Excel 工作表下方。但问题是我的数据透视表中的 Excel 工作表切片器在执行此任务时被破坏。我尝试了很多方法来找到它的解决方案,但我认为 openpyxl 没有办法避免这种情况。

我正在使用以下方法通过 openpyxl 来实现它-

#HELPER FUNCTION TO APPEND DATAFRAME BELOW EXCEL FILE
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', index=False)

# 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,data_only=True)

# 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 = 1

# write out the new sheet
df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)

# save the workbook
writer.save()

我已经看到 xlwings 和 win32com 可以做到这一点,但不知道如何使用这些库来做到这一点。我想问如何在现有 Excel 文件下方附加数据框而不丢失 Excel 数据透视表中的切片器我们可以在不使用 openpyxl 的情况下做到这一点,因为我认为 openpyxl 没有可用的方法。我收到以下 openpyxl 警告

C:\Users\Desktop\PycharmProjects\MyProject\venv\lib\site-packages\openpyxl\worksheet\_reader.py:292: UserWarning: Slicer List extension is not supported and will be removed
warn(msg)

最佳答案

我已经尝试了所有可能的解决方案,最后我可以说 openpyxl 是不可能的。我们可以使用 openpyxl 库来替代 xlwings。xlwings 库的处理速度非常快。我们可以借助以下代码将数据框附加到 Excel 工作表下方。通过这种方式,切片器值不会丢失或删除。

import xlwings as xw
import pandas as pd
df = pd.read_excel("File_to_append.xlsx")
wb = xw.Book(r"Existing_Excel.xlsx")
ws = wb.sheets('Sheet1') #Name of sheet where to append df
ws.cells(cell_row_number,cell_column_number).options(index=False, header=False).value = df
#Here cell_row_number and cell_column_number are integer valuesof row number and column number to append data

希望我很清楚。这是我找到的最好的解决方案

关于python - 如何在现有 Excel 工作表下方写入数据框而不丢失数据透视表工作表中的 Excel 切片器?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59151055/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com