XML生成Excel列表实例

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml;
using System.Xml.Serialization;

namespace BusinessRules
{
    public class ReportTableHelper
    {
        public static MemoryStream GenerateRdlc(List<ReportParas> paras, string reportName)
        {
            #region 先前的版本
            //XmlElement theBook = null, theElem = null, root = null;
            //XmlDocument xmldoc = new XmlDocument();

            ////找到文件路径后读取文件
            //string path = AppDomain.CurrentDomain.BaseDirectory + "/" + reportName;
            //xmldoc.Load(path);
            //root = xmldoc.DocumentElement;

            //#region 声明
            //XmlNodeList matrixFieldList = root.GetElementsByTagName("Textbox");
            //XmlNodeList tableFieldList = root.GetElementsByTagName("Value");
            //XmlNodeList pointList = root.GetElementsByTagName("Left");
            //#endregion

            //#region 对传入的数据进行处理，修改xml模板
            //int index = 0;
            //int pointIndex = 0;
            ////在XML中找到需要隐藏的数据列
            //for (int x = 0; x < paras.Count; x++)
            //{
            //    for (int i = 0; i < matrixFieldList.Count; i++)
            //    {
            //        if (matrixFieldList[i].Attributes["Name"].Value == paras[x].ParaValue)
            //        {
            //            //隐藏所找到的数据
            //            matrixFieldList[i].InnerXml += "<Visibility><Hidden>true</Hidden></Visibility>";
            //            //将Matrix数据列中的宽设置为零 达到隐藏该列的效果
            //            matrixFieldList[i].ParentNode.ParentNode.ParentNode.ChildNodes[0].InnerText = "0cm";
            //            index = i;//将当前的index保存，用来对应隐藏表头
            //            break;
            //        }
            //    }
           
            //    //在XML中找到需要隐藏的表头列
            //    for (int j = 0; j < tableFieldList.Count; j++)
            //    {
            //        if (tableFieldList[j].InnerText == paras[x].ParaHeader)
            //        {
            //            pointIndex = tableFieldList.Count - 2;
            //            //隐藏所找到的数据
            //            tableFieldList[j].ParentNode.InnerXml += "<Visibility><Hidden>true</Hidden></Visibility>";
            //            //重新设置各列的坐标
            //            while (pointIndex > j)
            //            {
            //                string content = matrixFieldList[pointIndex - 1].ChildNodes[4].InnerText;
            //                matrixFieldList[pointIndex].ChildNodes[4].InnerText = content;
            //                pointIndex--;
            //            }
            //            pointIndex++;
            //            break;
            //        }
            //    }

            //    #region 将Table中的Header对应列宽设置为零 达到隐藏该列的效果
            //    //XmlNodeList contentList = root.GetElementsByTagName("Style");
            //    //for (int temp = 0; temp < contentList.Count; temp++)
            //    //{
     
            //    //    contentList[temp].InnerXml += "<TextAlign>Center</TextAlign>";
            //    //}
            //    #endregion
            //}
            //#endregion

            //#region 将修改后的xml文件串行化为MemoryStream对象，返回
            //MemoryStream ms = new MemoryStream();
            //XmlSerializer serializer = new XmlSerializer(typeof(XmlDocument));
            //serializer.Serialize(ms, xmldoc);
            //ms.Position = 0;
            //return ms;
            //#endregion
            #endregion

            #region 声明
            XmlElement root = null;
            XmlDocument xmldoc = new XmlDocument();
            #endregion

            #region 找到文件路径后读取文件
            string path = AppDomain.CurrentDomain.BaseDirectory + "/" + reportName;
            xmldoc.Load(path);
            root = xmldoc.DocumentElement;
            #endregion

            #region 定义xmlNodeList
            XmlNodeList matrixFieldList = root.GetElementsByTagName("Textbox");
            XmlNodeList tableFieldList = root.GetElementsByTagName("Value");
            #endregion

            #region 添加命名空间
            XmlNamespaceManager nsmanager = new XmlNamespaceManager(xmldoc.NameTable);
            nsmanager.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner");
            nsmanager.AddNamespace("xx", "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition");
            #endregion

            #region 重构XM结构 使所有Rectangle中的TextBox节点都包含Left子节点
            bool leftExsit = false;//是否存在Left节点
            XmlNodeList leftList = root.GetElementsByTagName("Rectangle");
            XmlNode reportList = null;
            if (leftList.Count > 0)
            {
                //找到ReportItems节点
                for (int b = 0; b < leftList[0].ChildNodes.Count; b++)
                {
                    if (leftList[0].ChildNodes[b].Name == "ReportItems")
                    {
                        reportList = leftList[0].ChildNodes[b];
                    }
                }
                if (reportList != null)
                {
                    for (int temp = 0; temp < reportList.ChildNodes.Count; temp++)
                    {
                        for (int a = 0; a < reportList.ChildNodes[temp].ChildNodes.Count; a++)
                        {
                            if (reportList.ChildNodes[temp].ChildNodes[a].Name == "Left")
                            {
                                leftExsit = true;//Left节点存在
                            }
                        }
                        if (!leftExsit)//不存在Left节点，则添加Left节点
                        {
                            reportList.ChildNodes[temp].InnerXml += "<Left>0cm</Left>";
                        }
                    }
                }
                else//不存在节点，抛出异常
                {
                    throw new Exception("Must include \"ReportItems\" in the Node \"Rectangle\"");
                }
            }
            #endregion

            #region 对传入的数据进行处理，修改xml模板
            int index = 0;
            int pointIndex = 0;
            //在XML中找到需要隐藏的数据列
            for (int x = 0; x < paras.Count; x++)
            {
                for (int i = 0; i < matrixFieldList.Count; i++)
                {
                    if (matrixFieldList[i].Attributes["Name"].Value == paras[x].ParaValue)
                    {
                        //隐藏所找到的数据
                        matrixFieldList[i].InnerXml += "<Visibility><Hidden>true</Hidden></Visibility>";
                        //将Matrix数据列中的宽设置为零 达到隐藏该列的效果
                        matrixFieldList[i].ParentNode.ParentNode.ParentNode.ChildNodes[0].InnerText = "0cm";
                        index = i;//将当前的index保存，用来对应隐藏表头
                        break;
                    }
                }
                string content = "";
                //在XML中找到需要隐藏的表头列
                for (int j = 0; j < tableFieldList.Count; j++)
                {
                    if (tableFieldList[j].InnerText == paras[x].ParaHeader)
                    {
                        pointIndex = tableFieldList.Count - 2;
                        //隐藏所找到的数据
                        tableFieldList[j].ParentNode.InnerXml += "<Visibility><Hidden>true</Hidden></Visibility>";
                        //重新设置各列的坐标
                        while (pointIndex > j)
                        {
                            //遍历所有子节点 得到坐标值后 依次更新先前的节点坐标
                            for (int c = 0; c < matrixFieldList[pointIndex - 1].ChildNodes.Count; c++)
                            {
                                if (matrixFieldList[pointIndex - 1].ChildNodes[c].Name == "Left")
                                {
                                    content = matrixFieldList[pointIndex - 1].ChildNodes[c].InnerText;
                                }
                            }
                            if (content == "")//Left节点不存在，跑出异常
                            {
                                throw new Exception("Missing \"Left\" Node");
                            }
                            for (int d = 0; d < matrixFieldList[pointIndex].ChildNodes.Count; d++)
                            {
                                if (matrixFieldList[pointIndex].ChildNodes[d].Name == "Left")
                                {
                                    matrixFieldList[pointIndex].ChildNodes[d].InnerText = content;
                                }
                            }
                            pointIndex--;
                        }
                        pointIndex++;
                        break;
                    }
                }
            }
            #endregion

            #region 将修改后的xml文件串行化为MemoryStream对象，返回
            MemoryStream ms = new MemoryStream();
            XmlSerializer serializer = new XmlSerializer(typeof(XmlDocument));
            serializer.Serialize(ms, xmldoc);
            ms.Position = 0;
            return ms;
            #endregion
        }
    }
}