废话不多说,直接上代码
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 }
这里缺少了Excel导出生成,后续我会发的