XLSX "% Running Total In" aggregate setting lost from Pivot Table Values
Have an XLSX with a PivotTable that has some [Count of COL] Values. One is set to "Show Values As..." [% Running Total In]
When run through load_workbook and then write, the setting is lost and it just shows [No Calculation] (but retains the % formatting). Other settings like [% of Column Total] are preserved in the output.
Here is the code I'm using. sheet "DataSheet" has a defined Named Table used as the source for my Pivots and I overwrite that data. Probably not relevant, but I left it in.
`
# get Excel Template, add data
bio = BytesIO()
writer = pd.ExcelWriter(bio, engine='openpyxl')
tmpl = openpyxl.load_workbook(xlTemplate)
writer.book = tmpl
# get dataFrame from SQL result
sqltxt = open(sqlTemplate).read()
sqltxt = sqltxt.format(whereclause=buildwhere())
conn = pyodbc.connect('Driver={SQL Server Native Client 11.0}; Server=xxxxxxx; DATABASE=xxxxxxxxx; uid=xxxxx; pwd=xxxxxxxx; Trusted_Connection=No;')
df = pd.read_sql(sqltxt, conn)
writer.sheets = dict((ws.title, ws) for ws in tmpl.worksheets)
df.to_excel(writer, sheet_name = "DataSheet", header = True, index = False)
ws = tmpl["Summary"]
pivot = ws._pivots[0] # any will do as they share the same cache
pivot.cache.refreshOnLoad = True
# Save the workbook
#writer.save() # don't do this, corrupts the excel
writer.close()
print("File saved")
# Seek to the beginning and read to copy the workbook to a variable in memory
bio.seek(0)
workbook = bio.getbuffer()
with open(outputName,"wb") as f:
f.write(workbook)`