数据导出到Excel的解决方案 [转]

来源：互联网发布：华为工作知乎编辑：程序博客网时间：2024/05/20 21:48

//因为采用了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_ ------ < // > --------　_x003E_ ------ > // " -------- _x0022_ ------ " // * -------- _x002A_ ------ * // % -------- _x0025_ ------ % // & -------- _x0026_ ------ & // ( -------- _x0028_ ------ ( // ) -------- _x0029_ ------ ) // = -------- _x003D_ ------ = //************************************************* //************************************************* string strClmName = clm.ColumnName; string strRowName = clm.ColumnName; if(strClmName.IndexOf("&")!=-1) strClmName=strClmName.Replace("&","&"); if(strClmName.IndexOf("<")!=-1) strClmName=strClmName.Replace("<","<"); if(strClmName.IndexOf(">")!=-1) strClmName=strClmName.Replace(">",">"); if(strClmName.IndexOf(""")!=-1) strClmName=strClmName.Replace(""","""); 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; } }}