ASP.NET导出Excel实践-优快云博客

本文介绍了两种在ASP.NET中实现导出数据到Excel的方法，包括直接生成Excel文件及使用模板进行填充。提供了完整的代码示例，展示了如何设置HTTP响应以供用户下载。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

综合参考了网上的方法，生成Excel文件提供下载，然后删除生成的Excel文件。

1、引用Microsoft.Office.Interop.Excel;( 属性里的嵌入互操作类型改为Fasle)

2、Default10.aspx

<% @ Page Language = " C# " AutoEventWireup = " true " CodeFile = " Default10.aspx.cs " Inherits = " Default10 " %> <! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" > < html xmlns ="http://www.w3.org/1999/xhtml" > < head runat ="server" > < title ></ title > </ head > < body > < form id ="form1" runat ="server" > < div > < asp:Button ID ="ExportToExcel" runat ="server" Text ="导出Excel" onclick ="ExportToExcel_Click" /> </ div > </ form > </ body > </ html >

复制代码

3、Default10.aspx.cs

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using Excel = Microsoft.Office.Interop.Excel; // 添加引用 public partial class Default10 : System.Web.UI.Page { protected void Page_Load( object sender, EventArgs e) { } protected void ExportToExcel_Click( object sender, EventArgs e) { Excel.Application excel1 = new Excel.Application(); excel1.DisplayAlerts = false ; Excel.Workbook workbook1 = excel1.Workbooks.Add(Type.Missing); excel1.Visible = false ; Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Worksheets[ " sheet1 " ]; // 表头 worksheet1.Cells[ 1 , 1 ] = " 姓名 " ; // Excel里从第1行，第1列计算 worksheet1.Cells[ 1 , 2 ] = " 性别 " ; worksheet1.Cells[ 1 , 3 ] = " 联系电话 " ; System.Data.DataTable dt = GetTestData( 100 ); for ( int i = 0 ; i < dt.Rows.Count; i ++ ) { for ( int j = 0 ; j < dt.Columns.Count; j ++ ) worksheet1.Cells[i + 2 , j + 1 ] = dt.Rows[i][j].ToString(); } string fileName = DateTime.Now.ToString( " yyyyMMddHHmmss " ) + " .xls " ; string filePath = Server.MapPath( " ~/ " + fileName); workbook1.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); excel1.Workbooks.Close(); excel1.Quit(); int generation = GC.GetGeneration(excel1); System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1); excel1 = null ; GC.Collect(generation); // 打开要下载的文件，并把该文件存放在FileStream中 System.IO.FileStream Reader = System.IO.File.OpenRead(filePath); // 文件传送的剩余字节数：初始值为文件的总大小 long Length = Reader.Length; HttpContext.Current.Response.Buffer = false ; HttpContext.Current.Response.AddHeader( " Connection " , " Keep-Alive " ); HttpContext.Current.Response.ContentType = " application/octet-stream " ; HttpContext.Current.Response.AddHeader( " Content-Disposition " , " attachment; filename= " + fileName); HttpContext.Current.Response.AddHeader( " Content-Length " , Length.ToString()); byte [] Buffer = new Byte[ 10000 ]; // 存放欲发送数据的缓冲区 int ByteToRead; // 每次实际读取的字节数 while (Length > 0 ) { // 剩余字节数不为零，继续传送 if (Response.IsClientConnected) { // 客户端浏览器还打开着，继续传送 ByteToRead = Reader.Read(Buffer, 0 , 10000 ); // 往缓冲区读入数据 HttpContext.Current.Response.OutputStream.Write(Buffer, 0 , ByteToRead); // 把缓冲区的数据写入客户端浏览器 HttpContext.Current.Response.Flush(); // 立即写入客户端 Length -= ByteToRead; // 剩余字节数减少 } else { // 客户端浏览器已经断开，阻止继续循环 Length = - 1 ; } } // 关闭该文件 Reader.Close(); if (System.IO.File.Exists(filePath)) System.IO.File.Delete(filePath); } System.Data.DataTable GetTestData( int num) // 测试数据 { System.Data.DataTable dt = new System.Data.DataTable(); System.Data.DataRow dr; dt.Columns.Add( new System.Data.DataColumn( " ContactName " , typeof (String))); dt.Columns.Add( new System.Data.DataColumn( " ContactSex " , typeof (String))); dt.Columns.Add( new System.Data.DataColumn( " ContactPhone " , typeof (String))); for ( int i = 0 ; i < num; i ++ ) { Random rnd = new Random(Environment.TickCount * i); dr = dt.NewRow(); dr[ 0 ] = " 姓名 " + rnd.Next( 1 , num); dr[ 1 ] = rnd.Next( 1 , num) < num / 2 ? " 男 " : " 女 " ; dr[ 2 ] = rnd.Next( 1000000 , 99999999 ); dt.Rows.Add(dr); } return dt; } }

复制代码

另一种利用Excel模板生成Excel方法如下：

private void ExportToExcel(DataTable dt, string fileName) { // 转换为物理路径 string newFileName = HttpContext.Current.Server.MapPath( " ~/ " + fileName); // 根据模板正式生成该Excel文件 File.Copy(HttpContext.Current.Server.MapPath( " ~/ContactTemplate.xls " ), newFileName, true ); // 建立指向该Excel文件的数据库连接 string strConn = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + newFileName + " ;Extended Properties='Excel 8.0;HDR=yes;IMEX=2' " ; OleDbConnection Conn = new OleDbConnection(strConn); // 打开连接，为操作该文件做准备 Conn.Open(); OleDbCommand Cmd = new OleDbCommand( "" , Conn); foreach (DataRow DR in dt.Rows) { string XSqlString = " insert into [Sheet1$] " ; XSqlString += " ([姓名],[性别],[联系电话]) values( " ; XSqlString += " ' " + DR[ " ContactName " ] + " ', " ; XSqlString += " ' " + (DR[ " ContactSex " ].ToString() == " 1 " ? " 男 " : " 女 " ) + " ', " ; XSqlString += " ' " + DR[ " ContactPhone " ] + " ') " ; Cmd.CommandText = XSqlString; Cmd.ExecuteNonQuery(); } // 操作结束，关闭连接 Conn.Close(); // 打开要下载的文件，并把该文件存放在FileStream中 System.IO.FileStream Reader = System.IO.File.OpenRead(newFileName); // 文件传送的剩余字节数：初始值为文件的总大小 long Length = Reader.Length; HttpContext.Current.Response.Buffer = false ; HttpContext.Current.Response.AddHeader( " Connection " , " Keep-Alive " ); HttpContext.Current.Response.ContentType = " application/octet-stream " ; HttpContext.Current.Response.Charset = " utf-8 " ; HttpContext.Current.Response.AddHeader( " Content-Disposition " , " attachment; filename= " + fileName); HttpContext.Current.Response.AddHeader( " Content-Length " , Length.ToString()); byte [] Buffer = new Byte[ 10000 ]; // 存放欲发送数据的缓冲区 int ByteToRead; // 每次实际读取的字节数 while (Length > 0 ) { // 剩余字节数不为零，继续传送 if (Response.IsClientConnected) { // 客户端浏览器还打开着，继续传送 ByteToRead = Reader.Read(Buffer, 0 , 10000 ); // 往缓冲区读入数据 HttpContext.Current.Response.OutputStream.Write(Buffer, 0 , ByteToRead); // 把缓冲区的数据写入客户端浏览器 HttpContext.Current.Response.Flush(); // 立即写入客户端 Length -= ByteToRead; // 剩余字节数减少 } else { // 客户端浏览器已经断开，阻止继续循环 Length = - 1 ; } } // 关闭该文件 Reader.Close(); // 删除该Excel文件 if (File.Exists(newFileName)) File.Delete(newFileName); }

复制代码

---------------------------------------------------------------------------

2010-8-26 备注：

在项目中使用第2种方法时，出现"操作必须使用一个可更新的查询"的错误提示，原因是项目采用TFS管理，使Excel文件属性是只读的，解决方法是去掉只读属性：

File.Copy(HttpContext.Current.Server.MapPath("~/Views/ActivityAdmin/ContactTemplate.xls"), newFileName, true);

在上面代码的下面加上：

FileInfo f = new FileInfo(newFileName);
if (f.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
f.Attributes = FileAttributes.Normal;
}

---------------------------------------------------------------------------

17:11 2010-12-23 备注：

用企业库读取Excel：

web.config配置：

< add name ="testXls" connectionString ="Provider=Microsoft.Jet.OLEDB.4.0;data source=|DataDirectory|test.xls;Extended Properties='Excel 8.0;HDR=yes;IMEX=1'" providerName ="System.Data.OleDb" />

复制代码

*.aspx.cs代码：

using Microsoft.Practices.EnterpriseLibrary.Data; Database db = DatabaseFactory.CreateDatabase( " testXls " ); // [B0201$A2:C33]表示读取表B0201$的区域范围A2:C33 DataTable dt = db.ExecuteDataSet(CommandType.Text, " select * from [B0201$A2:C33] " ).Tables[ 0 ];

复制代码

另一种不错方法：

使用HTML,CSS快速导出数据到Excel

http://www.cnblogs.com/ruinet/archive/2009/10/17/1585320.html

稍微改了下

public static void CreateExcel( string strTable, string fileName) { string HEADER = " <html xmlns:x=\"urn:schemas-microsoft-com:office:excel\"> " + " <meta http-equiv=Content-Type content=\"text/html; charset=\"gb2312\"> " + " <head> " + "  " ; System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding( " GB2312 " ); System.Web.HttpContext.Current.Response.AppendHeader( " Content-Disposition " , " attachment;filename= " + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); System.Web.HttpContext.Current.Response.ContentType = " ms-excel/application " ; StringBuilder sbHtml = new StringBuilder(); sbHtml.AppendFormat( @" {0}</head> <body>{1}</body> </html> " , HEADER, strTable); System.Web.HttpContext.Current.Response.Write(sbHtml.ToString()); System.Web.HttpContext.Current.Response.Flush(); System.Web.HttpContext.Current.Response.Clear(); System.Web.HttpContext.Current.Response.End(); }

http://www.cnblogs.com/gdjlc/archive/2010/08/13/2086886.html