二十七、OpenXml/Excel 操作

在企业管理系统中，系统对接Office功能是非常常见的需求。

什么是OpenXml

[来自百度百科] 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 官方文档

https://github.com/OfficeDev/Open-XML-SDK

如何使用

在 Hoa Framework 中，基于标准的 OpenXml/ClosedXML 开发的快捷Excel操作方式，可以大大减少Excel的复杂度以及大数据导出性能过高问题。

下面分为两个章节介绍 Excel 导入导出功能。

注入 IWorkbookContext 实例

首先在 Hoa.Applicaiton.HoaApplicationModule.cs 中注入 实例，如：

builder.RegisterGeneric(typeof(WorkbookContext<>))
       .As(typeof(IWorkbookContext<>));

关于 IWorkbookContext 接口

IWorkbookContext<T>/WorkbookContext<T> 是Hoa Framework 框架独创的操作Excel上下文对象，该对象有三个方法，使用非常简单。

using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;

namespace Hoa.Workbook
{
    public interface IWorkbookContext<T> : IDisposable
    {
        // 导出并保存到文件中
        Task ExportToSaveAsync(string fileName, IEnumerable<T> datas);

        // 导出并保存到文件中（重载）
        Task ExportToSaveAsync(string fileName, params T[] datas);

        // 导出到内存流中
        Task<MemoryStream> ExportToMemoryStreamAsync(IEnumerable<T> datas);

        // 导出到内存流中（重载）
        Task<MemoryStream> ExportToMemoryStreamAsync(params T[] datas);

        // 导出到MVC的FileStreamResult中，可直接配合MVC下载
        Task<FileStreamResult> ExportToFileStreamResultAsync(string fileDownloadName, IEnumerable<T> datas);

        // 导出到MVC的FileStreamResult中，可直接配合MVC下载（重载）
        Task<FileStreamResult> ExportToFileStreamResultAsync(string fileDownloadName, params T[] datas);
    }
}

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

前端Angular下载示例

Get请求

只需要通过 <a href='下载地址'>下载</a> 即可。

Post请求

// 导入HttpClient/HttpResponse 包
import { HttpClient, HttpResponse } from '@angular/common/http';

// 构造函数初始化
constructor(
    private httpClient: HttpClient
  ) { }

// 下载，第一个参数为post地址，第二个参数为post参数
this.httpClient.post('http://localhost:57310/api/Member/ExportMemberClaims', [{}], {
      responseType: 'blob',
      observe: 'events',
    }).subscribe((res: HttpResponse<Blob>) => {
      if (res.type !== 4) { return; }

      const objUrl = window.URL.createObjectURL(res.body);
      const a = document.createElement('a');
      a.href = objUrl;
      
      const fileName = 'excel.xlsx';  // 设置下载文件名
      a.download = decodeURIComponent(fileName);
      a.click();
      
      window.URL.revokeObjectURL(objUrl);
    });

更多功能

更多功能不断集成开发中，敬请期待。

特性说明

[Excel] 特性配置

配置Excel使用区域信息、样式。

• SheetNames：配置Excel多个Sheet

• Scale：配置每个Sheet数据比例，比如有两个Sheet，数据有1000条，则：1:3表示第一个Sheet有250条，第二个Sheet有750条。

• OutsideBorder：配置数据外边框样式，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串

• InsideBorder：配置数据内边框样式，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串

• AdjustToContents：是否自动调整内容宽度

[ExcelCell] 特性配置

配置单元格信息、样式。

• Name：配置列名，不配置采用属性名

• BackgroundColor：背景颜色

• CellType：单元格类型，有（Text：文本类型，Image：图片类型，HyperLink：链接类型）

• Hyperlink：链接值，如果等于 @Value 则取单元格的值，只有 CellType=HyperLink有效

• Tooltip：链接获取焦点提示，只有 CellType=HyperLink有效

• ImageScale：图片缩放比例，只有 CellType=Image有效

• AlignmentHorizontal：单元格数据水平对其方式

• AlignmentVertical：单元格数据垂直对其方式

• FontSize：字体大小

• FontBold：是否加粗

• FontColor：字体颜色

• Width：单元格宽度

• OutsideBorder：单元格外边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串

• InsideBorder：单元格内边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串

• Border：单元格四个方向边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串，第三个参数是方向 BorderDirection枚举类型

• SetToColumnStyle：是否将单元格样式设置给全局列

• Format：单元格内容格式，支持OpenXml和StringFormat格式

[ExcelGroup] 特性配置

配置分组单元格信息、样式。

• Name：配置列名，不配置采用属性名

• BackgroundColor：背景颜色

• CellType：单元格类型，有（Text：文本类型，Image：图片类型，HyperLink：链接类型）

• Hyperlink：链接值，如果等于 @Value 则取单元格的值，只有 CellType=HyperLink有效

• Tooltip：链接获取焦点提示，只有 CellType=HyperLink有效

• ImageScale：图片缩放比例，只有 CellType=Image有效

• AlignmentHorizontal：单元格数据水平对其方式

• AlignmentVertical：单元格数据垂直对其方式

• FontSize：字体大小

• FontBold：是否加粗

• FontColor：字体颜色

• Height：单元格高度

• OutsideBorder：单元格外边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串

• InsideBorder：单元格内边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串

• Border：单元格四个方向边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串，第三个参数是方向 BorderDirection枚举类型

• SetToColumnStyle：是否将单元格样式设置给全局列

• Format：单元格内容格式，支持OpenXml和StringFormat格式

[ExcelHead] 特性配置

配置表头信息、样式。

• BackgroundColor：背景颜色

• AlignmentHorizontal：单元格数据水平对其方式

• AlignmentVertical：单元格数据垂直对其方式

• FontSize：字体大小

• FontBold：是否加粗

• FontColor：字体颜色

• Height：单元格高度

• OutsideBorder：单元格外边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串

• InsideBorder：单元格内边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串

• Border：单元格四个方向边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串，第三个参数是方向 BorderDirection枚举类型

• Format：单元格内容格式，支持OpenXml和StringFormat格式

[ExcelRow] 特性配置

• Index：行索引，（0：不做任何行操作，> 0：特性行操作，-1/-2：奇偶行操作）

• Name：行的名字（暂未启用该功能）

• BackgroundColor：背景颜色

• AlignmentHorizontal：单元格数据水平对其方式

• AlignmentVertical：单元格数据垂直对其方式

• FontSize：字体大小

• FontBold：是否加粗

• FontColor：字体颜色

• Height：单元格高度

• OutsideBorder：单元格外边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串

• InsideBorder：单元格内边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串

• Border：单元格四个方向边框，对象数组类型，数组第一个参数为 BorderStyle 枚举，第二个参数为 颜色值字符串，第三个参数是方向 BorderDirection枚举类型

Excel 导入