So I'm generating this pivot table: PivotGenerated.
With this code:
pivot_table_cell = ws2.Cells(15,15)
pivot_table_range = ws2.Range(pivot_table_cell, pivot_table_cell)
pivot_table_name = 'AddCosts'
pivot_cache = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=ws1.UsedRange)
pivot_table = pivot_cache.CreatePivotTable(TableDestination = pivot_table_range, TableName = pivot_table_name)
pivot_table.PivotFields('Supplier').Orientation = win32c.xlRowField
pivot_table.PivotFields('BPO#').Orientation = win32c.xlRowField
pivot_table.PivotFields('SPL').Orientation = win32c.xlRowField
pivot_table.PivotFields('Country').Orientation = win32c.xlRowField
pivot_table.PivotFields('Currency').Orientation = win32c.xlRowField
ws2.PivotTables(pivot_table_name).AddDataField(ws2.PivotTables(pivot_table_name).PivotFields('Case ID'), Caption = 'Count of Case ID', Function=win32c.xlCount).NumberFormat = '0'
ws2.PivotTables(pivot_table_name).AddDataField(ws2.PivotTables(pivot_table_name).PivotFields('Labor Rate'), Caption = 'Sum of Labor Rate', Function=win32c.xlSum).NumberFormat = '{0} #.##0,00'.format(cur)
ws2.PivotTables(pivot_table_name).AddDataField(ws2.PivotTables(pivot_table_name).PivotFields('Logistic Rate'), Caption = 'Sum of Logistics Rate', Function=win32c.xlSum).NumberFormat = '{0} #.##0,00'.format(cur)
ws2.PivotTables(pivot_table_name).AddDataField(ws2.PivotTables(pivot_table_name).PivotFields('Material Fee'), Caption = 'Sum of Material Fee', Function=win32c.xlSum).NumberFormat = '{0} #.##0,00'.format(cur)
ws2.PivotTables(pivot_table_name).AddDataField(ws2.PivotTables(pivot_table_name).PivotFields('Additional Costs'), Caption = 'AdditionalCosts', Function=win32c.xlSum).NumberFormat = '{0} #.##0,00'.format(cur)
ws2.PivotTables(pivot_table_name).AddDataField(ws2.PivotTables(pivot_table_name).PivotFields('Total Rate'), Caption = 'Total', Function=win32c.xlSum).NumberFormat = '{0} #.##0,00'.format(cur)
Which works fine.
But I was wondering how can I apply the next excel pivot design changes on the code:
1- Subtotals: do not show subtotals.
2- Report layouts: show in tabular form.
3- Report layouts: repeat all item labels.
To make it look like this DesiredPivot.
Thank you in advance!
Probably not the cleanest but I just had to add this:
pivot_table.PivotFields('Supplier').Subtotals = tuple(False for _ in range(12))
pivot_table.PivotFields('BPO#').Subtotals = tuple(False for _ in range(12))
pivot_table.PivotFields('SPL').Subtotals = tuple(False for _ in range(12))
pivot_table.PivotFields('Country').Subtotals = tuple(False for _ in range(12))
pivot_table.PivotFields('Currency').Subtotals = tuple(False for _ in range(12))
pivot_table.RowAxisLayout(win32c.xlTabularRow)
pivot_table.RepeatAllLabels(2)