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

This article was previously published under Q294798
This article has been archived. It is offered "as is" and will no longer be updated.
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:
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.

owc pivotlist pivot list 2002

Article ID: 294798 - Last Review: 12/06/2015 01:49:01 - Revision: 3.2

Microsoft Active Server Pages 4.0, Microsoft Office XP Web Components

  • kbnosurvey kbarchive kbhowto kbofficewebpivot kbpivottable KB294798