This project is part of the .NET Foundation and operates under their code of conduct.

Your Stars or Donations can make MiniExcel better

Introduction

MiniExcel is a simple and efficient Excel processing tool for .NET, specifically designed to minimize memory usage.

Currently, most popular frameworks require loading all data from an Excel document into memory for operation, which can lead to memory consumption issues. MiniExcel takes a different approach: it processes data row by row in a streaming fashion, reducing original consumption from potentially hundreds of megabytes to just a few megabytes, effectively preventing out-of-memory (OOM) issues.

flowchart LR
    A1(["Excel analysis
process"]) --> A2{{"Unzipping
XLSX file"}} --> A3{{"Parsing
OpenXML"}} --> A4{{"Model
conversion"}} --> A5(["Output"])
    B1(["Other Excel
Frameworks"]) --> B2{{"Memory"}} --> B3{{"Memory"}} --> B4{{"Workbooks &
Worksheets"}} --> B5(["All rows at
the same time"])
    C1(["MiniExcel"]) --> C2{{"Stream"}} --> C3{{"Stream"}} --> C4{{"POCO or dynamic"}} --> C5(["Deferred execution
row by row"])
    classDef analysis fill:#D0E8FF,stroke:#1E88E5,color:#0D47A1,font-weight:bold;
    classDef others fill:#FCE4EC,stroke:#EC407A,color:#880E4F,font-weight:bold;
    classDef miniexcel fill:#E8F5E9,stroke:#388E3C,color:#1B5E20,font-weight:bold;    class A1,A2,A3,A4,A5 analysis;
    class B1,B2,B3,B4,B5 others;
    class C1,C2,C3,C4,C5 miniexcel;

Features

Minimizes memory usage, preventing out-of-memory (OOM) errors and avoiding full garbage collection
Supports real-time, row-level data operations for improved performance on large datasets
Enables LINQ with deferred execution, providing fast, memory-efficient paging and complex querying
Lightweight, does not require Microsoft Office or COM+ components, and DLL size is under 500KB
Simple and intuitive API style for reading/writing/filling excel files

Version 2.0 preview

We are developing a future version of MiniExcel, featuring a new modular and focused API, separate NuGet packages for Core and Csv functionalities, complete support for asynchronously streamed queries via IAsyncEnumerable, and more features coming soon! The packages will be available in pre-release, so feel free to check them out and provide feedback!

If you do, be sure to also review the new docs and the upgrade notes.

Get Started

Import/Query Excel

Export/Create Excel

Excel Template

Excel Column Name/Index/Ignore Attribute

Examples

Installation

You can install the package from NuGet

Release Notes

Please see Release Notes

TODO

Please Check TODO

Performance

The code for the benchmarks can be found in MiniExcel.Benchmarks.

The file used to test performance is Test1,000,000x10.xlsx, a 32MB document containing 1,000,000 rows * 10 columns whose cells are filled with the string "HelloWorld".

To run all the benchmarks use:

dotnet run -project .\benchmarks\MiniExcel.Benchmarks -c Release -f net9.0 -filter * --join

You can find the benchmarks' results for the latest release here.

Excel Query/Import

#### 1. Execute a query and map the results to a strongly typed IEnumerable [[Try it]](https://dotnetfiddle.net/w5WD1J)

It is recommended to use Stream.Query for 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(path);
// orusing (var stream = File.OpenRead(path))
    var rows = stream.Query();

#### 2. Execute a query and map it to a list of dynamic objects without using head [[Try it]](https://dotnetfiddle.net/w5WD1J)

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

#### 3. Execute a query with first header row [[Try it]](https://dotnetfiddle.net/w5WD1J)

note: if columns have the same name, the last one on the right is used

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

Query First

var row = MiniExcel.Query(path).First();
Assert.Equal("HelloWorld", row.A);
// orusing (var stream = File.OpenRead(path))
{
    var row = stream.Query().First();
    Assert.Equal("HelloWorld", row.A);
}

Performance between MiniExcel/ExcelDataReader/ClosedXML/EPPlus queryfirst

#### 5. Query by sheet name

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

#### 6. Query all sheet names and rows

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

#### 7. Get Columns

var columns = MiniExcel.GetColumns(path); // e.g result : ["A","B"...]var cnt = columns.Count;  // get column count

#### 8. Dynamic Query cast row to IDictionary

foreach(IDictionary row in MiniExcel.Query(path))
{
    //..
}// or
var rows = MiniExcel.Query(path).Cast>();
// or Query specified ranges (capitalized)
// A2 represents the second row of column A, C3 represents the third row of column C
// If you don't want to restrict rows, just don't include numbers
var rows = MiniExcel.QueryRange(path, startCell: "A2", endCell: "C3").Cast>();

#### 9. Query Excel return DataTable

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

``C# var table = MiniExcel.QueryAsDataTable(path, useHeaderRow: true);


#### 10. Specify the cell to start reading data

csharp
MiniExcel.Query(path,useHeaderRow:true,startCell:"B3")
#### 11. Fill Merged CellsNote: The efficiency is slower compared to not using merge fill
Reason: The OpenXml standard puts mergeCells at the bottom of the file, which leads to the need to foreach the sheetxml twice
csharp
    var config = new OpenXmlConfiguration()
    {
        FillMergedCells = true
    };
    var rows = MiniExcel.Query(path, configuration: config);
Supports variable length and width multi-row and multi-column filling
#### 12. Reading large files with disk-based cache (Disk-Base Cache - SharedString)
If the SharedStrings size exceeds 5 MB, MiniExcel will by default use local disk cache, for example, 10x100000.xlsx (one million rows of data). When disk cache is disabled, the maximum memory usage is 195 MB, but with disk cache enabled, it only needs 65 MB. Note, this optimization incurs some efficiency cost, so in this case, reading time will increase from 7.4 seconds to 27.2 seconds. If you do not need this feature, you can disable disk cache with the following code:csharp
var config = new OpenXmlConfiguration { EnableSharedStringCache = false };
MiniExcel.Query(path,configuration: config)
You can use SharedStringCacheSize to change the sharedString file size beyond the specified size for disk caching
csharp
var config = new OpenXmlConfiguration { SharedStringCacheSize=50010241024 };
MiniExcel.Query(path, configuration: config);
Create/Export Excel  
Must be a non-abstract type with a public parameterless constructor.
MiniExcel supports IEnumerable parameter deferred execution. If you want to use the least memory, please do not call methods such as ToList.
e.g.: ToList or not memory usage
#### 1. Anonymous or strongly type [[Try it]](https://dotnetfiddle.net/w5WD1J)
csharp
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>csharp
var values = new List>()
{
    new Dictionary{{ "Column1", "MiniExcel" }, { "Column2", 1 } },
    new Dictionary{{ "Column1", "Github" }, { "Column2", 2 } }
};
MiniExcel.SaveAs(path, values);
Create File Result :
| Column1   | Column2 |
|-----------|---------|
| MiniExcel | 1       |
| Github    | 2       |

#### 3.  IDataReader
Recommended, it can avoid loading all data into memory

csharp
MiniExcel.SaveAs(path, reader);
DataReader exports multiple sheets (recommended by Dapper ExecuteReader)csharp
using (var cnn = Connection)
{
    cnn.Open();
    var sheets = new Dictionary();
    sheets.Add("sheet1", cnn.ExecuteReader("select 1 id"));
    sheets.Add("sheet2", cnn.ExecuteReader("select 2 id"));
    MiniExcel.SaveAs("Demo.xlsx", sheets);
}
#### 4. DatatableNot recommended, it will load all data into memory
DataTable uses Caption for the column name first, then uses the column name
csharp
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 QueryThanks @shaofing #552, please use CommandDefinition + CommandFlags.NoCache
csharp
using (var connection = GetConnection(connectionString))
{
    var rows = connection.Query(
        new CommandDefinition(
            @"select 'MiniExcel' as Column1,1 as Column2 union all select 'Github',2"
            , flags: CommandFlags.NoCache)
        );
    // Note: QueryAsync will throw close connection exception
    MiniExcel.SaveAs(path, rows);
}
Below code will load all data into memorycsharp
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 to MemoryStream  [[Try it]](https://dotnetfiddle.net/JOen0e)
csharp
using (var stream = new MemoryStream()) //support FileStream,MemoryStream ect.
{
    stream.SaveAs(values);
}
e.g.: API for exporting Excelcsharp
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. Create Multiple Sheets
csharp
// 1. Dictionary
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
{
    ["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. TableStyles Options
Default style
Without style configurationcsharp
var config = new OpenXmlConfiguration()
{
     TableStyles = TableStyles.None
};
MiniExcel.SaveAs(path, value,configuration:config);

#### 9. AutoFilterSince v0.19.0, OpenXmlConfiguration.AutoFilter can enable/disable AutoFilter. The default value is true, and the way to set AutoFilter is as follows:
csharp
MiniExcel.SaveAs(path, value, configuration: new OpenXmlConfiguration() { AutoFilter = false });
#### 10. Create Imagecsharp
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 File ExportSince version 1.22.0, when the value type is byte[], the system will, by default, save the file path in the cell. Upon import, the system can convert it back to byte[]. If you do not wish to use this feature, you can set OpenXmlConfiguration.EnableConvertByteArray to false, which can improve system efficiency.
Since version 1.22.0, when the value type is byte[], the system will, by default, save the file path in the cell. Upon import, the system can convert it back to byte[]. If you do not wish to use this feature, you can set OpenXmlConfiguration.EnableConvertByteArray to false, which can improve system efficiency.
#### 12. Merge same cells vertically
This feature is only supported in the xlsx format and merges cells vertically between the @merge and @endmerge tags.
You can use @mergelimit to limit the boundaries of vertically merged cells.
csharp
var mergedFilePath = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid().ToString()}.xlsx");
var path = @"../../../../../samples/xlsx/TestMergeWithTag.xlsx";
MiniExcel.MergeSameCells(mergedFilePath, path);
csharp
var memoryStream = new MemoryStream();
var path = @"../../../../../samples/xlsx/TestMergeWithTag.xlsx";
memoryStream.MergeSameCells(path);
File content before and after merge:
Without merge limit:
With merge limit:
#### 13. Skip null values
New explicit option to write empty cells for null values:csharp
DataTable dt = new DataTable();
/ ... /
DataRow dr = dt.NewRow();
dr["Name1"] = "Somebody once";
dr["Name2"] = null;
dr["Name3"] = "told me.";
dt.Rows.Add(dr);
OpenXmlConfiguration configuration = new OpenXmlConfiguration()
{
     EnableWriteNullValueCell = true // Default value.
};
MiniExcel.SaveAs(@"C:\temp\Book1.xlsx", dt, configuration: configuration);
xml

    
        Somebody once
    
    
    
        told me.
    

Previous behavior:csharp
/ ... /
OpenXmlConfiguration configuration = new OpenXmlConfiguration()
{
     EnableWriteNullValueCell = false // Default value is true.
};
MiniExcel.SaveAs(@"C:\temp\Book1.xlsx", dt, configuration: configuration);
xml

    
        Somebody once
    
    
        
    
    
        told me.
    

Works for null and DBNull values.#### 14. Freeze Panes
csharp
/ ... /
OpenXmlConfiguration configuration = new OpenXmlConfiguration()
{
    FreezeRowCount = 1,     // default is 1
    FreezeColumnCount = 2   // default is 0
};
MiniExcel.SaveAs(@"C:\temp\Book1.xlsx", dt, configuration: configuration);
Fill Data To Excel Template 
The declaration is similar to Vue template {{variable name}}, or collection rendering {{collection name.field name}}

Collection rendering supports IEnumerable/DataTable/DapperRow
#### 1. Basic Fill
Template:
Result:
Code:
csharp
// 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()
{
    ["Name"] = "Jack",
    ["CreateDate"] = new DateTime(2021, 01, 01),
    ["VIP"] = true,
    ["Points"] = 123
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
#### 2. IEnumerable Data Fill
Note1: Use the first IEnumerable of the same column as the basis for filling list
Template:
Result:
Code:
csharp
//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()
{
    ["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. Complex Data Fill
Note: Supports multiple sheets and using the same variable
Template:
Result:
csharp
// 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()
{
    ["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. Fill Big Data Performance
NOTE: Using IEnumerable deferred execution not ToList can save max memory usage in MiniExcel
#### 5. Cell value auto mapping type
Template
Result
Classcsharp
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; }
}
Codecsharp
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 :  List Github Projects
Template

Result
Codecsharp
var projects = new[]
{
    new {Name = "MiniExcel",Link="https://github.com/mini-software/MiniExcel",Star=146, CreateTime=new DateTime(2021,03,01)},
    new {Name = "HtmlTableHelper",Link="https://github.com/mini-software/HtmlTableHelper",Star=16, CreateTime=new DateTime(2020,02,01)},
    new {Name = "PocoClassGenerator",Link="https://github.com/mini-software/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. Grouped Data Fillcsharp
var value = new Dictionary()
{
    ["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"}
    }
};
await MiniExcel.SaveAsByTemplateAsync(path, templatePath, value);
##### 1. With @group tag and with @header tag
Before
After
##### 2. With @group tag and without @header tag
Before
After
##### 3. Without @group tag
Before
After
#### 8. If/ElseIf/Else Statements inside cell
Rules:
Supports DateTime, Double, Int with ==, !=, >, >=, <, <= operators.
Supports String with ==, != operators.
Each statement should be new line.
Single space should be added before and after operators.
There shouldn't be new line inside of statements.
Cell should be in exact format as below.
csharp
@if(name == Jack)
{{employees.name}}
@elseif(name == Neo)
Test {{employees.name}}
@else
{{employees.department}}
@endif
Before
After
#### 9. DataTable as parametercsharp
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()
{
    ["title"] = "FooCompany",
    ["managers"] = managers,
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
#### 10. Formulas##### 1. Example
Prefix your formula with $ and use $enumrowstart and $enumrowend to mark references to the enumerable start and end rows:
When the template is rendered, the $ prefix will be removed and $enumrowstart and $enumrowend will be replaced with the start and end row numbers of the enumerable:
##### 2. Other Example Formulas:
|              |                                                                                           |
|--------------|-------------------------------------------------------------------------------------------|
| Sum          | $=SUM(C{{$enumrowstart}}:C{{$enumrowend}})                                              |
| Alt. Average | $=SUM(C{{$enumrowstart}}:C{{$enumrowend}}) / COUNT(C{{$enumrowstart}}:C{{$enumrowend}}) |
| Range        | $=MAX(C{{$enumrowstart}}:C{{$enumrowend}}) - MIN(C{{$enumrowstart}}:C{{$enumrowend}})   |

#### 11. Other
##### 1. Checking template parameter key
Since V1.24.0 , default ignore template missing parameter key and replace it with empty string, IgnoreTemplateParameterMissing can control throwing exception or not.
csharp
var config = new OpenXmlConfiguration()
{
    IgnoreTemplateParameterMissing = false,
};
MiniExcel.SaveAsByTemplate(path, templatePath, value, config)
Excel Column Name/Index/Ignore Attribute 
#### 1. Specify the column name, column index, column ignore
Excel Example
Codecsharp
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(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. Custom Format (ExcelFormatAttribute)Since V0.21.0, supports classes that contain a ToString(string content) method for formatting
Class
csharp
public class Dto
{
    public string Name { get; set; }
    [ExcelFormat("MMMM dd, yyyy")]
    public DateTime InDate { get; set; }
}
Codecsharp
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);
Result
Query supports custom format conversion
#### 3. Set Column Width(ExcelColumnWidthAttribute)csharp
public class Dto
{
    [ExcelColumnWidth(20)]
    public int ID { get; set; }
    [ExcelColumnWidth(15.50)]
    public string Name { get; set; }
}
#### 4. Multiple column names mapping to the same property.csharp
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
Since version 1.24.0, the system supports System.ComponentModel.DisplayNameAttribute = ExcelColumnName.excelColumnNameAttributeC#
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
Since V1.26.0, multiple attributes can be simplified like:
csharp
        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
Since V1.26.0, we can set the attributes of Column dynamicallycsharp
            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);
#### 8. DynamicSheetAttributeSince V1.31.4, we can set the attributes of a Sheet dynamically. We can set the sheet name and state (visibility).csharp
            var configuration = new OpenXmlConfiguration
            {
                DynamicSheets = new DynamicExcelSheet[] {
                    new DynamicExcelSheet("usersSheet") { Name = "Users", State = SheetState.Visible },
                    new DynamicExcelSheet("departmentSheet") { Name = "Departments", State = SheetState.Hidden }
                }
            };
            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
            {
                ["usersSheet"] = users,
                ["departmentSheet"] = department
            };
            var path = PathHelper.GetTempPath();
            MiniExcel.SaveAs(path, sheets, configuration: configuration);
We can also use the new attribute ExcelSheetAttribute:C#
   [ExcelSheet(Name = "Departments", State = SheetState.Hidden)]
   private class DepartmentDto
   {
      [ExcelColumn(Name = "ID",Index = 0)]
      public string ID { get; set; }
      [ExcelColumn(Name = "Name",Index = 1)]
      public string Name { get; set; }
   }
### Add, Delete, Update
#### Add
v1.28.0 supports inserting N rows of CSV data after the last rowcsharp
// Origin
{
    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);
}
// Insert 1 rows after last
{
    var value = new { ID=3,Name = "Mike", InDate = new DateTime(2021, 04, 23) };
    MiniExcel.Insert(path, value);
}
// Insert N rows after last
{
    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);
}
v1.37.0 supports inserting a new sheet into an existing Excel workbookcsharp
// Origin excel
{
    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, sheetName: "Sheet1");
}
// Insert a new sheet
{
    var value = new { ID=3,Name = "Mike", InDate = new DateTime(2021, 04, 23) };
    MiniExcel.Insert(path, table, sheetName: "Sheet2");
}
#### Delete(waiting)
#### Update(waiting)
Excel Type Auto Check 
MiniExcel will check whether it is xlsx or csv based on the file extension by default, but there may be inaccuracy, please specify it manually.

Stream cannot be know from which excel, please specify it manually.
csharp
stream.SaveAs(excelType:ExcelType.CSV);
//or
stream.SaveAs(excelType:ExcelType.XLSX);
//or
stream.Query(excelType:ExcelType.CSV);
//or
stream.Query(excelType:ExcelType.XLSX);
CSV
#### NoteBy default, it returns the string type, and values will not be converted to numbers or datetime unless the type is explicitly defined by strong typing generics.
#### Custom separatorThe default separator is ,, but you can modify the Seperator property to customize it.
csharp
var config = new MiniExcelLibs.Csv.CsvConfiguration()
{
    Seperator=';'
};
MiniExcel.SaveAs(path, values,configuration: config);
Since V1.30.1, support for custom separators has been added (thanks @hyzx86)csharp
var config = new CsvConfiguration()
{
    SplitFn = (row) => Regex.Split(row, $"\"" target="_blank" rel="noopener noreferrer">\t,$)")
        .Select(s => Regex.Replace(s.Replace("\"\"", "\""), "^\"|\"$", "")).ToArray()
};
var rows = MiniExcel.Query(path, configuration: config).ToList();
#### Custom line breakThe default newline character is \r\n, you can modify the NewLine property for customization
csharp
var config = new MiniExcelLibs.Csv.CsvConfiguration()
{
    NewLine='\n'
};
MiniExcel.SaveAs(path, values,configuration: config);
#### Custom coding
The default encoding is "Detect Encoding From Byte Order Marks"  (detectEncodingFromByteOrderMarks: true)
If you have custom encoding requirements, please modify the StreamReaderFunc / StreamWriterFunc  property
csharp
// 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);
#### Read empty string as null
By default, empty values are mapped to string.Empty. You can modify this behaviorcsharp
var config = new MiniExcelLibs.Csv.CsvConfiguration()
{
   ReadEmptyStringAsNull = true
};
### DataReader
#### 1. GetReader
Since 1.23.0, you can GetDataReader
csharp
    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 supports Async (thanks to isdaniel (SHIH, BING-SIOU)](https://github.com/isdaniel))
csharp
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> QueryAsync(string path, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null)
public static Task> QueryAsync(this Stream stream, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) where T : class, new()
public static Task> QueryAsync(string path, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) where T : class, new()
public static Task>> QueryAsync(this Stream stream, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null)
public static Task SaveAsByTemplateAsync(this Stream stream, string templatePath, object value)
public static Task SaveAsByTemplateAsync(this Stream stream, byte[] templateBytes, object value)
public static Task SaveAsByTemplateAsync(string path, string templatePath, object value)
public static Task SaveAsByTemplateAsync(string path, byte[] templateBytes, object value)
public static Task QueryAsDataTableAsync(string path, bool useHeaderRow = true, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null)
-  v1.25.0 supports cancellationToken.
Others
#### 1. Enum
Be sure the Excel & property name are the same, the system will auto-map (case insensitive)
Enum Description supported since V0.18.0csharp
public class Dto
{
    public string Name { get; set; }
    public I49RYZUserType UserType { get; set; }
}
public enum Type
{
    [Description("General User")]
    V1,
    [Description("General Administrator")]
    V2,
    [Description("Super Administrator")]
    V3
}
Since version 1.30.0, Excel Description to Enum is supported, thanks to @KaneLeung
#### 2. Convert CSV to XLSX or Convert XLSX to CSVcsharp
MiniExcel.ConvertXlsxToCsv(xlsxPath, csvPath);
MiniExcel.ConvertXlsxToCsv(xlsxStream, csvStream);
MiniExcel.ConvertCsvToXlsx(csvPath, xlsxPath);
MiniExcel.ConvertCsvToXlsx(csvStream, xlsxStream);
`csharp
using (var excelStream = new FileStream(path: filePath, FileMode.Open, FileAccess.Read))
using (var csvStream = new MemoryStream())
{
   MiniExcel.ConvertXlsxToCsv(excelStream, csvStream);
}
#### 3. Custom CultureInfo

Since 1.22.0, you can customize CultureInfo as shown below, with the system default being CultureInfo.InvariantCulture.

var config = new CsvConfiguration()
{
    Culture = new CultureInfo("fr-FR"),
};
MiniExcel.SaveAs(path, value, configuration: config);// or
MiniExcel.Query(path, configuration: config);


#### 4. Custom Buffer Size
    public abstract class Configuration : IConfiguration
    {
        public int BufferSize { get; set; } = 1024 * 512;
    }
#### 5. FastMode
System will not control memory, but you can get faster save speed.
var config = new OpenXmlConfiguration() { FastMode = true };
MiniExcel.SaveAs(path, reader,configuration:config);
#### 6. Batch Add Image (MiniExcel.AddPicture)
Please add pictures before batch generating row data, or the system will use a large amount of memory when calling AddPicture.
var images = new[]
{
    new MiniExcelPicture
    {
        ImageBytes = File.ReadAllBytes(PathHelper.GetFile("images/github_logo.png")),
        SheetName = null, // default null is first sheet
        CellAddress = "C3", // required
    },
    new MiniExcelPicture
    {
        ImageBytes = File.ReadAllBytes(PathHelper.GetFile("images/google_logo.png")),
        PictureType = "image/png", // default PictureType = image/png
        SheetName = "Demo",
        CellAddress = "C9", // required
        WidthPx = 100,
        HeightPx = 100,
    },
};
MiniExcel.AddPicture(path, images);
#### 7. Get Sheets Dimension
var dim = MiniExcel.GetSheetDimensions(path);
Examples:

#### 1. 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:
#### 2. ASP.NET Core 3.1 or MVC 5 Download/Upload Excel Xlsx API Demo Try it
public class ApiController : Controller
{
    public IActionResult Index()
    {
        return new ContentResult
        {
            ContentType = "text/html",
            StatusCode = (int)HttpStatusCode.OK,
            Content = @"
DownloadExcel

DownloadExcelFromTemplatePath

DownloadExcelFromTemplateBytes

Upload 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"
        };
    }
    public IActionResult DownloadExcelFromTemplatePath()
    {
        string templatePath = "TestTemplateComplex.xlsx";
        Dictionary value = new Dictionary()
        {
            ["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"}
            }
        };
        MemoryStream memoryStream = new MemoryStream();
        memoryStream.SaveAsByTemplate(templatePath, value);
        memoryStream.Seek(0, SeekOrigin.Begin);
        return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            FileDownloadName = "demo.xlsx"
        };
    }
    private static Dictionary TemplateBytesCache = new Dictionary();
    static ApiController()
    {
        string templatePath = "TestTemplateComplex.xlsx";
        byte[] bytes = System.IO.File.ReadAllBytes(templatePath);
        TemplateBytesCache.Add(templatePath, bytes);
    }
    public IActionResult DownloadExcelFromTemplateBytes()
    {
        byte[] bytes = TemplateBytesCache["TestTemplateComplex.xlsx"];
        Dictionary value = new Dictionary()
        {
            ["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"}
            }
        };
        MemoryStream memoryStream = new MemoryStream();
        memoryStream.SaveAsByTemplate(bytes, value);
        memoryStream.Seek(0, SeekOrigin.Begin);
        return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        {
            FileDownloadName = "demo.xlsx"
        };
    }
    public IActionResult UploadExcel(IFormFile excel)
    {
        var stream = new MemoryStream();
        excel.CopyTo(stream);
        foreach (var item in stream.Query(true))
        {
            // do your logic etc.
        }        return Ok("File uploaded successfully");
    }
}
####  3. Paging Query
void Main()
{
    var rows = MiniExcel.Query(path);
    Console.WriteLine("==== No.1 Page ====");
    Console.WriteLine(Page(rows,pageSize:3,page:1));
    Console.WriteLine("==== No.50 Page ====");
    Console.WriteLine(Page(rows,pageSize:3,page:50));
    Console.WriteLine("==== No.5000 Page ====");
    Console.WriteLine(Page(rows,pageSize:3,page:5000));
}public static IEnumerable Page(IEnumerable en, int pageSize, int page)
{
    return en.Skip(page * pageSize).Take(pageSize);
}
#### 4. WebForm export Excel by memorystream
var fileName = "Demo.xlsx";
var sheetName = "Sheet1";
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.AddHeader("Content-Disposition", $"attachment;filename=\"{fileName}\"");
var values = new[] {
    new { Column1 = "MiniExcel", Column2 = 1 },
    new { Column1 = "Github", Column2 = 2}
};
var memoryStream = new MemoryStream();
memoryStream.SaveAs(values, sheetName: sheetName);
memoryStream.Seek(0, SeekOrigin.Begin);
memoryStream.CopyTo(Response.OutputStream);
response.End();

#### 5. Dynamic i18n Multi-language and Role Authority Management

As in the example, create a method to handle i18n and permission management, and use yield return to return IEnumerable> to achieve dynamic and low-memory processing effects

void Main()
{
    var value = new Order[] {
        new Order(){OrderNo = "SO01",CustomerID="C001",ProductID="P001",Qty=100,Amt=500},
        new Order(){OrderNo = "SO02",CustomerID="C002",ProductID="P002",Qty=300,Amt=400},
    };
    Console.WriteLine("en-Us and Sales role");
    {
        var path = Path.GetTempPath() + Guid.NewGuid() + ".xlsx";
        var lang = "en-US";
        var role = "Sales";
        MiniExcel.SaveAs(path, GetOrders(lang, role, value));
        MiniExcel.Query(path, true).Dump();
    }
    Console.WriteLine("zh-CN and PMC role");
    {
        var path = Path.GetTempPath() + Guid.NewGuid() + ".xlsx";
        var lang = "zh-CN";
        var role = "PMC";
        MiniExcel.SaveAs(path, GetOrders(lang, role, value));
        MiniExcel.Query(path, true).Dump();
    }
}
private IEnumerable> GetOrders(string lang, string role, Order[] orders)
{
    foreach (var order in orders)
    {
        var newOrder = new Dictionary();
        if (lang == "zh-CN")
        {
            newOrder.Add("客户编号", order.CustomerID);
            newOrder.Add("订单编号", order.OrderNo);
            newOrder.Add("产品编号", order.ProductID);
            newOrder.Add("数量", order.Qty);
            if (role == "Sales")
                newOrder.Add("价格", order.Amt);
            yield return newOrder;
        }
        else if (lang == "en-US")
        {
            newOrder.Add("Customer ID", order.CustomerID);
            newOrder.Add("Order No", order.OrderNo);
            newOrder.Add("Product ID", order.ProductID);
            newOrder.Add("Quantity", order.Qty);
            if (role == "Sales")
                newOrder.Add("Amount", order.Amt);
            yield return newOrder;
        }
        else
        {
            throw new InvalidDataException($"lang {lang} wrong");
        }
    }
}public class Order
{
    public string OrderNo { get; set; }
    public string CustomerID { get; set; }
    public decimal Qty { get; set; }
    public string ProductID { get; set; }
    public decimal Amt { get; set; }
}


FAQ
#### Q: Excel header title not equal class property name, how to mapping?
A. Please use the ExcelColumnName attribute
#### Q. How to query or export multiple-sheets?

A. Use the GetSheetNames method with the Query sheetName parameter.

var sheets = MiniExcel.GetSheetNames(path);
foreach (var sheet in sheets)
{
    Console.WriteLine($"sheet name : {sheet} ");
    var rows = MiniExcel.Query(path,useHeaderRow:true,sheetName:sheet);
    Console.WriteLine(rows);
}


#### Q. How to query or export information about sheet visibility?

A. Use the GetSheetInformations method.

var sheets = MiniExcel.GetSheetInformations(path);
foreach (var sheetInfo in sheets)
{
    Console.WriteLine($"sheet index : {sheetInfo.Index} "); // next sheet index - numbered from 0
    Console.WriteLine($"sheet name : {sheetInfo.Name} ");   // sheet name
    Console.WriteLine($"sheet state : {sheetInfo.State} "); // sheet visibility state - visible / hidden
}


#### Q. Whether using Count will load all data into memory?
No, in the image test with 1 million rows * 10 columns of data, the peak memory usage is <60MB, and it takes 13.65 seconds.
#### Q. How does Query use integer indexes?
The default index of Query is the string Key: A,B,C.... If you want to switch to numeric indexes, please create the following method for conversion.
void Main()
{
    var path = @"D:\git\MiniExcel\samples\xlsx\TestTypeMapping.xlsx";
    var rows = MiniExcel.Query(path,true);
    foreach (var r in ConvertToIntIndexRows(rows))
    {
        Console.Write($"column 0 : {r[0]} ,column 1 : {r[1]}");
        Console.WriteLine();
    }
}
private IEnumerable> ConvertToIntIndexRows(IEnumerable

MiniExcel

Introduction

Features

Version 2.0 preview

Get Started

Installation

Release Notes

TODO

Performance

Excel Query/Import

Create/Export Excel

Fill Data To Excel Template

Excel Column Name/Index/Ignore Attribute

Excel Type Auto Check

CSV

Async

Others

Examples:

FAQ

Limitations and caveats

Reference

Thanks

Contribution sharing donate

Contributors