UltraWebGrid手写导出excel方法(非自带控件方法)

 

//因为采用了Infragistics控件，所以进行了一部分的集成，如果
//做其它方面的用途，可以对控件部分做适当的修改。

using System;
using System.Data;
using System.Collections;
using System.Collections.Specialized;
using Infragistics.WebUI.UltraWebGrid;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
using System.Xml.XPath;

namespace WEBUI_1
{
    /// <summary>
    /// GridToExcel 的摘要说明。
    /// </summary>
    public class GridToExcel
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public GridToExcel()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        /// <summary>
        /// 根据Grid显示样式，导出数据集数据到指定名称的文件中
        /// </summary>
        ///<param name="strPath">导出路径</param>
        ///<param name="grid">当前显示Grid</param>
        ///<param name="ds">当前数据集</param>
        public void ExportDataByGridWithXSL(string strPath,UltraWebGrid grid,DataSet ds)
        {
            DataSet _ds = GetFilterDataSet(grid,ds);
            BuildExcel(_ds,strPath);
        }
        
        /// <summary>
        /// 创建转换格式文件(XSL)
        /// </summary>
        /// <param name="ds">要导出的数据集</param>
        /// <param name="XslPath">xsl文件存放路径</param>
        private  void GetXSLFile(DataSet ds,string XslPath)
        {
            string strColumn = "";
            string strRow = "";
            string dsName=ds.DataSetName;
            string tableName=ds.Tables[0].TableName;
            string header = dsName + "/" + tableName;
            foreach(DataColumn clm in ds.Tables[0].Columns)
            {
                //特殊字符 <,>,",*,%,(,),& 替换
                //*************************************************
                //*************************************************
                // 符号         xml下的值      excel中的值
                //  < --------  _x003C_  ------ &lt;
                //  > --------　_x003E_  ------ &gt;
                //  " --------  _x0022_  ------ &quot;
                //  * --------  _x002A_  ------ *
                //  % --------  _x0025_  ------ %
                //  & --------  _x0026_  ------ &amp;
                //  ( --------  _x0028_  ------ (
                //  ) --------  _x0029_  ------ )
                //  = --------  _x003D_  ------ =
                //*************************************************
                //*************************************************

                string strClmName = clm.ColumnName;
                string strRowName = clm.ColumnName;
                
                if(strClmName.IndexOf("&")!=-1)
                    strClmName=strClmName.Replace("&","&amp;");
                if(strClmName.IndexOf("<")!=-1)
                    strClmName=strClmName.Replace("<","&lt;");
                if(strClmName.IndexOf(">")!=-1)
                    strClmName=strClmName.Replace(">","&gt;");
                if(strClmName.IndexOf(""")!=-1)
                    strClmName=strClmName.Replace(""","&quot;");
                
                if(strRowName.IndexOf("<")!=-1)
                    strRowName=strRowName.Replace("<","_x003C_");
                if(strRowName.IndexOf(">")!=-1)
                    strRowName=strRowName.Replace(">","_x003E_");
                if(strRowName.IndexOf(""")!=-1)
                    strRowName=strRowName.Replace(""","_x0022_");
                if(strRowName.IndexOf("*")!=-1)
                    strRowName=strRowName.Replace("*","_x002A_");
                if(strRowName.IndexOf("%")!=-1)
                    strRowName=strRowName.Replace("%","_x0025_");
                if(strRowName.IndexOf("&")!=-1)
                    strRowName=strRowName.Replace("&","_x0026_");
                if(strRowName.IndexOf("(")!=-1)
                    strRowName=strRowName.Replace("(","_x0028_");
                if(strRowName.IndexOf(")")!=-1)
                    strRowName=strRowName.Replace(")","_x0029_");
                if(strRowName.IndexOf("=")!=-1)
                    strRowName=strRowName.Replace("=","_x003D_");
                

                strColumn += "<th>" + strClmName +"</th>" + "rn";
                strRow += "<td>" + "<xsl:value-of select=" + """ + strRowName + """ +"/>" + "</td>" + "rn";
            }
            string str = @"<xsl:stylesheet version=""1.0"" xmlns:xsl=""http://www.w3.org/1999/XSL/Transform"">
            <xsl:template match=""/"">
            <html xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns=""http://www.w3.org/TR/REC-html40"">
            <head>
            <meta http-equiv=""Content-Type"" content=""text/html;charset=utf-8"" />
            <style>
            .xl24{mso-style-parent:style0;mso-number-format:""@"";text-align:right;}
            </style>
            <xml>
            <x:ExcelWorkbook>
            <x:ExcelWorksheets>
            <x:ExcelWorksheet>
            <x:Name>Sheet1</x:Name>
            <x:WorksheetOptions>
                    <x:ProtectContents>False</x:ProtectContents>
                    <x:ProtectObjects>False</x:ProtectObjects>
                    <x:ProtectScenarios>False</x:ProtectScenarios>
            </x:WorksheetOptions>
            </x:ExcelWorksheet>
            </x:ExcelWorksheets>
            </x:ExcelWorkbook>
            </xml>
            </head>  
            <body> ";
            str += "rn" +  @"<table border=""1"" cellpadding=""0"" cellspacing=""0"">
                    <tr>" + "rn";
            str += strColumn;
            str += @" </tr>
                    <xsl:for-each select="""+header+@""">
                    <tr>";
            str += "rn" + strRow;
            str += @"</tr>
                    </xsl:for-each>
                    </table>
                    </body>
                    </html>
                      
                     
                    </xsl:template>
                    </xsl:stylesheet> ";

            string path = XslPath;
            if(File.Exists(path))
            {
                File.Delete(path);
            }
            FileStream fs = File.Create(path);
            StreamWriter sw=new StreamWriter(fs);
            sw.Write(str);
            sw.Close();
            fs.Close();
        }

        /// <summary>
        /// 根据数据集,生成替换后的xml文件
        /// </summary>
        /// <param name="ds">数据集合</param>
        /// <param name="XmlFilePath">xml文件路径</param>
        private  void GetXmlFile(DataSet ds,string XmlFilePath)
        {
            string strXml = ds.GetXml();
            if(File.Exists(XmlFilePath))
            {
                File.Delete(XmlFilePath);
            }
            FileStream fs1 = File.Create(XmlFilePath);
            StreamWriter writer = new StreamWriter(fs1);
            writer.Write(strXml);
            writer.Close();
            fs1.Close();
        }

        /// <summary>
        /// 生成Excel文件
        /// </summary>
        /// <param name="path">Excel导出全路径</param>
        /// <param name="ds">数据集</param>
        private  void BuildExcel(DataSet ds,string path)
        {
            if(File.Exists(path))
            {
                File.Delete(path);
            }
            string _path = path.Substring(0,path.Length-4);
            string _fileXml=_path + ".xml";
            string _fileXsl=_path + ".xsl";
            string _fileXls=_path+".xls";

            try
            {
                GetXmlFile(ds,_fileXml);
                GetXSLFile(ds,_fileXsl);

                //Excel转换
                XmlDocument doc = new XmlDocument();
                doc.Load(_fileXml);
                XslTransform xslt = new XslTransform();
                xslt.Load(_fileXsl);
                XmlElement root = doc.DocumentElement;
                XPathNavigator nav = root.CreateNavigator();
                XmlTextWriter writer = new XmlTextWriter(_fileXls, null);
                xslt.Transform(nav, null, writer, null);
                writer.Close();
                File.Delete(_fileXml);
                File.Delete(_fileXsl);
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 更据Grid格式，设置数据集格式
        /// </summary>
        /// <param name="grid">显示数据的Grid</param>
        /// <param name="ds">存储数据的DataSet数据集</param>
        /// <returns>设置好的数据集DataSet</returns>
        private  DataSet GetFilterDataSet(UltraWebGrid grid,DataSet ds)
        {
            DataColumnCollection col = ds.Tables[0].Columns;

            foreach(UltraGridColumn clm in grid.Columns)
            {
                //如果该列隐藏，那么删除该数据集中的该列数据
                if(clm.Hidden)
                {
                    if(col.Contains(clm.Key))
                        col.Remove(clm.Key);
                }
                    //在显示列的情况下，设置该列的名称为Grid的列标题
                else
                {
                    if(col.Contains(clm.Key))
                        col[clm.Key].ColumnName=clm.HeaderText;
                }
            }
            return ds;
        }

    }
}