OWC控件使用XML数据源展现数据

PS:本示例的XML文件只能展示二维表的数据，不能展现矩阵类型或是CUBE，不知道是不是这么写的。

HTML文件部分 BlanceAnalyseCellOwc.aspx

 <div>
        <object id="pivotTable" style="margin: 5px; width: 980px; height: 400px;" classid="clsid:0002E55A-0000-0000-C000-000000000046">
            <param name="AutoFit" value="false" />
            <table width="100%" cellpadding="0" cellspacing="0" border="0">
                <tr>
                    <td bgcolor="#336699" height="25" width="10%">
                         
                    </td>
                    <td bgcolor="#666666" width="85%">
                        <font face="宋体" color="white" size="4"><b>缺少Web组件</b> </font>
                    </td>
                </tr>
                <tr>
                    <td bgcolor="#cccccc" width="15">
                         
                    </td>
                    <td bgcolor="#cccccc" width="500px">
                        <br>
                        <font face="宋体" size="2">此网页要求Web组件。
                            <p align="center">
                                <a href="/Common/download/owc11.exe">单击此处安装Web组件。</a>.
                            </p>
                        </font>
                        <p>
                            此网页同时要求 Microsoft Internet Explorer 5.01 或更高版本。</p>
                        <p align="center" />
                        <a href="http://www.microsoft.com/windows/ie/default.htm">单击此处安装最新的 Internet Explorer。</a>
                        <br>
                         
                    </td>
                </tr>
            </table>
        </object>
    </div>

<script type="text/javascript" language="javascript">

        $(document).ready(function () {
            try {
                GetDataSource();
            } catch (e) {

            }
        });

        function GetDataSource() {
            var filename = "<%=FileName %>";
            var webroot = "<%=WebRoot %>";
            var rs = new ActiveXObject("ADODB.RecordSet");
            var url = webroot + "/Common/Owc/xml/" + filename;
            rs.open(url, "Provider=MSPersist");
            pvt = document.all.pivotTable;
            pvtconstants = pvt.Constants;
            pvt.DataSource = rs;
            pvt.ActiveView.AutoLayout();//自动加载数据中所有列，默认是不加载的。
        }

        function initPivotTable() {

            //document.all.pivotTable.ConnectionString = ''
            //document.all.pivotTable.CommandText = "select * from hmd";
            document.all.pivotTable.BackColor = 'Wheat';
            document.all.pivotTable.BorderColor = 'LightBlue';
            document.all.pivotTable.DataMemberCaption = '维度分析';

            document.all.pivotTable.DisplayFieldList = true;
            document.all.pivotTable.DisplayToolbar = true;
            document.all.pivotTable.DisplayExpandIndicator = true;
            document.all.pivotTable.DisplayAlerts = true;
            document.all.pivotTable.DisplayBranding = true;
            document.all.pivotTable.DisplayDesignTimeUI = false;
            document.all.pivotTable.DisplayFieldList = true;
            document.all.pivotTable.DisplayOfficeLogo = false;
            document.all.pivotTable.DisplayPropertyToolbox = true;  //是否打开默认连接
            document.all.pivotTable.DisplayScreenTips = true;

            document.all.pivotTable.ActiveView.TitleBar.Visible = true;
            document.all.pivotTable.ActiveView.TitleBar.Caption = '泰岳OWC工具';
            document.all.pivotTable.ActiveView.TitleBar.Font.Name = 'arial';
            document.all.pivotTable.ActiveView.TitleBar.Font.Size = 10;
            document.all.pivotTable.ActiveView.TitleBar.BackColor = 'blue';

            document.all.pivotTable.ActiveView.TotalBackColor = 'CornSilk';
            document.all.pivotTable.ActiveView.TotalFont.Name = 'arial';
            document.all.pivotTable.ActiveView.TotalFont.Size = 9;

            document.all.pivotTable.ActiveView.FieldLabelFont.Name = 'arial';
            document.all.pivotTable.ActiveView.FieldLabelFont.Size = 9;
            document.all.pivotTable.ActiveView.FieldLabelBackColor = 'Gold';

            document.all.pivotTable.ActiveView.HeaderFont.Name = 'arial';
            document.all.pivotTable.ActiveView.HeaderFont.Size = 9;
            document.all.pivotTable.ActiveView.HeaderBackColor = 'Gold';

            document.all.pivotTable.ActiveView.PropertyCaptionFont.Name = 'arial';
            document.all.pivotTable.ActiveView.PropertyCaptionFont.Size = 9;
            document.all.pivotTable.ActiveView.PropertyValueFont.Name = 'arial';
            document.all.pivotTable.ActiveView.PropertyValueFont.Size = 9;

            document.all.pivotTable.ActiveView.AllowAdditions = true;
            document.all.pivotTable.ActiveView.AllowDeletions = true;
            document.all.pivotTable.ActiveView.AllowEdits = true;

            document.all.pivotTable.ActiveView.ExpandMembers = document.all.pivotTable.Constants.plExpandNever;
            document.all.pivotTable.ActiveView.ExpandDetails = document.all.pivotTable.Constants.plExpandNever;

            for (var i = 0; i <= document.all.pivotTable.ActiveView.FieldSets.Count - 1; i++) {
                document.all.pivotTable.ActiveView.FieldSets(i).Fields(0).DetailFont.Name = 'arial';
                document.all.pivotTable.ActiveView.FieldSets(i).Fields(0).DetailFont.Size = 8;
                document.all.pivotTable.ActiveView.FieldSets(i).Fields(0).GroupedFont.Name = 'arial';
                document.all.pivotTable.ActiveView.FieldSets(i).Fields(0).GroupedFont.Size = 8;
                document.all.pivotTable.ActiveView.FieldSets(i).Fields(0).SubtotalLabelFont.Name = 'arial';
                document.all.pivotTable.ActiveView.FieldSets(i).Fields(0).SubtotalLabelFont.Size = 8;
                document.all.pivotTable.ActiveView.FieldSets(i).Fields(0).SubtotalFont.Name = 'arial';
                document.all.pivotTable.ActiveView.FieldSets(i).Fields(0).SubtotalFont.Size = 8;
                document.all.pivotTable.ActiveView.FieldSets(i).Fields(0).SubtotalBackColor = 'LightPink';
            }

            for (var i = 0; i <= document.all.pivotTable.ActiveView.FilterAxis.FieldSets.Count - 1; i++) {
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).DetailFont.Name = 'arial';
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).DetailFont.Size = 8;
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).DetailBackColor = '#EBF3FD';
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).GroupedFont.Name = 'arial';
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).GroupedFont.Name = 'arial';
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).GroupedFont.Size = 8;
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).GroupedBackColor = '#EBF3FD';
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).SubtotalLabelFont.Name = 'arial';
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).SubtotalLabelFont.Size = 8;
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).SubtotalFont.Name = 'arial';
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).SubtotalFont.Size = 8;
                document.all.pivotTable.ActiveView.FilterAxis.FieldSets(i).Fields(0).SubtotalBackColor = '#EBF3FD';
            }
            //                CSpace.DataSource = pivotTable;
            //                CSpace.AllowPropertyToolbox = true;
            //                CSpace.DisplayToolbar = true;
            //                CSpace.DisplayOfficeLogo = false; //图隐藏微软图标
            //                CSpace.Interior.Color = 'LightBlue';
            //                CSpace.Border.Color = 'LightBlue';
            //                CSpace.HasSelectionMarks = true;
            //                CSpace.AllowPropertyToolbox = true;

            //                CSpace.Charts(0).PlotArea.Interior.Color = 'Khaki';  //画图区域颜色
        }
    </script>

CS文件部分

public partial class BlanceAnalyseCellOwc : System.Web.UI.Page
    {
        GTWBll bll = new GTWBll();
        GTWHelper tools = new GTWHelper();

        public string FileName = "";
        public string WebRoot = "";

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BuilderXML();
            }
        }        

        private void BuilderXML()
        {
            try
            {
                WebRoot = string.Format("{0}://{1}", Request.Url.Scheme, Request.Url.Authority);
                string time = Request.QueryString["time"];
                string timedim = Request.QueryString["timedim"];
                string fileName = "f_blance_" + DateTime.Now.ToString("yyyyMMddhhmmsss") + ".xml";
                FileName = fileName;
                string savePath = Server.MapPath("~/Common/Owc/xml/");

                
                DataTable dt = bll.GetBalanceAnalyseTable(int.Parse(timedim), int.Parse(time));	//获取数据源DataTable

                OwcEntity entity = new OwcEntity();
                entity.DataSource = dt;
                entity.FileName = fileName;
                entity.SavePath = savePath;
                entity.Columns = new List<NOAS.Common.Owc.ColumnItem>();
                entity.Columns.Add(new ColumnItem() { ColumnNameDataFild = "NAME1", ColumnNameCn = "区域" });	//DataTable对应的列名，中文和字段名称
                entity.Columns.Add(new ColumnItem() { ColumnNameDataFild = "NAME2", ColumnNameCn = "分公司" });
                entity.Columns.Add(new ColumnItem() { ColumnNameDataFild = "TIME_ID", ColumnNameCn = "时间" });

                if (OwcHelper.BuilderXmlByEntity(entity))
                { 
                    //XML数据文件生成成功，后续处理
                }
                
            }
            catch (Exception e)
            {
                string msg = e.Message;
                throw e;
            }
        }
    }

三个辅助类

 public class OwcEntity
    {
        /// <summary>
        /// 数据源
        /// </summary>
        public DataTable DataSource { get; set; }

        /// <summary>
        /// 需要显示的列数据
        /// </summary>
        public List<ColumnItem> Columns { get; set; }

        /// <summary>
        /// XML文件名称  如:xxx.xml
        /// </summary>
        public string FileName { get; set; }

        /// <summary>
        /// 保存路径 c:\temp\
        /// </summary>
        public string SavePath { get; set; }
    }

    public class ColumnItem {
        /// <summary>
        /// 数据库字段名称
        /// </summary>
        public string ColumnNameDataFild { get; set; }

        /// <summary>
        /// 中文名称，暂不支持各种符号如().&。%等
        /// </summary>
        public string ColumnNameCn { get; set; }
    }

/// <summary>
    /// OWC数据生成工具
    /// </summary>
    public class OwcHelper
    {
        /// <summary>
        /// 生成文件
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static bool BuilderXmlByEntity(OwcEntity entity)
        {
            try
            {
                StringBuilder sbXml = new StringBuilder();
                sbXml.AppendLine(@"<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
	                                xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
	                                xmlns:rs='urn:schemas-microsoft-com:rowset'
	                                xmlns:z='#RowsetSchema'>");

                sbXml.AppendLine("<s:Schema id='RowsetSchema'>");

                int columncount = entity.DataSource.Columns.Count;

                sbXml.AppendLine("<s:ElementType name='row' content='eltOnly'>");
                List<ColumnItem> columns = new List<ColumnItem>();

                int columnindex = 0;
                foreach (var item in entity.Columns)
                {
                    if (!string.IsNullOrEmpty(item.ColumnNameDataFild))
                    {
                        columnindex++;
                        sbXml.AppendFormat("<s:AttributeType name='{0}' rs:number='{1}' rs:nullable='true' rs:writeunknown='true'>", item.ColumnNameCn, columnindex);
                        sbXml.AppendLine(GetColumnsType(entity.DataSource.Columns[item.ColumnNameDataFild]));
                        sbXml.AppendLine("</s:AttributeType>");
                        columns.Add(item);
                    }
                }
                //for (int i = 0; i < columncount; i++)
                //{
                //    sbXml.AppendFormat("<s:AttributeType name='{0}' rs:number='{1}' rs:nullable='true' rs:writeunknown='true'>", entity.DataSource.Columns[i].ColumnName, i + 1);
                //    sbXml.AppendLine(GetColumnsType(entity.DataSource.Columns[i]));
                //    sbXml.AppendLine("</s:AttributeType>");
                //    columns.Add(entity.DataSource.Columns[i].ColumnName);
                //}
                sbXml.AppendLine("<s:extends type='rs:rowbase'/>");
                sbXml.AppendLine("</s:ElementType>");
                sbXml.AppendLine("</s:Schema>");

                //数据
                sbXml.AppendLine("<rs:data>");
                int rowscount = entity.DataSource.Rows.Count;
                for (int i = 0; i < rowscount; i++)
                {
                    sbXml.Append("<z:row ");
                    foreach (var item in columns)
                    {
                        sbXml.AppendFormat("{0}='{1}' ", item.ColumnNameCn, entity.DataSource.Rows[i][item.ColumnNameDataFild].ToString());
                    }
                    sbXml.Append(" />");
                }
                sbXml.AppendLine("</rs:data>");
                sbXml.AppendLine("</xml>");

                string fileName = entity.FileName;
                string path = entity.SavePath + fileName;
                using (StreamWriter write = new StreamWriter(path))
                {
                    write.Write(sbXml.ToString());
                    write.Flush();
                    write.Close();
                }

                return true;
            }
            catch (Exception ex)
            {                
                throw ex;
            }
        }

        static string GetColumnsType(DataColumn column)
        {
            StringBuilder sbxml = new StringBuilder();
            sbxml.Append("<s:datatype ");
            if (column.DataType.ToString() == typeof(String).ToString())
            {
                sbxml.Append(" dt:type='string' ");
                sbxml.Append(" rs:dbtype='str' ");

                sbxml.AppendFormat(" dt:maxLength='{0}' ", column.MaxLength == -1 ? 255 : column.MaxLength);
            }
            else
            {
                sbxml.Append(" dt:type='number' ");
                if (column.DataType.ToString() == typeof(float).ToString())
                {
                    sbxml.Append(" rs:dbtype='varnumeric' dt:maxLength='20' rs:scale='0' rs:precision='38' ");
                }
                else
                {
                    sbxml.Append("rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='38' rs:fixedlength='true' ");
                }
            }

            sbxml.Append(" />");

            return sbxml.ToString(); ;
        }
    }

文件会在访问时生成一个XML文件，如果有多个，不要重复。还要记得处理这种经常生成的文件。OWC上面使用的是OWC11客户端。