OWC PivotTable的使用方法

OWC(Office Web Component)是微软提供的一组Activex组件，其中的PivotTable控件提供了数据透视的功能。OWC既可以在WinForm程序中使用，也可以在嵌入在IE中使用。

一. 安装

如果系统中已经安装了Office 2003，则系统已经自动安装好OWC11.0了，否则可以从网上下载安装包来安装。安装完成后可以在C:\Program Files\Common Files\Microsoft Shared\Web Components\11\2052目录下找到OWC的帮助和API参考手册。不过其中的API手册是及其难用。

二. 嵌入到浏览器中使用

将以下代码加入到源程序中即可，控件的大小也可以使用CSS来控制。 

<object id="PivotTable1" style="margin: 5px;" classid="clsid:0002E55A-0000-0000-C000-000000000046">
</object>

上面的是OWC11版本的控件，如果是其它的版本，则它的Classid是不一样的。可以在注册表中查找PivotTable来找到该控件相应的classid.

PivotTable自身带有工具栏，右键菜单，还可以在运行时通过对话框来配置控件，方便了用户的使用。不过有时我们希望只提供给用户受限的功能，比如隐藏工具栏和菜单什么的。这可以通过控件提供的一组属性来控制，这些属性包括：

• DisplayAlerts，是否允许在运行时显示警告和消息
• DisplayDesignTimeUI， 是否显示“命令和选项”对话框
• DisplayExpandIndicator， 是否显示细节展开指示器
• DisplayFieldList,是否显示字段列表
• DisplayScreenTips,是否显示屏幕提示
• DisplayToolbar， 是否显示工具栏
• AutoFit, 是否根据内容自动调整控件大小

要屏蔽菜单的话，必须处理近件的BeforeContextMenu(x, y, Menu, Cancel)事件，并设置其中的Cancle参数为True. 示例的Javascrip代码如下。

var beforeContextMenu = function(x,y,menu,cancle){
        cancle.Value = true;
}
this.pivotTable.attachEvent('BeforeContextMenu',beforeContextMenu);

二. 数据源

通过给PivotTable的DataSource属性赋值来指定它的数据源。PivotTable控件支持多种数据源。

• 关系数据库
• Excel工作表
• OLAP数据库

对于后两种数据源，我没有试过，就不说了。下面只以关系数据库为例。由于我是在IE中使用PivotTable控件的，所以只能使用离线数据。基本的思路是

客户端的脚本示例如下:

var xml = ...; // 从服务器端发过来的Xml文本
var rs = new ActiveXObject('ADODB.Recordset');			
var doc = new ActiveXObject('MSXML2.DOMDocument');			
doc.loadXML(xml);
rs.Open(doc);
PivotTable1.DataSource = rs;	

但是且慢，还有一个问题要解决。现在我的服务器是基于Asp.net的，所有数据库访问也是基于ADO.net的，而不是ADO。ADO.Recordset和DataSet都可以直接保存为Xml文件，但不幸的是，它们的格式是不兼容的。要解决这个问题，要么在服务器端使用ADO来访问数据，要么想办法将DataSet的保存为和Recordset相容的XML格式文档。经过一番Baidu,发现微软的一篇文章，点击这儿查看原文。文章所列的VB.net 代码如下：

'FILE:        ADOConversion.vb                                                                                   
'AUTHOR:      Kevin Rucker
'DATE:        02/27/2005                                                                                        
'DESCRIPTION: Based on the code in Microsoft Support Knowledge Base article #316337
'             "How To Convert an ADO.NET DataSet to ADO Recordset in Visual Basic.NET ..."
'             This class converts an ADO.NET DataSet to the XML persistance format for 
'             an ADODB Recordset.

Imports System.IO
Imports System.Xml
Imports System.Text
Imports System.Data

'This class converts a .NET DataSet to an ADODB Recordset.
Public Class ConvertToRs

    'Method Name : GetADORS
    'Description : Takes a DataSet and converts into a Recordset. The converted
    '              ADODB recordset is returned as a Recordset persisted XML string.
    'Output      : String containing ADODB formatted XML
    'Input parameters:
    '            1. DataSet object
    '            2. Database Name
    Public Function GetADORS(ByVal DS As DataSet, ByVal dbName As String) As String

        Try
            'Create a MemoryStream to contain the XML
            Dim mStream As New MemoryStream
            'Create an XmlWriter object, to write the formatted XML to the MemoryStream
            Dim xWriter As New XmlTextWriter(mStream, Nothing)

            'Additional formatting for XML
            xWriter.Indentation = 8
            xWriter.Formatting = Formatting.Indented
            'call this Sub to write the ADONamespaces
            WriteADONamespaces(xWriter)
            'call this Sub to write the ADO Recordset Schema
            WriteSchemaElement(DS, dbName, xWriter)
            'Call this sub to transform the data portion of the Dataset
            TransformData(DS, xWriter)
            'Flush all input to XmlWriter
            xWriter.Flush()

            'Prepare the return value
            mStream.Position = 0
            Dim Buffer As Array
            Buffer = Array.CreateInstance(GetType(Byte), mStream.Length)
            mStream.Read(Buffer, 0, mStream.Length)
            Dim TextConverter As New UTF8Encoding
            Return TextConverter.GetString(Buffer)

        Catch ex As Exception
            'Returns error message to the calling function.
            Err.Raise(100, ex.Source, ex.ToString)
        End Try

    End Function


    'Add ADO XML namespaces to the XML output
    Private Sub WriteADONamespaces(ByRef xWriter As XmlTextWriter)
        'Use the following line to change the encoding if special characters are required
        'writer.WriteProcessingInstruction("xml", "version='1.0' encoding='ISO-8859-1'")

        'Add XML start element
        xWriter.WriteStartElement("", "xml", "")

        'Append the ADO Recordset namespaces
        xWriter.WriteAttributeString("xmlns", "s", Nothing, "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
        xWriter.WriteAttributeString("xmlns", "dt", Nothing, "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882")
        xWriter.WriteAttributeString("xmlns", "rs", Nothing, "urn:schemas-microsoft-com:rowset")
        xWriter.WriteAttributeString("xmlns", "z", Nothing, "#RowsetSchema")
        xWriter.Flush()
    End Sub

    'Add Schema element to the XML output
    Private Sub WriteSchemaElement(ByVal DS As DataSet, ByVal dbName As String, ByRef xWriter As XmlTextWriter)
        'write element Schema
        xWriter.WriteStartElement("s", "Schema", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
        xWriter.WriteAttributeString("id", "RowsetSchema")

        'write element ElementType
        xWriter.WriteStartElement("s", "ElementType", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")

        'write the attributes for ElementType
        xWriter.WriteAttributeString("name", "", "row")
        xWriter.WriteAttributeString("content", "", "eltOnly")
        xWriter.WriteAttributeString("rs", "updatable", "urn:schemas-microsoft-com:rowset", "true")

        WriteSchema(DS, dbName, xWriter)
        'write the end element for ElementType
        xWriter.WriteFullEndElement()

        'write the end element for Schema
        xWriter.WriteFullEndElement()
        xWriter.Flush()
    End Sub

    'Add field definitions to the schema
    Private Sub WriteSchema(ByVal DS As DataSet, ByVal dbName As String, ByRef xWriter As XmlTextWriter)
        Dim i As Int32 = 1
        Dim DC As DataColumn

        For Each DC In DS.Tables(0).Columns

            DC.ColumnMapping = MappingType.Attribute

            xWriter.WriteStartElement("s", "AttributeType", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
            'write all the attributes
            xWriter.WriteAttributeString("name", "", DC.ToString)
            xWriter.WriteAttributeString("rs", "number", "urn:schemas-microsoft-com:rowset", i.ToString)
            xWriter.WriteAttributeString("rs", "baseCatalog", "urn:schemas-microsoft-com:rowset", dbName)
            xWriter.WriteAttributeString("rs", "baseTable", "urn:schemas-microsoft-com:rowset", DC.Table.TableName.ToString)
            xWriter.WriteAttributeString("rs", "keycolumn", "urn:schemas-microsoft-com:rowset", DC.Unique.ToString)
            xWriter.WriteAttributeString("rs", "autoincrement", "urn:schemas-microsoft-com:rowset", DC.AutoIncrement.ToString)
            'write child element
            xWriter.WriteStartElement("s", "datatype", "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882")
            'write attributes
            xWriter.WriteAttributeString("dt", "type", "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882", GetDatatype(DC.DataType.ToString))
            xWriter.WriteAttributeString("dt", "maxlength", "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882", DC.MaxLength.ToString)
            xWriter.WriteAttributeString("rs", "maybenull", "urn:schemas-microsoft-com:rowset", DC.AllowDBNull.ToString)
            'write end element for datatype
            xWriter.WriteEndElement()
            'end element for AttributeType
            xWriter.WriteEndElement()
            xWriter.Flush()
            i = i + 1
        Next
        DC = Nothing
    End Sub

    'Function to get the ADO compatible datatype
    Private Function GetDatatype(ByVal DType As String) As String
        Select Case (DType)
            Case "System.Int32", "System.Int16", "System.Integer"
                Return "int"
            Case "System.DateTime"
                Return "dateTime.iso8601tz"
            Case "System.String"
                Return "string"
            Case "System.Byte[]"
                Return "bin.hex"
            Case "System.Boolean"
                Return "boolean"
            Case "System.Guid"
                Return "guid"
            Case Else
                Return "string"
        End Select
    End Function


    'Transform the data format to ADO Recordset data format
    'This only transforms the data
    Private Sub TransformData(ByVal DS As DataSet, ByRef xWriter As XmlTextWriter)
        'Loop through DataSet and add data to XML
        xWriter.WriteStartElement("", "rs:data", "")
        Dim i As Long
        Dim j As Integer
        'For each row...
        For i = 0 To DS.Tables(0).Rows.Count - 1
            'Write the start element for the row
            xWriter.WriteStartElement("", "z:row", "")
            'For each field in the row...
            For j = 0 To DS.Tables(0).Columns.Count - 1
                'Write the attribute that describes this field and it's value
                If DS.Tables(0).Columns(j).DataType.ToString = "System.Byte[]" Then
                    'Binary data must be properly encoded (bin.hex)
                    If Not IsDBNull(DS.Tables(0).Rows(i).Item(DS.Tables(0).Columns(j).ColumnName)) Then
                        xWriter.WriteAttributeString(DS.Tables(0).Columns(j).ColumnName, DataToBinHex(DS.Tables(0).Rows(i).Item(DS.Tables(0).Columns(j).ColumnName)))
                    End If
                Else
                    If Not IsDBNull(DS.Tables(0).Rows(i).Item(DS.Tables(0).Columns(j).ColumnName)) Then
                        xWriter.WriteAttributeString(DS.Tables(0).Columns(j).ColumnName, CType(DS.Tables(0).Rows(i).Item(DS.Tables(0).Columns(j).ColumnName), String))
                    End If
                End If
            Next
            'End the row element
            xWriter.WriteEndElement()
        Next
        'Write the end element for rs:data
        xWriter.WriteEndElement()
        'Write the end element for xml
        xWriter.WriteEndElement()
        xWriter.Flush()
    End Sub

    'Helper function - encodes binary data to a bin.hex string
    Private Function DataToBinHex(ByVal thisData As Byte()) As String
        Dim sb As New StringBuilder
        Dim i As Integer = 0
        For i = 0 To thisData.Length - 1
            'First nibble of byte (4 most significant bits)
            sb.Append(Hex((thisData(i) And &HF0) / 2 ^ 4))
            'Second nibble of byte (4 least significant bits)
            sb.Append(Hex(thisData(i) And &HF))
        Next
        Return sb.ToString
    End Function

End Class

 

所以，基本上服务器端的代码就变成了下面这样(C#代码):

DataTable table = pt.Query("userName");
var convert = new ConvertToRs();
DataSet dataSet = new DataSet();
dataSet.Tables.Add(table);
var xml = convert.GetADORS(dataSet, "db");

Response.Clear();
Response.ContentType = "text/xml";
Response.Write(xml);
Response.End();

三. 设置透视字段

设置好数据源后，就要设置相关的字段了。有四种字段：

• 行字段, 用于行分类的字段
• 列字段, 用于列分类的字段
• 值字段, 要进行分析的字段
• 分页字段, 用于筛选的字段

这个步骤比较简单

通过PivotTable.ActiveView.FieldSets属性可以访问到所有的FieldSet对象，可以通过字段名称来获得相应的Fieldset对象。然后使用ActiveView.RowAxis.InsertFieldSet方法增加一个行字段。相应的通过ColumnAxis,DataAxis,PageAxis的InsertFieldSet方法来设置字段。

四. 设置汇总的显示方式

字段的汇总可以以多种方式显示。

• plShowAsPercentOfColumnParent 以相对于每项的父列总计的百分比显示总计。
• plShowAsPercentOfColumnTotal 以相对于每项的列总计的百分比显示总计。
• plShowAsPercentOfGrandTotal 以相对于所有数据总计的百分比显示总计。
• plShowAsPercentOfRowParent 以相对于每项的父行总计的百分比显示总计。
• plShowAsPercentOfRowTotal 以相对于每项的行总计的百分比显示总计。

可以通过PivotTotal对象的ShowAs属性指定汇总的显示方式，ActiveView.DataAxis.Totals集合中包含了所有的PivotTotal对象。

OWC中的PivotTable控件的功能非常强大，不过它的模型也比较复杂，加上自带的示例比较少，所以我也只是简单的使用，还有一些问题没能解决。下面一张PivotTable在IE中的使用的实例截个图：