Office Web Component(OWC)中的PivotTable(透视表)使用XML数据源

06 November 2006

以前使用OWC的PivotTable都是为该对象设定一个连接串,然后在指定一条sql语句,这个PivotTable就能连接到数据库上,并把内容显示出来了.

这是在局域网上.但如果在互联网上呢,你还敢把连接串放到网页里面吗?即使放了,你的数据库服务器也很有可能在内网中,网关不做端口转发,你仍旧访问不到.那该怎么办呢?

别怕,我们用XML作为其数据源.

步骤是这样的:

客户端利用Ajax请求服务器的一个页面(datasource.aspx)
datasource.aspx将访问数据库,并将读到的结果放到一个DataTable对象中
利用自己写的一个函数将DataTable的XML转换成RecordSet XML
返回该XML
客户端声明一个XML对象,将字符串转换成XML Document
客户端声明一个RecodSet对象,将XMLDocument作为其数据源,并打开数据源
指定PivotTable的数据源为上面的RecordSet对象,从而完成了对数据源的加载
后面再根据需要操作PivotTable将某些列添加到行/列/过滤/内容区域

总体顺序是这样的,我把部分代码贴上来,供大家参考.

/// 
/// 将DataTable转换成RecordXML函数
/// 
/// 要转换的DataTable
/// 转换好的XML字符串
public static string ConvertDataTableToXML(DataTable dt)
{
    /*DataTable支持的数据类型
        system.int64
        system.byte[]
        system.boolean
        system.string
        system.datetime
        system.decimal
        system.double
        system.int32
        system.single
        system.int16
        system.object
        system.byte
        system.guid
     */
    string TempValue;

    for(int i = ; i < dt.Columns.Count; i++)
    {
        TempValue = dt.Columns[i].ColumnName;
        Regex rex = new Regex("\\W");
        TempValue = rex.Replace(TempValue, "");

        rex = new Regex("^\\d");
        if(rex.Replace(TempValue, "").Length != TempValue.Length)
        {
            TempValue = "N" + TempValue;
        }
        dt.Columns[i].ColumnName = TempValue;
    }

    StringBuilder sb = new StringBuilder();

    sb.Append("");
    sb.Append("");
    sb.Append("\t");

    for (int i = ; i < dt.Columns.Count; i++)
    {
        string DataType;
        switch (dt.Columns[i].DataType.ToString().ToLower())
        {
            case "system.boolean":
                DataType = "string";
                break;

            case "system.byte":
            case "system.int16":
            case "system.int32":
            case "system.int64":
                DataType = "int";
                break;

            case "system.decimal":
            case "system.double":
            case "system.single":
                DataType = "float";
                break;

            case "system.string":
                DataType = "string";
                break;
            case "system.datetime":
                DataType = "datetime";
                break;

            default:
                throw new ArgumentException();
        }
        sb.Append("\t\t");
        sb.Append("\t\t\t");
        sb.Append("\t\t");
    }

    sb.Append("\t\t");
    sb.Append("\t");
    sb.Append("");
    sb.Append("");

    for (int i = ; i < dt.Rows.Count; i++)
    {
        sb.Append("\t", "").Replace("'", "").Replace("\"", "");

            if (dt.Columns[j].DataType.ToString().ToLower() == "system.datetime" && TempValue == string.Empty)
            {
                TempValue = INVALIDDATE.ToString(DATEMASK);
            }
            sb.Append(dt.Columns[j].ColumnName + "='" + TempValue + "' ");
        }
        sb.Append("/>");
    }

    sb.Append("");
    sb.Append("");

    return sb.ToString();
}

通过这个函数就能将一个DataTable转换成RecordSet XML

下面是完整的客户端页面,包括详尽的注释(牵扯到公司有关的代码都被我Cut掉了,但不影响这个页面)

<html>

<head>
  <script language="javascript" type="text/javascript">
    window.onload = LoadPage;
    var SEP = "/,/";
    var SEP2 = "/,,/";

    //初始化报告参数
    //TODO:这里为了做成通用的报告，变量名称需要变更
    //需要添加到行区域的字段名称集合,以Common.SEP分割
    var strRowFieldNames = "<%=Server.UrlDecode(Request.QueryString["RowFieldNames"])%>";
    //需要添加到列区域的字段名称集合,以Common.SEP分割
    var strColumnFieldNames = "<%=Server.UrlDecode(Request.QueryString["ColumnFieldNames"])%>";
    //过滤字段名称集合,以Common.SEP分割
    var strFilterFieldNames = "<%=Server.UrlDecode(Request.QueryString["FilterFieldNames"])%>";
    //需要进行分段汇总的字段名称
    //格式为:字段名/,,/分组类型/,,/起始值/,,/间隔值/,/第二个分组
    //其中/,,/为Common.SEP2, /,/为Common.SEP
    var strGroupFieldNamesAndValues = "<%=Server.UrlDecode(Request.QueryString["GroupFieldNamesAndValues"])%>";

    //图表类型
    var intChartType = parseInt("<%=Server.UrlDecode(Request.QueryString["ChartType"])%>");

    //声明变量，用来存储页面上的OWC Object
    //透视表对象及透视表的常量对象
    var pvt, pvtconstants;

    //图表对象及图标的常量对象
    var cht, chtconstants;

    function LoadPage() {
      //初始化透视表
      InitPivotTable();

      //初始化图表
      InitChartSpace();
    }

    /**
     * 初始化透视表
     */
    function InitPivotTable() {
      //声明RecordSet对象
      var adors = new ActiveXObject("ADODB.Recordset");

      //声明XMLDocument对象
      //TODO:msxml2.domdocument有可能是msxml3.domdocument或msxml4.domdocument,有待证明
      var xmldoc = new ActiveXObject("msxml2.domdocument");

      //服务器端返回的XML字符串,用来构造RecordSet
      var str = "<%=XMLString%>";

      //XMLDocument对象加载XML字符串
      xmldoc.loadXML(str);

      //RecordSet设定数据源为上面的XMLDocument对象,并打开
      adors.Open(xmldoc);

      pvt = document.pvtTable;

      pvtconstants = pvt.Constants;
      //设定透视表的数据源为上面的RecordSet对象
      pvt.DataSource = adors;

      //设定透视表的一些属性,具体请参考OWC For VBA
      pvt.AutoFit = true;
      pvt.DisplayToolbar = true;
      pvt.DisplayExpandIndicator = true;
      pvt.DisplayAlerts = true;
      pvt.DisplayBranding = true;
      pvt.DisplayDesignTimeUI = true;
      pvt.DisplayFieldList = true;
      pvt.DisplayOfficeLogo = false;
      pvt.DisplayPropertyToolbox = false;
      pvt.DisplayScreenTips = true;

      pvt.ActiveView.TitleBar.Visible = true;
      pvt.ActiveView.TitleBar.Caption = "<%=Title%>";
      pvt.ActiveView.TitleBar.Font.Name = "arial";
      pvt.ActiveView.TitleBar.Font.Size = 10;

      pvt.ActiveView.TotalBackColor = "CornSilk";
      pvt.ActiveView.TotalFont.Name = "arial";
      pvt.ActiveView.TotalFont.Size = 8;
      pvt.ActiveView.FieldLabelFont.Name = "arial";
      pvt.ActiveView.FieldLabelFont.Size = 8;
      pvt.ActiveView.HeaderFont.Name = "arial";
      pvt.ActiveView.HeaderFont.Size = 8;
      pvt.ActiveView.PropertyCaptionFont.Name = "arial";
      pvt.ActiveView.PropertyCaptionFont.Size = 8;
      pvt.ActiveView.PropertyValueFont.Name = "arial";
      pvt.ActiveView.PropertyValueFont.Size = 8;
      pvt.ActiveView.ExpandMembers = pvtconstants.plExpandNever;
      pvt.ActiveView.ExpandDetails = pvtconstants.plExpandNever;

      //设定每一个字段的字体,字号,颜色等
      for (var i = ; i <= pvt.ActiveView.FieldSets.Count - 1; i++) {
        pvt.ActiveView.FieldSets(i).Fields(0).DetailFont.Name = "arial";
        pvt.ActiveView.FieldSets(i).Fields(0).DetailFont.Size = 8;
        pvt.ActiveView.FieldSets(i).Fields(0).GroupedFont.Name = "arial";
        pvt.ActiveView.FieldSets(i).Fields(0).GroupedFont.Name = "arial";
        pvt.ActiveView.FieldSets(i).Fields(0).GroupedFont.Size = 8;
        pvt.ActiveView.FieldSets(i).Fields(0).SubtotalLabelFont.Name = "arial";
        pvt.ActiveView.FieldSets(i).Fields(0).SubtotalLabelFont.Size = 8;
        pvt.ActiveView.FieldSets(i).Fields(0).SubtotalFont.Name = "arial";
        pvt.ActiveView.FieldSets(i).Fields(0).SubtotalFont.Size = 8;
        pvt.ActiveView.FieldSets(i).Fields(0).SubtotalBackColor = "LightSteelBlue";
      }

      //将所有字段都添加到"过滤"区域
      for (var i = ; i < pvt.ActiveView.FieldSets.Count; i++) {
        pvt.ActiveView.FilterAxis.InsertFieldSet(pvt.ActiveView.FieldSets(i));
      }

      /*
       * 添加一个"合计"字段
       * AddTotal方法原型: AddTotal(Name, Field, Function)
       * Name合计字段的名字
       * Field需要进行合计的列对象
       * Function合计方法
       */
      var ctotal = pvt.ActiveView.AddTotal("汇总", pvt.ActiveView.FieldSets(0).Fields(0), pvtconstants.plFunctionCount);

      /*
       * 将"合计"字段添加到"数据区域"
       * InsertTotal方法原型: InsertTotal(Total, Before)
       * Total是一个PivotTotal类型的对象,也就是上面声明的对象
       * Bebore是指定要在其之前插入总计的总计索引。如果不指定该参数，则总计插入到集合的末尾。
       */
      pvt.ActiveView.DataAxis.InsertTotal(ctotal, 0);

      //设定行区域
      SetRowFields();

      //设定列区域
      SetColumnFields();

      //设定明细区域
      SetDataFields();

      //设定分组
      SetGroupFields();

      /*
       * 隐藏指定对象的明细单元格。
       * 如果指定对象为 PivotData 对象，将隐藏所有明细单元格。
       * 如果指定对象为 PivotRowMember 对象，将隐藏该行的所有明细单元格。
       * 如果指定对象为 PivotColumnMember 对象，将隐藏该列的所有明细单元格。
       */
      pvt.ActiveData.HideDetails();
    }

    //设定行区域
    function SetRowFields() {
      if (strRowFieldNames == "") {
        return;
      }

      var OrgCollection = strRowFieldNames.split(SEP);

      //循环要显示在行上的字段
      for (var i = ; i < OrgCollection.length; i++) {
        pvt.ActiveView.FieldSets(OrgCollection[i]).Fields(0).IsIncluded = true;
        pvt.ActiveView.RowAxis.InsertFieldSet(pvt.ActiveView.FieldSets(OrgCollection[i]));
      }
    }

    //设定列区域
    function SetColumnFields() {
      if (strColumnFieldNames == "") {
        return;
      }

      var ColumnFieldsCollection = strColumnFieldNames.split(SEP);

      for (var i = ; i < ColumnFieldsCollection.length; i++) {
        pvt.ActiveView.FieldSets(ColumnFieldsCollection[i]).Fields(0).IsIncluded = true;
        pvt.ActiveView.ColumnAxis.InsertFieldSet(pvt.ActiveView.FieldSets(ColumnFieldsCollection[i]));
      }
    }

    //设定数据明细区域
    function SetDataFields() {
      var ColumnFieldsCollection = strColumnFieldNames + SEP + strRowFieldNames + SEP + strFilterFieldNames + SEP;
      for (var i = ; i < pvt.ActiveView.FieldSets.Count; i++) {
        if (ColumnFieldsCollection.indexOf(pvt.ActiveView.FieldSets(i).Name + SEP) == -1) {
          pvt.ActiveView.DataAxis.InsertFieldSet(pvt.ActiveView.FieldSets(i));
        }
      }
    }

    //设定字段分组
    function SetGroupFields() {
      //得到分组字段定义的数组,数组的每个元素是字段分组的一个完整定义
      var GroupCollection = strGroupFieldNamesAndValues.split(SEP);

      for (var i = ; i < GroupCollection.length; i++) {
        if (GroupCollection[i] == "") {
          break;
        }

        //得到其中的一个字段分组定义
        var OneGroupDefination = GroupCollection[i].split(SEP2);

        if (OneGroupDefination.length != 4) {
          break;
        }

        //字段名称
        var FieldName = OneGroupDefination[];
        //分组类型
        var GroupMethod = parseInt(OneGroupDefination[1]);
        //基数
        var BaseAmount = parseFloat(OneGroupDefination[2]);
        //间隔值
        var IntervalAmount = parseFloat(OneGroupDefination[3]);

        if (IntervalAmount == 0) {
          pvt.ActiveView.FieldSets(FieldName).Fields(0).GroupOn = pvtconstants.plGroupOnEachValue;
        }
        else {
          pvt.ActiveView.FieldSets(FieldName).Fields(0).GroupOn = GroupMethod;
          pvt.ActiveView.FieldSets(FieldName).Fields(0).GroupInterval = IntervalAmount;
          pvt.ActiveView.FieldSets(FieldName).Fields(0).GroupStart = BaseAmount;
        }
      }
    }

    //初始化图表
    function InitChartSpace() {
      //设定图表对象
      cht = document.chtSpace;

      //图表常量
      chtconstants = cht.Constants;

      //设定图表数据源
      cht.DataSource = pvt;

      //设置图表的一些属性
      cht.DisplayFieldButtons = false;
      cht.object.Border.Color = -2;
      cht.DisplayToolbar = true;
      cht.DisplayFieldButtons = false;
      cht.DisplayFieldList = false;
      cht.DisplayOfficeLogo = false;
      cht.HasChartSpaceLegend = true;
      cht.HasPassiveAlerts = false;

      //如果是柱状图(0)或3D柱状图(46)
      if (intChartType ==  || intChartType == 46) {
        cht.style.width = "100%";
      }

      //设定图表类型
      cht.Charts(0).Type = intChartType;
    }
  </script>

</head>

<body>
  <table cellspacing="0" cellpadding="0" border="0">
    <tr>
      <td>
        <object id="pvtTable" style="width: 502px; height: 217px" height="217" width="502"
          classid="clsid:0002E552-0000-0000-C000-000000000046" viewastext="VIEWASTEXT">
          <param name="XMLData" value='<xml xmlns:x="urn:schemas-microsoft-com:office:excel">
                                        <x:PivotTable>
                                        <x:OWCVersion>10.0.0.6619     </x:OWCVersion>
                                        <x:DisplayScreenTips/>
                                        <x:CubeProvider>msolap.2</x:CubeProvider>
                                        <x:CacheDetails/>
                                        <x:PivotView>
                                        <x:IsNotFiltered/>
                                        </x:PivotView>
                                        </x:PivotTable>
                                        </xml>'>
        </object>
      </td>
    </tr>
    <tr>
      <td>
        <object id="chtSpace" classid="clsid:0002E556-0000-0000-C000-000000000046" viewastext>
        </object>
      </td>
    </tr>
    <tr height="100%">
      <td>
      </td>
    </tr>
  </table>
</body>

</html>