教程地址：http://www.cnblogs.com/atao/category/209358.html
NPOI文件包：http://u.115.com/file/aqr5pjb7
程序源码:http://u.115.com/file/bhvr12dq
代码中数据库链接字符串自己修改，还有对应的字段名之类的信息；
新建一个Handler.ashx(一般处理程序) 代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
namespace ExportToExcelWeb
{
/// <summary>
/// ExportToExcel 的摘要说明
/// </summary>
public class ExportToExcel : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/x-excel";
string fileName = HttpUtility.UrlEncode("数据库文备份.xls");
context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName); //添加http协议报文;
HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个xls；
HSSFSheet sheet = workbook.CreateSheet(); //创建一个Sheet页
string connectString = @"server=localhost\sql2008;database=MyBlog; uid=sa; pwd=sql2008";
SqlConnection connection = new SqlConnection(connectString);
connection.Open();
using (IDbCommand cmd = connection.CreateCommand()) //接口编徎;
{
cmd.CommandText = "select * from Users";
using (IDataReader reader = cmd.ExecuteReader())
{
int rowsNum = 0; //行号
while (reader.Read())
{
//根据字段名找出ID
string LoginId = reader.GetString(reader.GetOrdinal("LoginId"));
string LoginPwd = reader.GetString(reader.GetOrdinal("LoginPwd"));
string Name = reader.GetString(reader.GetOrdinal("Name"));
string QQ = reader.GetString(reader.GetOrdinal("QQ"));
string Mail = reader.GetString(reader.GetOrdinal("Mail"));
/******************以上代码对应数据库表中的字段*********************/
HSSFRow row = sheet.CreateRow(rowsNum);
row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue(LoginId);
row.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue(LoginPwd);
row.CreateCell(2, HSSFCell.CELL_TYPE_STRING).SetCellValue(Name);
row.CreateCell(3, HSSFCell.CELL_TYPE_STRING).SetCellValue(QQ);
row.CreateCell(4, HSSFCell.CELL_TYPE_STRING).SetCellValue(Mail);
/******************以上代码对应Excel文件的列************************/
rowsNum++;
}
}
}
workbook.Write(context.Response.OutputStream); //输出到流中
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
namespace ExportToExcelWeb
{
/// <summary>
/// ExportToExcel 的摘要说明
/// </summary>
public class ExportToExcel : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/x-excel";
string fileName = HttpUtility.UrlEncode("数据库文备份.xls");
context.Response.AddHeader("Content-Disposition", "attachment; fileName=" + fileName); //添加http协议报文;
HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个xls；
HSSFSheet sheet = workbook.CreateSheet(); //创建一个Sheet页
string connectString = @"server=localhost\sql2008;database=MyBlog; uid=sa; pwd=sql2008";
SqlConnection connection = new SqlConnection(connectString);
connection.Open();
using (IDbCommand cmd = connection.CreateCommand()) //接口编徎;
{
cmd.CommandText = "select * from Users";
using (IDataReader reader = cmd.ExecuteReader())
{
int rowsNum = 0; //行号
while (reader.Read())
{
//根据字段名找出ID
string LoginId = reader.GetString(reader.GetOrdinal("LoginId"));
string LoginPwd = reader.GetString(reader.GetOrdinal("LoginPwd"));
string Name = reader.GetString(reader.GetOrdinal("Name"));
string QQ = reader.GetString(reader.GetOrdinal("QQ"));
string Mail = reader.GetString(reader.GetOrdinal("Mail"));
/******************以上代码对应数据库表中的字段*********************/
HSSFRow row = sheet.CreateRow(rowsNum);
row.CreateCell(0, HSSFCell.CELL_TYPE_STRING).SetCellValue(LoginId);
row.CreateCell(1, HSSFCell.CELL_TYPE_STRING).SetCellValue(LoginPwd);
row.CreateCell(2, HSSFCell.CELL_TYPE_STRING).SetCellValue(Name);
row.CreateCell(3, HSSFCell.CELL_TYPE_STRING).SetCellValue(QQ);
row.CreateCell(4, HSSFCell.CELL_TYPE_STRING).SetCellValue(Mail);
/******************以上代码对应Excel文件的列************************/
rowsNum++;
}
}
}
workbook.Write(context.Response.OutputStream); //输出到流中
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
Aspx页面代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExportToExcelWeb.Default" %>
<!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>
<a href="ExportToExcel.ashx">下载备份数据库文件到Excel格式</a>
</div>
</form>
</body>
</html>
<!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>
<a href="ExportToExcel.ashx">下载备份数据库文件到Excel格式</a>
</div>
</form>
</body>
</html>