ExcelUtility 类库经过我（梦在旅途）近期不断的优化与新增功能，现已基本趋向稳定，功能上也基本可以满足绝大部份的EXCEL导出需求，该类库已在我们公司大型ERP系统全面使用，效果不错，今天应用户的特殊需求，我又新增了一个功能，导出时动态生成多Sheet EXCEL。
新增方法一：由GetFormatterContainer Func委托导出基于EXCEL模板的多Sheet文件，方法定义如下：
|
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
|
/// <summary>
/// 由GetFormatterContainer Func委托导出基于EXCEL模板的多工作薄文件
/// </summary>
/// <typeparam name="T">数据源可枚举项类型</typeparam>
/// <param name="templatePath">模板路径</param>
/// <param name="sheetName">模板中使用的工作薄名称</param>
/// <param name="dataSource">数据源</param>
/// <param name="getFormatterContainer">生成模板数据格式化容器（SheetFormatterContainer）委托,在委托方法中实现模板的格式化过程</param>
/// <param name="sheetSize">每个工作薄显示的数据记录数</param>
/// <param name="filePath">导出路径，可选</param>
/// <returns></returns>
public
static
string
ToExcelWithTemplate<T>(
string
templatePath,
string
sheetName, IEnumerable<T> dataSource, Func<IEnumerable<T>, SheetFormatterContainer> getFormatterContainer,
int
sheetSize,
string
filePath =
null
)
{
if
(!File.Exists(templatePath))
{
throw
new
FileNotFoundException(templatePath +
"文件不存在！"
);
}
bool
isCompatible = Common.GetIsCompatible(templatePath);
if
(
string
.IsNullOrEmpty(filePath))
{
filePath = Common.GetSaveFilePath(isCompatible);
}
else
if
(isCompatible && !Path.GetExtension(filePath).Equals(
".xls"
, StringComparison.OrdinalIgnoreCase))
{
throw
new
ArgumentException(
"当模板采用兼容模式时（低版本格式，如：xls,xlt），则指定的导出文件格式必需为xls。"
);
}
if
(
string
.IsNullOrEmpty(filePath))
return
null
;
int
sheetCount = 0;
var
formatterContainers =
new
Dictionary<
string
, SheetFormatterContainer>();
IEnumerable<T> data =
null
;
while
((data = dataSource.Take(sheetSize)).Count() > 0)
{
var
sheetFormatterContainer = getFormatterContainer(data);
sheetCount++;
if
(sheetCount == 1)
{
formatterContainers.Add(sheetName, sheetFormatterContainer);
}
else
{
formatterContainers.Add(sheetName + sheetCount.ToString(), sheetFormatterContainer);
}
dataSource = dataSource.Skip(sheetSize);
}
string
temp_templatePath =
null
;
try
{
temp_templatePath = Common.CreateTempFileByTemplate(templatePath, sheetName, sheetCount);
filePath = ToExcelWithTemplate(temp_templatePath, formatterContainers, filePath);
}
finally
{
if
(!
string
.IsNullOrEmpty(temp_templatePath) && File.Exists(temp_templatePath))
{
File.Delete(temp_templatePath);
}
string
temp_templateConfigFilePath = Path.ChangeExtension(temp_templatePath,
".xml"
);
if
(File.Exists(temp_templateConfigFilePath))
{
File.Delete(temp_templateConfigFilePath);
}
}
return
filePath;
}
|
简要说明上述方法实现原理步骤：
1.指定模板路径、初始工作薄名称、导出的数据源、每个工作薄显示的记录数、封装生成模板数据格式化容器（SheetFormatterContainer）委托,在委托方法中实现模板的格式化过程;
2.依据每个工作薄显示的记录数，循环拆分数据源，并计算出需要的工作薄总数以及生成模板数据格式化容器字典（Key:Sheet名称，Value:模板数据格式化容器对象）；
3.生成2中计算的所需的工作薄的临时模板文件（存放在系统的本地临时目录：Temp）
4.调用ToExcelWithTemplate的其它重载方法（基于模板+多sheet生成EXCEL方法）来完成EXCEL的导出；
5.无论最终导出成功与否，将删除临时模板及临时模板配置文件；
测试示例代码如下：
|
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
|
/// <summary>
/// 测试方法：测试依据模板+DataTable来生成多工作薄的EXCEL
/// </summary>
[TestMethod]
public
void
TestExportToExcelWithTemplateByDataTable2()
{
DataTable dt = GetDataTable();
string
templateFilePath = AppDomain.CurrentDomain.BaseDirectory +
"/excel.xls"
;
//获得EXCEL模板路径
string
excelPath = ExcelUtility.Export.ToExcelWithTemplate<DataRow>(templateFilePath,
"table"
, dt.Select(), (data) =>
{
SheetFormatterContainer formatterContainers =
new
SheetFormatterContainer();
//实例化一个模板数据格式化容器
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);
//添加到工作薄格式容器中，注意只有添加进去了才会生效
//实例化一个表格格式化器，data是已拆分后的数据源(这里是10条记录)，name表示的模板表格中第一行第一个单元格要填充的数据参数名
TableFormatterBuilder<DataRow> tableFormatterBuilder =
new
TableFormatterBuilder<DataRow>(data,
"name"
);
//这里的数据源设置：data是重点
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中的列Col4
{
"result"
,r=>r[
"Col5"
]}
//将模板表格中result对应DataTable中的列Co5
});
formatterContainers.AppendFormatterBuilder(tableFormatterBuilder);
//添加到工作薄格式容器中，注意只有添加进去了才会生效
return
formatterContainers;
//返回一个模板数据格式化容器
}, 10);
//注意这里的10表示动态生成新的工作薄，且每个工作薄显示10条记录
Assert.IsTrue(File.Exists(excelPath));
}
|
测试结果如下：
1.生成的临时模板及模板配置文件：
2.导出的EXCE结果如下：
3.临时模板及模板配置文件已被清除。
新增方法二：增加由DataTable导出多Sheet Excel方法（准确的说是修改ToExcel方法，增加一个sheetSize参数），方法定义如下：
|
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
|
/// <summary>
/// 由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作薄名称，可选</param>
/// <param name="filePath">导出路径，可选</param>
/// <param name="colNames">需要导出的列名,可选</param>
/// <param name="colAliasNames">导出的列名重命名，可选</param>
/// <param name="colDataFormats">列格式化集合，可选</param>
/// <param name="sheetSize">指定每个工作薄显示的记录数，可选（不指定或指定小于0，则表示只生成一个工作薄）</param>
/// <returns></returns>
public
static
string
ToExcel(DataTable sourceTable,
string
sheetName =
"result"
,
string
filePath =
null
,
string
[] colNames =
null
, IDictionary<
string
,
string
> colAliasNames =
null
, IDictionary<
string
,
string
> colDataFormats =
null
,
int
sheetSize = 0)
{
if
(sourceTable.Rows.Count <= 0)
return
null
;
if
(
string
.IsNullOrEmpty(filePath))
{
filePath = Common.GetSaveFilePath();
}
if
(
string
.IsNullOrEmpty(filePath))
return
null
;
bool
isCompatible = Common.GetIsCompatible(filePath);
IWorkbook workbook = Common.CreateWorkbook(isCompatible);
ICellStyle headerCellStyle = Common.GetCellStyle(workbook,
true
);
//ICellStyle cellStyle = Common.GetCellStyle(workbook);
if
(colNames ==
null
|| colNames.Length <= 0)
{
colNames = sourceTable.Columns.Cast<DataColumn>().OrderBy(c => c.Ordinal).Select(c => c.ColumnName).ToArray();
}
IEnumerable<DataRow> batchDataRows, dataRows = sourceTable.Rows.Cast<DataRow>();
int
sheetCount = 0;
if
(sheetSize <= 0)
{
sheetSize = sourceTable.Rows.Count;
}
while
((batchDataRows = dataRows.Take(sheetSize)).Count() > 0)
{
Dictionary<
int
, ICellStyle> colStyles =
new
Dictionary<
int
, ICellStyle>();
ISheet sheet = workbook.CreateSheet(sheetName + (++sheetCount).ToString());
IRow headerRow = sheet.CreateRow(0);
// handling header.
for
(
int
i = 0; i < colNames.Length; i++)
{
ICell headerCell = headerRow.CreateCell(i);
if
(colAliasNames !=
null
&& colAliasNames.ContainsKey(colNames[i]))
{
headerCell.SetCellValue(colAliasNames[colNames[i]]);
}
else
{
headerCell.SetCellValue(colNames[i]);
}
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
if
(colDataFormats !=
null
&& colDataFormats.ContainsKey(colNames[i]))
{
colStyles[headerCell.ColumnIndex] = Common.GetCellStyleWithDataFormat(workbook, colDataFormats[colNames[i]]);
}
else
{
colStyles[headerCell.ColumnIndex] = Common.GetCellStyle(workbook);
}
}
// handling value.
int
rowIndex = 1;
foreach
(DataRow row
in
batchDataRows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for
(
int
i = 0; i < colNames.Length; i++)
{
ICell cell = dataRow.CreateCell(i);
//cell.SetCellValue((row[colNames[i]] ?? "").ToString());
//cell.CellStyle = cellStyle;
Common.SetCellValue(cell, (row[colNames[i]] ??
""
).ToString(), sourceTable.Columns[colNames[i]].DataType, colStyles);
Common.ReSizeColumnWidth(sheet, cell);
}
rowIndex++;
}
sheet.ForceFormulaRecalculation =
true
;
dataRows = dataRows.Skip(sheetSize);
}
FileStream fs =
new
FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
workbook =
null
;
return
filePath;
}
|
修改代码部份说明：增加依据指定的每个工作薄显示的记录数(sheetSize)来循环拆分数据源及创建多个工作薄；
测试示例代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
|
/// <summary>
/// 测试方法：测试将DataTable导出到多工作薄EXCEL
/// </summary>
[TestMethod]
public
void
TestExportToExcelByDataTable8()
{
DataTable dt = GetDataTable();
string
excelPath = ExcelUtility.Export.ToExcel(dt,
"sheet"
, sheetSize: 10);
//指定每个工作薄显示的记录数
Assert.IsTrue(File.Exists(excelPath));
}
|
导出的EXCE结果如下：
源代码同步更新至开源社区的GIT目录中，具体地址请看我该系列之前的文章有列出，在此就不再说明。
分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库：ExcelUtility 其它相关文章链接：
分享我基于NPOI+ExcelReport实现的导入与导出EXCEL类库：ExcelUtility （续2篇-模板导出综合示例）
本文转自 梦在旅途 博客园博客，原文链接：http://www.cnblogs.com/zuowj/p/5175726.html ，如需转载请自行联系原作者
