The Office XP PivotTable component exposes a new property,
the
HTMLData property, that enables you to programmatically retrieve an HTML
representation of the PivotTable. The HTML that is returned by
HTMLData portrays both the view and the data in the PivotTable. As the
code sample in this article demonstrates, you can build a PivotTable
server-side and stream the HTML returned from the PivotTable's
HTMLData to your clients.
The following Active Server Pages (ASP) script demonstrates
how you can create a PivotTable server-side and render an HTML representation
of the PivotTable by using the
HTMLData property. The sample uses data from the Microsoft Access
Northwind.mdb sample database. To test this code, you may need to modify the
path to Northwind.mdb so that it matches your installation of Office.
<%@ Language=VBScript %>
<%
'Create an in-memory reference to the PivotTable component.
Dim oPivot
Set oPivot = Server.CreateObject("OWC10.PivotTable")
'Connect to the database and provide the commandtext for the rowset.
oPivot.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = c:\program files\microsoft office\office\" & _
"samples\northwind.mdb"
oPivot.CommandText = "Select * from [Product Sales for 1997]"
Dim oView
Set oView = oPivot.ActiveView
'Add fields to the row axis and column axis for grouping.
Dim oCategoryFields, oProductFields, oQtrFields
Set oCategoryFields = oView.FieldSets("CategoryName")
oCategoryFields.Fields(0).Caption = "Category"
oView.RowAxis.InsertFieldSet oCategoryFields
Set oProductFields = oView.FieldSets("ProductName")
oProductFields.Fields(0).Caption = "Product"
oView.RowAxis.InsertFieldSet oProductFields
Set oQtrFields = oView.FieldSets("ShippedQuarter")
oView.ColumnAxis.InsertFieldSet oQtrFields
oQtrFields.Fields(0).Caption = "Quarter"
'Add a total for the ProductSales fieldset.
oView.DataAxis.InsertTotal oView.AddTotal("Sales Total", _
oView.FieldSets("ProductSales").Fields(0), _
oPivot.Constants.plFunctionSum)
oView.Totals("Sales Total").NumberFormat = "$#,##0"
'Collapse rows and columns.
oView.ExpandDetails = oPivot.Constants.plExpandNever
'Hide the Filter axis and change the PivotTable title.
oView.FilterAxis.Label.Visible = False
oView.TitleBar.Caption = "Product Sales for 1997"
oView.TitleBar.BackColor = "Black"
'Change colors and font style for the fields and subtotals.
oPivot.BackColor = "Lavender"
oView.FieldLabelBackColor = "Lavender"
oView.TotalBackColor = "White"
oView.HeaderBackColor = "Gold"
oQtrFields.Fields(0).GroupedBackColor = "MidnightBlue"
oQtrFields.Fields(0).GroupedFont.Color = "Lavender"
oQtrFields.Fields(0).SubtotalBackColor = "Gold"
oQtrFields.Fields(0).SubtotalLabelBackColor = "MidnightBlue"
oQtrFields.Fields(0).SubtotalLabelFont.Bold = True
oQtrFields.Fields(0).SubtotalLabelFont.Color = "Lavender"
oProductFields.Fields(0).GroupedBackColor = "MidnightBlue"
oProductFields.Fields(0).GroupedFont.Color = "Lavender"
oProductFields.Fields(0).SubtotalBackColor = "LemonChiffon"
oProductFields.Fields(0).SubtotalLabelBackColor = "MidNightBlue"
oProductFields.Fields(0).SubtotalFont.Bold = True
oProductFields.Fields(0).SubtotalLabelFont.Color = "LemonChiffon"
oProductFields.Fields(0).SubtotalLabelFont.Bold = True
oCategoryFields.Fields(0).GroupedBackColor = "MidnightBlue"
oCategoryFields.Fields(0).GroupedFont.Color = "Lavender"
oCategoryFields.Fields(0).SubtotalBackColor = "Gold"
oCategoryFields.Fields(0).SubtotalLabelBackColor = "MidNightBlue"
oCategoryFields.Fields(0).SubtotalLabelFont.Bold = True
oCategoryFields.Fields(0).SubtotalLabelFont.Color = "Lavender"
Response.Write oPivot.HTMLData
%>
For more information, see the following Microsoft Web
sites:
For additional
information, click the following article number to view the article in the
Microsoft Knowledge Base:
294782
(http://support.microsoft.com/kb/294782/
)
HOWTO: Extract Cell Aggregate Values From the Office XP PivotTable Component
(c) Microsoft Corporation 2001, All Rights
Reserved. Contributions by Lori B. Turner, Microsoft Corporation.
Back to the top
