openpyxl - overwrite datasheet and preserve pivot table

Question

I've seen a few answers around to this question but none of them are working. eg: How to write to an existing excel file without breaking formulas with openpyxl?

Docs give nothing away it seems: http://openpyxl.readthedocs.io/en/latest/api/openpyxl.reader.excel.html I tried replacing xls.load_workbook with xls.reader.excel.load_workbook but it doesn't change anything.

My current code overwrites the data in the data sheet, but kills the pivot table functionality in the other sheet (the sheet is still there but only with values). Any idea how to keep the pivot table?

import pandas as pd
import openpyxl as xls
from shutil import copyfile

template_file = 'openpy_test.xlsx' 
output_file = 'openpy_output.xlsx' 

copyfile(template_file, output_file)

book = xls.load_workbook(output_file,guess_types=False,data_only=False)
writer = pd.ExcelWriter(output_file,engine='openpyxl')

writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer,sheet_name='data',index=False,encoding='utf8')
writer.save()

I have also tried book.save('dummycopy.xlsx'), which also saves with a non-funcitoning pivot table. So I am sure the problem is related to the load_workbook function.

Package versions:

openpyxl 2.4.10 py36_0

pandas 0.20.3 py36hce827b7_2

Answer 1

i don't think openpyxl supports excel pivot tables currently. I had to switch to using win32com library.

here is a wrapper module i wrote to do specific stuff with pivot tables; it's basically VBA translated to python (record macros and read the VBA, it'll make sense). hope it helps. it's still a work in progress but should be enough for you to work with.

import os, datetime
import win32com.client as win32
win32c = win32.constants
import sys, datetime

letters = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ' #space to compensate for index. if letter is a if column is 1

def Pull_excel_workbook(path = '', filename = '', visible = False):
    '''function to run excel on the given filename'''
    if path == '': path = os.getcwd()
    if filename == '': raise FileNotFoundError('Please supply a file')
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.Visible = visible
    try: wb = excel.Workbooks.Open(path + filename)
    except: print('Try again\n{}'.format(sys.exc_info()))
    ws = wb.ActiveSheet
    data = list(ws.UsedRange.Value) #2d list of rows and columns
    src = '{}!R1C1:R{}C{}'.format(ws.Name, len(data), len(data[0]))
    return excel, wb, src

    #wb.SaveAs(path + filename)

def Create_pivottable(wb, src, table_name = 'Pivot'):
    '''creates Pivot Table object in the wb in a new Pivot worksheet'''
    ws = wb.Sheets.Add()    #should also change wb.ActiveSheet to the new one.
    ws.Name = table_name
    tname = ws.Name
    starting_point = (4,1)  #row, column
    pc = wb.PivotCaches().Add(SourceType = win32c.xlDatabase,
                              SourceData = src)
    try:
        pt = pc.CreatePivotTable(TableDestination = '{}!R{}C{}'.format(tname, starting_point[0], starting_point[1]),
                            TableName = table_name,
                            DefaultVersion = win32c.xlPivotTableVersion10 #15
                            )
    except:             #not sure if will work...
        print('{}:{}:{}:{}'.format(wb, src, table_name, '{}!R{}C{}'.format(tname, starting_point[0], starting_point[1])))
        #tabledestination format of RN Pivot!R4C1 is not correct format, should be 'RN Pivot'!R4C1
        pt = pc.CreatePivotTable(TableDestination = '{}!R{}C{}'.format(tname, starting_point[0], starting_point[1]),
                            TableName = table_name,
                            DefaultVersion = win32c.xlPivotTableVersion15
                            )
    wb.Sheets(ws.Name).Select()
    wb.Sheets(ws.Name).Cells(3,1).Select()

def Add_to_Filter(wb, tname, field_name):
    ''' '''
    field = wb.ActiveSheet.PivotTables(tname).PivotFields(field_name)
    field.Orientation = win32c.xlPageField
    field.Position = 1

def Add_to_Row(wb, tname, field_name, position = 1):
    ''' '''
    field = wb.ActiveSheet.PivotTables(tname).PivotFields(field_name)
    field.Orientation = win32c.xlRowField
    field.Position = position

def Add_to_Column(wb, tname, field_name, position = 1):
    ''' '''
    field = wb.ActiveSheet.PivotTables(tname).PivotFields(field_name)
    field.Orientation = win32c.xlColumnField
    field.Position = position
    if position > 1:
        text = 'maybe do something here....'
        pass

def Add_to_Value(wb, tname, field_name, alias = '', calculation = 'xlSum'):
    ''' '''
    if type(calculation) is str and calculation in win32c.__dict__['__dicts__'][0]:
        calculation = win32c.__dict__['__dicts__'][0][calculation]
    datafield = wb.ActiveSheet.PivotTables(tname).PivotFields(field_name)
    wb.ActiveSheet.PivotTables(tname).AddDataField(datafield, alias, calculation)

def LtoC(letter):
    global letters
    col = letters.index(letter)
    return col

def CtoL(col):
    global letters
    letter = letters[col]
    return letter

def Format_pretty(wb, tname, row_to_colapse):
    '''makes it look prettier'''
    wb.ActiveSheet.PivotTables(tname).TableStyle2 = 'PivotStyleMedium9'
    if type(row_to_colapse) is not str:
        for row in row_to_colapse:
            wb.ActiveSheet.PivotTables(tname).PivotFields(row).ShowDetail = False #collapses
            wb.ActiveSheet.PivotTables(tname).PivotFields(row).RepeatLabels = True #repeats labels
    else:
        wb.ActiveSheet.PivotTables(tname).PivotFields(row_to_colapse).ShowDetail = False #collapses
        wb.ActiveSheet.PivotTables(tname).PivotFields(row_to_colapse).RepeatLabels = True #repeats labels
    wb.ActiveSheet.Columns('A:Z').EntireColumn.AutoFit()
    wb.ActiveSheet.Range('A1').Select()

def Add_calcd_col(ws, col, row_start, row_end, formula, style = '', col_title = 'default'):
    '''col and rows should be int
    '''
    letter = CtoL(col)
    ws.Range('{0}{1}:{0}{2}'.format(letter, row_start, row_end)).Select()
    ws.Cells(row_start, col).Value = col_title
    for row in range(row_start + 1, row_end + 1):
        ws.Cells(row, col).Value = formula.format(row)
    ws.Range('{0}{1}:{0}{2}'.format(letter, row_start, row_end)).Style = style
    #print("ws.Range('{0}1:{0}200'.format({0})).Style = style".format(letter))
    #ws.Range('{0}1:{0}200'.format(letter)).Style = style

def Values_to_columns(wb,tname, position = 2):
    ''' '''
    wb.ActiveSheet.PivotTables(tname).DataPivotField.Orientation = win32c.xlColumnField
    wb.ActiveSheet.PivotTables(tname).DataPivotField.Position = position

def WB_save(wb, path, tname, filename):
    '''clean save of the new file '''
    #Format_pretty(wb, tname, 'Division') #that needs to be fixed....
    new_filename = filename[:-5] + '-{}.xlsx'.format(datetime.date.today().strftime('%m.%d.%y'))
    wb.SaveAs(path + new_filename)

def Pivot_refresh(path, filename, pivot_sheet_name, pivot_table_name = 'Pivot'):
    '''function to refresh the pivot table
    tested and functional with recruiting prod report'''
    excel, wb, src = Pull_excel_workbook(path = path, filename = filename)
    wb.Sheets(pivot_sheet_name).Select()
    cell = 'A6' #need a better way for this
    excel.Worksheets(pivot_sheet_name).Range(cell).PivotTable.RefreshTable()
    #pvt = excel.Worksheets(pivot_sheet_name).Range(cell).PivotTable
    #pvt.RefreshTable()
    WB_save(wb, path, pivot_table_name, filename)
    #pivot refresh
    #new = filename[:-5] + '-{}.xlsx'.format(2)
    #Pivot_refresh(path = path, filename = new, pivot_sheet_name = 'Pivot')

def Hide_columns(wb, tname, start, end):
    '''Hides columns'''
    if type(start) is not str: start = CtoL(start)
    if type(end) is not str: end = CtoL(end)
    wb.ActiveSheet.Columns('{}:{}'.format(start, end)).EntireColumn.Hidden = True