[.Net] - 从Excel(.csv / .xlsx)文件中读取记录到 DataTable
从 .xlsx 文件中读取记录
从 .csv 文件中读取记录
Excel Helper
using ExcelDataReader; using OfficeOpenXml; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Reflection; internal class ExcelHelper { internal static DataTable GetDataTableFromCsv(string path) { var stream = File.Open(path, FileMode.Open, FileAccess.Read); var ds = ExcelReaderFactory.CreateCsvReader(stream).AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration() { UseHeaderRow = true } }); if (ds.Tables.Count == 0) return null; var dt = ds.Tables[0]; foreach (DataColumn col in dt.Columns) { if (string.IsNullOrEmpty(col.ColumnName)) continue; col.ColumnName = col.ColumnName.Replace(" ", ""); } return dt; } internal static DataTable GetDataTableFromExcel(string path, bool hasHeader = true) { using (var pck = new ExcelPackage()) { using (var stream = File.OpenRead(path)) { pck.Load(stream); } var ws = pck.Workbook.Worksheets.First(); DataTable tbl = new DataTable(); foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { var columnName = string.Empty; if (hasHeader) { columnName = string.IsNullOrEmpty(firstRowCell.Text) ? string.Empty : firstRowCell.Text.Replace(" ", ""); } tbl.Columns.Add(hasHeader ? columnName : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; DataRow row = tbl.Rows.Add(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } } return tbl; } } internal static DataTable ToDataTable<T>(List<T> items) { DataTable dataTable = new DataTable(typeof(T).Name); PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance); foreach (PropertyInfo prop in Props) { var type = prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType; dataTable.Columns.Add(prop.Name, type); } foreach (T item in items) { var values = new object[Props.Length]; for (int i = 0; i < Props.Length; i++) { values[i] = Props[i].GetValue(item, null); } dataTable.Rows.Add(values); } return dataTable; } internal static List<T> ConvertDataTable<T>(DataTable dt) { List<T> data = new List<T>(); foreach (DataRow row in dt.Rows) { T item = GetItem<T>(row); data.Add(item); } return data; } internal static T GetItem<T>(DataRow dr) { Type temp = typeof(T); T obj = Activator.CreateInstance<T>(); foreach (DataColumn column in dr.Table.Columns) { foreach (PropertyInfo pro in temp.GetProperties()) { if (pro.Name == column.ColumnName) pro.SetValue(obj, dr[column.ColumnName], null); else continue; } } return obj; } }
参考资料
https://www.nuget.org/packages/ExcelDataReader
https://www.nuget.org/packages/ExcelDataReader.DataSet
https://www.nuget.org/packages/EPPlus
https://stackoverflow.com/a/13396787
https://www.cnblogs.com/holyson/p/3690831.html
https://stackoverflow.com/a/55024803
https://stackoverflow.com/a/13698809
https://forums.asp.net/post/6134316.aspx
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· 单线程的Redis速度为什么快?
· 展开说说关于C#中ORM框架的用法!
· Pantheons:用 TypeScript 打造主流大模型对话的一站式集成库