I am currently using the xlwings package to manipulate an excel file. So far, this package is fantastic, but I do not find a lot of specific documentation on how to modify a pivot table. My main question is how can I change the data source for a specific pivot table?
I think this is a general question so I won't provide any kind of code or excel file.
Thank you for the help.
CodePudding user response:
Ok, a friend of mine found this website with an answer https://blog.csdn.net/weixin_39906906/article/details/111374735
but since it's in Chinese, I will post the necessary code below.
I don't understand everything about this answer. The win32c
object is unknown to me and therefore, I am not comfortable in explaining everything.
import xlwings as xw
import win32com.client as win32
win32c = win32.constants
# open you excel workbook
wb = xw.Book('excel.xlsx')
# select sheet containing the pivot table
sheet_with_pivot_table = wb.sheets['sheet_pivot_table']
# Write the data range as written in the excel app
data_range = 'sheet_with_data!$A$1:$D$4'
# get the pivot table
pivot_table = sheet_with_pivot_table.api.PivotTables('pivot_table_name')
# This applies the new data
pivot_table.ChangePivotCache(wb.api.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=data_range, Version=win32c.xlPivotTableVersion12))
Hopefully, this helps someone else.
CodePudding user response:
See in this example, with the pivotname
that you can change.
import xlwings as xw
app_excel = xw.App(visible = False)
wbook = xw.Book( 'Excelfile.xlsx' )
wbook.sheets['datatab'].select()
wbook.api.ActiveSheet.PivotTables('pivotname').PivotCache().refresh()