19 Star 195 Fork 22

dotNET China / MiniExcel

Create your Gitee Account
Explore and code with more than 6 million developers,Free private repositories !:)
Sign up
Clone or download
Cancel
Notice: Creating folder will generate an empty file .keep, because not support in Git
Loading...
README.md

NuGet Build status .NET Framework .NET Standard .NET


English / 繁體中文 / 简体中文


Introduction

MiniExcel is simple and efficient to avoid OOM's .NET processing Excel tool.

At present, most popular frameworks need to load all the data into the memory to facilitate operation, but it will cause memory consumption problems. MiniExcel tries to use algorithm from a stream to reduce the original 1000 MB occupation to a few MB to avoid OOM(out of memory).

image

Features

  • Low memory consumption, avoid OOM (out of memory) and full GC
  • Support real-time operation of each row of data miniexcel_lazy_load
  • Support LINQ deferred execution, it can do low-consumption, fast paging and other complex queries queryfirst
  • Lightweight, does not with any third-party dependencies, DLL is less than 100KB
  • Easy Dapper API style

Demo

Installation

You can install the package from NuGet

Release Notes

Please Check Release Notes

Discussions / TODO

Please Check Discussions / TODO

Performance

Test1,000,000x10.xlsx as performance test basic file,A total of 10,000,000 "HelloWorld" with a file size of 23 MB

Benchmarks logic can be found in MiniExcel.Benchmarks , and test cli

dotnet run -p .\benchmarks\MiniExcel.Benchmarks\ -c Release -f netcoreapp3.1 -- -f * --join

Output from the latest run is :

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  
Method Max Memory Usage Mean Gen 0 Gen 1 Gen 2
'MiniExcel QueryFirst' 0.109 MB 726.4 us - - -
'ExcelDataReader QueryFirst' 15.24 MB 10,664,238.2 us 566000.0000 1000.0000 -
'MiniExcel Query' 17.3 MB 14,179,334.8 us 367000.0000 96000.0000 7000.0000
'ExcelDataReader Query' 17.3 MB 22,565,088.7 us 1210000.0000 2000.0000 -
'Epplus QueryFirst' 1,452 MB 18,198,015.4 us 535000.0000 132000.0000 9000.0000
'Epplus Query' 1,451 MB 23,647,471.1 us 1451000.0000 133000.0000 9000.0000
'OpenXmlSDK Query' 1,412 MB 52,003,270.1 us 978000.0000 353000.0000 11000.0000
'OpenXmlSDK QueryFirst' 1,413 MB 52,348,659.1 us 978000.0000 353000.0000 11000.0000
'ClosedXml QueryFirst' 2,158 MB 66,188,979.6 us 2156000.0000 575000.0000 9000.0000
'ClosedXml Query' 2,184 MB 191,434,126.6 us 2165000.0000 577000.0000 10000.0000
Method Max Memory Usage Mean Gen 0 Gen 1 Gen 2
'MiniExcel Create Xlsx' 15 MB 11,531,819.8 us 1020000.0000 - -
'Epplus Create Xlsx' 1,204 MB 22,509,717.7 us 1370000.0000 60000.0000 30000.0000
'OpenXmlSdk Create Xlsx' 2,621 MB 42,473,998.9 us 1370000.0000 460000.0000 50000.0000
'ClosedXml Create Xlsx' 7,141 MB 140,939,928.6 us 5520000.0000 1500000.0000 80000.0000

Execute a query and map the results to a strongly typed IEnumerable [Try it]

Recommand to use Stream.Query because of better efficiency.

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

image

Execute a query and map it to a list of dynamic objects without using head [Try it]

  • dynamic key is A.B.C.D..
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);
}

Execute a query with first header row [Try it]

note : same column name use last right one

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 Support LINQ Extension First/Take/Skip ...etc

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

Performance between MiniExcel/ExcelDataReader/ClosedXML/EPPlus
queryfirst

Create Excel file [Try it]

  1. Must be a non-abstract type with a public parameterless constructor .

  2. MiniExcel support parameter IEnumerable Deferred Execution, If you want to use least memory, please do not call methods such as ToList

e.g : ToList or not memory usage
image

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

Create File Result :

Column1 Column2
MiniExcel 1
Github 2

SaveAs Stream [Try it]

using (var stream = File.Create(path))
{
    stream.SaveAs(values);
}

Excel Column Name/Index/Ignore Attribute

e.g

input excel :

image

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")] // system will convert "I" to 8 index
    public string Test4 { get; set; } 
    public string Test5 { get; } //wihout set will ignore
    public string Test6 { get; private set; } //un-public set will ignore
    [ExcelColumnIndex(3)] // start with 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);

Query by sheet name

MiniExcel.Query(path, sheetName: "SheetName");
//or
stream.Query(sheetName: "SheetName");

Query all sheet name and rows

var sheetNames = MiniExcel.GetSheetNames(path).ToList();
foreach (var sheetName in sheetNames)
{
    var rows = MiniExcel.Query(path, sheetName: sheetName);
}

SQLite & Dapper Large Size File SQL Insert Avoid OOM

note : please don't call ToList/ToArray methods after Query, it'll load all data into memory

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

performance: image

ASP.NET Core 3.1 or MVC 5 Download Excel Xlsx API Demo

public class ExcelController : Controller
{
    public IActionResult Download()
    {
        var values = new[] {
            new { Column1 = "MiniExcel", Column2 = 1 },
            new { Column1 = "Github", Column2 = 2}
        };
        var stream = new MemoryStream();
        stream.SaveAs(values);
        return File(stream,
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            "demo.xlsx");
    }
}

Excel Type Auto Check

Default system will auto check file path or stream is from xlsx or csv, but if you need to specify type, it can use excelType parameter.

stream.SaveAs(excelType:ExcelType.CSV);
//or
stream.SaveAs(excelType:ExcelType.XLSX);
//or
stream.Query(excelType:ExcelType.CSV);
//or
stream.Query(excelType:ExcelType.XLSX);

Dynamic Query cast row to IDictionary<string,object>

foreach(IDictionary<string,object> row in MiniExcel.Query(path))
{
    //..
}

Limitations and caveats

  • Not support xls and encrypted file now

Reference

Contributors :

Comments ( 9 )

Sign in for post a comment

About

MiniExcel 简单、高效避免OOM的.NET处理Excel工具 spread retract
Cancel

Releases (2)

All

Gitee Metrics

Contributors

All

Activities

load more
can not load any more
C#
1
https://gitee.com/dotnetchina/MiniExcel.git
git@gitee.com:dotnetchina/MiniExcel.git
dotnetchina
MiniExcel
MiniExcel
master

Search