C#用NPOI控件把MySQL数据库中查询符合条件的数据导出到EXCEL

一、NPOI控件概述

        NPOI，顾名思义，就是POI的.NET版本。那POI又是什么呢？POI是一套用Java写成的库，能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件、Office2007以及Office2010，支持的文件格式包括xls, doc, ppt等。

        NPOI 2.0+主要由SS, HPSF, DDF, HSSF, XWPF, XSSF, OpenXml4Net, OpenXmlFormats组成，具体列表如下：

Assembly名称	模块/命名空间	说明
NPOI.DLL	NPOI.POIFS	OLE2/ActiveX文档属性读写库
NPOI.DLL	NPOI.DDF	微软Office Drawing读写库
NPOI.DLL	NPOI.HPSF	OLE2/ActiveX文档读写库
NPOI.DLL	NPOI.HSSF	微软Excel BIFF(Excel 97-2003, doc)格式读写库（导出xls格式）
NPOI.DLL	NPOI.SS	Excel公用接口及Excel公式计算引擎
NPOI.DLL	NPOI.Util	基础类库，提供了很多实用功能，可用于其他读写文件格式项目的开发
NPOI.OOXML.DLL	NPOI.XSSF	Excel 2007(xlsx)格式读写库（导出xlsx格式）
NPOI.OOXML.DLL	NPOI.XWPF	Word 2007(docx)格式读写库
NPOI.OpenXml4Net.DLL	NPOI.OpenXml4Net	OpenXml底层zip包读写库
NPOI.OpenXmlFormats.DLL	NPOI.OpenXmlFormats	微软Office OpenXml对象关系库

导出xls格式用HSSF，请包含：using NPOI.HSSF.UserModel;

using NPOI.HSSF.UserModel;

导出xlsx格式用XSSF ，请包含：using NPOI.XSSF.UserModel;

using NPOI.XSSF.UserModel;

 

二、NPOI控件下载

        有2种途径下载NPOI控件，方法1是官网下载，但是比较麻烦，需要自己编译成DLL文件。方法2是利用Visual Studio 2017的NuGet包管理器。

       用Visual Studio 2017的NuGet包管理器下载最方便，可以直接下载到DLL库。

        1、官网下载NPOI

http://npoi.codeplex.com/releases/

       

        2、Visual Studio 2017的NuGet包管理器下载

 

三、创建数据库

        数据库名称=CarSYS
        数据库CarSYS内有一个Brand表
        数据库CarSYS的Brand表内有2个字段：BrandId，BrandName。

四、创建ASP.NET WEB程序

五、用Visual Studio 2017的NuGet包管理器下载NPOI库

1、鼠标右键单击【引用】，弹出下拉菜单中选择【管理NuGet程序包】

鼠标点击浏览，在输入框中输入NPOI，会在下面出现NPOI下载项（我下载的时候NPOI最新版本为V2.5.1）。 

鼠标点击NPO由NPOI Contributors,5.62M个下载，右边会出现当前版本号和安装按钮。（当前版本号为：最新稳定版本2.5.1）

鼠标点击上图的【安装】按钮，安装进程参见下图

安装完毕，可在右边的【引用】中观察到NPOI库，包括：

        LCSharpCode.SharpZIPLib，NPOI，NPOI.OOXML，NPOI.OpenXml4Net，NPOI.OpenXmlFormats。

 

六、添加MySQL库

要想添加MySQL库前提是你的电脑上已经安装好了MySQL数据库。

 

七、编写代码

1、在WebForm1.aspx中添加一个按钮，命名为btnExport，按钮文本为“导出到EXCEL”。

2、在在WebForm1.aspx.cs中编写代码

（1）、一定要添加MySQL的命名空间

using MySql.Data.MySqlClient;

（2）、一定要添加NPOI的命名空间

using NPOI;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

 （3）、添加程序代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using MySql.Data.MySqlClient;

using NPOI;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;


namespace 用NPOI控件把MySQL数据库中查询出来的数据数据导出到EXCEL
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            string constr = "Server=localhost;UserId=root;Password=68331;Database=CarSYS;pooling=false;CharSet=utf8;port=3306";//声明一个字符串用来存放连接数据库的信息
            //string sql = "select * from Brand";//声明一个字符串，用来存放查询数据库表语句
            MySqlConnection con = new MySqlConnection(constr);//创建一个SqlConnection对象，由于我使用的是MySQL数据库，因此注意要引用using MySql.Data.MySqlClient;
            try       //将可能出错的语句放在try语句里
            {
                con.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandText = "select * from Brand where BrandId>=1 && BrandId <=3";  //声明一个字符串，用来存放查询数据库表语句
                MySqlDataReader reader = cmd.ExecuteReader();
                DataTable dt = ReaderToTable(reader);
                ExportExcel(dt);

                con.Close();
                con.Dispose();
                cmd.Dispose();
                reader.Close();
                dt.Dispose();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                con.Close();//关闭数据库连接
            }
        }


        protected DataTable ReaderToTable(MySqlDataReader dr)
        {
            DataTable dt = new DataTable();

            for (int i = 0; i < dr.FieldCount; i++)
            {
                dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
            }

            object[] objValues = new object[dr.FieldCount];
            while (dr.Read())
            {
                dr.GetValues(objValues);
                dt.LoadDataRow(objValues, true);
            }
            dr.Close();
            return dt;
        }


        protected void ExportExcel(DataTable dt)
        {
            HttpContext curContext = HttpContext.Current;
            //设置编码及附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            string fullName = HttpUtility.UrlEncode("FileName.xls", Encoding.UTF8);
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(fullName, Encoding.UTF8));  //attachment后面是分号

            byte[] data = TableToExcel(dt, fullName).GetBuffer();
            curContext.Response.BinaryWrite(TableToExcel(dt, fullName).GetBuffer());
            curContext.Response.End();
        }


        public MemoryStream TableToExcel(DataTable dt, string file)
        {
            //创建workbook
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx")
                workbook = new XSSFWorkbook();
            else if (fileExt == ".xls")
                workbook = new HSSFWorkbook();
            else
                workbook = null;
            //创建sheet
            ISheet sheet = workbook.CreateSheet("Sheet1");

            //表头
            IRow headrow = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell headcell = headrow.CreateCell(i);
                headcell.SetCellValue(dt.Columns[i].ColumnName);
            }
            //表内数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转化为字节数组
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }

    }
}

 结果发现如下错误：

 错误1参见下图

添加   using System.Data;   后，DataTable错误消失。

using System.Data;

 

添加后   using System.Text;   ，Encoding错误消失。

using System.Text;

添加   using System.IO;   后，Path和MemoryStream错误消失。

 

（4）、完整无任何错误的程序代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using MySql.Data.MySqlClient;

using NPOI;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

using System.Data;
using System.Text;
using System.IO;

namespace 用NPOI控件把MySQL数据库中查询出来的数据数据导出到EXCEL
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            string constr = "Server=localhost;UserId=root;Password=68331;Database=CarSYS;pooling=false;CharSet=utf8;port=3306";//声明一个字符串用来存放连接数据库的信息
            //string sql = "select * from Brand";//声明一个字符串，用来存放查询数据库表语句
            MySqlConnection con = new MySqlConnection(constr);//创建一个SqlConnection对象，由于我使用的是MySQL数据库，因此注意要引用using MySql.Data.MySqlClient;
            try       //将可能出错的语句放在try语句里
            {
                con.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = con;
                cmd.CommandText = "select * from Brand where BrandId>=1 && BrandId <=3";  //声明一个字符串，用来存放查询数据库表语句
                MySqlDataReader reader = cmd.ExecuteReader();
                DataTable dt = ReaderToTable(reader);
                ExportExcel(dt);

                con.Close();
                con.Dispose();
                cmd.Dispose();
                reader.Close();
                dt.Dispose();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
            finally
            {
                con.Close();//关闭数据库连接
            }
        }


        protected DataTable ReaderToTable(MySqlDataReader dr)
        {
            DataTable dt = new DataTable();

            for (int i = 0; i < dr.FieldCount; i++)
            {
                dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i));
            }

            object[] objValues = new object[dr.FieldCount];
            while (dr.Read())
            {
                dr.GetValues(objValues);
                dt.LoadDataRow(objValues, true);
            }
            dr.Close();
            return dt;
        }


        protected void ExportExcel(DataTable dt)
        {
            HttpContext curContext = HttpContext.Current;
            //设置编码及附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            string fullName = HttpUtility.UrlEncode("FileName.xls", Encoding.UTF8);
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(fullName, Encoding.UTF8));  //attachment后面是分号

            byte[] data = TableToExcel(dt, fullName).GetBuffer();
            curContext.Response.BinaryWrite(TableToExcel(dt, fullName).GetBuffer());
            curContext.Response.End();
        }


        public MemoryStream TableToExcel(DataTable dt, string file)
        {
            //创建workbook
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx")
                workbook = new XSSFWorkbook();
            else if (fileExt == ".xls")
                workbook = new HSSFWorkbook();
            else
                workbook = null;
            //创建sheet
            ISheet sheet = workbook.CreateSheet("Sheet1");

            //表头
            IRow headrow = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell headcell = headrow.CreateCell(i);
                headcell.SetCellValue(dt.Columns[i].ColumnName);
            }
            //表内数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转化为字节数组
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }

    }
}

八、运行程序

       程序运行后， 鼠标点【导出到EXCEL】按钮无任何反应。

        （1）、打开WebForm1.aspx，发现单击按钮btnExport关联的是Page_Load

 

（2）、把按钮btnExport的关联由Page_Load修改为btnExport_Click，参见下图

（3）、再次运行程序

点击【导出到EXCEL】，出现如下提示“你想怎么处理FileName.xls”，点击【保存】即可将MYSQL数据库符合条件的数据导出到EXCEL文件中。

 

（4）、将MYSQL数据库符合条件的数据导出到EXCEL文件参见下图