园友好,最近晚辈延续上篇,尝试进阶做成Excel模板填充数据生成工具 MiniExcel Template。
主要特点
- 同样以
Stream流、延迟查询
避免全部数据载入内存情况,做到1GB内存降低到只需60多MB - 简单使用,宣告方式类似 Vue 模板
{{变量名称}}
, 或是集合渲染{{集合名称.栏位名称}}
- 轻量化,总包大小100多KB,不依赖 com 跟第三方套件
使用方式介绍 :
0. 安装
1. 基本填充
模板:
最终效果:
代码:
// 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);
2. IEnumerable 数据填充
Note1: 同行从左往右以第一个 IEnumerableUse 当列表来源 (不支持同列多集合)
模板:
最终效果:
代码:
//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);
3. 复杂数据填充
Note: 支持多 sheet 填充,并共用同一组参数
模板:
最终效果:
代码:
// 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);
4. 大数据填充效率比较
NOTE: 在 MiniExcel 使用 IEnumerable 延迟 ( 不ToList ) 可以节省内存使用
5. Cell 值自动类别对应
模板
最终效果
类别
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; }
}
代码
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 : 列出 Github 专案
模板
最终效果
代码
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. 做成 ASP.NET Core API
模板生成 Excel Download API
public IActionResult DownloadExcelFromTemplatePath()
{
string templatePath = "TestTemplateComplex.xlsx";
Dictionary<string, object> value = new Dictionary<string, object>()
{
//..
};
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"
};
}
建议可以将模板转为 Byte[]
缓存,避免多线程读取I/O问题
private static Dictionary<string, Byte[]> TemplateBytesCache = new Dictionary<string, byte[]>();
static HomeController()
{
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>()
{
//..
};
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"
};
}
广告时间
MiniExcel 最近加入 dotNET China组织
喜欢 MiniExcel / dotnet china 的园友求帮点个 Star