This issue recommends one. NET processing Excel search, write, fill data tool – MiniExcel.

At present, most mainstream frameworks need to load all data into memory for easy operation, but this will lead to memory consumption problems, MiniExcel tries to write the underlying algorithm logic from the Stream Angle, which can reduce the original 1000 MB occupancy to a few MB, to avoid insufficient memory.

peculiarity

• Low memory consumption, avoiding OOM and frequent Full GC
• Supports real-time operation of each row of data
• Combined with LINQ delayed query feature, it can achieve low consumption, fast paging and other complex queries
• Lightweight, do not need to install Microsoft Office, COM+, DLL less than 150KB
• asy to use API style

Performance comparison

• Import and query Excel comparison

Logic: Test1,000,000×10.xlsx benchmark and mainstream framework for performance testing, a total of 1,000,000 rows * 10 columns pen “HelloWorld”, file size 23 MB

• Export and create Excel comparison

Logic: Create 10 million “HelloWorld”

Code example

• Query The query Excel returns the IEnumerable data

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 supports Deferred Execution, which can work with LINQ First/Take/Skip to make complex queries with low consumption and high efficiency

For example, query the first batch of data

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

Efficiency comparison with other frameworks:

• Query reads Excel and returns the DataTable

Note: Not recommended because DataTable loads all data into memory and loses MiniExcel’s low memory consumption.

Note: Not recommended because DataTable loads all data into memory and loses MiniExcel's low memory consumption.提醒 : 不建议使用，因为DataTable会将数据全载入内存，失去MiniExcel低内存消耗功能。

• Specifies the cell to start reading data.

MiniExcel.Query(path,useHeaderRow:true,startCell:"B3")

• Create multiple sheets

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

• IEnumerable Data fill

Note1: Peer from left to right with the first IEnumerableUse as the list source (multiple sets in the same column are not supported)

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

Template:

The end result:

—END—

Open source protocol: Apache2.0