数据导出到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;         }     } }