access编程使用数据透视表和数据透视图视图详解

时　间:2008-07-31 08:14:37
作　者:黄海   ID：11  城市：上海  QQ：3002789054
摘　要:Access编程使用数据透视表和数据透视图视图详解
正　文:

Access 中的窗体和数据表支持两种新视图：PivotTable®（数据透视表）视图和 PivotChart®（数据透视图）视图。数据透视表视图使用 Office 数据透视表组件，易于进行交互式数据分析。数据透视图视图使用 Office Chart 组件，帮助您创建动态的交互式图表。这些视图支持交互操作，例如添加、筛选和排序数据，而无需写入代码。然而，如果要生成基于用户输入的运行时视图，则需要编写代码。本文介绍了在数据透视表和数据透视图视图中执行简单任务（例如移动和筛选字段）的示例代码。

有关向窗体添加代码的详细信息，请参阅 Access 帮助。

 注释   下面的示例基于 Access 包含的罗斯文贸易示例数据库。

包含对 Office Web 组件库的引用 

要运行下面的示例代码，您的数据库必须包含对 Owc11.dll（对于 Access 2003）或 Owc10.dll（对于 Access 2002）的引用。使用 Access 2000 或更早版本创建的数据库，或使用 Access 2002 或更高版本创建但以 Access 2000 文件格式保存的数据库，不包括此引用。

将对 Owc11.dll 或 Owd10.dll 的引用添加至 Access 数据库 

打开准备添加引用的数据库。
在工具栏上单击“代码”，打开 Microsoft Visual Basic® 编辑器。
在“工具”菜单上，单击“引用”。
单击“浏览”并导航到 Owc11.dll 或 Owc10.dll 文件所在的位置。
对于 Access 2003，该文件的默认位置是 C:/Program Files/Common Files/Microsoft Shared/Web Components/11；对于 Access 2002，默认位置是 C:/Program Files/Common Files/Microsoft Shared/Web Components/10。

选择该文件，单击“打开”，然后单击“确定”。
“数据透视表”视图示例
更改视图的布局

向行区域、列区域和明细区域添加字段

下面的代码将 CustomerID、ShipVia 和 Freight 字段添加至窗体（基于“Orders”表）“数据透视表”视图的行、列和明细区域。

Dim fset1, fset2, fset3 As PivotFieldSet

Set fset1 = Me.PivotTable.ActiveView.FieldSets("CustomerID")
Set fset2 = Me.PivotTable.ActiveView.FieldSets("ShipVia")
Set fset3 = Me.PivotTable.ActiveView.FieldSets("Freight")

Me.PivotTable.ActiveView.RowAxis.InsertFieldSet fset1
Me.PivotTable.ActiveView.ColumnAxis.InsertFieldSet fset2
Me.PivotTable.ActiveView.DataAxis.InsertFieldSet fset3

添加总计字段

下面的代码创建了用于计算 CustomerID 数目的汇总字段，并将该字段添加至视图的明细区域。

Me.PivotTable.ActiveView.AddTotal "Count Of Customers", _
    Me.PivotTable.ActiveView.FieldSets("CustomerID").Fields("CustomerID"), _
    plFunctionCount

Me.PivotTable.ActiveView.DataAxis.InsertTotal _
    Me.PivotTable.ActiveView.Totals("Count Of Customers")

添加计算汇总字段

下面的代码创建了一个总计字段用于计算 7% 运费值，并将该字段添加至视图的明细区域。表达式参数将接受任何有效、带有“安全”Visual Basic for Applications (VBA) 函数的 Microsoft Jet Database Engine 表达式。

Me.PivotTable.ActiveView.AddCalculatedTotal "FTax", "运费税", "[Freight] * 0.07"

Me.PivotTable.ActiveView.DataAxis.InsertTotal Me.PivotTable.ActiveView.Totals("FTax") 注释   运行此代码多于一次将导致错误信息。

删除视图中的所有字段

下面的代码删除“数据透视表”视图中的所有字段和汇总，使之空白。其基本思想是循环各个坐标轴并删除所有总计及字段。

 注释   此示例代码并不删除应用于字段和汇总的格式。

Dim ptable As PivotTable
Set ptable = Me.PivotTable
With ptable.ActiveView
    Do While .RowAxis.FieldSets.Count > 0
        .RowAxis.RemoveFieldSet 0
    Loop

    Do While .ColumnAxis.FieldSets.Count > 0
        .ColumnAxis.RemoveFieldSet 0
    Loop
    Do While .FilterAxis.FieldSets.Count > 0
        .FilterAxis.RemoveFieldSet (0)
    Loop
    Do While .DataAxis.FieldSets.Count > 0
        .DataAxis.RemoveFieldSet (0)
    Loop
    Do While .DataAxis.Totals.Count > 0
        .DataAxis.RemoveTotal (0)
    Loop
End With

筛选和排序数据

筛选行字段

下面的代码筛选行区域中的 CustomerID 字段。通过使用 IncludedMember 属性将您要查看的值传递给视图。

将此代码添加至 Form_DblClick (Cancel As Integer) 事件。要运行此代码，请在打开后双击窗体。

Dim arrFilter As Variant

arrFilter = Array("ALFKI", "BLAUS", "CHOPS", "EASTC")

Me.PivotTable.ActiveView.RowAxis.FieldSets("CustomerID") _
    .Fields("CustomerID").IncludedMembers = arrFilter

为字段指定自定义排序

下面的代码根据您定义的自定义顺序对行字段中的项目进行排序。要创建此顺序，您需要指定顺序及排序项目的方向。

将此代码添加至 Form_DblClick (Cancel As Integer) 事件。要运行此代码，请在打开后双击窗体。

Dim arrSort As Variant

arrSort = Array("Vice President, Sales", "Sales Manager", _
              "Sales Representative", "Inside Sales Coordinator")

Me.PivotTable.ActiveView.RowAxis.FieldSets("Title").Fields("Title") _
              .OrderedMembers = arrSort

Me.PivotTable.ActiveView.RowAxis.FieldSets("Title").Fields("Title") _
              .SortDirection = plSortDirectionCustom

设置元素格式

更改显示小计的单元格的背景颜色和字体样式

下面的代码设置视图中小计的格式。使用小计所属的字段的引用，然后对小计属性进行相应调整。

Me.PivotTable.ActiveView.rowaxis.FieldSets(0).Fields(0).SubtotalBackColor = "Red"
Me.PivotTable.ActiveView.rowaxis.FieldSets(0).Fields(0).SubtotalFont.Bold = True更改明细字段的背景颜色和数值格式

下面的代码设置明细区域中字段的格式。第一行设置视图中第一个明细字段的背景颜色，第二行设置字段的自定义数值格式。

Me.PivotTable.Activeview.DataAxis.FieldSets(0).Fields(0).DetailBackColor = "Green"
Me.PivotTable.ActiveView.DataAxis.FieldSets(0).Fields(0).NumberFormat = "$#,##0.00" 注释   每次在数据透视表视图中打开对象时都必须运行此代码以设置字段的数值格式。

显示或隐藏元素

显示或隐藏明细

下面的代码隐藏并显示此视图中所有项目的明细。

Me.PivotTable.ActiveData.HideDetails
Me.PivotTable.ActiveData.ShowDetails
如果想在此视图中显示或隐藏数据子集的明细，请使用 ChildRowMembers 对象的 ShowDetails 和 HideDetails 方法。下面的代码显示和隐藏行区域中第八个项目的明细。

Me.PivotTable.ActiveData.RowAxis.RowMember _
.ChildRowMembers(7).ShowDetails
Me.PivotTable.ActiveData.RowAxis.RowMember _
.ChildRowMembers(7).HideDetails

显示或隐藏下拉区域

下面的代码显示或隐藏“数据透视表”视图中的下拉区域。与“数据透视图”视图不同，“数据透视表”视图允许您显示和隐藏指定的下拉区域。

Me.PivotTable.ActiveView.DataAxis.Label.Visible = False
Me.PivotTable.ActiveView.RowAxis.Label.Visible = True
Me.PivotTable.ActiveView.ColumnAxis.Label.Visible = True
Me.PivotTable.ActiveView.FilterAxis.Label.Visible = False导出视图

将视图导出至 Microsoft Excel

使用下面的代码将“数据透视表”视图导出至 Excel。您可能需要此代码来利用 Excel 的更高级数据透视功能（例如打印）。

Me.PivotTable.Export "此处是您的文件名", plExportActionOpenInExcel
将视图导出为图像

下面的代码将“数据透视表”视图导出为 .gif 图像。将视图导出为图像可以使您与其他用户共享此视图的静态图像。

' 视图大小为 1024 X 1024。
Me.PivotTable.ExportPicture "C:\yourdirectory\filename.gif", , 1024, 1024
在运行时限制用户

下面的代码防止用户在“数据透视表”视图上执行任务（例如筛选和分组）。请注意没有禁用字段列表的属性。

Me.PivotTable.AllowFiltering = False
Me.PivotTable.AllowGrouping = False
Me.PivotTable.AllowCustomOrdering = False
Me.PivotTable.AllowDetail = False
Me.PivotTable.AllowPropertyToolbox = False
 注释   AllowEdits、AllowAdditions 和 AllowDeletion 的属性设置为 False，您不能将其设置为 True。

获取用户双击的单元格的值

下面的代码确定用户在“数据透视表”视图中双击的位置，然后返回所选项目的值和类型。

要查看此代码的操作，请将下面的代码添加至窗体（其默认视图设置为“数据透视表”视图）的 Form_DblClick (Cancel As Integer) 事件过程。

If Me.CurrentView = acCurViewPivotTable Then

' 创建一些变量。
 Dim sel As Object
 Dim pivotagg As PivotAggregate
 Dim sTotal As String
 Dim sColMems As String
 Dim sRowMems As String
 Dim sFilters As String
 Dim sMsg As String

' 获取所选范围。
Set sel = Me.PivotTable.Selection

' 用户可以单击以下类型对象之一：
' PivotAggregates, PivotTotals, PivotMembers, PivotFields, etc.
' 使用 TypeName() 函数来确定对象类型。

MsgBox "选定属性返回一个 '" & TypeName(sel) & "' 对象。", _
vbInformation, "选定属性类型"

' 如果类型为 PivotAggregates，则显示如何获取用于定义聚合的行
' 和列成员。

If TypeName(sel) = "PivotAggregates" Then

' PivotAggregates 可以包含很多项目，
' 但由于用户可能只双击了单个项目，
' 所以现在它只包含一个项目，即 item(0)。
Set pivotagg = sel.Item(0)

' 显示值。
MsgBox "您双击的单元格值为 '" & pivotagg.Value & _
"'.", vbInformation, "单元格的值"

' 获取总计名称、行和列成员及当前筛选器。
sTotal = pivotagg.Total.Caption

sColMems = BuildFullName(pivotagg.Cell.ColumnMember)
sRowMems = BuildFullName(pivotagg.Cell.RowMember)

' 生成并显示消息。
sMsg = "值为 " & sTotal & " by " & sRowMems & " by " & sColMems

If Len(sFilters) > 0 Then
sMsg = sMsg & " for " & Left(sFilters, Len(sFilters) - 2)
End If

MsgBox sMsg, vbInformation, "值信息"

End If ' typename(sel) = "PivotAggregates"

End If ' Me.CurrentView = acCurViewPivotTable
然后将下面的函数添加至窗体的模块。

Function BuildFullName(PivotMem)

Dim pmTemp As PivotMember ' Temporary PivotMember reference
Dim sFullName As String

' 由获取当前成员的名称开始。
sFullName = PivotMem.Caption

' 将临时变量设置为当前成员。
Set pmTemp = PivotMem

' 在未单击任何项目之前定位到父级目录。
While Not (pmTemp.ParentMember Is Nothing)
Set pmTemp = pmTemp.ParentMember
sFullName = pmTemp.Caption & "-" & sFullName
Wend

' 返回 sFullName。
BuildFullName = sFullName

End Function
在“数据透视表”视图中编译、保存、关闭和重新打开窗体。将字段和总计添加到视图，然后双击单元格来查看会发生什么。

“数据透视图”视图示例
更改视图的布局

向视图添加字段

下面的代码将字段添加到视图中的类别和值下拉区域。

将此代码添加至窗体（其默认视图设置为“数据透视图”视图）的 Form_Open (Cancel As Integer) 事件处理程序。

Me.ChartSpace.SetData c.chDimCategories, c.chDataBound, "Orderdate"

Me.ChartSpace.SetData c.chDimValues, c.chDataBound, "Freight"启用多个图表并设置统一的刻度

下面的代码启用多图表并使图表空间中的图表具有统一的刻度。

forms(0).Form.ChartSpace.HasMultipleCharts = True
forms(0).Form.ChartSpace.HasUnifiedScales = True拆分图表的坐标轴

下面的代码拆分图表的坐标轴，以使较小的值和较大的值以合理的形式显示。拆分实际上是在与坐标轴相关的 chScaling 对象中设置的。

将下面的代码添加至窗体的 Form_Open (Cancel As Integer) 事件处理程序。

Dim sc As ChScaling
Set sc = Me.ChartSpace.Charts(0).Axes(chAxisPositionLeft).Scaling

sc.HasSplit = True
sc.SplitMinimum = 1
sc.SplitMaximum = 3
清除图表空间并生成常值图表

下面的代码清除图表空间中的所有图表并生成一个常值图表。常值图表不绑定任何数据；仅用于演示的目的。此代码还将关闭图表的选择标志。

Me.ChartSpace.Clear
Me.ChartSpace.BuildLitChart
Me.ChartSpace.Charts(0).Type = chChartTypeColumnClustered
排序和设置数据格式

排序字段

下面的代码对图表系列轴的第一个字段执行排序命令。

Dim ser As ChSeries
Set ser = Me.ChartSpace.Charts(0).SeriesCollection(0)

ser.Select
Me.ChartSpace.Commands(chCommandSortDescending).Execute

添加图表标题并设置格式

下面的代码为图表空间添加标题并设置其格式。如果图表空间包含多个图表，则采用类似的过程为每个图表添加标题。

Me.ChartSpace.HasChartSpaceTitle = True
With Me.ChartSpaceTitle
    .Caption = "Here's Your Title"
    .Interior.Color = vbWhite
    .Border.Color = vbBlack
    .Border.DashStyle = chLineDashDot
    .Font.Size = 16
    .Font.Name = "verdana"
    .Font.Color = vbGreen
End With
更改坐标轴的位置和刻度

下面的代码将坐标轴从左上部移至右下部，并将坐标轴的刻度由线形更改为记录。

Me.ChartSpace.Charts(0).Axes(0).Position = chAxisPositionTop
Me.ChartSpace.Charts(0).Axes(1).Position = chAxisPositionRight
Me.ChartSpace.Charts(0).Axes(0).Scaling.Type = chScaleTypeLogarithmic添加格式映射（条件格式化）

下面的代码在“数据透视图”视图中添加一个至 Access 的映射。将此代码添加至窗体（其默认视图设置为“数据透视图”视图）的 Form_Open (Cancel As Integer) 事件处理程序。

' 以下三行代码将向图表中添加字段。第三个值是
' 格式值，它将用于设置数据点格式。在本例中，
' 运费的值将用带颜色的代码表示，从而显示给出数据点值的
' 相关名次。
Me.ChartSpace.SetData c.chDimCategories, c.chDataBound, "OrderDate"
Me.ChartSpace.SetData c.chDimValues, c.chDataBound, "Freight"
Me.ChartSpace.SetData c.chDimFormatValues, c.chDataBound, "Freight"

' 添加格式映射，然后设置格式映射的属性。
' 值较大的数据点用红色阴影表示，而值较小的数据点用
' 蓝色阴影表示。
Dim ch
Set ch = Me.ChartSpace.Charts(0)
ch.SeriesCollection(0).FormatMap.Segments.Add
ch.SeriesCollection(0).FormatMap.Segments(0).Begin.Interior.Color = vbRed
ch.SeriesCollection(0).FormatMap.Segments(0).End.Interior.Color = vbBlue
ch.SeriesCollection(0).FormatMap.Segments(0).HasDiscreteDivisions = True
' 将图表类型设置到列串。
ch.Type = chChartTypeColumnClustered

' 美化图表的外观。
With Me.ChartSpace
    .DisplayFieldButtons = True
    .DisplayToolbar = False
    .HasChartspaceLegend = True
    .HasChartSpaceTitle = True
    .ChartSpaceTitle.Caption = "Freight Totals Over Time"
End With
显示或隐藏元素

显示或隐藏下拉区域

下面的代码隐藏图表的下拉区域。

Me.ChartSpace.DisplayFieldButtons = False
添加图例

下面的代码将图例添加至视图并设置其格式，然后将其移至视图底部。

Me.ChartSpace.Charts(0).HasLegend = True
With Me.ChartSpace.Charts(0).Legend
    .Interior.Color = vbBlack
    .Font.Color = vbWhite
    .Border.Color = vbGreen
End With
Me.ChartSpace.charts(0).Legend.Position = chLegendPositionBottom

添加次要网格线

下面的代码将次要网格线添加至 0 坐标轴。

Me.ChartSpace.Charts(0).Axes(0).HasMinorGridlines = True

添加自定义网格线

下面的代码演示了如何在“数据透视图”视图中向窗体绘制对象和添加文本。此示例计算一个系列上几个点的平均值并根据此值在图表上绘制一条线。

将此代码添加至窗体（其默认视图设置为“数据透视图”视图）的 Form_Open (Cancel As Integer) 事件处理程序。向下拉区域添加字段，并向图表添加图例。

' 此代码将重新绘制图表空间，从而引发窗体的
' AfterFinalRender 事件。该事件处理程序将向图表绘制对象和
' 添加文本。
Me.ChartSpace.repaint
将下面的代码添加至 Form_AfterFinalRender (drawObject as object) 事件处理程序。

' 这些代码将设置执行计算以及
' 在图表上准确绘制线条和文本所需的变量。

Dim average as Double
Dim total as Double
Dim number as Double
Dim s as chSeries
Dim p as chPoint
Dim x as Integer
Dim y as Integer
Dim pa as chPlotArea

' 这些代码将在所有图表的所有数据点中循环，
' 并计算所有值的汇总和数据点的数量。然后，代码将计算
' 点的平均值。
Set pa = Me.ChartSpace.Charts(0).PlotArea
For Each s In Me.ChartSpace.Charts(0).SeriesCollection
    For Each p In s.Points
        total = total + p.GetValue(chDimValues)
        number = number + 1
    Next
Next
Average = total/number
' 这些代码使用系列 ValueToPoint 方法将平均值转换为
' 图表上的像素位置。
Me.ChartSpace.Charts(0).SeriesCollection(0).ValueToPoint x, y, 0, average
' 此代码设置 drawObject（本例中为图表）属性。
drawObject.Line.Color = "red"
drawObject.Line.Weight = 2
drawObject.Font.Color = "red"
drawObject.Font.Size = 9
' 此代码使用 DrawLine 和 DrawText 方法在图表上绘制一条直线和一些
' 文本。其他变量绘制方法可以提供很多
' 有趣的绘图能力。
drawObject.DrawLine pa.Left + 1, y, pa.Right - 1, y
drawObject.DrawText "Average", pa.left - 45, y - 10

将“数据透视图”视图导出为图像

下面的代码将“数据透视图”视图导出为 .gif 图像。将视图导出为图像可以使您与其他用户共享此视图的静态图像。

Me.ChartSpace.ExportPicture "C:\yourdirectory\filename.gif", , 1024, 1024

在运行时限制用户

下面的代码在“数据透视图”视图中为 Access 窗体设置保护属性。请注意，没有限制访问字段列表的属性。

Me.ChartSpace.AllowFiltering = False
Me.ChartSpace.AllowGrouping = False
Me.ChartSpace.AllowPropertyToolbox = False
Me.ChartSpace.HasSelectionMarks = False

Access软件网QQ交流群 (群号:54525238) 　　　　　　Access源码网店