在工作表中刷新数据透视表

在工作表中刷新数据透视表

本文介绍了Python:在工作表中刷新数据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在构建一个python脚本,让我打开一个Excel 2010工作表并打印出来。



我得到了最多的方法

  import win32com.client 

office = win32com.client.Dispatch(Excel.Application)
wb = office.Workbooks.Open(rpath\to\excel\file\to\print.xlsm)

count = wb.Sheets.Count
for我的范围(计数):
ws = wb.Worksheets [i]

pivotCount = ws.PivotTables()。计数
为范围内的j(1,pivotCount + 1 )
#TODO代码刷新每个数据透视表

ws.PrintOut()
打印工作表:%s - 已发送到打印机%(ws.Name )

如您所见,我仍然缺少工作表中的数据透视表刷新。 / p>

刷新的VBA代码是:

  ActiveSheet.PivotTables(1 ).PivotCache.Refresh 

我似乎不能打破代码到python win32com语法。我最接近的是:

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

其中< bound方法CDispatch.Refresh< COMObject PivotCache>> 但没有结果在工作表。



任何帮助将非常感谢!



解决方案



我最终自己找到解决方案,但要保留帖子,以便它可以帮助所有程序员有一个类似的问题。

  import win32com.client 

office = win32com.client.Dispatch( Excel.Application)
wb = office.Workbooks.Open(rpath\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()。对于范围(1,pivotCount + 1)中的j计数

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

#将保护放回
ws.Protect(DrawingObjects = True,Contents = True,Scenarios = True,AllowUsingPivotTables = True)

ws.PrintOut()
printWorksheet:% s - 已发送到打印机%(ws.Name)

不要忘记投票如果您喜欢或使用代码。

解决方案

我发现了我的问题。我在工作表上有一个保护...傻我..


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 <bound method CDispatch.Refresh of <COMObject PivotCache>> 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..

这篇关于Python:在工作表中刷新数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-23 20:46