1. ExcelUtility功能：
1.将数据导出到EXCEL(支持XLS,XLSX，支持多种类型模板，支持列宽自适应)
类名：ExcelUtility. Export
2.将EXCEL数据导入到数据对象中（DataTable、Dataset，支持XLS,XLSX）
类名：ExcelUtility. Import
类库项目文件结构如下图示：
2. ExcelUtility依赖组件：
1.NPOI 操作EXCEL核心类库
2.NPOI.Extend NPOI扩展功能
3. ExcelReport 基于NPOI的二次扩展，实现模板化导出功能
4. System.Windows.Forms 导出或导入时，弹出文件选择对话框（如果用在WEB中可以不需要，但我这里以CS端为主）
3.使用环境准备：
1.通过NUGet引用NPOI包、ExcelReport 包；（ExcelReport 存在BUG,可能需要用我项目中修复过后的DLL）
2.引用ExcelUtility类库；
4.具体使用方法介绍（示例代码，全部为测试方法）：
导出方法测试：
|
1
2
3
4
5
6
7
8
9
10
|
/// <summary>
/// 测试方法：测试将DataTable导出到EXCEL，无模板
/// </summary>
[TestMethod]
public
void
TestExportToExcelByDataTable()
{
DataTable dt = GetDataTable();
string
excelPath = ExcelUtility.Export.ToExcel(dt,
"导出结果"
);
Assert.IsTrue(File.Exists(excelPath));
}
|
结果如下图示：
|
1
2
3
4
5
6
7
8
9
10
11
|
/// <summary>
/// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出的列名
/// </summary>
[TestMethod]
public
void
TestExportToExcelByDataTable2()
{
DataTable dt = GetDataTable();
string
[] expColNames = {
"Col1"
,
"Col2"
,
"Col3"
,
"Col4"
,
"Col5"
};
string
excelPath = ExcelUtility.Export.ToExcel(dt,
"导出结果"
,
null
, expColNames);
Assert.IsTrue(File.Exists(excelPath));
}
|
结果如下图示：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
/// <summary>
/// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出的列名，以及导出列名的重命名
/// </summary>
[TestMethod]
public
void
TestExportToExcelByDataTable3()
{
DataTable dt = GetDataTable();
string
[] expColNames = {
"Col1"
,
"Col2"
,
"Col3"
,
"Col4"
,
"Col5"
};
Dictionary<
string
,
string
> expColAsNames =
new
Dictionary<
string
,
string
>() {
{
"Col1"
,
"列一"
},
{
"Col2"
,
"列二"
},
{
"Col3"
,
"列三"
},
{
"Col4"
,
"列四"
},
{
"Col5"
,
"列五"
}
};
string
excelPath = ExcelUtility.Export.ToExcel(dt,
"导出结果"
,
null
, expColNames,expColAsNames);
Assert.IsTrue(File.Exists(excelPath));
}
|
结果如下图示：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
/// <summary>
/// 测试方法：测试将DataTable导出到EXCEL，无模板，且指定导出列名的重命名
/// </summary>
[TestMethod]
public
void
TestExportToExcelByDataTable4()
{
DataTable dt = GetDataTable();
Dictionary<
string
,
string
> expColAsNames =
new
Dictionary<
string
,
string
>() {
{
"Col1"
,
"列一"
},
{
"Col5"
,
"列五"
}
};
string
excelPath = ExcelUtility.Export.ToExcel(dt,
"导出结果"
,
null
,
null
, expColAsNames);
Assert.IsTrue(File.Exists(excelPath));
}
|
结果如下图示：
|
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
|
/// <summary>
/// 测试方法：测试依据模板+DataTable来生成EXCEL
/// </summary>
[TestMethod]
public
void
TestExportToExcelWithTemplateByDataTable()
{
DataTable dt = GetDataTable();
//获取数据
string
templateFilePath = AppDomain.CurrentDomain.BaseDirectory +
"/excel.xlsx"
;
//获得EXCEL模板路径
SheetFormatterContainer<DataRow> formatterContainers =
new
SheetFormatterContainer<DataRow>();
//实例化一个模板数据格式化容器
PartFormatterBuilder partFormatterBuilder =
new
PartFormatterBuilder();
//实例化一个局部元素格式化器
partFormatterBuilder.AddFormatter(
"Title"
,
"IT学员"
);
//将模板表格中Title的值设置为跨越IT学员
formatterContainers.AppendFormatterBuilder(partFormatterBuilder);
//添加到工作薄格式容器中，注意只有添加进去了才会生效
CellFormatterBuilder cellFormatterBuilder =
new
CellFormatterBuilder();
//实例化一个单元格格式化器
cellFormatterBuilder.AddFormatter(
"rptdate"
, DateTime.Today.ToString(
"yyyy-MM-dd HH:mm"
));
//将模板表格中rptdate的值设置为当前日期
formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);
//添加到工作薄格式容器中，注意只有添加进去了才会生效
//实例化一个表格格式化器，dt.Select()是将DataTable转换成DataRow[]，name表示的模板表格中第一行第一个单元格要填充的数据参数名
TableFormatterBuilder<DataRow> tableFormatterBuilder =
new
TableFormatterBuilder<DataRow>(dt.Select(),
"name"
);
tableFormatterBuilder.AddFormatters(
new
Dictionary<
string
, Func<DataRow,
object
>>{
{
"name"
,r=>r[
"Col1"
]},
//将模板表格中name对应DataTable中的列Col1
{
"sex"
,r=>r[
"Col2"
]},
//将模板表格中sex对应DataTable中的列Col2
{
"km"
,r=>r[
"Col3"
]},
//将模板表格中km对应DataTable中的列Col3
{
"score"
,r=>r[
"Col4"
]},
//将模板表格中score对应DataTable中的列Col
{
"result"
,r=>r[
"Col5"
]}
//将模板表格中result对应DataTable中的列Co5
});
formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);
//添加到工作薄格式容器中，注意只有添加进去了才会生效
string
excelPath = ExcelUtility.Export.ToExcelWithTemplate<DataRow>(templateFilePath,
"table"
, formatterContainers);
Assert.IsTrue(File.Exists(excelPath));
}
|
模板如下图示：
结果如下图示：
|
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
|
/// <summary>
/// 测试方法：测试依据模板+List来生成EXCEL
/// </summary>
[TestMethod]
public
void
TestExportToExcelWithTemplateByList()
{
List<Student> studentList = GetStudentList();
//获取数据
string
templateFilePath = AppDomain.CurrentDomain.BaseDirectory +
"/excel.xlsx"
;
//获得EXCEL模板路径
SheetFormatterContainer<Student> formatterContainers =
new
SheetFormatterContainer<Student>();
//实例化一个模板数据格式化容器
PartFormatterBuilder partFormatterBuilder =
new
PartFormatterBuilder();
//实例化一个局部元素格式化器
partFormatterBuilder.AddFormatter(
"Title"
,
"IT学员"
);
//将模板表格中Title的值设置为跨越IT学员
formatterContainers.AppendFormatterBuilder(partFormatterBuilder);
//添加到工作薄格式容器中，注意只有添加进去了才会生效
CellFormatterBuilder cellFormatterBuilder =
new
CellFormatterBuilder();
//实例化一个单元格格式化器
cellFormatterBuilder.AddFormatter(
"rptdate"
, DateTime.Today.ToString(
"yyyy-MM-dd HH:mm"
));
//将模板表格中rptdate的值设置为当前日期
formatterContainers.AppendFormatterBuilder(cellFormatterBuilder);
//添加到工作薄格式容器中，注意只有添加进去了才会生效
//实例化一个表格格式化器，studentList本身就是可枚举的无需转换，name表示的模板表格中第一行第一个单元格要填充的数据参数名
TableFormatterBuilder<Student> tableFormatterBuilder =
new
TableFormatterBuilder<Student>(studentList,
"name"
);
tableFormatterBuilder.AddFormatters(
new
Dictionary<
string
, Func<Student,
object
>>{
{
"name"
,r=>r.Name},
//将模板表格中name对应Student对象中的属性Name
{
"sex"
,r=>r.Sex},
//将模板表格中sex对应Student对象中的属性Sex
{
"km"
,r=>r.KM},
//将模板表格中km对应Student对象中的属性KM
{
"score"
,r=>r.Score},
//将模板表格中score对应Student对象中的属性Score
{
"result"
,r=>r.Result}
//将模板表格中result对应Student对象中的属性Result
});
formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);
string
excelPath = ExcelUtility.Export.ToExcelWithTemplate<Student>(templateFilePath,
"table"
, formatterContainers);
Assert.IsTrue(File.Exists(excelPath));
}
|
结果如下图示：（模板与上面相同）
|
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
|
/// <summary>
/// 测试方法：测试依据模板+DataTable来生成多表格EXCEL（注意：由于NPOI框架限制，目前仅支持模板文件格式为：xls）
/// </summary>
[TestMethod]
public
void
TestExportToRepeaterExcelWithTemplateByDataTable()
{
DataTable dt = GetDataTable();
//获取数据
string
templateFilePath = AppDomain.CurrentDomain.BaseDirectory +
"/excel2.xls"
;
//获得EXCEL模板路径
SheetFormatterContainer<DataRow> formatterContainers =
new
SheetFormatterContainer<DataRow>();
//实例化一个模板数据格式化容器
//实例化一个可重复表格格式化器，dt.Select()是将DataTable转换成DataRow[]，rpt_begin表示的模板表格开始位置参数名，rpt_end表示的模板表格结束位置参数名
RepeaterFormatterBuilder<DataRow> tableFormatterBuilder =
new
RepeaterFormatterBuilder<DataRow>(dt.Select(),
"rpt_begin"
,
"rpt_end"
);
tableFormatterBuilder.AddFormatters(
new
Dictionary<
string
, Func<DataRow,
object
>>{
{
"sex"
,r=>r[
"Col2"
]},
//将模板表格中sex对应DataTable中的列Col2
{
"km"
,r=>r[
"Col3"
]},
//将模板表格中km对应DataTable中的列Col3
{
"score"
,r=>r[
"Col4"
]},
//将模板表格中score对应DataTable中的列Col
{
"result"
,r=>r[
"Col5"
]}
//将模板表格中result对应DataTable中的列Co5
});
PartFormatterBuilder<DataRow> partFormatterBuilder2 =
new
PartFormatterBuilder<DataRow>();
//实例化一个可嵌套的局部元素格式化器
partFormatterBuilder2.AddFormatter(
"name"
, r => r[
"Col1"
]);
//将模板表格中name对应DataTable中的列Col1
tableFormatterBuilder.AppendFormatterBuilder(partFormatterBuilder2);
//添加到可重复表格格式化器中，作为其子格式化器
CellFormatterBuilder<DataRow> cellFormatterBuilder =
new
CellFormatterBuilder<DataRow>();
//实例化一个可嵌套的单元格格式化器
cellFormatterBuilder.AddFormatter(
"rptdate"
, r => DateTime.Today.ToString(
"yyyy-MM-dd HH:mm"
));
//将模板表格中rptdate的值设置为当前日期
tableFormatterBuilder.AppendFormatterBuilder(cellFormatterBuilder);
//添加到可重复表格格式化器中，作为其子格式化器
formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);
//添加到工作薄格式容器中，注意只有添加进去了才会生效
string
excelPath = ExcelUtility.Export.ToExcelWithTemplate<DataRow>(templateFilePath,
"multtable"
, formatterContainers);
Assert.IsTrue(File.Exists(excelPath));
}
|
模板如下图示：（注意：该模板仅支持XLS格式文件，XLSX下存在问题）
结果如下图示：
以下是模拟数据来源所定义的方法（配合测试）：
|
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
|
private
DataTable GetDataTable()
{
DataTable dt =
new
DataTable();
for
(
int
i = 1; i <= 6; i++)
{
if
(i == 4)
{
dt.Columns.Add(
"Col"
+ i.ToString(),
typeof
(
double
));
}
else
{
dt.Columns.Add(
"Col"
+ i.ToString(),
typeof
(
string
));
}
}
for
(
int
i = 1; i <= 10; i++)
{
dt.Rows.Add(
"Name"
+ i.ToString(), (i % 2) > 0 ?
"男"
:
"女"
,
"科目"
+ i.ToString(), i *
new
Random().Next(1, 5),
"待定"
, Guid.NewGuid().ToString(
"N"
));
}
return
dt;
}
private
List<Student> GetStudentList()
{
List<Student> studentList =
new
List<Student>();
for
(
int
i = 1; i <= 10; i++)
{
studentList.Add(
new
Student
{
Name =
"Name"
+ i.ToString(),
Sex = (i % 2) > 0 ?
"男"
:
"女"
,
KM =
"科目"
+ i.ToString(),
Score = i *
new
Random().Next(1, 5),
Result =
"待定"
});
}
return
studentList;
}
class
Student
{
public
string
Name {
get
;
set
; }
public
string
Sex {
get
;
set
; }
public
string
KM {
get
;
set
; }
public
double
Score {
get
;
set
; }
public
string
Result {
get
;
set
; }
}
|
导入方法测试：
|
1
2
3
4
5
6
7
8
9
10
|
/// <summary>
/// 测试方法：测试将指定的EXCEL数据导入到DataTable
/// </summary>
[TestMethod]
public
void
TestImportToDataTableFromExcel()
{
//null表示由用户选择EXCEL文件路径，data表示要导入的sheet名,0表示数据标题行
DataTable dt= ExcelUtility.Import.ToDataTable(
null
,
"data"
, 0);
Assert.AreNotEqual(0, dt.Rows.Count);
}
|
数据源文件内容如下图示：
下面贴出该类库主要源代码：
ExcelUtility.Export类：
ExcelUtility.Import类：
Common类根据单元格内容重新设置列宽ReSizeColumnWidth
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
/// <summary>
/// 根据单元格内容重新设置列宽
/// </summary>
/// <param name="sheet"></param>
/// <param name="cell"></param>
public
static
void
ReSizeColumnWidth(ISheet sheet, ICell cell)
{
int
cellLength = (Encoding.Default.GetBytes(cell.ToString()).Length + 5) * 256;
const
int
maxLength = 255 * 256;
if
(cellLength > maxLength)
{
cellLength = maxLength;
}
int
colWidth = sheet.GetColumnWidth(cell.ColumnIndex);
if
(colWidth < cellLength)
{
sheet.SetColumnWidth(cell.ColumnIndex, cellLength);
}
}
|
注意这个方法中，列宽自动设置最大宽度为255个字符宽度。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
/// <summary>
/// 创建表格样式
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
public
static
ICellStyle GetCellStyle(IWorkbook workbook,
bool
isHeaderRow =
false
)
{
ICellStyle style = workbook.CreateCellStyle();
if
(isHeaderRow)
{
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
IFont f = workbook.CreateFont();
f.Boldweight = (
short
)FontBoldWeight.Bold;
style.SetFont(f);
}
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
return
style;
}
|
发文时，部份方法代码已经更新了，所以实际效果以GIT项目中的为准。
该类库源码已分享到该路径中：http://git.oschina.net/zuowj/ExcelUtility GIT Repository路径：git@git.oschina.net:zuowj/ExcelUtility.git
本文转自 梦在旅途 博客园博客，原文链接：http://www.cnblogs.com/zuowj/p/5113812.html ，如需转载请自行联系原作者
