简单的导出到Excel中：
代码如下：
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
namespace Space
{
/// <summary>
/// 标题：将 DataSet, DataTable 导出到 Excel
/// 描述：对之前做的导出 Excel 做调整以支持对 DataSet 及 DataTable 的导出；
/// DataSet 导出时可以指定需要导出的 DataTable
/// DataTable 导出时可以指定需要导出的 DataColumn 及自定义导出后的列名
/// </summary>
public static class ExcelExportProvider
{
public static string BuildExportHTML(System.Data.DataTable dt)
{
string result = string.Empty;
int readCnt = dt.Rows.Count;
int colCount = dt.Columns.Count;
int pagerecords = ;
result = "<?xml version=\"1.0\" encoding=\"gb2312\"?>";
result += "<?mso-application progid=\"Excel.Sheet\"?>";
result += "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ";
result += "xmlns:o=\"urn:schemas-microsoft-com:office:office\" ";
result += "xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ";
result += "xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ";
result += "xmlns:html=\"http://www.w3.org/TR/REC-html40\"> ";
string strTitleRow = "";
//设置每行的标题行
strTitleRow = "<Row ss:AutoFitHeight='0'>";
for (int j = ; j < colCount; j++)
{
var tempColName = dt.Columns[j].ColumnName;
if (tempColName.IndexOf("@@@@@@@@@@@@") >= ) //把"@@@@@@@@@@@@"作为分隔符号 前面为显示内容，后面为主键id。
{
tempColName = tempColName.Split(new string[] { "@@@@@@@@@@@@" }, StringSplitOptions.None)[];
}
strTitleRow += "<Cell><Data ss:Type=\"String\">" + tempColName + "</Data></Cell>";
}
strTitleRow += "</Row>";
StringBuilder strRows = new StringBuilder();
//在变长的字符操作方面stringbuilder的效率比string高得多
int page = ; //分成的sheet数
int cnt = ; //输入的记录数
int sheetcolnum = ; //每个sheet的行数，其实就等于cnt+1
for (int i = ; i < readCnt; i++)
{
strRows.Append("<Row ss:AutoFitHeight=\"0\">");
for (int j = ; j < colCount; j++)
{
if (dt.Columns[j].DataType.Name == "DateTime" || dt.Columns[j].DataType.Name == "SmallDateTime")
{
if (dt.Rows[i][j].ToString() != string.Empty)
{
strRows.Append("<Cell><Data ss:Type=\"String\">" + Convert.ToDateTime(dt.Rows[i][j].ToString()).ToShortDateString() + "</Data></Cell>");
}
else
strRows.Append("<Cell><Data ss:Type=\"String\"></Data></Cell>");
}
//alter by taomin 2012-11-13 新增decimal类型数据的处理方式 避免decimal类型数据导入EXCEL是未被转化为数字型，不利于在excel中进行统计计算
else if (dt.Columns[j].DataType.Name == "Int32" || dt.Columns[j].DataType.Name == "Int64" || dt.Columns[j].DataType.Name.ToLower() == "decimal")
{
strRows.Append("<Cell><Data ss:Type= \"Number\">" + dt.Rows[i][j].ToString().Trim() + "</Data></Cell>");
}
else
{
strRows.Append("<Cell><Data ss:Type=\"String\">" + dt.Rows[i][j].ToString().Trim() + "</Data></Cell>");
}
}
strRows.Append("</Row>");
cnt++;
//到设定行数时，要输出一页，防止office打不开，同时要注意string和stringbuilder的长度限制
if (cnt >= pagerecords + )
{
sheetcolnum = cnt + ;
result += "<Worksheet ss:Name=\"Sheet" + page.ToString() + "\"><Table ss:ExpandedColumnCount=\"" + colCount.ToString() + "\" ss:ExpandedRowCount=\"" + sheetcolnum.ToString() + "\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultColumnWidth=\"104\" ss:DefaultRowHeight=\"13.5\">" + strTitleRow.ToString() + strRows.ToString() + "</Table></Worksheet>";
strRows.Remove(, strRows.Length);
cnt = ; //下一个sheet重新计数
page++;
}
}
sheetcolnum = cnt + ;
result = result + "<Worksheet ss:Name='Sheet" + page.ToString() + "'><Table ss:ExpandedColumnCount='" + colCount.ToString() + "' ss:ExpandedRowCount='" + sheetcolnum.ToString() + "' x:FullColumns='1' x:FullRows='1' ss:DefaultColumnWidth='104' ss:DefaultRowHeight='13.5'>" + strTitleRow.ToString() + strRows.ToString() + "</Table></Worksheet></Workbook>";
return result;
}
/// <summary>
/// 导出Excel
/// 例子 Controller里面调用 ExcelExportProvider.DataTable2Excel(dt, "ClickExcel.xls");
/// </summary>
public static void DataTable2Excel(DataTable dt, string fileName)
{
string outputFileName = null;
HttpContext curContext = System.Web.HttpContext.Current;
string browser = curContext.Request.UserAgent.ToUpper();
if (browser.Contains("FIREFOX") == true)
{
outputFileName = "\"" + fileName + "\"";
}
else
{
outputFileName = HttpUtility.UrlEncode(fileName);
}
curContext.Response.ContentType = "application/ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + outputFileName));
curContext.Response.Charset = "";
curContext.Response.Write(BuildExportHTML(dt));
curContext.Response.Flush();
curContext.Response.End();
}
}
}
使用方法：
前台：
eg：<button id="ExportToExcel">导出到Excel</button>
$("#ExportToExcel").click(function(){
Location.href= "/ExportToExcel.do"
});
后台：
public void ExportToExcel(DataTable dt)
{
string fileName = "test.xls";
ExcelExportProvider.DataTable2Excel(dt,fileName);
}
点击Button时会调用资源管理器的路径选择对话框，在此时选择文件存储路径
PS：转载请注明出处。