先去官网:http://npoi.codeplex.com/下载需要引入dll(可以选择.net2.0或者.net4.0的dll),然后在网站中添加引用。
protected void Button1_Click(object sender, EventArgs e)
{
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
string sql = "select top 10 id,title,add_time from dy_article";
OleDbDataAdapter oda = new OleDbDataAdapter(sql, conn);
DataTable dt = new DataTable();
oda.Fill(dt);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("sheet1");//Excel表名
//第一行
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("ID");//第一列
row.CreateCell(1).SetCellValue("标题");//第二列
row.CreateCell(2).SetCellValue("添加时间");//第三列
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet.CreateRow(i);//循环第i行
row.CreateCell(0).SetCellValue(dt.Rows[i]["id"].ToString());//第一列
row.CreateCell(1).SetCellValue(dt.Rows[i]["title"].ToString());//第二列
row.CreateCell(2).SetCellValue(dt.Rows[i]["add_time"].ToString());//第三列
}
// ...
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(ms.ToArray());
book = null;
ms.Close();
ms.Dispose();
}
}