NpoiHelper.cs
3.56 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
using HHECS.BllModel;
using HHECS.DAQShared.Common.Enums;
using HHECS.DAQShared.Models;
using NPOI.XSSF.UserModel;
namespace HHECS.Tool.Utils
{
internal static class NpoiHelper
{
public static BllResult<List<Equipment>> GetEquipmentData(string filePath)
{
try
{
using var file = new FileStream(filePath, FileMode.Open, FileAccess.Read);
var hssfworkbook = new XSSFWorkbook(file);
var equipmentSheet = hssfworkbook.GetSheetAt(0);
var equipmentPropSheet = hssfworkbook.GetSheetAt(1);
var equipments = new List<Equipment>();
//设备
for (int i = 1; i <= equipmentSheet.LastRowNum; i++)
{
var row = equipmentSheet.GetRow(i);
if (row == null)
{
continue;
}
equipments.Add(new Equipment
{
Id = (int)row.GetCell(0).NumericCellValue,
Code = row.GetCell(1).StringCellValue,
Name = row.GetCell(2).StringCellValue,
EquipmentTypeId = (int)row.GetCell(3).NumericCellValue,
IP = row.GetCell(4).StringCellValue,
DestinationArea = row.GetCell(5).NumericCellValue.ToString(),
Description = row.GetCell(6).StringCellValue,
Disable = false,
ConnectName = row.GetCell(7).StringCellValue,
ProjectCode = row.GetCell(8).StringCellValue,
FactoryCode = row.GetCell(9).StringCellValue,
});
}
//设备属性
for (int i = 1; i <= equipmentPropSheet.LastRowNum; i++)
{
var row = equipmentPropSheet.GetRow(i);
if (row == null)
{
continue;
}
_ = Enum.TryParse<EquipmentPropType>(row.GetCell(4).StringCellValue, out var propType);
_ = Enum.TryParse<EquipmentDataType>(row.GetCell(5).StringCellValue, out var dataType);
var equipmentProp = new EquipmentProp
{
EquipmentId = (int)row.GetCell(0).NumericCellValue,
Code = row.GetCell(1).StringCellValue,
Name = row.GetCell(2).StringCellValue,
Address = row.GetCell(3).StringCellValue,
PropType = propType,
DataType = dataType,
MonitorCompareValue = row.GetCell(6)?.BooleanCellValue.ToString() ?? string.Empty,
MonitorFailure = row.GetCell(7)?.StringCellValue ?? string.Empty,
};
var equipment = equipments.Where(x => x.Id == equipmentProp.EquipmentId).FirstOrDefault();
if (equipment == null)
{
return BllResultFactory.Error<List<Equipment>>($"设备属性{equipmentProp.Code}未绑定设备！文件路径：“{filePath}”，行号：{i}");
}
equipment.EquipmentProps.Add(equipmentProp);
}
return BllResultFactory.Success(equipments);
}
catch (Exception ex)
{
return BllResultFactory.Error<List<Equipment>>(ex.Message);
}
}
}
}