2024-04-12终于解决python生成PIVOT下面的问题:
raise AttributeError(a)
AttributeError: xlPageField
不知道什么原因,4月初代码都可以正常使用。是PYWIN32库的方法改变了(因为新版本306升级了方法):
import win32com.client as win32 # xlApp = win32.Dispatch('Excel.Application') --原代码 xlApp = win32.gencache.EnsureDispatch('Excel.Application')在
在python中用pywin32模块来生成EXCEL程序及它的一些子对象,如:CreatePivotTable方法来生成透视表对象,再利用VBA编码来实现透视表数据及格式的设置。
用VBA编码实现对透视表自定义分组。在数据透视数据表中,可以根据日期、数字和文本值对数据进行分组。
终于搞定:pt.PivotFields('Creation Time').LabelRange.Group会报错说range对象不对。
下面是最终正确的代码:
rngGroup = pt.PivotFields("Creation Time").DataRange rngGroup.Cells(1).Group(Periods=list([False, False, False, False, True, False, True]))
Excel VBA - How to Group by Date Field in a PivotTable – (bluepecantraining.com)
上面文章说明了PivotField 对象的 LabelRange 属性
PivotField object (Excel) | Microsoft Learn 这是PivotField对象的URL,通过它可找到PivotField.LabelRange property
PivotField.LabelRange property (Excel) | Microsoft Learn 此URL里有下面一段话:
Returns a Range object that represents the cell (or cells) that contain the field label. Read-only.
Range.Group method (Excel) | Microsoft Learn. (此链接说明了RANG对象的GOUP方法)
所以,还是要回到RANGE对象去看。仔细看看Range.Group method
Group is a method with the following parameters
Name | Required/Optional | Data Type | Description |
---|---|---|---|
Start | Optional | Variant | If omitted or True , the first value in the field is used. |
End | Optional | Variant | If omitted or True , the last value in the field is used. |
By | Optional | Variant | If field is numeric , this argument specifies the size of each group. If the field is a date, use to group by x days. |
Periods | Optional | Variant | Not relevant to numeric fields |
下面是完成PIVOT GROUP的代码:
Dim ws As Worksheet Set ws = ActiveSheet Dim wb As Workbook Set wb = ActiveWorkbook Dim PTcache As PivotCache Dim PT As PivotTable Dim SLcache As SlicerCache Dim SL As Slicer Dim df As PivotField 'Define the cache for the PivotTable Set PTcache = wb.PivotCaches.Create(SourceType:=xlDatabase, _ SourceData:=Range("Sales_Data[#All]"),Version:=xlPivotTableVersion15) 'Create the PivotTable Set PT = ws.PivotTables.Add(PivotCache:=PTcache, _ TableDestination:=Range("J1"), TableName:="SalesAnalysis") PT.AddFields RowFields:="Date",ColumnFields:="Region" PT.AddDataField Field:=PT.PivotFields("Revenue"), _ Caption:="No of Transactions", Function:=xlCount 'Group Date Field by Month and Year PT.RowAxisLayout xlTabularRow Set df = PT.PivotFields("Date") df.LabelRange.Group _ Start:=True, End:=True, _ Periods:=Array(False, False, False, False, True, False, True) df.Caption = "Month"
总述:python通过PyWin32 库来实现对windows COM模型的访问。
Python for Windows Extensions download | SourceForge.net
Releases · mhammond/pywin32 (github.com) PyWin32
较复杂python代码的实现:Extending Pivot Table Data | Python Excels
如何用Excel中的VBA来做数据透视表? - 知乎 (zhihu.com) 后面有透视表的详细说明
一、其它实现方式:
1、除了上面提到的,还有下面URL中用的DataRange属性
VBA: Pivot Table Groups Dates (Years/Quarters/Mths etc) - Microsoft Community
Dim PvtTbl As PivotTable
Dim rngGroup As Range
Set PvtTbl = Worksheets("Sheet6").PivotTables("PivotTable1")
Set rngGroup = PvtTbl.PivotFields("Dates").DataRange
rngGroup.Cells(1).Group Periods:=Array(False, False, False, True, True, True, False)
当然,它也是返回:Returns a Range object ,如下URL:
PivotField.DataRange property (Excel) | Microsoft Learn
二、其它的参考:
python win32com 操作excel (tcy)_excel tcy-CSDN博客 对EXCEL的操作写得详细
python操作excel的数据透视表pivottable(基于win32com) - 知乎 (zhihu.com) 实例讲得细
其它解决方式:Group pivot items with VBA | MrExcel Message Board
使用实例PIVOT FOR PYTHON :How to Create a Pivot Table in Excel with the Python win32com Module (trenton3983.github.io)
下面代码可以获取其属性:
# 获取constants对象的所有属性 attributes = dir(win32.constants) # 打印所有属性 for cls_name in dir(win32.constants): cls = getattr(win32.constants, cls_name) print("Class:", cls_name) print("Attributes:") for attr in dir(cls): print("- ", attr) print()
下面实现对分组的内容进行判断并生成颜色。
VBA / Pivot Table / Comparing values from two fields and highlighting | MrExcel Message Board