当前位置：首页 > backend >正文

C#使用ExcelDataReader高效读取excel文件写入数据库

backend 2025/6/18 19:09:21

分享一个库ExcelDataReader ，它专注读取、支持 .xls/.xlsx、内存优化。

首先安装NuGet 包
dotnet add package ExcelDataReader
dotnet add package System.Text.Encoding.CodePages

编码

内存优化：每次仅读取一行，适合处理百万级数据。
类型安全方法：可用 GetString(0)、GetDouble(1) 等强类型方法（需确保类型匹配）。
多工作表支持：reader.NextResult() 切换工作表

public async Task<dynamic> ImportDataAsync(IFormFile file)
{// 注册编码Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);// 解决 .NET Core 编码问题[1,2,6](@ref)using var stream = new MemoryStream();await file.CopyToAsync(stream);stream.Position = 0;//var reader = ExcelReaderFactory.CreateReader(stream, new ExcelReaderConfiguration//{//	Password = "your-password" // 支持加密文件[4](@ref)//});int importCount = 0;using var reader = ExcelReaderFactory.CreateReader(stream);var batch = new List<B_BasicInformation>();// 跳过表头（假设占1行）if (reader.Read()) { }while (reader.Read()){//流式读取大文件batch.Add(new B_BasicInformation{Name = reader.GetString(0),//可用 GetString(0)、GetDouble(1) 等强类型方法（需确保类型匹配）IdCard = reader.GetString(1),Province = reader.GetString(2),City = reader.GetString(3),Area = reader.GetString(4),Phone = reader.GetConvertString(5),Address = reader.GetString(6),StudyPhase = reader.GetString(7),Grade = reader.GetString(8),Class = reader.GetString(9),School = reader.GetString(10),SchoolCode = reader.GetConvertStringGuid(11),Gender = idCardResult.gender,Birthday = idCardResult.birthday}); if (batch.Count >= 100){//批量插入_repository._Db.Insertable(batch).ExecuteCommand();batch.Clear();}}return new {total=importCount };
}

小文件读取

public DataSet ReadExcelAsDataSet(string filePath)
{using var stream = File.Open(filePath, FileMode.Open, FileAccess.Read);using var reader = ExcelReaderFactory.CreateReader(stream);// 配置：首行作为列名，忽略空行var result = reader.AsDataSet(new ExcelDataSetConfiguration(){ConfigureDataTable = _ => new ExcelDataTableConfiguration(){UseHeaderRow = true, // 第一行为列名[4,7](@ref)FilterRow = row => row[0]?.ToString() != "" // 跳过空行[4](@ref)}});return result;
}// 使用示例：
var dataSet = ReadExcelAsDataSet("data.xlsx");
foreach (DataTable table in dataSet.Tables)
{Console.WriteLine($"表名: {table.TableName}");foreach (DataRow row in table.Rows){Console.WriteLine($"{row["姓名"]}, 年龄: {row["年龄"]}");}
}