weekly

GitHub C# Trending

The latest build: 2024-07-19Source of data: GitHubTrendingRSS

Fast, Low-Memory, Easy Excel .NET helper to import/export/template spreadsheet (support Linux, Mac)


NuGetBuild statusstarGitHub starsversion


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


English | |


Your Star and Donate can make MiniExcel better

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
  • 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

Installation

You can install the package from NuGet

Release Notes

Please Check Release Notes

TODO

Please Check TODO

Performance

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.19042Intel 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 RyuJITIterationCount=3 LaunchCount=3 WarmupCount=3

Benchmark History : Link

Import/Query Excel

Logic : Test1,000,000x10.xlsx as performance test basic file, 1,000,000 rows * 10 columns "HelloWorld" cells, 23 MB file size

LibraryMethodMax Memory UsageMean
MiniExcel'MiniExcel QueryFirst'0.109 MB0.0007264 sec
ExcelDataReader'ExcelDataReader QueryFirst'15.24 MB10.66421 sec
MiniExcel'MiniExcel Query'17.3 MB14.17933 sec
ExcelDataReader'ExcelDataReader Query'17.3 MB22.56508 sec
Epplus'Epplus QueryFirst'1,452 MB18.19801 sec
Epplus'Epplus Query'1,451 MB23.64747 sec
OpenXmlSDK'OpenXmlSDK Query'1,412 MB52.00327 sec
OpenXmlSDK'OpenXmlSDK QueryFirst'1,413 MB52.34865 sec
ClosedXml'ClosedXml QueryFirst'2,158 MB66.18897 sec
ClosedXml'ClosedXml Query'2,184 MB191.43412 sec

Export/Create Excel

Logic : create a total of 10,000,000 "HelloWorld" excel

LibraryMethodMax Memory UsageMean
MiniExcel'MiniExcel Create Xlsx'15 MB11.53181 sec
Epplus'Epplus Create Xlsx'1,204 MB22.50971 sec
OpenXmlSdk'OpenXmlSdk Create Xlsx'2,621 MB42.47399 sec
ClosedXml'ClosedXml Create Xlsx'7,141 MB140.93992 sec

Excel Query/Import

1. 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);// orusing (var stream = File.OpenRead(path)) var rows = stream.Query<UserAccount>();

image

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

  • dynamic key is A.B.C.D..
MiniExcel1
Github2
var rows = MiniExcel.Query(path).ToList();// orusing (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]

note : same column name use last right one

Input Excel :

Column1Column2
MiniExcel1
Github2
var rows = MiniExcel.Query(useHeaderRow:true).ToList();// orusing (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");//orstream.Query(sheetName: "SheetName");

6. Query all sheet name 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<string,object>

foreach(IDictionary<string,object> row in MiniExcel.Query(path)){ //..}// orvar rows = MiniExcel.Query(path).Cast<IDictionary<string,object>>();

9. Query Excel return DataTable

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

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

image

10. Specify the cell to start reading data

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

image

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

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

image

support variable length and width multi-row and column filling

image

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(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:

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

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

image

image

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 image

1. Anonymous or strongly type [Try it]

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<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 :

Column1Column2
MiniExcel1
Github2

3. IDataReader

  • Recommended, it can avoid to load all data into memory
MiniExcel.SaveAs(path, reader);

image

DataReader export multiple sheets (recommand by Dapper ExecuteReader)

using (var cnn = Connection){ cnn.Open(); var sheets = new Dictionary<string,object>(); 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

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

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

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]

using (var stream = new MemoryStream()) //support FileStream,MemoryStream ect.{ stream.SaveAs(values);}

e.g : api of export 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" };}

7. Create Multiple Sheets

// 1. Dictionary<string,object>var users = new[] { new { Name = "Jack", Age = 25 }, new { Name = "Mike", Age = 44 } };var department = new[] { new { ID = "01", Name = "HR" }, new { ID = "02", Name = "IT" } };var sheets = new Dictionary<string, object>{ ["users"] = users, ["department"] = department};MiniExcel.SaveAs(path, sheets);// 2. DataSetvar sheets = new DataSet();sheets.Add(UsersDataTable);sheets.Add(DepartmentDataTable);//..MiniExcel.SaveAs(path, sheets);

image

8. TableStyles Options

Default style

image

Without style configuration

var config = new OpenXmlConfiguration(){ TableStyles = TableStyles.None};MiniExcel.SaveAs(path, value,configuration:config);

image

9. AutoFilter

Since v0.19.0 OpenXmlConfiguration.AutoFilter can en/unable AutoFilter , default value is true, and setting AutoFilter way:

MiniExcel.SaveAs(path, value, configuration: new OpenXmlConfiguration() { AutoFilter = false });

10. Create Image

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

image

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.

image

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.

image

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.

var mergedFilePath = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid().ToString()}.xlsx");var path = @"../../../../../samples/xlsx/TestMergeWithTag.xlsx";MiniExcel.MergeSameCells(mergedFilePath, path);
var memoryStream = new MemoryStream();var path = @"../../../../../samples/xlsx/TestMergeWithTag.xlsx";memoryStream.MergeSameCells(path);

File content before and after merge:

Without merge limit:

Screenshot 2023-08-07 at 11 59 24Screenshot 2023-08-07 at 11 59 57

With merge limit:

Screenshot 2023-08-08 at 18 21 00Screenshot 2023-08-08 at 18 21 40

13. Skip null values

New explicit option to write empty cells for null values:

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

image

<x:row r="2"> <x:c r="A2" t ="str" s="2"> <x:v>Somebody once</x:v> </x:c> <x:c r="B2" s="2"></x:c> <x:c r="C2" t ="str" s="2"> <x:v>told me.</x:v> </x:c></x:row>

Previous behavior:

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

image

<x:row r="2"> <x:c r="A2" t ="str" s="2"> <x:v>Somebody once</x:v> </x:c> <x:c r="B2" t ="str" s="2"> <x:v></x:v> </x:c> <x:c r="C2" t ="str" s="2"> <x:v>told me.</x:v> </x:c></x:row>

Works for null and DBNull values.

14. Freeze Panes

/* ... */OpenXmlConfiguration configuration = new OpenXmlConfiguration(){ FreezeRowCount = 1, // default is 1 FreezeColumnCount = 2 // default is 0};MiniExcel.SaveAs(@"C:\temp\Book1.xlsx", dt, configuration: configuration);

image

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: image

Result: image

Code:

// 1. By POCOvar value = new{ Name = "Jack", CreateDate = new DateTime(2021, 01, 01), VIP = true, Points = 123};MiniExcel.SaveAsByTemplate(path, templatePath, value);// 2. By Dictionaryvar value = new Dictionary<string, object>(){ ["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: image

Result: image

Code:

//1. By POCOvar 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 Dictionaryvar value = new Dictionary<string, object>(){ ["employees"] = new[] { new {name="Jack",department="HR"}, new {name="Lisa",department="HR"}, new {name="John",department="HR"}, new {name="Mike",department="IT"}, new {name="Neo",department="IT"}, new {name="Loan",department="IT"} }};MiniExcel.SaveAsByTemplate(path, templatePath, value);

3. Complex Data Fill

Note: Support multi-sheets and using same varible

Template:

image

Result:

image

// 1. By POCOvar 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 Dictionaryvar value = new Dictionary<string, object>(){ ["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

image

5. Cell value auto mapping type

Template

image

Result

image

Class

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

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

image

Result

image

Code

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

var value = new Dictionary<string, object>(){ ["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

before_with_header

After

after_with_header

2. With @group tag and without @header tag

Before

before_without_header

After

after_without_header

3. Without @group tag

Before

without_group

After

without_group_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.
@if(name == Jack){{employees.name}}@elseif(name == Neo)Test {{employees.name}}@else{{employees.department}}@endif

Before

if_before

After

if_after

9. DataTable as parameter

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<string, object>(){ ["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:

image

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:

image

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.

var config = new OpenXmlConfiguration(){ IgnoreTemplateParameterMissing = false,};MiniExcel.SaveAsByTemplate(path, templatePath, value, config)

image

Excel Column Name/Index/Ignore Attribute

1. Specify the column name, column index, column ignore

Excel Example

image

Code

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

2. Custom Format (ExcelFormatAttribute)

Since V0.21.0 support class which contains ToString(string content) method format

Class

public class Dto{ public string Name { get; set; } [ExcelFormat("MMMM dd, yyyy")] public DateTime InDate { get; set; }}

Code

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

image

Query supports custom format conversion

image

3. Set Column Width(ExcelColumnWidthAttribute)

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.

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

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 :

 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

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

image

8. DynamicSheetAttribute

Since V1.31.4 we can set the attributes of Sheet dynamically. We can set sheet name and state (visibility).

 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<string, object> { ["usersSheet"] = users, ["departmentSheet"] = department }; var path = PathHelper.GetTempPath(); MiniExcel.SaveAs(path, sheets, configuration: configuration);

We can also use new attribute ExcelSheetAttribute:

 [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

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

image

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.
stream.SaveAs(excelType:ExcelType.CSV);//orstream.SaveAs(excelType:ExcelType.XLSX);//orstream.Query(excelType:ExcelType.CSV);//orstream.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

var config = new MiniExcelLibs.Csv.CsvConfiguration(){ Seperator=';'};MiniExcel.SaveAs(path, values,configuration: config);

Since V1.30.1 support function to custom separator (thanks @hyzx86)

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

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
// Readvar config = new MiniExcelLibs.Csv.CsvConfiguration(){ StreamReaderFunc = (stream) => new StreamReader(stream,Encoding.GetEncoding("gb2312"))};var rows = MiniExcel.Query(path, true,excelType:ExcelType.CSV,configuration: config);// Writevar 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

var config = new MiniExcelLibs.Csv.CsvConfiguration(){ ReadEmptyStringAsNull = true};

DataReader

1. GetReader

Since 1.23.0, you can GetDataReader

 using (var reader = MiniExcel.GetReader(path,true)) { while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { var value = reader.GetValue(i); } } }

Async

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<IEnumerable<dynamic>> QueryAsync(string path, bool useHeaderRow = false, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null)public static Task<IEnumerable<T>> QueryAsync<T>(this Stream stream, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) where T : class, new()public static Task<IEnumerable<T>> QueryAsync<T>(string path, string sheetName = null, ExcelType excelType = ExcelType.UNKNOWN, string startCell = "A1", IConfiguration configuration = null) where T : class, new()public static Task<IEnumerable<IDictionary<string, object>>> 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<DataTable> 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)

image

Since V0.18.0 support Enum Description

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}

image

Since 1.30.0 version support excel Description to Enum , thanks @KaneLeung

2. Convert CSV to XLSX or Convert XLSX to CSV

MiniExcel.ConvertXlsxToCsv(xlsxPath, csvPath);MiniExcel.ConvertXlsxToCsv(xlsxStream, csvStream);MiniExcel.ConvertCsvToXlsx(csvPath, xlsxPath);MiniExcel.ConvertCsvToXlsx(csvStream, xlsxStream);
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.

var config = new CsvConfiguration(){ Culture = new CultureInfo("fr-FR"),};MiniExcel.SaveAs(path, value, configuration: config);// orMiniExcel.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);

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: image

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 = @"<html><body><a href='api/DownloadExcel'>DownloadExcel</a><br><a href='api/DownloadExcelFromTemplatePath'>DownloadExcelFromTemplatePath</a><br><a href='api/DownloadExcelFromTemplateBytes'>DownloadExcelFromTemplateBytes</a><br><p>Upload Excel</p><form method='post' enctype='multipart/form-data' action='/api/uploadexcel'> <input type='file' name='excel'> <br> <input type='submit' ></form></body></html>" }; } 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<string, object> value = new Dictionary<string, object>() { ["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<string, Byte[]> TemplateBytesCache = new Dictionary<string, byte[]>(); 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<string, object> value = new Dictionary<string, object>() { ["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<T> Page<T>(IEnumerable<T> en, int pageSize, int page){ return en.Skip(page * pageSize).Take(pageSize);}

20210419

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

Like the example, create a method to handle i18n and permission management, and use yield return to return IEnumerable<Dictionary<string, object>> 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<Dictionary<string, object>> GetOrders(string lang, string role, Order[] orders){ foreach (var order in orders) { var newOrder = new Dictionary<string, object>(); 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; }}

image

FAQ

Q: Excel header title not equal class property name, how to mapping?

A. Please use ExcelColumnName attribute

image

Q. How to query or export multiple-sheets?

A. GetSheetNames method with 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);}

image

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

A. 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 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

image

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

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<Dictionary<int, object>> ConvertToIntIndexRows(IEnumerable<object> rows){ ICollection<string> keys = null; var isFirst = true; foreach (IDictionary<string,object> r in rows) { if(isFirst) { keys = r.Keys; isFirst = false; } var dic = new Dictionary<int, object>(); var index = 0; foreach (var key in keys) dic[index++] = r[key]; yield return dic; }}

Q. No title empty excel is generated when the value is empty when exporting Excel

Because MiniExcel uses a logic similar to JSON.NET to dynamically get type from values to simplify API operations, type cannot be knew without data. You can check issue #133 for understanding.

image

Strong type & DataTable will generate headers, but Dictionary are still empty Excel

Q. How to stop the foreach when blank row?

MiniExcel can be used with LINQ TakeWhile to stop foreach iterator.

Image

Q. How to remove empty rows?

image

IEnumerable :

public static IEnumerable<dynamic> QueryWithoutEmptyRow(Stream stream, bool useHeaderRow, string sheetName, ExcelType excelType, string startCell, IConfiguration configuration){ var rows = stream.Query(useHeaderRow,sheetName,excelType,startCell,configuration); foreach (IDictionary<string,object> row in rows) { if(row.Keys.Any(key=>row[key]!=null)) yield return row; }}

DataTable :

public static DataTable QueryAsDataTableWithoutEmptyRow(Stream stream, bool useHeaderRow, string sheetName, ExcelType excelType, string startCell, IConfiguration configuration){ if (sheetName == null && excelType != ExcelType.CSV) /*Issue #279*/ sheetName = stream.GetSheetNames().First(); var dt = new DataTable(sheetName); var first = true; var rows = stream.Query(useHeaderRow,sheetName,excelType,startCell,configuration); foreach (IDictionary<string, object> row in rows) { if (first) { foreach (var key in row.Keys) { var column = new DataColumn(key, typeof(object)) { Caption = key }; dt.Columns.Add(column); } dt.BeginLoadData(); first = false; } var newRow = dt.NewRow(); var isNull=true; foreach (var key in row.Keys) { var _v = row[key]; if(_v!=null) isNull = false; newRow[key] = _v; } if(!isNull) dt.Rows.Add(newRow); } dt.EndLoadData(); return dt;}

Q. How SaveAs(path,value) to replace exists file and without throwing "The file ...xlsx already exists error"

Please use Stream class to custom file creating logic, e.g:

 using (var stream = File.Create("Demo.xlsx")) MiniExcel.SaveAs(stream,value);

or, since V1.25.0, SaveAs support overwriteFile parameter for enable/unable overwriting exist file

 MiniExcel.SaveAs(path, value, overwriteFile: true);

Limitations and caveats

  • Not support xls and encrypted file now
  • xlsm only support Query

Reference

ExcelDataReader / ClosedXML / Dapper / ExcelNumberFormat

Thanks

Jetbrains

jetbrains-variant-2

Thanks for providing a free All product IDE for this project (License)

Benefit

Link https://github.com/mini-software/MiniExcel/issues/560#issue-2080619180

Contributors

The ultimate clean architecture template for .NET applications


BuildPublish template to NuGet

GitHub contributorsGitHub StarsGitHub licensecodecov


Clean Architecture Template Title


dotnet new install Amantinband.CleanArchitecture.Templatedotnet new clean-arch -o CleanArchitecture

Important notice

This template is still under construction .

Check out my comprehensive course on Dometrain where I cover everything you need to know when building production applications structured following clean architecture. Use the exclusive coupon code GITHUB to get 5% off (btw this is the only promo code for a discount on the bundle, which is already 20% off).

Give it a star

Loving it? Show your support by giving this project a star!

Domain Overview

This is a simple reminder application. It allows users to create and manage their reminders.

To create reminders, a user must have an active subscription.

Basic Subscription

Users with a basic subscription can create up to 3 daily reminders.

Pro Subscription

Users with a pro subscription do not have a daily limit on the number of reminders.

Use Cases / Features

Subscriptions

  1. Create Subscription
  2. Get Subscription
  3. Cancel Subscription

Reminders

  1. Set Reminder
  2. Get Reminder
  3. Delete Reminder
  4. Dismiss Reminder
  5. List Reminders

Getting Started

YouTube Tutorial

Clean Architecture Tutorial

Install the template or clone the project

dotnet new install Amantinband.CleanArchitecture.Templatedotnet new clean-arch -o CleanArchitecture

or

git clone https://github.com/amantinband/clean-architecture

Run the service using Docker or the .NET CLI

docker compose up

or

dotnet run --project src/CleanArchitecture.Api

Generate a token

Navigate to requests/Tokens/GenerateToken.http and generate a token.

Note: Since most systems use an external identity provider, this project uses a simple token generator endpoint that generates a token based on the details you provide. This is a simple way to generate a token for testing purposes and is closer to how your system will likely be designed when using an external identity provider.

POST {{host}}/tokens/generateContent-Type: application/json
{ "Id": "bae93bf5-9e3c-47b3-aace-3034653b6bb2", "FirstName": "Amichai", "LastName": "Mantinband", "Email": "[email protected]", "Permissions": [ "set:reminder", "get:reminder", "dismiss:reminder", "delete:reminder", "create:subscription", "delete:subscription", "get:subscription" ], "Roles": [ "Admin" ]}

NOTE: Replacing http file variables ({{variableName}})

Option 1 (recommended) - Using the REST Client extension for VS Code

Use the REST Client extension for VS Code + update the values under .vscode/settings.json. This will update the value for all http files.

{ "rest-client.environmentVariables": { "$shared": { // these will be shared across all http files, regardless of the environment "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJuYW1lIjoiTGlvciIsImZhbWlseV9uYW1lIjoiRGFnYW4iLCJlbWFpbCI6Imxpb3JAZGFnYW4uY29tIiwiaWQiOiJhYWU5M2JmNS05ZTNjLTQ3YjMtYWFjZS0zMDM0NjUzYjZiYjIiLCJodHRwOi8vc2NoZW1hcy5taWNyb3NvZnQuY29tL3dzLzIwMDgvMDYvaWRlbnRpdHkvY2xhaW1zL3JvbGUiOiJBZG1pbiIsInBlcm1pc3Npb25zIjpbInNldDpyZW1pbmRlciIsImdldDpyZW1pbmRlciIsImRpc21pc3M6cmVtaW5kZXIiLCJkZWxldGU6cmVtaW5kZXIiLCJjcmVhdGU6c3Vic2NyaXB0aW9uIiwiZGVsZXRlOnN1YnNjcmlwdGlvbiIsImdldDpzdWJzY3JpcHRpb24iXSwiZXhwIjoxNzA0MTM0MTIzLCJpc3MiOiJSZW1pbmRlclNlcnZpY2UiLCJhdWQiOiJSZW1pbmRlclNlcnZpY2UifQ.wyvn9cq3ohp-JPTmbBd3G1cAU1A6COpiQd3C_e_Ng5s", "userId": "aae93bf5-9e3c-47b3-aace-3034653b6bb2", "subscriptionId": "c8ee11f0-d4bb-4b43-a448-d511924b520e", "reminderId": "08233bb1-ce29-49e2-b346-5f8b7cf61593" }, "dev": { // when the environment is set to dev, these values will be used "host": "http://localhost:5001", }, "prod": { // when the environment is set to prod, these values will be used "host": "http://your-prod-endpoint.com", } }}

Options 2 - Defining the variables in the http file itself

Define the variables in the http file itself. This will only update the value for the current http file.

@host = http://localhost:5001POST {{host}}/tokens/generate

Option 3 - Manually

Replace the variables manually.

POST {{host}}/tokens/generate

POST http://localhost:5001/tokens/generate

Create a subscription

POST {{host}}/users/{{userId}}/subscriptionsContent-Type: application/jsonAuthorization: Bearer {{token}}
{ "SubscriptionType": "Basic"}

Create a reminder

POST {{host}}/users/{{userId}}/subscriptions/{{subscriptionId}}/remindersContent-Type: application/jsonAuthorization: Bearer {{token}}
{ "text": "let's do it", "dateTime": "2025-2-26"}

Folder Structure

Folder structure

You can use the this figma community file to explore or create your own folder structure respresentation.

Authorization

This project puts an emphasis on complex authorization scenarios and supports role-based, permission-based and policy-based authorization.

Authorization Types

Role-Based Authorization

To apply role based authorization, use the Authorize attribute with the Roles parameter and implement the IAuthorizeableRequest interface.

For example:

[Authorize(Roles = "Admin")]public record CancelSubscriptionCommand(Guid UserId, Guid SubscriptionId) : IAuthorizeableRequest<ErrorOr<Success>>;

Will only allow users with the Admin role to cancel subscriptions.

Permission-Based Authorization

To apply permission based authorization, use the Authorize attribute with the Permissions parameter and implement the IAuthorizeableRequest interface.

For example:

[Authorize(Permissions = "get:reminder")]public record GetReminderQuery(Guid UserId, Guid SubscriptionId, Guid ReminderId) : IAuthorizeableRequest<ErrorOr<Reminder>>;

Will only allow users with the get:reminder permission to get a subscription.

Policy-Based Authorization

To apply policy based authorization, use the Authorize attribute with the Policy parameter and implement the IAuthorizeableRequest interface.

For example:

[Authorize(Policies = "SelfOrAdmin")]public record GetReminderQuery(Guid UserId, Guid SubscriptionId, Guid ReminderId) : IAuthorizeableRequest<ErrorOr<Reminder>>;

Will only allow users who pass the SelfOrAdmin policy to get a subscription.

Each policy is implemented as a simple method in the PolicyEnforcer class.

The policy "SelfOrAdmin" for example, can be implemented as follows:

public class PolicyEnforcer : IPolicyEnforcer{ public ErrorOr<Success> Authorize<T>( IAuthorizeableRequest<T> request, CurrentUser currentUser, string policy) { return policy switch { "SelfOrAdmin" => SelfOrAdminPolicy(request, currentUser), _ => Error.Unexpected(description: "Unknown policy name"), }; } private static ErrorOr<Success> SelfOrAdminPolicy<T>(IAuthorizeableRequest<T> request, CurrentUser currentUser) => request.UserId == currentUser.Id || currentUser.Roles.Contains(Role.Admin) ? Result.Success : Error.Unauthorized(description: "Requesting user failed policy requirement");}

Mixing Authorization Types

You can mix and match authorization types to create complex authorization scenarios.

For example:

[Authorize(Permissions = "get:reminder,list:reminder", Policies = "SelfOrAdmin", Roles = "ReminderManager")]public record ListRemindersQuery(Guid UserId, Guid SubscriptionId, Guid ReminderId) : IAuthorizeableRequest<ErrorOr<Reminder>>;

Will only allow users with the get:reminder and list:reminder permission, and who pass the SelfOrAdmin policy, and who have the ReminderManager role to list reminders.

Another option, is specifying the Authorize attribute multiple times:

[Authorize(Permissions = "get:reminder")][Authorize(Permissions = "list:reminder")][Authorize(Policies = "SelfOrAdmin")][Authorize(Roles = "ReminderManager")]public record ListRemindersQuery(Guid UserId, Guid SubscriptionId, Guid ReminderId) : IAuthorizeableRequest<ErrorOr<Reminder>>;

Testing

This project puts an emphasis on testability and comes with a comprehensive test suite.

Test Types

Domain Layer Unit Tests

The domain layer is tested using unit tests. By the bare minimum, each domain entity should have a test that verifies its invariants.

Domain Layer unit tests

Application Layer Unit Tests

The application layer is tested using both unit tests and subcutaneous tests.

Since each one of the application layer use cases has its corresponding subcutaneous tests, the unit tests are used to test the application layer standalone components, such as the ValidationBehavior and the AuthorizationBehavior.

Application Layer unit tests

Application Layer Subcutaneous Tests

Subcutaneous tests are tests that operate right under the presentation layer. These tests are responsible for testing the core logic of our application, which is the application layer and the domain layer.

The reason there are so many of these tests, is because each one of the application layer use cases has its corresponding subcutaneous tests.

This allows us to test the application layer and the domain layer based on the actual expected usage, giving us the confidence that our application works as expected and that the system cannot be manipulated in a way we don't allow.

I recommend spending more effort on these tests than the other tests, since they aren't too expensive to write, and the value they provide is huge.

Presentation Layer Integration Tests

The api layer is tested using integration tests. This is where we want to cover the entire system, including the database, external dependencies and the presentation layer.

Unlike the subcutaneous tests, the focus of these tests is to ensure the integration between the various components of our system and other systems.

Integration Tests

Fun features

Domain Events & Eventual Consistency

Note: Eventual consistency and the domain events pattern add a layer of complexity. If you don't need it, don't use it. If you need it, make sure your system is designed properly and that you have the right tools to manage failures.

The domain is designed so each use case which manipulates data, updates a single domain object in a single transaction.

For example, when a user cancels a subscription, the only change that happens atomically is the subscription is marked as canceled:

public ErrorOr<Success> CancelSubscription(Guid subscriptionId){ if (subscriptionId != Subscription.Id) { return Error.NotFound("Subscription not found"); } Subscription = Subscription.Canceled; _domainEvents.Add(new SubscriptionCanceledEvent(this, subscriptionId)); return Result.Success;}

Then, in an eventual consistency manner, the system will update all the relevant data. Which includes:

  1. Deleting the subscription from the database and marking all reminders as deleted (Subscriptions/Events/SubscriptionDeletedEventHandler.cs])
  2. Deleting all the reminders marked as deleted from the database (Reminders/Events/ReminderDeletedEventHandler.cs]

Note: Alongside the performance benefits, this allows to reuse reactive behavior. For example, the ReminderDeletedEventHandler is invoked both when a subscription is deleted and when a reminder is deleted.

Eventual Consistency Mechanism

  1. Each invariant is encapsulated in a single domain object. This allows performing changes by updating a single domain object in a single transaction.
  2. If domain object B needs to react to changes in domain object A, a Domain Event is added to domain object A alongside the changes.
  3. Upon persisting domain object A changes to the database, the domain events are extracted and added to a queue for offline processing:
    private void AddDomainEventsToOfflineProcessingQueue(List<IDomainEvent> domainEvents){ Queue<IDomainEvent> domainEventsQueue = new(); domainEvents.ForEach(domainEventsQueue.Enqueue); _httpContextAccessor.HttpContext.Items["DomainEvents"] = domainEventsQueue;}
  4. After the user receives a response, the EventualConsistencyMiddleware is invoked and processes the domain events:
    public async Task InvokeAsync(HttpContext context, IEventualConsistencyProcessor eventualConsistencyProcessor){ context.Response.OnCompleted(async () => { if (context.Items.TryGetValue("DomainEvents", out var value) || value is not Queue<IDomainEvent> domainEvents) { return; } while (domainEvents.TryDequeue(out var nextEvent)) { await publisher.Publish(nextEvent); } });}

Note: the code snippets above are a simplified version of the actual implementation.

Background service for sending email reminders

There is a simple background service that runs every minute and sends email reminders for all reminders that are due (ReminderEmailBackgroundService):

private async void SendEmailNotifications(object? state){ await _fluentEmail .To(user.Email) .Subject($"{dueReminders.Count} reminders due!") .Body($""" Dear {user.FirstName} {user.LastName} from the present. I hope this email finds you well. I'm writing you this email to remind you about the following reminders: {string.Join('\n', dueReminders.Select((reminder, i) => $"{i + 1}. {reminder.Text}"))} Best, {user.FirstName} from the past. """) .SendAsync();}

Configure Email Settings

To configure the service to send emails, make sure to update the email settings under the appsettings.json/appsettings.Development.json file:

You can use your own SMTP server or use a service like Brevo.

Configure Email Settings Manually

{ "EmailSettings": { "EnableEmailNotifications": false, "DefaultFromEmail": "[email protected] (also, change EnableEmailNotifications to true )", "SmtpSettings": { "Server": "smtp.gmail.com", "Port": 587, "Username": "[email protected]", "Password": "your-password" } }}

note: you may need to allow less secure apps to access your email account.

Configure Email Settings via User Secrets

dotnet user-secrets --project src/CleanArchitecture.Api set EmailSettings:EnableEmailNotifications truedotnet user-secrets --project src/CleanArchitecture.Api set EmailSettings:DefaultFromEmail [email protected] user-secrets --project src/CleanArchitecture.Api set EmailSettings:SmtpSettings:Server smtp-relay.brevo.comdotnet user-secrets --project src/CleanArchitecture.Api set EmailSettings:SmtpSettings:Port 587dotnet user-secrets --project src/CleanArchitecture.Api set EmailSettings:SmtpSettings:Username [email protected] user-secrets --project src/CleanArchitecture.Api set EmailSettings:SmtpSettings:Password your-password

Contribution

If you have any questions, comments, or suggestions, please open an issue or create a pull request

Credits

License

This project is licensed under the terms of the MIT license.