Main
Main
文章目录
  1. 简介
  2. 2.常用属性方法-2016/12/7更新
    1. 01.创建工作簿
    2. 02.创建工作表
    3. 03.创建行
    4. 04.设置行高
    5. 05.创建列
    6. 06.设置列宽
    7. 07.合并单元格
    8. 08.设置单元格 style
    9. 09.ICellStyle属性
    10. 10.给单元格设置内容
  3. 1.通用的web导出Excel
    1. 01.下载引入相关dll
    2. 02.引用namespace
    3. 03.初始化Workbook添加属性信息
    4. 04.web导出
    5. 05.ExportStream

Npoi操作Excel

简介

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
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;
}

}
支持一下
  • 微信扫一扫
  • 支付宝扫一扫