ASP.NET使用NPOI导出数据库到Excel文件

最新推荐文章于 2024-07-16 11:27:21 发布

ba_wang_mao

最新推荐文章于 2024-07-16 11:27:21 发布

阅读量433

点赞数

分类专栏： NPOI

原文链接：https://download.csdn.net/download/dream_shine/4538575

版权

NPOI 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

使用NPOI导出数据库到Excel文件

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>

https://download.csdn.net/download/dream_shine/4538575

ba_wang_mao

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ASP.NET使用NPOI导出数据库到Excel文件

使用NPOI导出数据库到Excel文件使用NPOI导出数据库到Excel文件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&g.
复制链接

扫一扫

专栏目录