---
### 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).
### Features
- Low memory consumption, avoid OOM (out of memory) and full GC
- Support `real-time` operation of each row of data
- Support LINQ deferred execution, it can do low-consumption, fast paging and other complex queries
- Lightweight, without Microsoft Office installed, no COM+, DLL size is less than 150KB
- Easy API style to read/write/fill excel
### Get Started
- [Import/Query Excel](#getstart1)
- [Export/Create Excel](#getstart2)
- [Excel Template](#getstart3)
- [Excel Column Name/Index/Ignore Attribute](#getstart4)
- [Examples](#getstart5)
### Installation
You can install the package [from NuGet](https://www.nuget.org/packages/MiniExcel)
### Release Notes
Please Check [Release Notes](docs)
### TODO
Please Check [TODO](https://github.com/shps951023/MiniExcel/projects/1?fullscreen=true)
### Performance
Benchmarks logic can be found in [MiniExcel.Benchmarks](benchmarks/MiniExcel.Benchmarks/Program.cs) , and test cli
```bash
dotnet run -p .\benchmarks\MiniExcel.Benchmarks\ -c Release -f netcoreapp3.1 -- -f * --join
```
Output from the latest run is :
```bash
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
```
Benchmark History : [Link](https://github.com/shps951023/MiniExcel/issues/276)
#### Import/Query Excel
Logic : [**Test1,000,000x10.xlsx**](benchmarks/MiniExcel.Benchmarks/Test1%2C000%2C000x10.xlsx) as performance test basic file, 1,000,000 rows * 10 columns "HelloWorld" cells, 23 MB file size
| Library | Method | Max Memory Usage | Mean |
| ---------------------------- | -------------: | ---------------: | ---------------: |
| MiniExcel | 'MiniExcel QueryFirst' | 0.109 MB | 0.0007264 sec |
| ExcelDataReader | 'ExcelDataReader QueryFirst' | 15.24 MB | 10.66421 sec |
| MiniExcel | 'MiniExcel Query' | 17.3 MB | 14.17933 sec |
| ExcelDataReader | 'ExcelDataReader Query' | 17.3 MB | 22.56508 sec |
| Epplus | 'Epplus QueryFirst' | 1,452 MB | 18.19801 sec |
| Epplus | 'Epplus Query' | 1,451 MB | 23.64747 sec |
| OpenXmlSDK | 'OpenXmlSDK Query' | 1,412 MB | 52.00327 sec |
| OpenXmlSDK | 'OpenXmlSDK QueryFirst' | 1,413 MB | 52.34865 sec |
| ClosedXml | 'ClosedXml QueryFirst' | 2,158 MB | 66.18897 sec |
| ClosedXml | 'ClosedXml Query' | 2,184 MB | 191.43412 sec |
#### Export/Create Excel
Logic : create a total of 10,000,000 "HelloWorld" excel
| Library | Method | Max Memory Usage | Mean |
| ------------------------ | -------------: | ---------------: | -----------: |
| MiniExcel | 'MiniExcel Create Xlsx' | 15 MB | 11.53181 sec |
| Epplus | 'Epplus Create Xlsx' | 1,204 MB | 22.50971 sec |
| OpenXmlSdk | 'OpenXmlSdk Create Xlsx' | 2,621 MB | 42.47399 sec |
| ClosedXml | 'ClosedXml Create Xlsx' | 7,141 MB | 140.93992 sec |
### Excel Query/Import
#### 1. Execute a query and map the results to a strongly typed IEnumerable [[Try it]](https://dotnetfiddle.net/w5WD1J)
Recommand to use Stream.Query because of better efficiency.
```csharp
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);
// or
using (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 |
```csharp
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 : same column name use last right one
Input Excel :
| Column1 | Column2 |
| -------- | -------- |
| MiniExcel | 1 |
| Github | 2 |
```csharp
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
```csharp
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
#### 5. Query by sheet name
```csharp
MiniExcel.Query(path, sheetName: "SheetName");
//or
stream.Query(sheetName: "SheetName");
```
#### 6. Query all sheet name and rows
```csharp
var sheetNames = MiniExcel.GetSheetNames(path);
foreach (var sheetName in sheetNames)
{
var rows = MiniExcel.Query(path, sheetName: sheetName);
}
```
#### 7. Get Columns
```csharp
var columns = MiniExcel.GetColumns(path); // e.g result : ["A","B"...]
var cnt = columns.Count; // get column count
```
#### 8. Dynamic Query cast row to `IDictionary`
```csharp
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 Cells
Note: 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);
```
support variable length and width multi-row and column filling
#### 12. Reading big file by disk-base cache (Disk-Base Cache - SharedString)
If the SharedStrings size exceeds 5 MB, MiniExcel default will use local disk cache, e.g, [10x100000.xlsx](https://github.com/MiniExcel/MiniExcel/files/8403819/NotDuplicateSharedStrings_10x100000.xlsx)(one million rows data), when disable disk cache the maximum memory usage is 195MB, but able disk cache only needs 65MB. Note, this optimization needs some efficiency cost, so this case will increase reading time from 7.4 seconds to 27.2 seconds, If you don't need it that 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=500*1024*1024 };
MiniExcel.Query(path, configuration: config);
```
### Create/Export Excel
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
#### 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 to load all data into memory
```csharp
MiniExcel.SaveAs(path, reader);
```
DataReader export multiple sheets (recommand 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. Datatable
- `Not recommended`, it will load all data into memory
- DataTable use Caption for column name first, then use columname
```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 Query
Thanks @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 memory
```csharp
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 of export excel
```csharp
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 configuration
```csharp
var config = new OpenXmlConfiguration()
{
TableStyles = TableStyles.None
};
MiniExcel.SaveAs(path, value,configuration:config);
```
#### 9. AutoFilter
Since v0.19.0 `OpenXmlConfiguration.AutoFilter` can en/unable AutoFilter , default value is `true`, and setting AutoFilter way:
```csharp
MiniExcel.SaveAs(path, value, configuration: new OpenXmlConfiguration() { AutoFilter = false });
```
#### 10. Create Image
```csharp
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 Export
Since 1.22.0, when value type is `byte[]` then system will save file path at cell by default, and when import system can be converted to `byte[]`. And if you don't want to use it, you can set `OpenXmlConfiguration.EnableConvertByteArray` to `false`, it can improve the system efficiency.
Since 1.22.0, when value type is `byte[]` then system will save file path at cell by default, and when import system can be converted to `byte[]`. And if you don't want to use it, you can set `OpenXmlConfiguration.EnableConvertByteArray` to `false`, it can improve the system efficiency.
#### 12. Merge same cells vertically
This functionality is only supported in `xlsx` format and merges cells vertically between @merge and @endmerge tags.
You can use @mergelimit to limit boundaries of merging cells vertically.
```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 oncetold 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 oncetold 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 the collection rendering `{{collection name.field name}}`
- Collection rendering support 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: Support multi-sheets and using same varible
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
Class
```csharp
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; }
}
```
Code
```csharp
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
Code
```csharp
var projects = new[]
{
new {Name = "MiniExcel",Link="https://github.com/shps951023/MiniExcel",Star=146, CreateTime=new DateTime(2021,03,01)},
new {Name = "HtmlTableHelper",Link="https://github.com/shps951023/HtmlTableHelper",Star=16, CreateTime=new DateTime(2020,02,01)},
new {Name = "PocoClassGenerator",Link="https://github.com/shps951023/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 Fill
```csharp
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:
1. Supports DateTime, Double, Int with ==, !=, >, >=, <, <= operators.
2. Supports String with ==, != operators.
3. Each statement should be new line.
4. Single space should be added before and after operators.
5. There shouldn't be new line inside of statements.
6. 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 parameter
```csharp
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
Code
```csharp
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 support class which contains `ToString(string content)` method format
Class
```csharp
public class Dto
{
public string Name { get; set; }
[ExcelFormat("MMMM dd, yyyy")]
public DateTime InDate { get; set; }
}
```
Code
```csharp
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 1.24.0, system supports System.ComponentModel.DisplayNameAttribute = ExcelColumnName.excelColumnNameAttribute
```C#
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 dynamically
```csharp
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. DynamicSheetAttribute
Since V1.31.4 we can set the attributes of Sheet dynamically. We can set 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 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 support CSV insert N rows data after last row
```csharp
// 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);
}
```
#### 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
#### Note
- Default return `string` type, and value will not be converted to numbers or datetime, unless the type is defined by strong typing generic.
#### Custom separator
The default is `,` as the separator, you can modify the `Seperator` property for customization
```csharp
var config = new MiniExcelLibs.Csv.CsvConfiguration()
{
Seperator=';'
};
MiniExcel.SaveAs(path, values,configuration: config);
```
Since V1.30.1 support function to custom separator (thanks @hyzx86)
```csharp
var config = new CsvConfiguration()
{
SplitFn = (row) => Regex.Split(row, $"[\t,](?=(?:[^\"]|\"[^\"]*\")*$)")
.Select(s => Regex.Replace(s.Replace("\"\"", "\""), "^\"|\"$", "")).ToArray()
};
var rows = MiniExcel.Query(path, configuration: config).ToList();
```
#### Custom line break
The default is `\r\n` as the newline character, 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)
- f 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 behavior
```csharp
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 support Async (thanks 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 support `cancellationToken`。
### Others
#### 1. Enum
Be sure excel & property name same, system will auto mapping (case insensitive)
Since V0.18.0 support Enum Description
```csharp
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 1.30.0 version support excel Description to Enum , thanks @KaneLeung
#### 2. Convert CSV to XLSX or Convert XLSX to CSV
```csharp
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 custom CultureInfo like below, system default `CultureInfo.InvariantCulture`.
```csharp
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
```csharp
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.
```csharp
var config = new OpenXmlConfiguration() { FastMode = true };
MiniExcel.SaveAs(path, reader,configuration:config);
```
### 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
```csharp
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](tests/MiniExcel.Tests.AspNetCore)
```csharp
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
```csharp
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
```csharp
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
Like 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
```csharp
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 ExcelColumnName attribute
#### Q. How to query or export multiple-sheets?
A. `GetSheetNames` method with Query sheetName parameter.
```csharp
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. `GetSheetInformations` method.
```csharp
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 to use Count will load all data into the memory?
No, the image test has 1 million rows*10 columns of data, the maximum memory usage is <60MB, and it takes 13.65 seconds
#### Q. How does Query use integer indexs?
The default index of Query is the string Key: A,B,C.... If you want to change to numeric index, please create the following method to convert
```csharp
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
](https://www.dotnetfoundation.org/)