pythonexcelwin32com

Change the design of a Pivot Table being generated with code (win32com)


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!


Solution

  • 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)