[来自百度百科] Open XML 的标准化工作是由 Ecma International 通过其技术委员会 45 (TC45) 执行的，来自 Apple、Barclays Capital、BP、The British Library、Essilor、Intel、Microsoft、NextPage、Novell、Statoil、Toshiba 和 United States Library of Congress 的代表参与了该项工作。该标准旨在提供现有 ISO 标准所无法提供的独特好处，其中包括能够实现从现有二进制格式向基于 XML 的格式的高保真移植。
OpenXml 官方文档
如何使用
在 Hoa Framework 中，基于标准的 OpenXml/ClosedXML 开发的快捷Excel操作方式，可以大大减少Excel的复杂度以及大数据导出性能过高问题。
using Hoa.Workbook;
using Hoa.Workbook.Enums;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Threading.Tasks;
namespace Hoa.Web.Host.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class HoaController : ControllerBase
{
private readonly IWorkbookContext<object> _workbookContext;
public HoaController(
IWorkbookContext<object> workbookContext
{
_workbookContext = workbookContext;
}
[HttpGet]
[Route(nameof(ExportValueType))]
public async Task ExportValueType()
{
await _workbookContext.ExportToSaveAsync("object.xlsx",
1, // int
1d, // double
10f, // float
1m, // decimal
true, // bool
false, // bool
DateTime.Now, // DateTime
100000000000000000, // long
1.9, // number
BorderStyle.DashDot, // enum
"string type" // string
);
}
}
}
导出强类型
using Hoa.Workbook;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace Hoa.Web.Host.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class HoaController : ControllerBase
{
private readonly IWorkbookContext<Person> _workbookContext;
public HoaController(
IWorkbookContext<Person> workbookContext)
{
_workbookContext = workbookContext;
}
[HttpGet]
[Route(nameof(ExportValueType))]
public async Task ExportValueType()
{
var persons = new List<Person>()
{
new Person(){Id=1,Name="Monk",Age=27,Address="广东省珠海市香洲区" },
new Person(){Id=2,Name="Suncoder",Age=22,Address="广东省中山市石岐区" },
new Person(){Id=3,Name="Cocoli",Age=30,Address="香港自治区" },
new Person(){Id=4,Name="Steft",Age=45,Address="广西省安宁市某某乡镇" },
new Person(){Id=5,Name="Dave",Age=35,Address="广东省珠海市南方软件园" },
new Person(){Id=6,Name="Teyn",Age=20,Address="澳门经济特区" }
};
await _workbookContext.ExportToSaveAsync("persons", persons);
}
}
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public string Address { get; set; }
}
}
更加丰富页面导出
using Hoa.Workbook;
using Hoa.Workbook.Attributes;
using Hoa.Workbook.Enums;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;
namespace Hoa.Web.Host.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class HoaController : ControllerBase
{
private readonly IWorkbookContext<Person> _workbookContext;
public HoaController(
IWorkbookContext<Person> workbookContext)
{
_workbookContext = workbookContext;
}
[HttpGet]
[Route(nameof(ExportValueType))]
public async Task ExportValueType()
{
var persons = new List<Person>()
{
new Person(){Id=1,Name="Monk",Age=27,Address="广东省珠海市香洲区",Photo=@"C:\images\bxs.jpg",Site="https://monksoul.gitbook.io/",PhoneNumber="13800138000" },
new Person(){Id=2,Name="Suncoder",Age=22,Address="广东省中山市石岐区" ,Photo=@"C:\images\xtx.jpg",Site="https://www.baidu.com/",PhoneNumber="18676675647" },
new Person(){Id=3,Name="Cocoli",Age=30,Address="香港自治区",Photo=@"C:\images\bxs.jpg",Site="https://www.google.com/",PhoneNumber="16609987544" },
new Person(){Id=4,Name="Steft",Age=45,Address="广西省安宁市某某乡镇",Photo=@"C:\images\xtx.jpg",Site="https://www.cnblogs.com/",PhoneNumber="13476437890" },
new Person(){Id=5,Name="Dave",Age=35,Address="广东省珠海市南方软件园",Photo=@"C:\images\bxs.jpg",Site="https://www.ithome.com/",PhoneNumber="16773467833" },
new Person(){Id=6,Name="Teyn",Age=20,Address="澳门经济特区",Photo=@"C:\images\xtx.jpg",Site="https://www.csdn.net/",PhoneNumber="18934568890" }
};
await _workbookContext.ExportToSaveAsync("persons", persons);
}
}
[Excel("员工列表", "绩效列表")]
[ExcelGroup("员工列表汇总", BackgroundColor = "#cccccc", Height = 30)]
[ExcelHead(BackgroundColor = "#dedede", Height = 25)]
[ExcelRow(-1, Height = 50, BackgroundColor = "#d9d9d9")]
[ExcelRow(-2, Height = 50, BackgroundColor = "#f0f0f0")]
public class Person
{
[ExcelCell("编号")]
public int Id { get; set; }
[ExcelCell("名称", FontBold = true, BackgroundColor = "#f5f5f5", FontColor = "#ff0000")]
public string Name { get; set; }
[ExcelCell("年龄", AlignmentHorizontal = Workbook.Enums.HorizontalAlignment.Right)]
public int Age { get; set; }
[ExcelCell("地址", Width = 30)]
public string Address { get; set; }
[ExcelCell("头像", CellType = Workbook.Enums.CellType.Image, ImageScale = 0.06, AlignmentHorizontal = HorizontalAlignment.Center, AlignmentVertical = VerticalAlignment.Center)]
public string Photo { get; set; }
[ExcelCell("个人主页", CellType = Workbook.Enums.CellType.Hyperlink, Tooltip = "这是是我个人主页", Width = 30)]
public string Site { get; set; }
[ExcelCell("联系方式", Format = "###,###,####", Width = 30, OutsideBorder = new object[] { BorderStyle.DashDot, "#0000ff" })]
public string PhoneNumber { get; set; }
}
}
导出并下载到本地
using Hoa.Workbook;
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;
namespace Hoa.Web.Host.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class HoaController : ControllerBase
{
private readonly IWorkbookContext<object> _workbookContext;
public HoaController(IWorkbookContext<object> workbookContext)
{
_workbookContext = workbookContext;
}
[HttpGet]
[Route(nameof(DownloadFile))]
public async Task<IActionResult> DownloadFile()
{
var fileStreamResult = await _workbookContext.ExportToFileStreamResultAsync("test.xlsx", 1, 2, 3, 4, true, false);
return fileStreamResult;
}
}
}
