代碼中資料庫連結字元串自己修改,還有對應的字段名之類的資訊;
建立一個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;
}
}
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>