How To Create a Pivot Table using Cache Using xlwings?

Method

def create_pivot_table():

    #Location of pivot table

    rng_pvt=sht_pvt.api.Range(‘A1’)

    #Create pivot table cache

    pvc=bk.api.PivotCaches().Create(\

          SourceType=xw.constants.PivotTableSourceType.xlDatabase,\

          SourceData=rng_data)

    #Create pivot table

    pvt=pvc.CreatePivotTable(\

          TableDestination=rng_pvt,\

          TableName=’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 with Cache

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=root+r'\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():
    #Location of pivot table
    rng_pvt=sht_pvt.api.Range('A1')
    #Create pivot table cache
    pvc=bk.api.PivotCaches().Create(\
          SourceType=xw.constants.PivotTableSourceType.xlDatabase,\
          SourceData=rng_data)
    #Create pivot table
    pvt=pvc.CreatePivotTable(\
          TableDestination=rng_pvt,\
          TableName='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()