NpoiHelper.cs
5.71 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
using HHECS.BllModel;
using HHECS.WorkHourStatistics.Dtos;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace HHECS.WorkHourStatistics.Utils
{
internal class NpoiHelper
{
/// <summary>
/// 导出数据
/// </summary>
/// <param name="fullPath">文件完整路径</param>
public static BllResult ExportReport(string fullPath, List<ReportModel> reports, List<string> headExtend)
{
try
{
IWorkbook workbook;
short foregroundColor = HSSFColor.SkyBlue.Index;
if (fullPath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook();
//var xssfColor = new XSSFColor();
//byte[] colorRgb = { 135, 206, 250 };
//xssfColor.SetRgb(colorRgb);
//foregroundColor = XSSFColor.ToXSSFColor(xssfColor).Index;
}
else if (fullPath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook();
}
else
return BllResultFactory.Error("文件类型不正确，文件后缀必须为“.xlsx”或“.xls”！");
ISheet sheet = workbook.CreateSheet("WT时间统计");
//设置样式
ICellStyle style = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.IsBold = true;
style.SetFont(font);
style.FillForegroundColor = foregroundColor;
style.FillPattern = FillPattern.SolidForeground;
foreach (var item in headExtend)
{
sheet.SetColumnWidth(headExtend.IndexOf(item) + 3, 15 * 256);
}
sheet.SetColumnWidth(1, 10 * 256);
List<string> excelHeader = new() { "工号", "姓名", "部门", "合计" };
excelHeader.InsertRange(3, headExtend);
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < excelHeader.Count; i++)
{
var cell = headerRow.CreateCell(i);
cell.SetCellValue(excelHeader[i]);
cell.CellStyle = style;
}
int rowNum = 1;
foreach (var item in reports)
{
IRow row = sheet.CreateRow(rowNum);
row.CreateCell(0).SetCellValue(item.WorkNo);
row.CreateCell(1).SetCellValue(item.Name);
row.CreateCell(2).SetCellValue(item.SysOrgCode);
foreach (var wt in item.Values)
{
var headIndex = headExtend.IndexOf(wt.Key.Split("#").Last()) + 3;
row.CreateCell(headIndex).SetCellValue(item.Values.First(x => x.Key == wt.Key).Value);
}
row.CreateCell(excelHeader.Count - 1).SetCellValue(item.Total);
rowNum++;
}
//写入到Excel中
using var fs = new FileStream(fullPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs, false);
return BllResultFactory.Success();
}
catch (Exception ex)
{
return BllResultFactory.Error(ex.Message);
}
}
public static BllResult<IWorkbook> GetWorkbook(IEnumerable<ReportModel> reports, IList<string> headExtend)
{
try
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("WT时间统计");
//设置样式
ICellStyle style = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.IsBold = true;
style.SetFont(font);
style.FillForegroundColor = HSSFColor.SkyBlue.Index;
style.FillPattern = FillPattern.SolidForeground;
foreach (var item in headExtend)
{
sheet.SetColumnWidth(headExtend.IndexOf(item) + 3, 15 * 256);
}
sheet.SetColumnWidth(1, 10 * 256);
List<string> excelHeader = new() { "工号", "姓名", "部门", "合计" };
excelHeader.InsertRange(3, headExtend);
IRow headerRow = sheet.CreateRow(0);
for (int i = 0; i < excelHeader.Count; i++)
{
var cell = headerRow.CreateCell(i);
cell.SetCellValue(excelHeader[i]);
cell.CellStyle = style;
}
int rowNum = 1;
foreach (var item in reports)
{
IRow row = sheet.CreateRow(rowNum);
row.CreateCell(0).SetCellValue(item.WorkNo);
row.CreateCell(1).SetCellValue(item.Name);
row.CreateCell(2).SetCellValue(item.SysOrgCode);
foreach (var wt in item.Values)
{
var headIndex = headExtend.IndexOf(wt.Key.Split("#").Last()) + 3;
row.CreateCell(headIndex).SetCellValue(item.Values.First(x => x.Key == wt.Key).Value);
}
row.CreateCell(excelHeader.Count - 1).SetCellValue(item.Total);
rowNum++;
}
return BllResultFactory.Success(workbook);
}
catch (Exception ex)
{
return BllResultFactory.Error<IWorkbook>(ex.Message);
}
}
}
}