python实现excel数据透视表,Python：在工作表中刷新数据透视表

I'm building a python script that will allow me to open a Excel 2010 worksheet and print it out.

I got most of the way

import win32com.client

office = win32com.client.Dispatch("Excel.Application")

wb = office.Workbooks.Open(r"path\to\excel\file\to\print.xlsm")

count = wb.Sheets.Count

for i in range(count):

ws = wb.Worksheets[i]

pivotCount = ws.PivotTables().Count

for j in range(1, pivotCount+1):

#TODO code to refresh each pivot table

ws.PrintOut()

print "Worksheet: %s - has been sent to the printer" % (ws.Name)

As you can see I'm still missing the refreshing of the pivot tables in the worksheet.

The VBA code for refreshing is:

ActiveSheet.PivotTables(1).PivotCache.Refresh

I can't seem to break the code into python win32com syntax. The closest I got is:

wb.WorkSheets(5).PivotTables(1).PivotCache.Refresh

which gives > but no result in the the worksheet.

Any help would be highly appreciated!

SOLUTION

I ended up finding the solution myself, but gonna keep the post so it can help all programmers with a similar problem.

import win32com.client

office = win32com.client.Dispatch("Excel.Application")

wb = office.Workbooks.Open(r"path\to\excel\file\to\print.xlsm")

count = wb.Sheets.Count

for i in range(count):

ws = wb.Worksheets[i]

ws.Unprotect() # IF protected

pivotCount = ws.PivotTables().Count

for j in range(1, pivotCount+1):

ws.PivotTables(j).PivotCache().Refresh()

# Put protection back on

ws.Protect(DrawingObjects=True, Contents=True, Scenarios=True, AllowUsingPivotTables=True)

ws.PrintOut()

print "Worksheet: %s - has been sent to the printer" % (ws.Name)

Don't forget to vote up if you like or used the code.

解决方案

I found my problem. I had a protection on the worksheet... Silly me..