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


The information in this article applies to:

  • Microsoft Active Server Pages
  • Microsoft Office XP Web Components
This article was previously published under Q294798

SUMMARY

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.

MORE INFORMATION

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
%>

REFERENCES

For more information, see the following Microsoft Web sites:

Using Office Web Components
http://support.microsoft.com/ofd

Microsoft Office Developer Center
http://msdn.microsoft.com/office

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.

Modification Type:MinorLast Reviewed:8/23/2005
Keywords:kbhowto kbOfficeWebPivot kbPivotTable KB294798
©2004 Microsoft Corporation. All rights reserved.