OfficeOpenXml.Extension.AspNetCore
1.0.0
.NET 5.0
This package targets .NET 5.0. The package is compatible with this framework or higher.
.NET Core 3.1
This package targets .NET Core 3.1. The package is compatible with this framework or higher.
.NET Standard 2.0
This package targets .NET Standard 2.0. The package is compatible with this framework or higher.
dotnet add package OfficeOpenXml.Extension.AspNetCore --version 1.0.0
NuGet\Install-Package OfficeOpenXml.Extension.AspNetCore -Version 1.0.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="OfficeOpenXml.Extension.AspNetCore" Version="1.0.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add OfficeOpenXml.Extension.AspNetCore --version 1.0.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#r "nuget: OfficeOpenXml.Extension.AspNetCore, 1.0.0"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install OfficeOpenXml.Extension.AspNetCore as a Cake Addin #addin nuget:?package=OfficeOpenXml.Extension.AspNetCore&version=1.0.0 // Install OfficeOpenXml.Extension.AspNetCore as a Cake Tool #tool nuget:?package=OfficeOpenXml.Extension.AspNetCore&version=1.0.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
OfficeOpenXml.Extension.AspNetCore
OfficeOpenXml.Extension.AspNetCore 是一个基于 OfficeOpenXml 拓展,它依赖于 EPPlus,用于根据模板输出 Excel。
注意: 由于 Excel 2003 版本 和 2007 之后版本文件结构的差异性,当前扩展无法同时兼容两种模式,仅支持 *.xlsx 文件!!!
快速使用
1. 安装组件
dotnet add package OfficeOpenXml.Extension.AspNetCore
2.使用组件
2.1 读取 Excel 模板, 导入数据
准备Excel模板
定义接收对象
[Worksheet(Index = 1, HasHeader = false)] public class ProjectRow { [Column(Number = 1)] public int Id { get; set; } [Column(Number = 2)] public string Name { get; set; } [Column(Number = 3)] public string Description { get; set; } }
- Worksheet - 表格属性,其中
Index
对应表格的索引(从 0 开始),HasHeader
对应当前表格是否包含表头 - Column - 单元格属性,其中
Number
对应单元格的列
- Worksheet - 表格属性,其中
读取 Excel 信息
[HttpGet("projects", Name = "Projects")] public IEnumerable<ProjectRow> GetProjects() { var excelFilePath = Path.Combine(_wwwroot, "templates", "Projects.xlsx"); var fileStream = new System.IO.FileStream(excelFilePath, FileMode.Open); using var excelPackage = new ExcelPackage(fileStream); return excelPackage.ParseWorksheet<ProjectRow>().ToList(); }
最终结果展示
[ { "id": 1, "name": "MyHRW", "description": "Case Management Tool" }, { "id": 2, "name": "PEX", "description": "Global Payroll Exchange" } ]
2.2 读取 Excel 模板,导出数据
准备Excel模板
读取模板文件
var excelFilePath = Path.Combine(_wwwroot, "templates", "tpl.xlsx"); var fileStream = new System.IO.FileStream(excelFilePath, FileMode.Open); using var excelPackage = new ExcelPackage(fileStream); var workBook = excelPackage.Workbook;
构造填充对象
Dictionary<string, IEnumerable<string>> _marketLists = new() { { "水果", new string[] { "桃子", "李子", "香蕉", "梨" } }, { "蔬菜", new string[] { "青菜", "土豆", "黄瓜", "啤酒" } } }; //构造model var model = new { ProjectName = "灰太狼", Name = "Jeff", CreatedAt = DateTime.Now, BuyerName = "Bill", Cates = _marketLists.Select(m => new { Name = m.Key, Items = m.Value.Select(n => new { Name = n, Price = (decimal)random.Next(1, 100), Amount = random.Next(1, 100) }) }) };
填充数据对象
// 下面的FillModel就是 OfficeOpenXml.Extension.AspNetCore 提供的拓展方法 workBook.Worksheets.First().FillModel(model);
导出模板文件
string fileName = "Lists_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; string exportFilePath = Path.Combine(_wwwroot, "outputs", fileName); var exportFile = new FileInfo(exportFilePath); excelPackage.SaveAs(exportFile); return File(exportFile.OpenRead(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
2.3 其他功能辅助说明
- 输出内容目前仅支持基础的变量、成员,不支持方法、运算等高级特性;控制代码目前仅支持 for 循环、嵌套 for 循环以及索引,使用索引时需要注意索引计数从1开始,因为excel中通常序号从1开始。
- 输出公式的功能用 @= 开头便于程序识别,解析时会将 @ 去掉,后面的内容对 {...} 进行解释并替换。R[-4]表示相对值-4行,R[-1]表示相对值-1行,C后面没有 [] 表示当前列。
- 具体细节可以进一步参考案例代码:
鸣谢
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 is compatible. net5.0-windows was computed. net6.0 is compatible. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp2.0 was computed. netcoreapp2.1 was computed. netcoreapp2.2 was computed. netcoreapp3.0 was computed. netcoreapp3.1 is compatible. |
.NET Standard | netstandard2.0 is compatible. netstandard2.1 is compatible. |
.NET Framework | net461 was computed. net462 was computed. net463 was computed. net47 was computed. net471 was computed. net472 was computed. net48 was computed. net481 was computed. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen40 was computed. tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
1.0.0 | 57,508 | 2/8/2023 |
Add more version support and correct package name