HOWTO: Use ASP to Create an Office XP PivotTable and Display the Results as HTML

Article translations Article translations
Article ID: 294798 - View products that this article applies to.
This article was previously published under Q294798
Expand all | Collapse all


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\" & _

    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), _
    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:
Using Office Web Components

Microsoft Office Developer Center
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
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.


Article ID: 294798 - Last Review: August 23, 2005 - Revision: 3.2
  • Microsoft Active Server Pages 4.0
  • Microsoft Office XP Web Components
kbhowto kbofficewebpivot kbpivottable KB294798
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from