OWC PivotTable的使用方法

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来控制。 

<objectid="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代码如下：

DataSet转换为 Recordset'FILE:       ADOConversion.vb                                                                                  

'AUTHOR:     Kevin Rucker

'DATE:       02/27/2005                                                                                       

'DESCRIPTION: Based on the codein Microsoft Support Knowledge Base article #316337

'            "How To Convert an ADO.NET DataSet to ADO Recordset in Visual Basic.NET ..."

'            Thisclass converts an ADO.NET DataSet to the XML persistance formatfor

'            an ADODB Recordset.

 

Imports System.IO

Imports System.Xml

Imports System.Text

Imports System.Data

 

'Thisclass 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 AsString) AsString

 

       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 AsString) AsString

       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()) AsString

       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中的使用的实例截个图：