我的excel工作表中的切片器在使用openpyxl将数据框附加到excel以下时被破坏 [英] Slicer in my excel sheet get destroyed while appending dataframe below excel using openpyxl
问题描述
我正在使用熊猫和openpyxl.
输入文件
我的程序中总共有三个输入excel文件.借助数据框 我正在处理输入的excel文件，并在处理后得到最终的数据框.
输出
在程序中获得最终数据框后，我将在openpyxl的帮助下将此数据框写入现有的excel文件下.此excel文件包含许多工作表.此excel文件中的某些工作表还包含数据透视表和切片器.数据框已成功附加到excel文件下面.
**但是问题是我的透视图的切片器在excel文件下写入数据帧时被破坏了.**我在执行程序期间收到以下警告
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的帮助下将数据框追加到现有excel工作表下方-
在EXCEL文件下追加数据帧的帮助功能
def append_df_to_excel(filename，df，sheet_name ='Sheet1'，startrow = None， truncate_sheet = False， ** to_excel_kwargs): " 将DataFrame [df]附加到现有Excel文件[filename] 放入[sheet_name]工作表. 如果[filename]不存在，则此函数将创建它.
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, data_only = 'True') # 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 = 1 # write out the new sheet df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs) # save the workbook writer.save() -
以下行用于调用上述辅助函数
existing_excel_file的路径= C:\ Users \ Desktop \ MyExcel.xlsx append_df_to_excel(path_of_existing_excel_file，df1，sheet_name ='MY-DATA'，index = False)
3.我能够获得所需的输出.唯一的问题是excel中的数据透视表被破坏了.我的excel文件中包含数据透视表的所有工作表都丢失了信息
这里的输出无用，因为我丢失了所有与数据透视表有关的信息.
希望我很清楚.现在我想问一下我的数据透视表不会被破坏的方式吗? 如何使包含数据透视表的工作表不受openpyxl的影响? 或如何在Excel工作表下编写数据框，以使我的数据透视表不会受到影响? 上述问题的任何答案都会对我有帮助
我尝试了很多解决方案.但是我认为openpyxl没有可用的解决方案.所以我可能不得不通过xlwings或win32com Library使用替代选项. /p>
如果我不正确，请纠正我.
I am working with pandas and openpyxl.
INPUT FILES
I have total three input excel files in my program. With the help of dataframes I am processing input excel files and getting a final dataframe after processing.
OUTPUT
After getting final dataframe in my program, I am writing this dataframe below an existing excel file with the help of openpyxl. This excel file contains many worksheets. Some worksheets in this excel file also contains pivot table and slicer. Dataframe is successfully appending below excel file.
**But problem is Slicer of my pivot is getting destroyed while writing dataframe below excel file.**I am getting following warning during execution of my program
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)
I am using following method to append dataframe below an existing excel sheet with the help of 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, data_only = 'True') # 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 = 1 # write out the new sheet df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs) # save the workbook writer.save()Following line is used to call above helper function
path_of_existing_excel_file = C:\Users\Desktop\MyExcel.xlsx append_df_to_excel(path_of_existing_excel_file, df1, sheet_name='MY-DATA',index = False )
3.I am able to obtain desired output.The only Problem is pivot table in excel is getting destroyed. All the sheets in my excel file which contains pivot table is losing information
Here my output is useless because I lost all the information related to my pivot table.
Hope I am clear. Now I want to ask the way by which my pivot table will not destroy ? How to do so that sheets which contains pivot table will not be affected by openpyxl? Or How can write dataframe below excel sheet so that my pivot table will not getting affected? Any of answers to above question will help me
I have tried many solutions.But I think no solution is available with openpyxl.So may be I have to use alternative option by using xlwings or win32com Library.
If I am not correct please correct me.
这篇关于我的excel工作表中的切片器在使用openpyxl将数据框附加到excel以下时被破坏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
