ImportOhter.cs
5.58 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
165
166
167
168
169
170
171
172
173
174
175
176
177
using HHECS.Model.ViewEntity;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
namespace Hh.Mes.Service.WebService.Planned
{
/// <summary>
/// 第一步 定义模板名称 和【Excel 页签导入名一致】
/// </summary>
public enum ExcelName
{
equipment,
equipmenttype,
EquipmentTypePart,
equipmentmaintainrule,
equipmentmaintainruledetail
}
/// <summary>
/// 第二步 定义表名
/// tableNamexxxxx ：对应数据库的表名
/// tableNamexxxxxData ：对应数据库的表名 数据源 保存到数据的
/// </summary>
public class TableName : TableNameBase
{
/// <summary>
/// 设备管理 数据库表名
/// </summary>
public static string tableNameEquipment = ExcelName.equipment.ToString();
/// <summary>
///设备管理数据源 sysDicDtSource key
/// </summary>
public static string tableNameDataEquipment = tableNameEquipment + tableSuffix;
/// <summary>
/// 设备管理 excel文件名 用于信息提示
/// </summary>
public static string excelEquipmentChinese = "设备管理";
/// <summary>
/// 设备类型 数据库表名
/// </summary>
public static string tableNameEquipmenttype = ExcelName.equipmenttype.ToString();
/// <summary>
/// 设备类型数据源 sysDicDtSource key
/// </summary>
public static string tableNameDataEquipmenttype= tableNameEquipmenttype + tableSuffix;
/// <summary>
/// 设备类型 excel 文件名 用于信息提示
/// </summary>
public static string excelEquipmenttypeChinese = "设备类型";
/// <summary>
/// 设备类型部件 数据库表名
/// </summary>
public static string tableNameEquipmentTypePart = ExcelName.EquipmentTypePart.ToString();
/// <summary>
/// 设备类型部件数据源 sysDicDtSource key
/// </summary>
public static string tableNameDataEquipmentTypePart = tableNameEquipmentTypePart + tableSuffix;
/// <summary>
/// 设备类型部件 excel文件名 用于信息提示
/// </summary>
public static string excelEquipmentTypePartChinese = "设备类型部件";
/// <summary>
/// 设备规则 数据库表名
/// </summary>
public static string tableNameEquipmentmaintainrule = ExcelName.equipmentmaintainrule.ToString();
/// <summary>
/// 设备规则数据源 sysDicDtSource key
/// </summary>
public static string tableNameDataEquipmentmaintainrule = tableNameEquipmentmaintainrule + tableSuffix;
/// <summary>
/// 设备规则 excel文件名 用于信息提示
/// </summary>
public static string excelEquipmentmaintainruleChinese = "设备规则";
/// <summary>
/// 设备规则明细 数据库表名
/// </summary>
public static string tableNameEquipmentmaintainruledetail = ExcelName.equipmentmaintainruledetail.ToString();
/// <summary>
/// 设备规则明细 数据源
/// </summary>
public static string tableNameDataEquipmentmaintainruledetail = tableNameEquipmentmaintainruledetail + tableSuffix;
/// <summary>
/// 设备规则明细 excel 文件名
/// </summary>
public static string excelEquipmentmaintainruledetailChinese = "设备规则明细";
}
#region 公共类 不需要理会
/// <summary>
/// 数据源 公共方法只处理后缀是_source
/// </summary>
public class TableNameBase
{
/// <summary>
/// 数据源 后缀(_source)
/// </summary>
public static string tableSuffix = "_source";
}
/// <summary>
/// ImportService 帮助类
/// </summary>
public static class ImportOhter
{
/// <summary>
/// 过滤列名
/// </summary>
public static DataTable DtModifyColumnName(this DataTable dt, ref Response response, string ExcelName)
{
//查询更改列名
for (int i = 0; i < dt.Columns.Count; i++)
{
var columnName = Regex.Replace(dt.Columns[i].ColumnName, @"[^a-zA-Z1-9]", "").Trim();
if (string.IsNullOrEmpty(columnName))
{
response.Code = 500;
response.Message = $"{ExcelName} 列【{dt.Columns[i].ColumnName}】不存在英文 ";
return dt;
}
dt.Columns[i].ColumnName = columnName;
}
return dt;
}
/// <summary>
/// 去重方法
/// </summary>
public static DataTable Distinct(this DataTable ds, string columns)
{
DataView dv = new DataView(ds);
DataTable where = dv.ToTable(true, columns);
DataTable data = ds.Clone();
foreach (DataRow item in where.Rows)
{
DataRow[] dataRow = ds.Select($"{columns}='{item[columns]}'");
data.ImportRow(dataRow[0]);
}
return data;
}
/// <summary>
/// 获取dt所有列
/// </summary>
public static List<string> GetColumnsByDataTable(this DataTable dt)
{
var temp = new List<string>();
foreach (DataColumn c in dt.Columns)
{
temp.Add(c.ColumnName);
}
return temp;
}
}
#endregion
}