How To Create a Pivot Table using the PivotTableWizard Method Using xlwings?

Method

def create_pivot_table():

    #Create pivot table

    pvt=sht_pvt.api.PivotTableWizard(\

        SourceType=xw.constants.PivotTableSourceType.xlDatabase,\

        SourceData=rng_data)

    pvt.Name=’Pivot’

    #Set fields

    pvt.PivotFields(‘Category’).Orientation=\

        xw.constants.PivotFieldOrientation.xlPageField    #Page fields

    pvt.PivotFields(‘Category’).Position=1

    pvt.PivotFields(‘Product’).Orientation=\

        xw.constants.PivotFieldOrientation.xlColumnField    #Column fields

    pvt.PivotFields(‘Product’).Position=1

    pvt.PivotFields(‘Origin’).Orientation=\

        xw.constants.PivotFieldOrientation.xlRowField    #Row fields

    pvt.PivotFields(‘Origin’).Position=1

    pvt.PivotFields(‘Amount’).Orientation=\

        xw.constants.PivotFieldOrientation.xlDataField    #Value fields

Sample Code

#Create Pivot Table using PivotTableWizard Method

import xlwings as xw    #Import xlwings
import os    #Import os

root = os.getcwd()    #Get current path
#Create Excel application window, visible, no workbook added
app=xw.App(visible=True, add_book=False)
#Open data file, writable
bk=app.books.open(fullname='Pivot.xlsx',read_only=False)
#Get data source worksheet
sht_data=bk.sheets.active
#Data cell range
rng_data=sht_data.api.Range('A1').CurrentRegion
#Create a new worksheet for the pivot table
sht_pvt=bk.sheets.add()
sht_pvt.name='Pivot Table'

def create_pivot_table():
    #Create pivot table
    pvt=sht_pvt.api.PivotTableWizard(\
        SourceType=xw.constants.PivotTableSourceType.xlDatabase,\
        SourceData=rng_data)
    pvt.Name='Pivot'

    #Set fields
    pvt.PivotFields('Category').Orientation=\
        xw.constants.PivotFieldOrientation.xlPageField    #Page fields
    pvt.PivotFields('Category').Position=1
    pvt.PivotFields('Product').Orientation=\
        xw.constants.PivotFieldOrientation.xlColumnField    #Column fields
    pvt.PivotFields('Product').Position=1
    pvt.PivotFields('Origin').Orientation=\
        xw.constants.PivotFieldOrientation.xlRowField    #Row fields
    pvt.PivotFields('Origin').Position=1
    pvt.PivotFields('Amount').Orientation=\
        xw.constants.PivotFieldOrientation.xlDataField    #Value fields


create_pivot_table()

#bk.save()
#bk.close()
#app.kill()