分享个我自己封装的Datatable拓展 - 我只吃饭不洗碗

废话不多说，直接上代码
  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导出生成，后续我会发的
posted on 2024-02-22 10:43 我只吃饭不洗碗阅读(45) 评论(0) 收藏举报
刷新页面返回顶部