pythonexcelpandasopenpyxl

Slicer in my excel sheet get destroyed while appending dataframe below excel using openpyxl


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)
  1. 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()
    
  2. 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


Solution

  • I have tried many solutions.But There is no solution available with openpyxl.

    So this problem can be solved by using xlwings library which is very efficient for data processing