Not clear how to configure a Table Totals Row
Created originally on Bitbucket by matt_trentini (Matt Trentini)
Let's start with a minimal example based on the standard Table Example:
#!python
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
wb = Workbook()
ws = wb.active
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
ws.append(row)
# Note the addition of 'totalsRowShown'
tab = Table(displayName="Table1", ref="A1:E5", totalsRowShown=True)
Now, how do we add a 'Totals Row'?
Guessing from methods in the docs, I've tried setting totalsRowShown
to True
when creating the table and then figure I have to configure totalsRowFunction
appropriately for each TableColumn
.
First I was stuck getting the TableColumns
out of the Table
:
#!python
>>> tab.tableColumns
>>> []
It looks like _initialise_columns()
actually creates the columns based on the range set so lets try and call that non-public method...
#!python
>>> tab._initialise_columns()
>>> columns = tab.tableColumns # Now tableColumns is populated!
>>> columns[0].totalsRowFunction = 'count'
>>> ws.add_table(tab)
>>> wb.save("table.xlsx")
That all works but, when I try to open table.xlsx
in Excel 2013 an error is reported:
Although Excel successfully recovers the document the totals row is not enabled:
How do we go about displaying a Totals Row and configuring the column total functions?