C# Excel开源操作库MiniExcel使用教程
简介
MiniExcel简单、高效避免OOM的.NET处理Excel查、写、填充数据工具。
目前主流框架大多需要将数据全载入到内存方便操作，但这会导致内存消耗问题，MiniExcel 尝试以 Stream 角度写底层算法逻辑，能让原本1000多MB占用降低到几MB，避免内存不够情况。
特点
- 低内存耗用，避免OOM、频繁 Full GC 情况
- 支持
即时操作每行数据 - 兼具搭配 LINQ 延迟查询特性，能办到低消耗、快速分页等复杂查询
- 轻量，不需要安装 Microsoft Office、COM+，DLL小于150KB
- 简便操作的 API 风格
快速开始
安装
请查看 NuGet
更新日志
请查看 Release Notes
TODO
请查看 TODO
性能比较、测试
Benchmarks 逻辑可以在 MiniExcel.Benchmarks 查看或是提交 PR，运行指令
dotnet run -p .\benchmarks\MiniExcel.Benchmarks\ -c Release -f netcoreapp3.1 -- -f * --join
最后一次运行规格、结果 :
BenchmarkDotNet=v0.12.1, OS=Windows 10.0.19042
Intel Core i7-7700 CPU 3.60GHz (Kaby Lake), 1 CPU, 8 logical and 4 physical cores
[Host] : .NET Framework 4.8 (4.8.4341.0), X64 RyuJIT
Job-ZYYABG : .NET Framework 4.8 (4.8.4341.0), X64 RyuJIT
IterationCount=3 LaunchCount=3 WarmupCount=3
Benchmark History : Link
导入、查询 Excel 比较
逻辑 : 以 Test1,000,000x10.xlsx 做基准与主流框架做性能测试，总共 1,000,000 行 * 10 列笔 "HelloWorld"，文件大小 23 MB
| Library | Method | 最大内存耗用 | 平均时间 |
|---|---|---|---|
| MiniExcel | 'MiniExcel QueryFirst' | 0.109 MB | 0.0007264 sec |
| ExcelDataReader | 'ExcelDataReader QueryFirst' | 15.24 MB | 10.66421 sec |
| MiniExcel | 'MiniExcel Query' | 17.3 MB | 14.17933 sec |
| ExcelDataReader | 'ExcelDataReader Query' | 17.3 MB | 22.56508 sec |
| Epplus | 'Epplus QueryFirst' | 1,452 MB | 18.19801 sec |
| Epplus | 'Epplus Query' | 1,451 MB | 23.64747 sec |
| OpenXmlSDK | 'OpenXmlSDK Query' | 1,412 MB | 52.00327 sec |
| OpenXmlSDK | 'OpenXmlSDK QueryFirst' | 1,413 MB | 52.34865 sec |
| ClosedXml | 'ClosedXml QueryFirst' | 2,158 MB | 66.18897 sec |
| ClosedXml | 'ClosedXml Query' | 2,184 MB | 191.43412 sec |
导出、创建 Excel 比较
逻辑 : 创建1千万笔 "HelloWorld"
| Library | Method | 最大内存耗用 | 平均时间 |
|---|---|---|---|
| MiniExcel | 'MiniExcel Create Xlsx' | 15 MB | 11.53181 sec |
| Epplus | 'Epplus Create Xlsx' | 1,204 MB | 22.50971 sec |
| OpenXmlSdk | 'OpenXmlSdk Create Xlsx' | 2,621 MB | 42.47399 sec |
| ClosedXml | 'ClosedXml Create Xlsx' | 7,141 MB | 140.93992 sec |
读/导入 Excel
1. Query 查询 Excel 返回强型别 IEnumerable 数据 [Try it]
public class UserAccount
{
public Guid ID { get; set; }
public string Name { get; set; }
public DateTime BoD { get; set; }
public int Age { get; set; }
public bool VIP { get; set; }
public decimal Points { get; set; }
}
var rows = MiniExcel.Query<UserAccount>(path);
// or
using (var stream = File.OpenRead(path))
var rows = stream.Query<UserAccount>();
2. Query 查询 Excel 返回Dynamic IEnumerable 数据 [Try it]
- Key 系统预设为
A,B,C,D...Z
| MiniExcel | 1 |
|---|---|
| Github | 2 |
var rows = MiniExcel.Query(path).ToList();
// or
using (var stream = File.OpenRead(path))
{
var rows = stream.Query().ToList();
Assert.Equal("MiniExcel", rows[0].A);
Assert.Equal(1, rows[0].B);
Assert.Equal("Github", rows[1].A);
Assert.Equal(2, rows[1].B);
}
3. 查询数据以第一行数据当Key [Try it]
注意 : 同名以右边数据为准
Input Excel :
| Column1 | Column2 |
|---|---|
| MiniExcel | 1 |
| Github | 2 |
var rows = MiniExcel.Query(useHeaderRow:true).ToList();
// or
using (var stream = File.OpenRead(path))
{
var rows = stream.Query(useHeaderRow:true).ToList();
Assert.Equal("MiniExcel", rows[0].Column1);
Assert.Equal(1, rows[0].Column2);
Assert.Equal("Github", rows[1].Column1);
Assert.Equal(2, rows[1].Column2);
}
4. Query 查询支援延迟加载(Deferred Execution)，能配合LINQ First/Take/Skip办到低消耗、高效率复杂查询
举例 : 查询第一笔数据
var row = MiniExcel.Query(path).First();
Assert.Equal("HelloWorld", row.A);
// or
using (var stream = File.OpenRead(path))
{
var row = stream.Query().First();
Assert.Equal("HelloWorld", row.A);
}
与其他框架效率比较 :
5. 查询指定 Sheet 名称
MiniExcel.Query(path, sheetName: "SheetName");
//or
stream.Query(sheetName: "SheetName");
6. 查询所有 Sheet 名称跟数据
var sheetNames = MiniExcel.GetSheetNames(path);
foreach (var sheetName in sheetNames)
{
var rows = MiniExcel.Query(path, sheetName: sheetName);
}
7. 查询所有栏(列)
var columns = MiniExcel.GetColumns(path); // e.g result : ["A","B"...]
or
var columns = MiniExcel.GetColumns(path, useHeaderRow: true);
// e.g result : ["excel表实际的列名称","excel表实际的列名称"...]
var cnt = columns.Count; // get column count
8. Dynamic Query 转成 IDictionary<string,object> 数据
foreach(IDictionary<string,object> row in MiniExcel.Query(path))
{
//..
}
// or
var rows = MiniExcel.Query(path).Cast<IDictionary<string,object>>();
9. Query 读 Excel 返回 DataTable
提醒 : 不建议使用，因为DataTable会将数据全载入内存，失去MiniExcel低内存消耗功能。
var table = MiniExcel.QueryAsDataTable(path, useHeaderRow: true);
10. 指定单元格开始读取数据
MiniExcel.Query(path,useHeaderRow:true,startCell:"B3")
11. 合并的单元格填充
注意 : 效率相对于没有使用合并填充来说差
底层原因 : OpenXml 标准将 mergeCells 放在文件最下方，导致需要遍历两次 sheetxml
var config = new OpenXmlConfiguration()
{
FillMergedCells = true
};
var rows = MiniExcel.Query(path, configuration: config);
支持不固定长宽多行列填充
12. 读取大文件硬盘缓存 (Disk-Base Cache - SharedString)
概念 : MiniExcel 当判断文件 SharedString 大小超过 5MB，预设会使用本地缓存，如 10x100000.xlsx(一百万笔数据)，读取不开启本地缓存需要最高内存使用约195MB，开启后降为65MB。但要特别注意，此优化是以时间换取内存减少，所以读取效率会变慢，此例子读取时间从 7.4 秒提高到 27.2 秒，假如不需要能用以下代码关闭硬盘缓存
var config = new OpenXmlConfiguration { EnableSharedStringCache = false };
MiniExcel.Query(path,configuration: config)
也能使用 SharedStringCacheSize 调整 sharedString 文件大小超过指定大小才做硬盘缓存
var config = new OpenXmlConfiguration { SharedStringCacheSize=500*1024*1024 };
MiniExcel.Query(path, configuration: config);
写/导出 Excel
- 必须是非abstract 类别有公开无参数构造函数
- MiniExcel SaveAs 支援
IEnumerable参数延迟查询，除非必要请不要使用 ToList 等方法读取全部数据到内存
图片 : 是否呼叫 ToList 的内存差别
1. 支持集合<匿名类别>或是<强型别> [Try it]
var path = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.xlsx");
MiniExcel.SaveAs(path, new[] {
new { Column1 = "MiniExcel", Column2 = 1 },
new { Column1 = "Github", Column2 = 2}
});
2. IEnumerable<IDictionary<string, object>>
var values = new List<Dictionary<string, object>>()
{
new Dictionary<string,object>{{ "Column1", "MiniExcel" }, { "Column2", 1 } },
new Dictionary<string,object>{{ "Column1", "Github" }, { "Column2", 2 } }
};
MiniExcel.SaveAs(path, values);
output :
| Column1 | Column2 |
|---|---|
| MiniExcel | 1 |
| Github | 2 |
3. IDataReader
- 推荐使用，可以避免载入全部数据到内存
MiniExcel.SaveAs(path, reader);
推荐 DataReader 多表格导出方式(建议使用 Dapper ExecuteReader )
using (var cnn = Connection)
{
cnn.Open();
var sheets = new Dictionary<string,object>();
sheets.Add("sheet1", cnn.ExecuteReader("select 1 id"));
sheets.Add("sheet2", cnn.ExecuteReader("select 2 id"));
MiniExcel.SaveAs("Demo.xlsx", sheets);
}
4. Datatable
- 不推荐使用，会将数据全载入内存
- 优先使用 Caption 当栏位名称
var path = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.xlsx");
var table = new DataTable();
{
table.Columns.Add("Column1", typeof(string));
table.Columns.Add("Column2", typeof(decimal));
table.Rows.Add("MiniExcel", 1);
table.Rows.Add("Github", 2);
}
MiniExcel.SaveAs(path, table);
5. Dapper Query
不推荐使用，会将数据全载入内存，建议使用ExecuteReader
using (var connection = GetConnection(connectionString))
{
var rows = connection.Query(@"select 'MiniExcel' as Column1,1 as Column2 union all select 'Github',2");
MiniExcel.SaveAs(path, rows);
}
6. SaveAs 支持 Stream，生成文件不落地 [Try it]
using (var stream = new MemoryStream()) //支持 FileStream,MemoryStream..等
{
stream.SaveAs(values);
}
像是 API 导出 Excel
public IActionResult DownloadExcel()
{
var values = new[] {
new { Column1 = "MiniExcel", Column2 = 1 },
new { Column1 = "Github", Column2 = 2}
};
var memoryStream = new MemoryStream();
memoryStream.SaveAs(values);
memoryStream.Seek(0, SeekOrigin.Begin);
return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
FileDownloadName = "demo.xlsx"
};
}
7. 创建多个工作表(Sheet)
// 1. Dictionary<string,object>
var users = new[] { new { Name = "Jack", Age = 25 }, new { Name = "Mike", Age = 44 } };
var department = new[] { new { ID = "01", Name = "HR" }, new { ID = "02", Name = "IT" } };
var sheets = new Dictionary<string, object>
{
["users"] = users,
["department"] = department
};
MiniExcel.SaveAs(path, sheets);
// 2. DataSet
var sheets = new DataSet();
sheets.Add(UsersDataTable);
sheets.Add(DepartmentDataTable);
//..
MiniExcel.SaveAs(path, sheets);
8. 表格样式选择
预设样式为
不需要样式
var config = new OpenXmlConfiguration()
{
TableStyles = TableStyles.None
};
MiniExcel.SaveAs(path, value,configuration:config);
9. AutoFilter 筛选
从 0.19.0 支持，可藉由 OpenXmlConfiguration.AutoFilter 设定，预设为True。关闭 AutoFilter 方式 :
MiniExcel.SaveAs(path, value, configuration: new OpenXmlConfiguration() { AutoFilter = false });
10. 图片生成
注意 : 目前此功能不支持避免OOM
var value = new[] {
new { Name="github",Image=File.ReadAllBytes(PathHelper.GetFile("images/github_logo.png"))},
new { Name="google",Image=File.ReadAllBytes(PathHelper.GetFile("images/google_logo.png"))},
new { Name="microsoft",Image=File.ReadAllBytes(PathHelper.GetFile("images/microsoft_logo.png"))},
new { Name="reddit",Image=File.ReadAllBytes(PathHelper.GetFile("images/reddit_logo.png"))},
new { Name="statck_overflow",Image=File.ReadAllBytes(PathHelper.GetFile("images/statck_overflow_logo.png"))},
};
MiniExcel.SaveAs(path, value);
11. Byte Array 文件导出
从 1.22.0 开始，当值类型为 byte[] 系统预设会转成保存文件路径以便导入时转回 byte[]，如不想转换可以将 OpenXmlConfiguration.EnableConvertByteArray 改为 false，能提升系统效率。
12. 垂直合并相同的单元格
只支持 xlsx 格式合并单元格
var mergedFilePath = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid().ToString()}.xlsx");
var path = @"../../../../../samples/xlsx/TestMergeSameCells.xlsx";
MiniExcel.MergeSameCells(mergedFilePath, path);
var memoryStream = new MemoryStream();
var path = @"../../../../../samples/xlsx/TestMergeSameCells.xlsx";
memoryStream.MergeSameCells(path);
合并前后对比
13. 是否写入 null values cell
预设:
DataTable dt = new DataTable();
/* ... */
DataRow dr = dt.NewRow();
dr["Name1"] = "Somebody once";
dr["Name2"] = null;
dr["Name3"] = "told me.";
dt.Rows.Add(dr);
MiniExcel.SaveAs(@"C:\temp\Book1.xlsx", dt);
<x:row r="2">
<x:c r="A2" t ="str" s="2">
<x:v>Somebody once</x:v>
</x:c>
<x:c r="B2" t ="str" s="2">
<x:v></x:v>
</x:c>
<x:c r="C2" t ="str" s="2">
<x:v>told me.</x:v>
</x:c>
</x:row>
设定不写入:
OpenXmlConfiguration configuration = new OpenXmlConfiguration()
{
EnableWriteNullValueCell = false // Default value is true.
};
MiniExcel.SaveAs(@"C:\temp\Book1.xlsx", dt, configuration: configuration);
<x:row r="2">
<x:c r="A2" t ="str" s="2">
<x:v>Somebody once</x:v>
</x:c>
<x:c r="B2" s="2"></x:c>
<x:c r="C2" t ="str" s="2">
<x:v>told me.</x:v>
</x:c>
</x:row>
模板填充 Excel
- 宣告方式类似 Vue 模板
{{变量名称}}, 或是集合渲染{{集合名称.栏位名称}} - 集合渲染支持 IEnumerable/DataTable/DapperRow
1. 基本填充
模板:
最终效果:
代码:
// 1. By POCO
var value = new
{
Name = "Jack",
CreateDate = new DateTime(2021, 01, 01),
VIP = true,
Points = 123
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
// 2. By Dictionary
var value = new Dictionary<string, object>()
{
["Name"] = "Jack",
["CreateDate"] = new DateTime(2021, 01, 01),
["VIP"] = true,
["Points"] = 123
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
2. IEnumerable 数据填充
Note1: 同行从左往右以第一个 IEnumerableUse 当列表来源 (不支持同列多集合)
模板:
最终效果:
代码:
//1. By POCO
var value = new
{
employees = new[] {
new {name="Jack",department="HR"},
new {name="Lisa",department="HR"},
new {name="John",department="HR"},
new {name="Mike",department="IT"},
new {name="Neo",department="IT"},
new {name="Loan",department="IT"}
}
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
//2. By Dictionary
var value = new Dictionary<string, object>()
{
["employees"] = new[] {
new {name="Jack",department="HR"},
new {name="Lisa",department="HR"},
new {name="John",department="HR"},
new {name="Mike",department="IT"},
new {name="Neo",department="IT"},
new {name="Loan",department="IT"}
}
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
3. 复杂数据填充
Note: 支持多 sheet 填充,并共用同一组参数
模板:
最终效果:
代码:
// 1. By POCO
var value = new
{
title = "FooCompany",
managers = new[] {
new {name="Jack",department="HR"},
new {name="Loan",department="IT"}
},
employees = new[] {
new {name="Wade",department="HR"},
new {name="Felix",department="HR"},
new {name="Eric",department="IT"},
new {name="Keaton",department="IT"}
}
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
// 2. By Dictionary
var value = new Dictionary<string, object>()
{
["title"] = "FooCompany",
["managers"] = new[] {
new {name="Jack",department="HR"},
new {name="Loan",department="IT"}
},
["employees"] = new[] {
new {name="Wade",department="HR"},
new {name="Felix",department="HR"},
new {name="Eric",department="IT"},
new {name="Keaton",department="IT"}
}
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
4. 大数据填充效率比较
NOTE: 在 MiniExcel 使用 IEnumerable 延迟 ( 不ToList ) 可以节省内存使用
5. Cell 值自动类别对应
模板
最终效果
类别
public class Poco
{
public string @string { get; set; }
public int? @int { get; set; }
public decimal? @decimal { get; set; }
public double? @double { get; set; }
public DateTime? datetime { get; set; }
public bool? @bool { get; set; }
public Guid? Guid { get; set; }
}
代码
var poco = new TestIEnumerableTypePoco { @string = "string", @int = 123, @decimal = decimal.Parse("123.45"), @double = (double)123.33, @datetime = new DateTime(2021, 4, 1), @bool = true, @Guid = Guid.NewGuid() };
var value = new
{
Ts = new[] {
poco,
new TestIEnumerableTypePoco{},
null,
poco
}
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
6. Example : 列出 Github 专案
模板
最终效果
代码
var projects = new[]
{
new {Name = "MiniExcel",Link="https://github.com/shps951023/MiniExcel",Star=146, CreateTime=new DateTime(2021,03,01)},
new {Name = "HtmlTableHelper",Link="https://github.com/shps951023/HtmlTableHelper",Star=16, CreateTime=new DateTime(2020,02,01)},
new {Name = "PocoClassGenerator",Link="https://github.com/shps951023/PocoClassGenerator",Star=16, CreateTime=new DateTime(2019,03,17)}
};
var value = new
{
User = "ITWeiHan",
Projects = projects,
TotalStar = projects.Sum(s => s.Star)
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
7. 分组数据填充
var value = new Dictionary<string, object>()
{
["employees"] = new[] {
new {name="Jack",department="HR"},
new {name="Jack",department="HR"},
new {name="John",department="HR"},
new {name="John",department="IT"},
new {name="Neo",department="IT"},
new {name="Loan",department="IT"}
}
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
1. 使用@group tag 和 @header` tag
Before
After
2. 使用 @group tag 没有 @header tag
Before
After
3. 没有 @group tag
Before
After
8. DataTable 当参数
var managers = new DataTable();
{
managers.Columns.Add("name");
managers.Columns.Add("department");
managers.Rows.Add("Jack", "HR");
managers.Rows.Add("Loan", "IT");
}
var value = new Dictionary<string, object>()
{
["title"] = "FooCompany",
["managers"] = managers,
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
9. 其他
1. 检查模版参数
从 V1.24.0 版本开始，预设忽略模版不存在的参数Key，IgnoreTemplateParameterMissing 可以决定是否抛出错误
var config = new OpenXmlConfiguration()
{
IgnoreTemplateParameterMissing = false,
};
MiniExcel.SaveAsByTemplate(path, templatePath, value, config)
Excel 列属性 (Excel Column Attribute)
1. 指定列名称、指定第几列、是否忽略该列
Excel例子
代码
public class ExcelAttributeDemo
{
[ExcelColumnName("Column1")]
public string Test1 { get; set; }
[ExcelColumnName("Column2")]
public string Test2 { get; set; }
[ExcelIgnore]
public string Test3 { get; set; }
[ExcelColumnIndex("I")] // 系统会自动转换"I"为第8列
public string Test4 { get; set; }
public string Test5 { get; } //系统会忽略此列
public string Test6 { get; private set; } //set非公开,系统会忽略
[ExcelColumnIndex(3)] // 从0开始索引
public string Test7 { get; set; }
}
var rows = MiniExcel.Query<ExcelAttributeDemo>(path).ToList();
Assert.Equal("Column1", rows[0].Test1);
Assert.Equal("Column2", rows[0].Test2);
Assert.Null(rows[0].Test3);
Assert.Equal("Test7", rows[0].Test4);
Assert.Null(rows[0].Test5);
Assert.Null(rows[0].Test6);
Assert.Equal("Test4", rows[0].Test7);
2. 自定义Format格式 (ExcelFormatAttribute)
從 V0.21.0 開始支持有 ToString(string content) 的類別 format
类别
public class Dto
{
public string Name { get; set; }
[ExcelFormat("MMMM dd, yyyy")]
public DateTime InDate { get; set; }
}
代码
var value = new Dto[] {
new Issue241Dto{ Name="Jack",InDate=new DateTime(2021,01,04)},
new Issue241Dto{ Name="Henry",InDate=new DateTime(2020,04,05)},
};
MiniExcel.SaveAs(path, value);
效果
Query 支持自定义格式转换
3. 指定列宽(ExcelColumnWidthAttribute)
public class Dto
{
[ExcelColumnWidth(20)]
public int ID { get; set; }
[ExcelColumnWidth(15.50)]
public string Name { get; set; }
}
4. 多列名对应同一属性
public class Dto
{
[ExcelColumnName(excelColumnName:"EmployeeNo",aliases:new[] { "EmpNo","No" })]
public string Empno { get; set; }
public string Name { get; set; }
}
5. System.ComponentModel.DisplayNameAttribute = ExcelColumnName.excelColumnNameAttribute
从 1.24.0 开始支持 System.ComponentModel.DisplayNameAttribute 等同于 ExcelColumnName.excelColumnNameAttribute 效果
public class TestIssueI4TXGTDto
{
public int ID { get; set; }
public string Name { get; set; }
[DisplayName("Specification")]
public string Spc { get; set; }
[DisplayName("Unit Price")]
public decimal Up { get; set; }
}
6. ExcelColumnAttribute
从 1.26.0 版本开始，可以简化多Attribute写法
public class TestIssueI4ZYUUDto
{
[ExcelColumn(Name = "ID",Index =0)]
public string MyProperty { get; set; }
[ExcelColumn(Name = "CreateDate", Index = 1,Format ="yyyy-MM",Width =100)]
public DateTime MyProperty2 { get; set; }
}
7. DynamicColumnAttribute 动态设定 Column
从 1.26.0 版本开始，可以动态设定 Column 的属性
var config = new OpenXmlConfiguration
{
DynamicColumns = new DynamicExcelColumn[] {
new DynamicExcelColumn("id"){Ignore=true},
new DynamicExcelColumn("name"){Index=1,Width=10},
new DynamicExcelColumn("createdate"){Index=0,Format="yyyy-MM-dd",Width=15},
new DynamicExcelColumn("point"){Index=2,Name="Account Point"},
}
};
var path = PathHelper.GetTempPath();
var value = new[] { new { id = 1, name = "Jack", createdate = new DateTime(2022, 04, 12) ,point = 123.456} };
MiniExcel.SaveAs(path, value, configuration: config);
新增、删除、修改
新增
v1.28.0 开始支持 CSV 插入新增，在最后一行新增N笔数据
// 原始数据
{
var value = new[] {
new { ID=1,Name ="Jack",InDate=new DateTime(2021,01,03)},
new { ID=2,Name ="Henry",InDate=new DateTime(2020,05,03)},
};
MiniExcel.SaveAs(path, value);
}
// 最后一行新增一行数据
{
var value = new { ID=3,Name = "Mike", InDate = new DateTime(2021, 04, 23) };
MiniExcel.Insert(path, value);
}
// 最后一行新增N行数据
{
var value = new[] {
new { ID=4,Name ="Frank",InDate=new DateTime(2021,06,07)},
new { ID=5,Name ="Gloria",InDate=new DateTime(2022,05,03)},
};
MiniExcel.Insert(path, value);
}
删除(未完成)
修改(未完成)
Excel 类别自动判断
- MiniExcel 预设会根据
文件扩展名判断是 xlsx 还是 csv，但会有失准时候，请自行指定。 - Stream 类别无法判断来源于哪种 excel 请自行指定
stream.SaveAs(excelType:ExcelType.CSV);
//or
stream.SaveAs(excelType:ExcelType.XLSX);
//or
stream.Query(excelType:ExcelType.CSV);
//or
stream.Query(excelType:ExcelType.XLSX);
CSV
概念
- 预设全以字串类型返回，预设不会转换为数字或者日期，除非有强型别定义泛型
自定分隔符
预设以 , 作为分隔符，自定义请修改 Seperator 属性
var config = new MiniExcelLibs.Csv.CsvConfiguration()
{
Seperator=';'
};
MiniExcel.SaveAs(path, values,configuration: config);
自定义换行符
预设以 \r\n 作为换行符，自定义请修改 NewLine 属性
var config = new MiniExcelLibs.Csv.CsvConfiguration()
{
NewLine='\n'
};
MiniExcel.SaveAs(path, values,configuration: config);
在 V1.30.1 版本开始支持动态更换换行符 (thanks @hyzx86)
var config = new CsvConfiguration()
{
SplitFn = (row) => Regex.Split(row, $"[\t,](?=(?:[^\"]|\"[^\"]*\")*$)")
.Select(s => Regex.Replace(s.Replace("\"\"", "\""), "^\"|\"$", "")).ToArray()
};
var rows = MiniExcel.Query(path, configuration: config).ToList();
自定义编码
- 预设编码为「从Byte顺序标记检测编码」(detectEncodingFromByteOrderMarks: true)
- 有自定义编码需求，请修改 StreamReaderFunc / StreamWriterFunc 属性
// Read
var config = new MiniExcelLibs.Csv.CsvConfiguration()
{
StreamReaderFunc = (stream) => new StreamReader(stream,Encoding.GetEncoding("gb2312"))
};
var rows = MiniExcel.Query(path, true,excelType:ExcelType.CSV,configuration: config);
// Write
var config = new MiniExcelLibs.Csv.CsvConfiguration()
{
StreamWriterFunc = (stream) => new StreamWriter(stream, Encoding.GetEncoding("gb2312"))
};
MiniExcel.SaveAs(path, value,excelType:ExcelType.CSV, configuration: config);
DataReader
1. GetReader
从 1.23.0 版本开始能获取 DataReader
using (var reader = MiniExcel.GetReader(path,true))
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
var value = reader.GetValue(i);
}
}
}
异步 Async
- 从 v0.17.0 版本开始支持异步 (感谢isdaniel ( SHIH,BING-SIOU))
public static Task SaveAsAsync(string path, object value, bool printHeader = true, string sheetName = "Sheet1", ExcelType excelType = ExcelType.UNKNOWN, IConfiguration configuration = null)
public static Task SaveAsAsync(this Stream stream, object value, bool printHeader = true, string sheetName = "Sheet1", ExcelType excelType = ExcelType.XLSX, IConfiguration configuration = null)
public static Task<IEnumerable<dynamic>> QueryAsync(string path, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null)
public static Task<IEnumerable<T>> QueryAsync<T>(this Stream stream, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) where T : class, new()
public static Task<IEnumerable<T>> QueryAsync<T>(string path, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) where T : class, new()
public static Task<IEnumerable<IDictionary<string, object>>> QueryAsync(this Stream stream, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell