用NPOI工具把Sql Server数据导入到Excel文件

作者：西西点击：226次评论：1次标签： Excel

【excel2007官方下载】Excel Viewer 2007官方中文原版

类型：办公软件大小：72.1M语言：中文评分：.9
标签：

立即下载

代码中数据库链接字符串自己修改，还有对应的字段名之类的信息；

新建一个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>

NPOI文件包：http://u.115.com/file/aqr5pjb7