常见的C#实现导出到Excel 功能
报表在生产中的应用非常广泛，项目中也会经常需要实现将自动生成报表，并导出到Excel 中的功能。以下是对该功能实现的一点总结。
1.利用GridView自带的导出功能
图1.1 页面呈现效果
1.2 导出到 Excel里的效果
前台直接拖放一个GridView控件。
后台代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Web.UI.HtmlControls;//加上这个命名空间才能使用HtmlForm类
namespace WebToExcel03
{
/// <summary>
/// GridView自带的属性
/// </summary>
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
Gv_Bind();
}
}
public void Gv_Bind()
{
string connStr = "Data Source=20110814-1820;Initial Catalog=NewHeleScann;Integrated Security=True";
string sql = "select * from Scan_JiDiTable ";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sdp = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
sdp.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
//在页面重写Web基类 否则报错:控件必须放在具有 runat=server 的窗体标记内"错误
public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for
}
private void ExportExcelFromDataGrid(string filename, GridView ToExcelGrid)
{
/* 传说此方法更好，可以让我们不用重写VerifyRenderingInServerForm方法
* 但是这里未调试成功
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter htw = new HtmlTextWriter(sw);
Page page = new Page();
HtmlForm form = new HtmlForm();
ToExcelGrid.EnableViewState = false;
// Deshabilitar la validación de eventos, sólo asp.net 2
page.EnableEventValidation = false;
// Realiza las inicializaciones de la instancia de la clase Page que requieran los diseñadores RAD.
page.DesignerInitialize();
page.Controls.Add(form);
form.Controls.Add(ToExcelGrid);
page.RenderControl(htw);
*/
Response.ClearHeaders();
Response.Clear();
Response.Expires = 0;
Response.Buffer = true;
Response.HeaderEncoding = System.Text.Encoding.UTF8;
// Response.Charset = "utf-8";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(filename));
Response.ContentEncoding = System.Text.Encoding.Default;//设置输出流为简体中文
// Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
Response.ContentType = "Application/octet-stream";
this.EnableViewState = false;
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("zh-CHS", true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
ToExcelGrid.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
protected void ToExcel_Click(object sender, EventArgs e)
{
string filename = "ToExcel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls" ;
ExportExcelFromDataGrid(filename, GridView1);
}
}
}
2 采用COM.Excel组件实现导出到Excel
COM.Excel是一个外部组件，所以首先要在当前项目中，添加对它的引用。
单击项目，右键选择“添加引用”，找到该组件，添加完成后效果如下：
为了方便查看数据，这里也在前台放了一个GridView控件(实际上与导出没有关系)。
导出效果如下：
后台源码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace ToExcelCom
{
/// <summary>
/// 采用COM.Excel组件实现导出到Excel
/// </summary>
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
//获取ds
DataSet ds = GetDs();
//绑定到GridView
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
public DataSet GetDs()
{
string connStr = "Data Source=20110814-1820;Initial Catalog=NewHeleScann;Integrated Security=True";
string sql = "select * from Scan_JiDiTable ";
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sdp = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
sdp.Fill(ds);
return ds;
}
protected void ToExcel_Click(object sender, EventArgs e)
{
string path = System.Web.HttpContext.Current.Server.MapPath("~/SaveExcelFiles/");//保存路径
//string filename = comm.OutputExcel(ds.Tables[0], DDLName.SelectedItem.Text.ToString(), path);//保存文件
Hashtable hs = new Hashtable();//表列名
DataSet ds = GetDs();
//循环添加表列名
foreach (DataColumn dc in ds.Tables[0].Columns)
{
hs.Add(dc.ColumnName, dc.ColumnName);
}
//DataTable dts = ds.Tables[0];
DataTable[] dts = { ds.Tables[0]};//数据集的集合
string strTitle = "";
string filename = DataExcels(dts ,strTitle, path, hs);
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
System.Web.HttpContext.Current.Response.TransmitFile(path + filename);//打开文件
//System.Web.HttpContext.Current.Response.TransmitFile(filename);//打开文件
System.Web.HttpContext.Current.Response.End();
}
/// <summary>
///
/// </summary>
/// <param name="dts"></param>
/// <param name="strTitle"></param>
/// <param name="FilePath"></param>
/// <param name="nameList"></param>
/// <param name="titles"></param>
/// <returns></returns>
public string DataExcels(System.Data.DataTable[] dts, string strTitle, string FilePath, Hashtable nameList)
{
COM.Excel.cExcelFile excel = new COM.Excel.cExcelFile();
//当文件大于10的时候 清空所有文件！！！
ClearFile(FilePath);
//文件名
string filename = strTitle+ DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
//生成相应的文件
excel.CreateFile(FilePath + filename);
//设置margin
COM.Excel.cExcelFile.MarginTypes mt1 = COM.Excel.cExcelFile.MarginTypes.xlsTopMargin;
COM.Excel.cExcelFile.MarginTypes mt2 = COM.Excel.cExcelFile.MarginTypes.xlsLeftMargin;
COM.Excel.cExcelFile.MarginTypes mt3 = COM.Excel.cExcelFile.MarginTypes.xlsRightMargin;
COM.Excel.cExcelFile.MarginTypes mt4 = COM.Excel.cExcelFile.MarginTypes.xlsBottomMargin;
double height = 2.2;
excel.SetMargin(ref mt1, ref height);
excel.SetMargin(ref mt2, ref height);
excel.SetMargin(ref mt3, ref height);
excel.SetMargin(ref mt4, ref height);
//设置字体！！
COM.Excel.cExcelFile.FontFormatting ff = COM.Excel.cExcelFile.FontFormatting.xlsNoFormat;
string font = "宋体";
short fontsize = 14;
excel.SetFont(ref font, ref fontsize, ref ff);
byte b1 = 1, b2 = 12;
short s3 = 12;
excel.SetColumnWidth(ref b1, ref b2, ref s3);
string header = "页眉";
string footer = "页脚";
excel.SetHeader(ref header);
excel.SetFooter(ref footer);
COM.Excel.cExcelFile.ValueTypes vt = COM.Excel.cExcelFile.ValueTypes.xlsText;
COM.Excel.cExcelFile.CellFont cf = COM.Excel.cExcelFile.CellFont.xlsFont0;
COM.Excel.cExcelFile.CellAlignment ca = COM.Excel.cExcelFile.CellAlignment.xlsCentreAlign;
COM.Excel.cExcelFile.CellHiddenLocked chl = COM.Excel.cExcelFile.CellHiddenLocked.xlsNormal;
// 报表标题
int cellformat = 1;
int rowIndex = 1;//起始行
int colIndex = 0;
foreach (System.Data.DataTable dt in dts)
{
colIndex = 0;
//取得列标题
foreach (DataColumn colhead in dt.Columns)
{
colIndex++;
string name = colhead.ColumnName.Trim();
object namestr = (object)name;
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref namestr, ref cellformat);
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
object str = (object)(Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); ;
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
}
else
{
object str = (object)row[col.ColumnName].ToString();
excel.WriteValue(ref vt, ref cf, ref ca, ref chl, ref rowIndex, ref colIndex, ref str, ref cellformat);
}
}
}
rowIndex += 3 ;
}
int ret = excel.CloseFile();
//return FilePath+filename;
return filename;
}
#region 清理过时的Excel文件
private void ClearFile(string FilePath)
{
String[] Files = System.IO.Directory.GetFiles(FilePath);
if (Files.Length > 10)
{
for (int i = 0; i < 10; i++)
{
try
{
System.IO.File.Delete(Files[i]);
}
catch
{
}
}
}
}
#endregion
}
}
对比两种导出的效果，显然第二种更加人性化一些。当然这两种方法，基本都被普遍使用。
3.使用asposecell组件，详细参考http://www.cnblogs.com/whitewolf/archive/2011/03/21/Aspose_Cells_Template1.html#commentform
该组件确实强大，比较好用，Excel模板可以事先做好，很直观，对于格式比较复杂的报表，优势明显。
效果如下：
这种方案，在http://www.cnblogs.com/whitewolf/archive/2011/03/21/Aspose_Cells_Template1.html#commentform有很详细的演示，这里不再獒述。
但是也有人从性能方面提出了更好的方案，如通过SSIS 包的实现，据说这种方案主要是针对于较大数据量的报表性能卓越，我认为值得尝试。
