Microsoft KB Archive/294798

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

Article ID: 294798

Article Last Modified on 8/23/2005

-

APPLIES TO


 * Microsoft Active Server Pages 4.0
 * Microsoft Office 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(&quot;OWC10.PivotTable&quot;) 'Connect to the database and provide the commandtext for the rowset. oPivot.ConnectionString = &quot;Provider = Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source = c:\program files\microsoft office\office\&quot; & _ &quot;samples\northwind.mdb&quot;

oPivot.CommandText = &quot;Select * from [Product Sales for 1997]&quot; 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(&quot;CategoryName&quot;) oCategoryFields.Fields(0).Caption = &quot;Category&quot; oView.RowAxis.InsertFieldSet oCategoryFields Set oProductFields = oView.FieldSets(&quot;ProductName&quot;) oProductFields.Fields(0).Caption = &quot;Product&quot; oView.RowAxis.InsertFieldSet oProductFields Set oQtrFields = oView.FieldSets(&quot;ShippedQuarter&quot;) oView.ColumnAxis.InsertFieldSet oQtrFields oQtrFields.Fields(0).Caption = &quot;Quarter&quot;

'Add a total for the ProductSales fieldset. oView.DataAxis.InsertTotal oView.AddTotal(&quot;Sales Total&quot;, _       oView.FieldSets(&quot;ProductSales&quot;).Fields(0), _        oPivot.Constants.plFunctionSum) oView.Totals(&quot;Sales Total&quot;).NumberFormat = &quot;$#,##0&quot; '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 = &quot;Product Sales for 1997&quot; oView.TitleBar.BackColor = &quot;Black&quot;

'Change colors and font style for the fields and subtotals. oPivot.BackColor = &quot;Lavender&quot; oView.FieldLabelBackColor = &quot;Lavender&quot; oView.TotalBackColor = &quot;White&quot; oView.HeaderBackColor = &quot;Gold&quot;

oQtrFields.Fields(0).GroupedBackColor = &quot;MidnightBlue&quot; oQtrFields.Fields(0).GroupedFont.Color = &quot;Lavender&quot; oQtrFields.Fields(0).SubtotalBackColor = &quot;Gold&quot; oQtrFields.Fields(0).SubtotalLabelBackColor = &quot;MidnightBlue&quot; oQtrFields.Fields(0).SubtotalLabelFont.Bold = True oQtrFields.Fields(0).SubtotalLabelFont.Color = &quot;Lavender&quot;

oProductFields.Fields(0).GroupedBackColor = &quot;MidnightBlue&quot; oProductFields.Fields(0).GroupedFont.Color = &quot;Lavender&quot; oProductFields.Fields(0).SubtotalBackColor = &quot;LemonChiffon&quot; oProductFields.Fields(0).SubtotalLabelBackColor = &quot;MidNightBlue&quot; oProductFields.Fields(0).SubtotalFont.Bold = True oProductFields.Fields(0).SubtotalLabelFont.Color = &quot;LemonChiffon&quot; oProductFields.Fields(0).SubtotalLabelFont.Bold = True

oCategoryFields.Fields(0).GroupedBackColor = &quot;MidnightBlue&quot; oCategoryFields.Fields(0).GroupedFont.Color = &quot;Lavender&quot; oCategoryFields.Fields(0).SubtotalBackColor = &quot;Gold&quot; oCategoryFields.Fields(0).SubtotalLabelBackColor = &quot;MidNightBlue&quot; oCategoryFields.Fields(0).SubtotalLabelFont.Bold = True oCategoryFields.Fields(0).SubtotalLabelFont.Color = &quot;Lavender&quot; Response.Write oPivot.HTMLData %>

