NpoiServiers.cs
6.68 KB
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
162
163
164
using HHECS.BllModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment;
using VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment;
namespace HHECS.Infrastructure.Excel.Npoi
{
/// <summary>
/// Npoi组件实现的Excel导入导出
/// </summary>
public class NpoiServiers
{
/// <summary>
/// 将泛型集合中的实体导出到指定的Excel文件
/// </summary>
/// <typeparam name="T">泛型方法中的类型</typeparam>
/// <param name="fileName">Excel路径和文件名</param>
/// <param name="dataList">包含若干对象的泛型集合</param>
/// <param name="columnNames">实体列的中文标题集合</param>
/// <param name="version">Excel的版本号，规定0:2007以前版本，否则为2007及以上版本</param>
/// <returns>成功返回ture</returns>
public async static Task<BllResult> ExportToExcel<T>(string filePath, string fileName, string sheetName, List<T> dataList,
Dictionary<string, string> columnNames, int version = 1) where T : class
{
if (dataList.Count < 1)
{
return BllResultFactory.Error($"无导入数据元素数:{dataList.Count}");
}
if (!Directory.Exists(filePath)) //如果文件不存在
{
Directory.CreateDirectory(filePath); //就创建一个文件夹
}
//文件路径
var url = filePath + "\\" + fileName;
//Step1：基于NPOI创建工作簿和工作表对象
//根据不同的office版本创建不同的工作簿对象
IWorkbook workBook = null;
if (version == 0)
workBook = new HSSFWorkbook(); //2007以下版本
else
workBook = new XSSFWorkbook(); //2007以上版本
//Step2：创建工作表
ISheet sheet = workBook.CreateSheet(sheetName);//表名参数
Type type = typeof(T);
PropertyInfo[] propertyinfos = type.GetProperties();//获取类型的公共属性
//Step3：循环生成列标题和设置样式
IRow rowTitle = sheet.CreateRow(0);
for (int i = 0; i < propertyinfos.Length; i++)
{
ICell cell = rowTitle.CreateCell(i); //创建单元格 propertyinfos[i].Name
if (columnNames[propertyinfos[i].Name] == null)
{
break;
}
cell.SetCellValue(columnNames[propertyinfos[i].Name]);// 设置行标题
SetCellStyle(workBook, cell); //设置单元格边框
SetColumnWidth(sheet, i, 20); //设置列宽
}
//Step4：循环实体集合生成各行数据
for (int i = 0; i < dataList.Count; i++)
{
IRow row = sheet.CreateRow(i + 1); //+1去除标题
for (int j = 0; j < propertyinfos.Length; j++)
{
ICell cell = row.CreateCell(j);
T model = dataList[i]; //根据泛型找到具体化的实体对象
string value = "";
try
{
//过滤DateTime，免得引发异常
if (propertyinfos[j].PropertyType == typeof(DateTime) || propertyinfos[j].PropertyType == typeof(DateTime?))
{
//不能ToString
if (propertyinfos[j].GetValue(model, null) == null)
{
value = "无数据";
}
else
{
value = ((DateTime)propertyinfos[j].GetValue(model)).ToString("yyyy-MM-dd HH:mm:ss.fff");
}
}
else
{
value = propertyinfos[j].GetValue(model, null).ToString();//基于反射获取实体属性值
}
cell.SetCellValue(value); //赋值Excel
SetCellStyle(workBook, cell);
}
catch (Exception ex)
{
value = "";
ex.ToString();
return BllResultFactory.Error($"导出异常：{ex}");
}
}
}
//Step5：保存为Excel文件
using (FileStream fs = File.OpenWrite(url))
{
await Task.Run(() =>
{
workBook.Write(fs);
return BllResultFactory.Success();
});
}
return BllResultFactory.Error();
}
/// <summary>
/// 设置cell单元格边框的公共方法
/// </summary>
/// <param name="workBook">接口类型的工作簿，能适应不同版本</param>
/// <param name="cell">cell单元格对象</param>
private static void SetCellStyle(IWorkbook workBook, ICell cell)
{
ICellStyle style = workBook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;//水平对齐
style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐
//设置字体
IFont font = workBook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeight = 15 * 15;
font.Color = IndexedColors.DarkGreen.Index; //字体颜色
//font.Color =(short )FontColor .Red ;
style.SetFont(font);
cell.CellStyle = style;
}
/// <summary>
/// 设置cell单元格列宽的公共方法
/// </summary>
/// <param name="sheet"></param>
/// <param name="index">第几列</param>
/// <param name="width">具体宽度值</param>
private static void SetColumnWidth(ISheet sheet, int index, int width)
{
sheet.SetColumnWidth(index, width * 300);
}
}
}