Excel 透视表与透视图的自动化

Excel中透视表对象层级模型为:PivotCaches->PivotTable->PivotFields、PivotItemsPivotCache：缓冲区域，用于数据源和透视表中的缓冲，适配器，同一份数据可以创建多个透视表。PivotTable：透视表PivotFields：透视表中各透视字段PivotItems：透视字段对应的透视选项ChartObject：透视图

假设我们的数据如下：

在Excel文件中创建透视表的代码如下：Dim pvc As PivotCacheDim pvt As PivotTable‘先创建缓冲区域再创建透视表Set pvc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _                             SourceData:=region) Set pvt = pvc.CreatePivotTable(TableDestination:=dws.Range('G2'), TableName:='PivotTable1')‘如果有缓冲区，则可以直接创建ThisWorkbook.PivotCaches(1).CreatePivotTable(TableDestination:=dws.Range('B35'), TableName:='PivotTable3')‘增加列字段With pvt With .PivotFields('年级')        .Orientation = xlColumnField        .Position = 1End With With .PivotFields('班级')        .Orientation = xlRowField        .Position = 1End With'增加计数项.AddDataField .PivotFields('姓名'), '计数项: 姓名', xlCountEnd With ‘通过pivotItems来改变某一列或行的属性信息：‘设置年级字段隐藏二年级属性dws.PivotTables(1).PivotFields('年级').PivotItems('二年级').Visible = False ‘另外可以设置透视表的属性，如空单元格显示的内容：dws.PivotTables(1).NullString = '0'

透视图的创建创建ChartObject对象:Set ch2 = dws.ChartObjects.Add(400, 150, 400, 250)这样就创建了一张图表：接下来设置图表的属性：With ch2.Chart         ‘图表类型.ChartType = xlColumnStacked‘基础数据.SetSourceData Source:=dws.PivotTables('PivotTable1').RowRange‘每个图形显示数据.ApplyDataLabels xlDataLabelsShowValue‘标题     .ChartTitle.Text = '班级信息'End With接下来设置图型中每个线条的格式：With .SeriesCollection(2).Format.Fill        .Visible = msoTrue        .ForeColor.RGB = RGB(255, 255, 0)        .Transparency = 0        .Solid    End With With .SeriesCollection(1).Format.Fill        .Visible = msoTrue        .ForeColor.RGB = RGB(0, 176, 80)        .Transparency = 0        .SolidEnd With

最终透视图效果如下：