使用Xml模版文檔導出Excel

/*
 * 功能﹕使用Xml模版文檔導出Excel-已經更親于2010-6-8 添加多個Sheet工作表的導入功能
 * 編寫人:盧定煥

 * 編寫時間:2008/10/25--已經更親于2010-6-8
 * 修改功能﹕

 * 修改人:
 * 修改時間:
 *
 *我在一些網站看到有人轉載我這個代碼.
 * 這里我給你們講一下這里的用法吧.
 *首先建立Excel表格，然後編輯你想在的格式表頭,，格式是你想要導出的數據的[字段名]來代替將要導入的數據,如：
 * [id] [name] [sex]  只有這些寫在同一行就可以了，其它的格式可以隨便寫；另一些特殊的參數，也就是單個數據。非表格內容的
 *用{0}這樣的格式寫，如同C#里的string.Format("{0}",5)，本功能就是用這樣子來取代內容的。
 *
 *編完Excel之后我們將Excel保存成“XML計算表(*.XML)”在保存的格式選項里有
 *
 * 簡單調用方法如下：
 *  
*      ExportTo et = new ExportTo(@"D:/Inetpub/wwwroot/RecruiPerson/RecruitPerson/WebUILayer/ExcelTemplate/UsersInfo2.xml", this);
        et.XmlSelectDataTable("Sheet2 (2)", UsersRelationHandle.GetAllRecords(""), 5,6);
        et.XmlSelectDataTable("Sheet2", UsersRelationHandle.GetAllRecords(""), "A",8);
        et.ExportToExcel();
*如果看不懂請給我留言提問，我會給大家解答的
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using System.Xml;
using System.IO;
using System.Data;
using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using System.Xml;
using System.IO;
using System.Data;
namespace Foxconn.BusinessLogicLayer.Comm
{
    /// <summary>
    /// 使用Xml模版文檔導出Excel
    /// </summary>
    public  partial  class ExportTo
    {
        private string m_strTemplateXmlPath = string.Empty;
        private XmlDocument mXmlDoc = new XmlDocument();
        private Page m_page =null;
        /// <summary>
        /// 初始化Xml模版文件,將Xml模版產生准備導出Excel使用的Xml文檔
        /// </summary>
        /// <param name="xmlPath">Xml模版文件路經﹐使用絕對路經</param>
        /// <param name="p">使用頁面</param>
        public ExportTo(string xmlPath,Page p)
        {
            m_strTemplateXmlPath = xmlPath;
            m_page = p;

            mXmlDoc.Load(m_strTemplateXmlPath);
        }

        public void XmlSelectDataTable(DataTable dt, params object[] pOthers)
        {
            XmlSelectDataTable("Sheet1", dt, pOthers);
        }
        /// <summary>
        /// Excel的Xml文檔開始接受DataTable及其它參數的設定,默認為對第一個WorkSheet操作,否測按用戶所給的WorkSheetName操作
        /// </summary>
        /// <param name="dt">DataTable數據﹐此數據的字段名稱與Excel的Xml文檔相對應,則輸出到Xml文檔中相應的字段</param>
        /// <param name="pOthers">Xml文檔其它參數格式與C#相同如string.format("{0}{1}{2}" ,para1,para2,para3),這里的pOthers接收para1,para2,para3</param>
        public   void XmlSelectDataTable(string pSheetName,DataTable dt, params object[] pOthers)
        {
            string TemXml = m_strTemplateXmlPath;
            //set Xml Doc
         
            //取得第一個sheet
            XmlNode XmlObjectWorkBook = null;
            XmlNode XmlObjectWorksheet = null;
            XmlNode XmlObjectTable = null;
            XmlObjectWorkBook = mXmlDoc.LastChild;
            if (XmlObjectWorkBook == null || XmlObjectWorkBook.Name != "Workbook")
            {
                XmlObjectWorkBook = GetChildObjectByName(mXmlDoc.ChildNodes, "Workbook");
            }
            XmlObjectWorksheet = XmlObjectWorkBook.LastChild;

            if (XmlObjectWorksheet == null || XmlObjectWorksheet.Name != "Worksheet" || XmlObjectWorksheet.Attributes[0].Value.ToUpper().Trim() != pSheetName.ToUpper().Trim())
            {
                XmlObjectWorksheet = GetWorksheetByName(XmlObjectWorkBook.ChildNodes, pSheetName);
            }

            XmlObjectTable = GetChildObjectByName(XmlObjectWorksheet.ChildNodes, "Table");

            ResetExcelObject(XmlObjectTable, dt);

            //參數為{n}的格式化
            if (pOthers.Length > 0)
            {
                try
                {
                    XmlObjectWorksheet.InnerXml = string.Format(XmlObjectWorksheet.InnerXml, pOthers);
                }
                catch
                {

                }
            }
         
        }

        private XmlNode GetWorksheetByName(XmlNodeList pChildNodes, string pSheetName)
        {
            
            XmlNode XmlObjectWorksheet = null;
            foreach (XmlNode n in pChildNodes)
            {
                if (n.Name == "Worksheet" && n.Attributes[0].Value.ToUpper().Trim() == pSheetName.ToUpper().Trim())
                {
                    XmlObjectWorksheet = n;
                }
            }
            return XmlObjectWorksheet;
        }
        private XmlNode GetChildObjectByName(XmlNodeList pChildNodes, string pName)
        {
            XmlNode XmlObject = null;
                foreach (XmlNode n in pChildNodes)
                {
                    if (n.Name == pName)
                    {
                        XmlObject = n;
                        break;
                    }
                }
           
            return XmlObject;
        }

        /*
         * 創建時間 09/8/27 修改人：吳星
         */
        /// <summary>
        /// 將XmlDocument存儲到指定URL地址和文件格式
        /// </summary>
        /// <param name="s"></param>
        public void SaveToExcel(string s)
        {
            mXmlDoc.Save(s);
        }
        private void ResetExcelObject(XmlNode XmlObjectTable, DataTable dt)
        {
            string _strColFormat = "yyyy/MM/dd";
            //取得行

            XmlNodeList XmlObjectTableRows = XmlObjectTable.ChildNodes;
            //產生行

            XmlNode XmlCreateRow = null;
            //取得行值

            int iRowCount = int.Parse(XmlObjectTable.Attributes[1].Value);
            //保存行值

            int iRow = iRowCount;
            for (int i = 0; i < iRow; i++)
            {
                //搜索到行
                if (XmlObjectTableRows[i].Name == "Row")
                {
                    //搜索到行內帶有[的

                    int indx = XmlObjectTableRows[i].InnerXml.IndexOf("[");
                    if (indx > 0)
                    {
                        for (int j = 0; j < dt.Rows.Count; j++)
                        {   //產生副本的行結點
                            bool _isTrue = false;
                             XmlCreateRow = XmlObjectTableRows[i].Clone();
                             XmlAttribute xmlat = XmlCreateRow.Attributes["ss:Index"];
                             if (xmlat != null&&j>0)
                                 XmlCreateRow.Attributes.Remove(xmlat);
                             for (int ixmlc = 0; ixmlc < XmlCreateRow.ChildNodes.Count; ixmlc++)
                             {
                                 int _startIndex = 0;
                                 _startIndex = XmlCreateRow.ChildNodes[ixmlc].InnerText.IndexOf("[");
                                 //排除不帶有[符號的單元格
                                 if (_startIndex < 0)
                                     continue;
                                 int _endIndex = 0;
                                 string ComName = string.Empty;
                                 _endIndex = XmlCreateRow.ChildNodes[ixmlc].InnerText.IndexOf("]");
                                 //排除不帶]符號的單元格
                                 if (_endIndex < 0)
                                     continue;
                               string  CName = XmlCreateRow.ChildNodes[ixmlc].InnerText.Substring(_startIndex + 1, _endIndex - _startIndex - 1);
                               string[] cs = CName.Split('|');
                                 ComName = cs[0];
                                 if (cs.Length > 1)
                                 {
                                     _strColFormat = cs[1];

                                     XmlCreateRow.ChildNodes[ixmlc].ChildNodes[0].InnerText = XmlCreateRow.ChildNodes[ixmlc].ChildNodes[0].InnerText.Replace(CName, ComName);
                                 }
                                 //排除不是表內字段的單元格
                                 if (dt.Columns.IndexOf(ComName.Trim()) < 0)
                                     continue;
                                 //給單元格賦值

                                 string _strDataCellText = dt.Rows[j][ComName].ToString();
                                 switch (dt.Columns[ComName].DataType.Name)
                                 {
                                     case "DateTime":
                                         try
                                         {  if(dt.Rows[j][ComName].ToString().Trim()!="")
                                             _strDataCellText = DateTime.Parse(dt.Rows[j][ComName].ToString().Trim()).ToString(_strColFormat);
                                         }
                                         catch { }
                                         break;
                                 }
                                 XmlCreateRow.ChildNodes[ixmlc].ChildNodes[0].InnerText = XmlCreateRow.ChildNodes[ixmlc].ChildNodes[0].InnerText.Replace("[" + ComName + "]", _strDataCellText.Trim());
                                 _isTrue = true;
                             }
                            //產生行排除沒有數據需要替代的
                             if (!_isTrue)
                                 break;
                            //把副本放入表中,放到數據行的下面
                            XmlObjectTable.InsertAfter(XmlCreateRow, XmlObjectTableRows[i+j]);
                            iRowCount++;//表內行增加

                            XmlObjectTable.Attributes[1].Value = iRowCount.ToString();

                        }
                        //完成后刪除么內為[]這個符號的行

                        XmlObjectTable.RemoveChild( XmlObjectTableRows[i]);
                        break;
                    }
                }
                else
                {//因為行name不為Row的結點要遍歷所以把搜索值增加

                    iRow++;
                }
            }
        }
        /// <summary>
        /// 立即導出Excel文檔
        /// </summary>
        public void ExportToExcel()
        {
            ExportToExcel("record.xls");
        }
        /// <summary>
        /// 一般方式直接導出Excel,默認的WorkSheet是第一個,否則按所給的WorkSheetName
        /// </summary>
        /// <param name="p">調用方法頁面</param>
        /// <param name="xmlPath">Xml文檔的絕對路經</param>
        /// <param name="dt">要寫入導出Excel文檔的DataTable數據</param>
        /// <param name="pOthers">其它參數﹐注﹕參數個數要與Xml模版的參數個數相同</param>
        public static void ExportToExcel(Page p, string xmlPath, DataTable dt, params object[] pOthers)
        {
            ExportTo et = new ExportTo(xmlPath, p);
            et.XmlSelectDataTable(dt, pOthers);
            et.ExportToExcel();
        }
        /// <summary>
        ///  一般方式直接導出Excel,默認的WorkSheet是第一個,否則按所給的WorkSheetName
        /// </summary>
        ///<param name="pWorksheetName">導出時的所要寫入數據的WorkSheet</param>
        /// <param name="p">調用方法頁面</param>
        /// <param name="xmlPath">Xml文檔的絕對路經</param>
        /// <param name="dt">要寫入導出Excel文檔的DataTable數據</param>
        /// <param name="pOthers">其它參數﹐注﹕參數個數要與Xml模版的參數個數相同</param>
        public static void ExportToExcelWithWorksheetName(Page p, string xmlPath, string pWorksheetName, DataTable dt, params object[] pOthers)
        {
            ExportTo et = new ExportTo(xmlPath, p);
            et.XmlSelectDataTable(pWorksheetName,dt, pOthers);
            et.ExportToExcel();
        }
        public void ExportToExcel(string pExcelName)
        {
            if (mXmlDoc == null) return;
            if (!pExcelName.ToLower().EndsWith(".xls"))
            {
                pExcelName = pExcelName + ".xls";
            }
            m_page.Response.AddHeader("content-disposition", "attachment; filename=" + pExcelName + "");
            m_page.Response.ContentType = "application/vnd.ms-excel";
            m_page.Response.Write(mXmlDoc.InnerXml);
            m_page.Response.End();
        }
    }

 

}

 

Excel模版格式制作如下图

 

 

然后另存格式

 

这是导出后的格式

 

 

 

 

现在最新更新已上传到我的资源，里面还有个例子

资源下载地址

http://d.download.csdn.net/down/2710026/gs0038

 

 爱情公寓第四季