数据导出到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_ ------ <
// > -------- _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>" + "\r\n";
strRow += "<td>" + "<xsl:value-of select=" + "\"" + strRowName + "\"" +"/>" + "</td>" + "\r\n";
}
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 += "\r\n" + @"<table border=""1"" cellpadding=""0"" cellspacing=""0"">
<tr>" + "\r\n";
str += strColumn;
str += @" </tr>
<xsl:for-each select="""+header+@""">
<tr>";
str += "\r\n" + 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;
}
}
}
//做其它方面的用途，可以对控件部分做适当的修改。
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>" + "\r\n";
strRow += "<td>" + "<xsl:value-of select=" + "\"" + strRowName + "\"" +"/>" + "</td>" + "\r\n";
}
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 += "\r\n" + @"<table border=""1"" cellpadding=""0"" cellspacing=""0"">
<tr>" + "\r\n";
str += strColumn;
str += @" </tr>
<xsl:for-each select="""+header+@""">
<tr>";
str += "\r\n" + 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;
}
}
}
