Trouble Writing Pivot Table To Excel File

I am using pandas/openpyxl to process an excel file and then create a pivot table to add to a new worksheet in the current workbook. When I execute my code, the new sheet gets crea

Solution 1:

You can't do this because openpyxl does not currently support pivot tables. See https://bitbucket.org/openpyxl/openpyxl/issues/295 for further information.

Solution 2:

Since pd.pivot_table returns a dataframe, you can just write the dataframe into excel. Here is how I write my output from a pandas dataframe to an excel template. Please note that if data is already present in the cells where you are trying to write the dataframe, it will not be overwritten and the dataframe will be written to a new sheet which is my i have included a step to clear existing data from the template. I have not tried to write output on merged cells so that might throw an error.

Setup

from openpyxl import load_workbook
from openpyxl.utils.dataframeimport dataframe_to_rows
file_path='Template.xlsx'
book=load_workbook(file_path)
writer = pd.ExcelWriter(file_path, engine='openpyxl')
writer.book = book
sheet_name="Template 1"
sheet=book[sheet_name]

Set first row and first column in the excel template where output is to be pasted. If my output is to be pasted starting in cell N2, row_start will be 2 and col_start will be 14

row_start=2col_start=14

Clear existing data in excel template

for c_idx, col in enumerate(df.columns,col_start):
    for r_idx inrange(row_start,10001):
        sheet.cell(row=r_idx, column=c_idx, value="")

Write dataframe to excel template

rows=dataframe_to_rows(df,index=False)
for r_idx, rowin enumerate(rows,row_start):
    for c_idx, col in enumerate(row,col_start):
        sheet.cell(row=r_idx, column=c_idx, value=col)

writer.save()
writer.close()