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).
real-time
operation of each row of dataYou can install the package from NuGet
Please Check Release Notes
Please Check TODO
Benchmarks logic can be found in MiniExcel.Benchmarks , and test cli
dotnet run -p .\benchmarks\MiniExcel.Benchmarks\ -c Release -f netcoreapp3.1 -- -f * --join
Output from the latest run is :
BenchmarkDotNet=v0.12.1, OS=Windows 10.0.19042
Intel Core i7-7700 CPU 3.60GHz (Kaby Lake), 1 CPU, 8 logical and 4 physical cores
[Host] : .NET Framework 4.8 (4.8.4341.0), X64 RyuJIT
Job-ZYYABG : .NET Framework 4.8 (4.8.4341.0), X64 RyuJIT
IterationCount=3 LaunchCount=3 WarmupCount=3
Benchmark History : Link
Logic : Test1,000,000x10.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 |
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 |
Recommand to use Stream.Query because of better efficiency.
public class UserAccount
{
public Guid ID { get; set; }
public string Name { get; set; }
public DateTime BoD { get; set; }
public int Age { get; set; }
public bool VIP { get; set; }
public decimal Points { get; set; }
}
var rows = MiniExcel.Query<UserAccount>(path);
// or
using (var stream = File.OpenRead(path))
var rows = stream.Query<UserAccount>();
A.B.C.D..
MiniExcel | 1 |
---|---|
Github | 2 |
var rows = MiniExcel.Query(path).ToList();
// or
using (var stream = File.OpenRead(path))
{
var rows = stream.Query().ToList();
Assert.Equal("MiniExcel", rows[0].A);
Assert.Equal(1, rows[0].B);
Assert.Equal("Github", rows[1].A);
Assert.Equal(2, rows[1].B);
}
note : same column name use last right one
Input Excel :
Column1 | Column2 |
---|---|
MiniExcel | 1 |
Github | 2 |
var rows = MiniExcel.Query(useHeaderRow:true).ToList();
// or
using (var stream = File.OpenRead(path))
{
var rows = stream.Query(useHeaderRow:true).ToList();
Assert.Equal("MiniExcel", rows[0].Column1);
Assert.Equal(1, rows[0].Column2);
Assert.Equal("Github", rows[1].Column1);
Assert.Equal(2, rows[1].Column2);
}
Query First
var row = MiniExcel.Query(path).First();
Assert.Equal("HelloWorld", row.A);
// or
using (var stream = File.OpenRead(path))
{
var row = stream.Query().First();
Assert.Equal("HelloWorld", row.A);
}
Performance between MiniExcel/ExcelDataReader/ClosedXML/EPPlus
MiniExcel.Query(path, sheetName: "SheetName");
//or
stream.Query(sheetName: "SheetName");
var sheetNames = MiniExcel.GetSheetNames(path);
foreach (var sheetName in sheetNames)
{
var rows = MiniExcel.Query(path, sheetName: sheetName);
}
var columns = MiniExcel.GetColumns(path); // e.g result : ["A","B"...]
var cnt = columns.Count; // get column count
IDictionary<string,object>
foreach(IDictionary<string,object> row in MiniExcel.Query(path))
{
//..
}
// or
var rows = MiniExcel.Query(path).Cast<IDictionary<string,object>>();
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);
MiniExcel.Query(path,useHeaderRow:true,startCell:"B3")
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);
support variable length and width multi-row and column filling
Must be a non-abstract type with a public parameterless constructor .
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
var path = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.xlsx");
MiniExcel.SaveAs(path, new[] {
new { Column1 = "MiniExcel", Column2 = 1 },
new { Column1 = "Github", Column2 = 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 :
Column1 | Column2 |
---|---|
MiniExcel | 1 |
Github | 2 |
Recommended
, it can avoid to load all data into memoryMiniExcel.SaveAs(path, reader);
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);
}
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);
Not recommended
, it will load all data into memory, please use ExecuteReader
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);
}
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"
};
}
// 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. DataSet
var sheets = new DataSet();
sheets.Add(UsersDataTable);
sheets.Add(DepartmentDataTable);
//..
MiniExcel.SaveAs(path, sheets);
From the v0.15.0 version, the default style has been changed to
Without style configuration
var config = new OpenXmlConfiguration()
{
TableStyles = TableStyles.None
};
MiniExcel.SaveAs(path, value,configuration:config);
{{variable name}}
, or the collection rendering {{collection name.field name}}
Template:
Result:
Code:
// 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<string, object>()
{
["Name"] = "Jack",
["CreateDate"] = new DateTime(2021, 01, 01),
["VIP"] = true,
["Points"] = 123
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);
Note1: Use the first IEnumerable of the same column as the basis for filling list
Template:
Result:
Code:
//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<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);
Note: Support multi-sheets and using same varible
Template:
Result:
// 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<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);
NOTE: Using IEnumerable deferred execution not ToList can save max memory usage in MiniExcel
Template
Result
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);
Template
Result
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);
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);
Excel Example
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);
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
Query supports custom format conversion
public class Dto
{
[ExcelColumnWidth(20)]
public int ID { get; set; }
[ExcelColumnWidth(15.50)]
public string Name { get; set; }
}
public class Dto
{
[ExcelColumnName(excelColumnName:"EmployeeNo",aliases:new[] { "EmpNo","No" })]
public string Empno { get; set; }
public string Name { get; set; }
}
file extension
by default, but there may be inaccuracy, please specify it manually.stream.SaveAs(excelType:ExcelType.CSV);
//or
stream.SaveAs(excelType:ExcelType.XLSX);
//or
stream.Query(excelType:ExcelType.CSV);
//or
stream.Query(excelType:ExcelType.XLSX);
string
type, and value will not be converted to numbers or datetime, unless the type is defined by strong typing generic.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);
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);
// 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);
v0.17.0 support Async (thanks isdaniel ( SHIH,BING-SIOU)](https://github.com/isdaniel))
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)
Be sure excel & property name same, system will auto mapping (case insensitive)
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
}
Large Size File
SQL Insert Avoid OOMnote : 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:
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");
}
}
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);
}
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();
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; }
}
public void CsvToXlsx(string csvPath, string xlsxPath)
{
var value = MiniExcel.Query(csvPath, true);
MiniExcel.SaveAs(xlsxPath, value);
}
A. Please use ExcelColumnName attribute
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);
}
No, the image test has 1 million rows*10 columns of data, the maximum memory usage is <60MB, and it takes 13.65 seconds
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;
}
}
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.
Strong type & DataTable will generate headers, but Dictionary are still empty Excel
MiniExcel can be used with LINQ TakeWhile
to stop foreach iterator.
ExcelDataReader / ClosedXML / Dapper
Thanks for providing a free All product IDE for this project (License)
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。