尝试做一个.NET简单、高效、避免OOM的Excel工具 (2)

推荐使用 Stream.Query 效率会相对较好。

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>();

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); } 查询数据以第一行数据当Key [Try it]

note : 同名以右边数据为准

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); } Query 查询支援延迟加载(Deferred Execution)，能配合LINQ First/Take/Skip办到低消耗、高效率复杂查询

Query First

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); } 建立 Excel 文件 [Try it]

必须是 non-abstract 类别有 public parameterless constructor

MiniExcel SaveAs 支援 IEnumerable参数``延迟查询，除非必要请不要使用 ToList 等方法读取全部数据到内存

图片 : 是否呼叫 ToList 的内存差别

Anonymous or strongly type:

var path = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.xlsx"); MiniExcel.SaveAs(path, new[] { new { Column1 = "MiniExcel", Column2 = 1 }, new { Column1 = "Github", Column2 = 2} });

Datatable:

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);

Dapper:

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); }

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  
SaveAs 支援 Stream [Try it] using (var stream = File.Create(path)) { stream.SaveAs(values); } Excel Column Name/Ignore Attribute

e.g

input excel :

Test1 Test2 Test3 Test4 Test5 Test6 Column1 Column2
Test1   Test2   Test3   Test4   Test5   Test6   Column1   Column2  
public class ExcelAttributeDemo { [ExcelColumnName("Column1")] public string Test1 { get; set; } [ExcelColumnName("Column2")] public string Test2 { get; set; } [ExcelIgnore] public string Test3 { get; set; } public string Test4 { get; set; } public string Test5 { get; } public string Test6 { get; private 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("Test4", rows[0].Test4); Assert.Null(rows[0].Test5); Assert.Null(rows[0].Test6); 例子 : SQLite & Dapper 读取大数据新增到数据库

note : 请不要呼叫 call ToList/ToArray 等方法，这会将所有数据读到内存内

using (var connection = new SQLiteConnection(connectionString)) { connection.Open(); using (var transaction = connection.BeginTransaction()) using (var stream = File.OpenRead(path)) { var rows = stream.Query(); foreach (var row in rows) connection.Execute("insert into T (A,B) values (@A,@B)", new { row.A, row.B }, transaction: transaction); transaction.Commit(); } }