NPOI组件下载、引用、基本使用

• 前言

NPOI是一组dll库文件，也叫COM组件。对于Excel表格用代码操作，主要有三种方式：

1.OLEDB类似于数据库的操作，需要安装AccessDataBase数据库引擎；
2.Office组件，也是引用dll文件，依赖Office软件，并且会因Office的版本有一些区别和问题；
3.NPOI组件，就是在程序中添加dll文件，然后进行Excel表格的读写，表格处理。

• 背景

我是用NPOI+C#的模式完成了表格的处理，并且不依赖任何的办公软件，比Office组件好用。处理的语句与Office的语句模式大致相同，细节略有不同。

• NPOI组件dll文件的来源

官网下载：http://npoi.codeplex.com/releases/

----解压，得到Release文件夹，其中有net20和net40，选择版本较高的net40。

----项目引用，右键添加引用，加入到项目中。

• 使用

注意：

NPOI 使用 HSSFWorkbook 类来处理 xls，XSSFWorkbook 类来处理 xlsx，它们都继承接口 IWorkbook，因此可以通过 IWorkbook 来统一处理 xls 和 xlsx 格式的文件。

添加头文件：

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

1
2
3
4
5

界面加一个button1,然后添加如下代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;

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

namespace NPOI_Test1
{
public partial class Page1 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {


    }

    protected void btnExport_Click(object sender, EventArgs e)
    {
        SqlConnection cn = new SqlConnection();
        cn.ConnectionString = "server=.;uid=sa;pwd=密码不告诉你;database=dawufan";
        cn.Open();
        string sqlstr = @"select * from interest";
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandText = sqlstr;
        SqlDataReader reader = cmd.ExecuteReader();
        DataTable dt = ReaderToTable(reader);

        ExportExcel(dt);

        cn.Close();
        cn.Dispose();
        cmd.Dispose();
        reader.Close();
        dt.Dispose();
    }

    protected DataTable ReaderToTable(SqlDataReader 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127

• 设置样式

Step1. 表头单元格样式
表头样式设置水平居中、大小14、黄背景红字、红底线。参考代码如下：

//表头样式
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.Boldweight = 20;
font.FontHeightInPoints = 14;
font.Color = HSSFColor.Red.Index;
headStyle.SetFont(font);
//以下三行为背景色
headStyle.FillForegroundColor = HSSFColor.Yellow.Index;
headStyle.FillPattern = FillPattern.Squares;
headStyle.FillBackgroundColor = HSSFColor.Yellow.Index;
//border
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
headStyle.BottomBorderColor = HSSFColor.Red.Index;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

Step2. 普通单元格样式
普通单元格样式设置水平居中、垂直居中、绿字。参考代码如下：

//普通单元格样式
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.Alignment = HorizontalAlignment.Center;
bodyStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Green.Index;
font1.Boldweight = 20;
bodyStyle.SetFont(font1);

1
2
3
4
5
6
7
8
9

Step3. 合并行
合并行使用 ISheet.AddMergedRegion 方法来实现。对于本文示例，可以从第0列即id列入手，在对sheet的行进行遍历的基础上，对于具有相同id的行的前两列进行纵向合并。

参考代码如下：

//合并行
for(int i = 1; i < dt.Rows.Count + 1; i++)
{
    string value = sheet.GetRow(i).GetCell(0).StringCellValue;
    int end = i;
    //找到结束为止
    for(int j = i + 1; j < dt.Rows.Count + 1; j++)
    {
        string value1= sheet.GetRow(j).GetCell(0).StringCellValue;
        if (value != value1)
        {
            end = j - 1;
            break;
        }
        else if(value==value1 && j == dt.Rows.Count)
        {
            end = j;
            break;
        }
    }
    sheet.AddMergedRegion(new CellRangeAddress(i, end, 0, 0));
    sheet.AddMergedRegion(new CellRangeAddress(i, end, 1, 1));
    i = end;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Step4. 设置列宽
参考代码如下：

//列宽
sheet.SetColumnWidth(0, 20 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);

1
2
3
4
5
6

• 结束语

本文介绍了在C#项目中使用NPOI组件进行表格处理的方法和步骤，以及DLL文件的来源，和实际使用的代码示例，记录在这里方便大家，还有我自己参考查阅。

参考：https://blog.csdn.net/wf824284257/article/details/77113691