废话不多说，直接上代码
1 public static class DataTableExtensions
2 {
3 /// <summary>
4 /// Determines whether the DataTable is null or empty.
5 /// </summary>
6 /// <param name="dt">The DataTable to check.</param>
7 /// <returns>True if the DataTable is null or empty; otherwise, false.</returns>
8 public static bool TableIsNull(this DataTable dt)
9 {
10 if (dt == null || dt.Rows.Count == 0)
11 return true;
12 return false;
13 }
14
15 /// <summary>
16 /// Gets the value of the DataRow at the specified column index.
17 /// </summary>
18 /// <param name="row">The DataRow to retrieve the value from.</param>
19 /// <param name="columnIndex">The column index.</param>
20 /// <returns>The string representation of the column value, or null if it is empty.</returns>
21 public static string GetValue(this DataRow row, int columnIndex)
22 {
23 try
24 {
25 if (row[columnIndex] == null)
26 {
27 return null;
28 }
29
30 return row[columnIndex].ToString();
31 }
32 catch
33 {
34 return null;
35 }
36 }
37
38 /// <summary>
39 /// Gets the value of the DataRow with the specified column name.
40 /// </summary>
41 /// <param name="row">The DataRow to retrieve the value from.</param>
42 /// <param name="columnName">The column name.</param>
43 /// <returns>The string representation of the column value, or null if it is empty.</returns>
44 public static string GetValue(this DataRow row, string columnName)
45 {
46 try
47 {
48 if (row[columnName] == null)
49 {
50 return null;
51 }
52
53 return row[columnName].ToString();
54 }
55 catch
56 {
57 return null;
58 }
59 }
60
61 /// <summary>
62 /// Gets the value of the first row and first column in the DataTable.
63 /// </summary>
64 /// <param name="dt">The DataTable to retrieve the value from.</param>
65 /// <returns>The string representation of the value in the first row and first column, or null if the DataTable is empty.</returns>
66 public static string FirstOrDefault(this DataTable dt)
67 {
68 return dt.Rows[0].GetValue(0);
69 }
70
71 public static List<string> GetFirstCellValues(this DataTable dt)
72 {
73 List<string> firstCellList = new List<string>();
74
75 if (dt.TableIsNull()) return firstCellList;
76 // Iterate through each row in the DataTable
77 for (int i = 0; i < dt.Rows.Count; i++)
78 {
79 // Get the value of the first cell in the current row and add it to the list
80 firstCellList.Add(dt.Rows[i].GetValue(0));
81 }
82
83 return firstCellList;
84 }
85
86 public static List<string> GetValuesByCellName(this DataTable dt, string cellName)
87 {
88 List<string> firstCellList = new List<string>();
89
90 if (dt.TableIsNull()) return firstCellList;
91 // Iterate through each row in the DataTable
92 for (int i = 0; i < dt.Rows.Count; i++)
93 {
94 // Get the value of the first cell in the current row and add it to the list
95 firstCellList.Add(dt.Rows[i].GetValue(cellName));
96 }
97
98 return firstCellList;
99 }
100
101 /// <summary>
102 /// Converts the DataTable to a list of objects of the specified type.
103 /// </summary>
104 /// <typeparam name="TModel">The target object type.</typeparam>
105 /// <param name="dataTable">The DataTable to convert.</param>
106 /// <returns>The converted list of objects.</returns>
107 public static List<TModel> ToObjectList<TModel>(this DataTable dataTable) where TModel : class, new()
108 {
109 var objectList = new List<TModel>();
110 try
111 {
112 foreach (DataRow row in dataTable.Rows)
113 {
114 TModel obj = Activator.CreateInstance<TModel>();
115
116 foreach (var property in typeof(TModel).GetProperties())
117 {
118 var attribute = property.GetCustomAttribute<DataTableFieldNameAttribute>();
119 var columnName = attribute?.ColumnName ?? property.Name;
120
121 if (dataTable.Columns.Contains(columnName))
122 {
123 var value = row[columnName];
124 if (value != DBNull.Value)
125 {
126 property.SetValue(obj, Convert.ChangeType(value, property.PropertyType));
127 }
128 }
129 }
130
131 objectList.Add(obj);
132 }
133
134 return objectList;
135 }
136 catch (Exception ex)
137 {
138 throw new Exception("Failed to convert DataTable:", ex);
139 }
140 }
141
142 /// <summary>
143 /// Converts a generic collection to a DataTable.
144 /// </summary>
145 /// <typeparam name="T">The type of items in the collection.</typeparam>
146 /// <param name="list">The collection to convert.</param>
147 /// <param name="tableName">The name of the table.</param>
148 /// <returns>The resulting DataTable.</returns>
149 public static DataTable ToDataTable<T>(this IList<T> list, string tableName = null)
150 {
151 var result = new DataTable(tableName);
152
153 if (list.Count == 0)
154 {
155 return result;
156 }
157
158 var properties = typeof(T).GetProperties();
159 result.Columns.AddRange(properties.Select(p =>
160 {
161 var columnType = p.PropertyType;
162 if (columnType.IsGenericType && columnType.GetGenericTypeDefinition() == typeof(Nullable<>))
163 {
164 columnType = Nullable.GetUnderlyingType(columnType);
165 }
166
167 return new DataColumn(p.GetCustomAttribute<DataTableFieldNameAttribute>()?.ColumnName ?? p.Name,
168 columnType);
169 }).ToArray());
170
171 list.ToList().ForEach(item => result.Rows.Add(properties.Select(p => p.GetValue(item)).ToArray()));
172
173 return result;
174 }
175
176 /// <summary>
177 /// Merge DataTables.
178 /// </summary>
179 /// <param name="commandDic">A dictionary containing command names as keys and DataTables as values.</param>
180 /// <returns>The merged DataTable.</returns>
181 public static DataTable MergeDataTables(this Dictionary<string, DataTable> commandDic)
182 {
183 // Create a new DataTable for the merged result
184 DataTable mergedDataTable = new DataTable();
185
186 // Iterate over the dictionary and add a new column for each key
187 foreach (var key in commandDic.Keys)
188 {
189 // Add a new column with the column name as the key in the dictionary
190 mergedDataTable.Columns.Add(key, typeof(string));
191 }
192
193 // Find the DataTable with the maximum number of rows
194 int maxRows = commandDic.Values.Max(dt => dt.Rows.Count);
195
196 // Initialize the rows of the new DataTable
197 for (int i = 0; i < maxRows; i++)
198 {
199 mergedDataTable.Rows.Add(mergedDataTable.NewRow());
200 }
201
202 // Iterate over the dictionary and populate the new DataTable with data
203 foreach (var pair in commandDic)
204 {
205 string columnName = pair.Key;
206 DataTable currentTable = pair.Value;
207
208 for (int i = 0; i < currentTable.Rows.Count; i++)
209 {
210 // Add the row data from the current DataTable to the corresponding column in the new DataTable
211 mergedDataTable.Rows[i][columnName] = currentTable.Rows[i][0];
212 }
213 }
214
215 return mergedDataTable;
216 }
217
218 /// <summary>
219 /// Adds an identity column to the DataTable.
220 /// If the DataTable already contains an 'identityid' column, it returns the DataTable without any modification.
221 /// </summary>
222 /// <param name="dt">The DataTable.</param>
223 /// <param name="columnName">The name of the identity column.</param>
224 /// <returns>The DataTable with the added 'identityid' column.</returns>
225 public static DataTable AddIdentityColumn(this DataTable dt, string columnName = "identityid")
226 {
227 if (!dt.Columns.Contains(columnName))
228 {
229 DataColumn identityColumn = new DataColumn(columnName);
230 dt.Columns.Add(identityColumn);
231
232 for (int i = 0; i < dt.Rows.Count; i++)
233 {
234 dt.Rows[i][columnName] = (i + 1).ToString();
235 }
236
237 dt.Columns[columnName].SetOrdinal(0); // Place the column at the first position
238 }
239
240 return dt;
241 }
242 #region Import Excel file and return DataTable object
243 /// <summary>
244 /// Import an Excel file and return a DataTable object.
245 /// </summary>
246 /// <param name="filePath">The file path of the Excel file.</param>
247 /// <param name="columnValidators">Dictionary of column value validators to apply.</param>
248 /// <returns>The imported DataTable.</returns>
249 /// <remarks>
250 /// Example usage:
251 /// var columnValidators = new Dictionary<string, Func<object, bool>>
252 /// {
253 /// { "Column1", value => Convert.ToInt32(value) < 10 },
254 /// { "Column2", value => Convert.ToDecimal(value) > 0 },
255 /// // Add more columns and validation functions
256 /// };
257 /// var dataTable = ExcelImporter.ImportExcel(filePath, columnValidators);
258 /// </remarks>
259 public static DataTable ImportExcel(string filePath, Dictionary<string, Func<object, bool>> columnValidators = null)
260 {
261 if (!File.Exists(filePath))
262 {
263 throw new Exception("File does not exist!");
264 }
265
266 IWorkbook workbook = filePath.OpenExcel();
267 var worksheet = workbook.GetSheetAt(0);
268 var dataTable = new DataTable();
269
270 // Read header row
271 var headerRow = worksheet.GetRow(0);
272 for (int col = 0; col < headerRow.LastCellNum; col++)
273 {
274 var columnHeader = headerRow.GetCell(col)?.ToString();
275 dataTable.Columns.Add(columnHeader);
276 }
277
278 // Read data rows
279 for (int row = 1; row <= worksheet.LastRowNum; row++)
280 {
281 var dataRow = dataTable.NewRow();
282 var currentRow = worksheet.GetRow(row);
283
284 for (int col = 0; col < currentRow.LastCellNum; col++)
285 {
286 var cell = currentRow.GetCell(col);
287 var cellValue = GetCellValue(cell);
288
289 dataRow[col] = cellValue;
290 }
291
292 if (columnValidators != null)
293 {
294 // Validate values of specific columns
295 foreach (var columnValidator in columnValidators)
296 {
297 var columnName = columnValidator.Key;
298 var validator = columnValidator.Value;
299
300 var columnValue = dataRow[columnName];
301 if (columnValue != null && columnValue != DBNull.Value)
302 {
303 if (!validator(columnValue))
304 {
305 throw new Exception($"Value in column '{columnName}' of row {row + 1} does not meet the requirements.");
306 }
307 }
308 }
309 }
310
311 dataTable.Rows.Add(dataRow);
312 }
313
314 return dataTable;
315 }
316
317 private static object GetCellValue(ICell cell)
318 {
319 if (cell == null)
320 return DBNull.Value;
321
322 switch (cell.CellType)
323 {
324 case CellType.Numeric:
325 if (DateUtil.IsCellDateFormatted(cell))
326 return cell.DateCellValue;
327 else
328 return cell.NumericCellValue;
329
330 case CellType.String:
331 return cell.StringCellValue;
332
333 case CellType.Boolean:
334 return cell.BooleanCellValue;
335
336 case CellType.Formula:
337 return cell.CellFormula;
338
339 default:
340 return DBNull.Value;
341 }
342 }
343 #endregion
344 }
DataTableExtensions
这里缺少了Excel导出生成，后续我会发的
标签：return,value,DataTable,new,封装,dt,分享,Datatable,row From： https://www.cnblogs.com/INetIMVC/p/18026821