Creating/Updating an excel file with slicers from python

Question

I have some python code which creates a few pandas data frames which I need to put into a spreadsheet, with different data on multiple sheets and with slicers for easy filtering. This then gets sent out so others can easily see/filter the data. Currently, I manually copy and paste the updated tables into a template excel file, resave and send out, but I would like to automate this if possible.

My first thought was to use xlsxwriter to write the formatting to generate this spreadsheet, however as per this answer, slicers aren't supported by the library, so that's no good.

I then considered seeing if I could automate opening a template excel document with the slicers already in place, delete the previous data in the tables and add in the new data. I found this question on using openpyxl, and used some of the answers to try the below code to first remove the rows:

wb = openpyxl.load_workbook(r'TEST.xlsx')
ws = wb['sheet 1']
rows = ws.max_row
ws.delete_rows(13, rows)
wb.save(r'TEST2.xlsx')

However this removes the slicers and all formatting in the excel file completely, and not only saves a new copy but also removes the formatting from the original template as well, so that's no good either.

Does anyone know of a way I could automate creating or updating an excel file from python with slicers?

Edit: I'm not looking for recommendations on opinions on different libraries as the close vote suggests. I'm looking for a way to solve a specific coding problem, if it's possible, for which the code and research I've already tried has brought me up short.

Answer 1

If it helps I tried Xlwings and Win32com.
Some issues getting Xlwings to add a slicer but seems OK with win32com

Using the data range as shown in the screen shot, "A1:H27" I've created an Excel Table then added a slicer on 'Car Model'.
Using 'record macro' you can get the details for creating a Table from a Range and then adding the slicer; e.g.
the macro line for;
Creating an Excel Table with name 'Table2' from a range of data

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$H$27"), , xlYes).Name = "Table2"

Note; The Table creation command uses Excel enumerated values like xlSrcRange and xlYes. These can be imported from a constants file, though Win32com does not appear to have one. You can use the one from Xlwings or set the contant values as done here or just simply use the numeric value.

Adding 'Car Model' is

ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Table2"), "Car Model").Slicers.Add ActiveSheet, , "Car Model", "Car Model", 10, 600, 150, 210

This can then be translated into something Win32com can use.

from win32com import client

xlSrcRange = 1
xlYes = 1

path = r'<path>\TEST.xlsx'
sheet = 'sheet 1'
table_name="Table2"

excel = client.Dispatch("Excel.Application")
wb = excel.Workbooks.Open(path)
ws = wb.Sheets[sheet]

### Convert range to Table
ws.ListObjects.Add(
        SourceType=xlSrcRange,
        Source=ws.Range("$A$1:$H$27"),
        XlListObjectHasHeaders=xlYes
).Name=table_name

### Add Slicer
wb.SlicerCaches.Add2(ws.ListObjects(table_name), "Car Model").Slicers.Add(
        SlicerDestination=ws,
        Name="Car Model Name",
        Caption="Car Model Caption",
        Top=10,
        Left=600,
        Width=150,
        Height=210)

wb.SaveAs(r'<path>\TEST_out.xlsx')
wb.Close()
excel.Quit()