Efficient Excel Solutions for .NET MiniExcel

dotnet add package MiniExcel --version 1.34.0

1、Query query Excel return strong type 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 = <UserAccount>(path);  

2, Query query Excel return Dynamic IEnumerable data

The Key system is preset to A,B,C,D.... .Z

Github

var rows = (path).ToList();

// or
using (var stream = (path))
{
    var rows = ().ToList();

    ("MiniExcel", rows[0].A);
    (1, rows[0].B);
    ("Github", rows[1].A);
    (2, rows[1].B);
}

3, query data to the first line of data as Key

Note: Names are based on the data on the right

Input Excel :

var rows = (useHeaderRow:true).ToList();

// or

using (var stream = (path))
{
    var rows = (useHeaderRow:true).ToList();

    ("MiniExcel", rows[0].Column1);
    (1, rows[0].Column2);
    ("Github", rows[1].Column1);
    (2, rows[1].Column2);
}

4, Query query support for delayed loading (Deferred Execution), with LINQ First/Take/Skip to do low-consumption, high-efficiency complex queries.

Example: Querying the first data

var row = (path).First();
("HelloWorld", );
// or
using (var stream = (path))
{
    var row = ().First();
    ("HelloWorld", );
}

5、Query the specified Sheet name

(path, sheetName: "SheetName");
//or
(sheetName: "SheetName");

6、Query all Sheet name and data

var sheetNames = (path);
foreach (var sheetName in sheetNames)
{
    var rows = (path, sheetName: sheetName);
}

7. Query all columns (columns)

var columns = (path); //  result : ["A","B"...]

or

var columns = (path, useHeaderRow: true);
//  result : ["excel sheet actual column names", "excel sheet actual column names"...]

var cnt = ;  // get column count

8, Dynamic Query into IDictionary<string,object> data

foreach(IDictionary<string,object> row in (path))
{
    //..
}
// or
var rows = (path).Cast<IDictionary<string,object>>();
// or query the specified range (capitalization is required for the query to work)
// A2 (top left) represents the second row of column A, C3 (bottom right) represents the third row of column C
// If you don't want to limit lines, don't include numbers
var rows = (path, startCell: "A2", endCell: "C3").Cast<IDictionary<string, object>>();

9, Query read Excel return DataTable

Reminder : Not recommended because DataTable will load all data into memory and lose MiniExcel's low memory consumption feature.

var table = (path, useHeaderRow: true);

10, the specified cell to start reading data

(path,useHeaderRow:true,startCell:"B3")

11, merged cell filling

Note: Efficiency is worse than if no merge padding is used.

Underlying reason: The OpenXml standard puts mergeCells at the bottom of the file, making it necessary to traverse sheetxml twice.

var config = new OpenXmlConfiguration()
{
    FillMergedCells = true
};
var rows = (path, configuration: config);

12, read large files hard disk cache (Disk-Base Cache - SharedString)

Concept : MiniExcel when judging the file SharedString size more than 5MB, the preset will use the local cache, such as (one million data), read without turning on the local cache requires the highest memory usage of about 195MB, after turning on the drop to 65MB.

But pay special attention, this optimization is time for memory reduction, so read efficiency will be slower, this example read time from 7.4 seconds to 27.2 seconds, if you do not need to use the following code to disable hard disk cache

var config = new OpenXmlConfiguration { EnableSharedStringCache = false };
(path,configuration: config)

You can also use SharedStringCacheSize to adjust the size of the sharedString file to be cached only if it exceeds a specified size.

var config = new OpenXmlConfiguration { SharedStringCacheSize=500*1024*1024 };
(path, configuration: config);

Write/Export Excel

Must be a non-abstract class with a public parameterless constructor.

MiniExcel SaveAs supports IEnumerable parameter deferred query, please don't use ToList and other methods to read all data into memory unless necessary.

The memory difference between calling ToList and not calling ToList is shown in the following figure:

1、Support set & lt; anonymous category & gt; or & lt; strong type & gt;

var path = ((), $"{()}.xlsx");  
(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 } }  
};  
(path, values); 

3、IDataReader

Recommended to avoid loading all data into memory (path, reader).

Recommended DataReader multi-table export method (recommended to use Dapper ExecuteReader )

using (var cnn = Connection)
{
    ();
    var sheets = new Dictionary<string,object>();
    ("sheet1", ("select 1 id"));
    ("sheet2", ("select 2 id"));
    ("", sheets);
}

4、Datatable

Not recommended, loads all data into memory.

Prioritize the use of Caption as the field name

var path = ((), $"{()}.xlsx");
var table = new DataTable();
{
    ("Column1", typeof(string));
    ("Column2", typeof(decimal));
    ("MiniExcel", 1);
    ("Github", 2);
}

(path, table);

5、Dapper Query

6, SaveAs support Stream, generating documents do not fall to the ground

7, create multiple worksheets (Sheet)

8, table style selection

9. AutoFilter Filter

10、Picture generation

11、Byte Array File Export

12, vertically merge the same cell

13, whether to write null values cell

Template Filling Excel

1、Basic Filling

2, IEnumerable data filling

3. Complex data filling

4. Comparison of the efficiency of big data filling

5、Cell value automatic category correspondence

6. Example: Listing Github Projects