Create Slicer Object in Excel Via Python

Question

I'm attempting to recreate some tableau functionality within a excel sheet using the XLwings package in python.

Specifically, I've written a script that wrangles data from our SQl Server and performs some aggregate/statistical functions whose output is a pandas dataframe. I can utilize xl wings to publish simple charts and visualizations in excel-but would like to incorporate an excel slicer object so that a subset of the information can be selected within the particular worksheet

Thank you in advance!

Hey there! I am not- a slicer object is a tool used in excel that selects a subset of data in a range of values (A user might select a entry from the slicer menu to highlight some particular data). — user7351362
– user7351362, Commented Jan 2, 2019 at 3:00

abdullah · Accepted Answer · 2022-10-02 14:50:39Z

0

Don't know about the Xlwings but this works with pywin32 here workBook is open workbook while worksheet is where table is

 workbook.SlicerCaches.Add2(worksheet.PivotTables("PivotTable1"),"FieldName").Slicers.Add(SlicerDestination= worksheet, Name="FieldName",Caption= "Any Caption", Top=189.75,Left= 452.25,Width= 144,Height=198.75)

answered Oct 2, 2022 at 14:50

abdullah

1261 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Wu · Accepted Answer · 2020-12-14 19:37:18Z

-1

Do you need slicers for connecting your chart object/ Pivot tables? Here is a complete solution to your problem.

I have used win32com.client library to create Pivot tables, Slicers and to establish slicers connection.

Code to create Pivot table:

def new_pivot_table(Field_Name,j,Cache,charttype,top,left):
 PivotTable = PivotCache.CreatePivotTable(TableDestination='Sheet1!R150C'+j, 
 TableName ='ReportPivotTable'+Cache, DefaultVersion=win32c.xlPivotTableVersion14)
 PivotTable.PivotFields(Field_Name).Orientation = win32c.xlRowField
 PivotTable.PivotFields(Field_Name).Position = 1
 DataField = PivotTable.AddDataField(PivotTable.PivotFields('count'))
 S = PivotTable._PivotSelect(2881,4)
 chart = Sheet2.Shapes.AddChart2(314,charttype,top,left, 50, 60)
 ChartObj = chart.Chart
 return PivotTable,ChartObj

Code to create slicers

def create_Slicer(Pivottable,Field_Name,Cache):
 S = Pivottable._PivotSelect(2881,4)
 SLcache = wb.SlicerCaches.Add2(Pivottable,Field_Name,"ProdCatSlicerCache"+Cache)
 SL=SLcache.Slicers.Add(SlicerDestination=wb.ActiveSheet,Name="ProdCatSlicer"+Cache,Top=65+int(Cache)*30,Left=800,Width=210,Height=70)
 return SLcache

Connecting Slicers to each of the Pivot tables/ Charts:

def slicer_connect(SLcache):
 for j in range(1,9,1):
   k=str(j)
   SLcache.PivotTables.AddPivotTable(wb.ActiveSheet.PivotTables("ReportPivotTable"+k))

edited Dec 14, 2020 at 19:37

Michael Wu

1,2772 gold badges15 silver badges35 bronze badges

answered Dec 14, 2020 at 18:05

pjain

11 bronze badge

1 Comment

MPadilla Over a year ago

What is the variable Cache in this context? A PivotCache?

Collectives™ on Stack Overflow

Create Slicer Object in Excel Via Python

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related