简介

01.NPOI 是 POI 的 C#版本，NPOI 的行和列的 index 都是从 0 开始。 02.整个 Excel 表格叫做工作表：WorkBook（工作薄）、页（工作表）Sheet、行 Row、单元格 Cell。

2.常用属性方法-2016/12/7 更新

01.创建工作簿
1
HSSFWorkbook workbook = new HSSFWorkbook();
02.创建工作表
1
ISheet sheet = workbook.CreateSheet();
03.创建行
1
IRow row = sheet.CreateRow(0); //创建第0行
04.设置行高
1
2
row.Height = 0;
row.HeightInPoints = 0;
05.创建列
1
ICell cell = row.CreateCell(0); //在第0行创建第0列
06.设置列宽
1
sheet.SetColumnWidth(index, width); //index代表哪一列，width代表宽度
07.合并单元格
1
sheet.AddMergedRegion(new CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol));
08.设置单元格 style
1
2
3
4
5
6
7
8
9
10
11
12
13
ICellStyle style = workbook.CreateCellStyle();
style.BorderLeft = BorderStyle.Thin; //0-13 //左边框、当然还有上下右
...
style.Alignment = HorizontalAlignment.Center;   //对齐方式-水平
style.VerticalAlignment = VerticalAlignment.Center; //对齐方式-垂直
style.WrapText = true; 换行
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 0;    //大小
font.FontName = ""; //字体格式 宋体-黑体-...
font.Color ;    //颜色
style.SetFont(font);
//给某一单元格设置style
row.GetCell(0).cellStyle = style;   //给row行第0列设置style,前提是已经创建cell(0)
09.ICellStyle 属性

ICellStyle

10.给单元格设置内容
1
row.CreateCell(0).SetCellValue("a"); //给row行第0列设置文字位a

1.通用的 web 导出 Excel

01.下载引入相关 dll

项目中引入 NPOI.dll

02.引用 namespace
1
2
3
4
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
03.初始化 Workbook 添加属性信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
var workbook = new HSSFWorkbook();
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
var si = PropertySetFactory.CreateSummaryInformation();
#region 文件属性信息

dsi.Company = "xxx";
workbook.DocumentSummaryInformation = dsi;

si.Author = ".Net";
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;

#endregion
return workbook;
04.web 导出
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="dt">DataTable 数据源</param>
/// <param name="headerText">excel文件表头</param>
/// <param name="fileName">文件名</param>
/// <param name="columnNames">列名</param>
public void ExportToWeb(DataTable dt, string headerText, string fileName, string[] columnNames)
{
    HttpContext current = HttpContext.Current;

    fileName = DateTime.Now.ToString().Replace("-", "").Replace(":", "").Replace(" ", "") +
                HttpUtility.UrlEncode($"{fileName}", Encoding.UTF8) + ".xls";

    // 设置编码和附件格式
    current.Response.ContentType = "application/vnd.ms-excel";
    current.Response.ContentEncoding = Encoding.UTF8;
    current.Response.Charset = "";
    current.Response.AppendHeader("Content-Disposition",
        "attachment;filename=" + fileName);
    current.Response.BinaryWrite(ExportStream(dt, headerText, columnNames).GetBuffer());
    current.Response.End();
}
05.ExportStream
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
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
private MemoryStream ExportStream(DataTable dt, string headerText, string[] columnNames)
{
    if (dt.Rows.Count == 0)
        throw new ArgumentException("参数不正确：DataTable数据源为空!");
    if (columnNames != null && dt.Columns.Count != columnNames.Length)
        throw new ArgumentException("参数不正确：DataTable数据源列和columnNames数据列个数不相等");

    HSSFWorkbook workbook = InitWorkbook();
    ISheet sheet = workbook.CreateSheet();

    ICellStyle cellStyle = workbook.CreateCellStyle();
    IDataFormat format = workbook.CreateDataFormat();
    cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
    cellStyle.BorderLeft = BorderStyle.Thin;
    cellStyle.BorderRight = BorderStyle.Thin;
    cellStyle.BorderTop = BorderStyle.Thin;
    cellStyle.BorderBottom = BorderStyle.Thin;
    cellStyle.Alignment = HorizontalAlignment.Center;
    cellStyle.VerticalAlignment = VerticalAlignment.Center;
    //cellStyle.WrapText = true; //换行
    //cellStyle.BorderBottom = BorderStyle.Thin;

    //取得列宽
    int[] arrColWidth = new int[dt.Columns.Count];

    foreach (DataColumn item in dt.Columns)
    {
        arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).Length + 1; //936-GB2312
    }

    for (int i = 0; i < dt.Rows.Count; i++)
    {
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;
            if (intTemp > arrColWidth[j])
            {
                arrColWidth[j] = intTemp;
            }
        }
    }
    int rowIndex = 0;
    //foreach (DataRow row in dt.Rows)
    //if (dt.Rows.Count > 0)
    {
        #region 新建表，填充表头，填充列头，样式
        if (rowIndex == 65535 || rowIndex == 0)
        {
            if (rowIndex != 0)
            {
                sheet = workbook.CreateSheet();
            }

            #region 表头及样式
            {
                IRow headerRow = sheet.CreateRow(0);
                headerRow.HeightInPoints = 25;
                headerRow.CreateCell(0).SetCellValue(headerText);

                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.Center;

                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 20;
                font.Boldweight = 700;
                headStyle.SetFont(font);

                headerRow.GetCell(0).CellStyle = headStyle;

                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1)); //合并单元格

            }
            #endregion


            #region 列头及样式
            {
                IRow headerRow = sheet.CreateRow(1);
                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.Center;
                headStyle.BorderLeft = BorderStyle.Thin;
                headStyle.BorderRight = BorderStyle.Thin;
                headStyle.BorderTop = BorderStyle.Thin;
                headStyle.BorderBottom = BorderStyle.Thin;

                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 12;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                if (columnNames == null)    //填充datatable中的ColumnName
                {
                    foreach (DataColumn column in dt.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                        //设置列宽
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

                        //sheet.SetActiveCellRange();
                    }
                }
                else //填充自定义列名
                {
                    foreach (DataColumn column in dt.Columns)
                    {
                        headerRow.CreateCell(column.Ordinal).SetCellValue(columnNames[column.Ordinal]);
                        headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                        //设置列宽
                        sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                    }
                }
            }
            #endregion

            rowIndex = 2;
        }
        #endregion


        #region 填充内容

        //dataRow.Height = 9 * 256;    //高度

        #region 样式
        ICellStyle contentStyle = workbook.CreateCellStyle();
        contentStyle.Alignment = HorizontalAlignment.Center;
        contentStyle.WrapText = true;
        contentStyle.VerticalAlignment = VerticalAlignment.Center;
        contentStyle.BorderLeft = BorderStyle.Thin;
        contentStyle.BorderRight = BorderStyle.Thin;
        contentStyle.BorderTop = BorderStyle.Thin;
        contentStyle.BorderBottom = BorderStyle.Thin;
        #endregion

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            IRow dataRow = sheet.CreateRow(rowIndex);

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                ICell newCell = dataRow.CreateCell(j);
                newCell.CellStyle = contentStyle;
                newCell.SetCellValue(dt.Rows[i][j].ToString());
            }

            rowIndex++;
        }
        #endregion

    }
    using (MemoryStream ms = new MemoryStream())
    {
        workbook.Write(ms);
        ms.Flush();
        ms.Position = 0;

        return ms;
    }

}