1。首先到网站http://npoi.codeplex.com/下载NPOI.dll;
2。添加到项目中(右击项目-添加引用-浏览-找到NPOI.dll-添加。);
3。添加引用
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
然后就可以写代码了;
首先建个简单的数据库,只有两个字段。UserName,Password
1). Excel1.aspx
<body>
<form id="form1" runat="server">
<div>
<a href="Excel.ashx">下载Excel数据表(动态数据)</a>
</div>
</form>
</body>
2).Excel.ashx
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using System.Data.SqlClient;
namespace WebApplication3.测试
{
/// <summary>
/// Excel1 的摘要说明
/// </summary>
public class Excel1 : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/x-excel";//或者application/octet-stream
string path = HttpUtility.UrlEncode("用户数据.xls");
context.Response.AddHeader("Content-Disposition", "attachment;filename=" + path);
context.Response.Clear();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Excel.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand com = conn.CreateCommand())
{
com.CommandText = "select * from T_User";
HSSFRow row1 = (HSSFRow)sheet.CreateRow(0);
row1.CreateCell(0, Cell_TYPE_STRING).SetCellValue("姓名");
row1.CreateCell(1, Cell_TYPE_STRING).SetCellValue("密码");
using (SqlDataReader dr = com.ExecuteReader())
{
int i = 1;
while (dr.Read())
{
HSSFRow row = (HSSFRow)sheet.CreateRow(i);
string name = dr.GetString(dr.GetOrdinal("UserName"));
string password = dr.GetString(dr.GetOrdinal("Password"));
row.CreateCell(0, Cell_TYPE_STRING).SetCellValue(name);
row.CreateCell(1, Cell_TYPE_STRING).SetCellValue(password);
//context.Response.Write(name);
i++;
}
}
}
}
workbook.Write(context.Response.OutputStream);
}
public bool IsReusable
{
get
{
return false;
}
}
public CellType Cell_TYPE_STRING { get; set; }
}
}