ASP.NET 开源导入导出库Magicodes.IE导出Excel

要点

  • 导出特性
  • 如何导出Excel表头
  • 如何导出数据、如何进行数据的切割、如何使用筛选器

导出特性

ExporterAttribute


  • Name:名称(当前Sheet 名称)
  • HeaderFontSize:头部字体大小
  • FontSize:正文字体大小
  • MaxRowNumberOnASheet:Sheet最大允许的行数,设置了之后将输出多个Sheet
  • TableStyle:表格样式风格
  • AutoFitAllColumn:bool 自适应所有列
  • Author:作者
  • ExporterHeaderFilter:头部筛选器

ExporterHeaderAttribute

DisplayName:显示名称

FontSize:字体大小

IsBold:是否加粗

Format:格式化

IsAutoFit:是否自适应

IsIgnore:是否忽略

主要步骤

1、安装包Magicodes.IE.Excel

Install-Package Magicodes.IE.Excel

2、导出Excel表头

  • 通过数组导出

public async Task ExportHeader()
{
    IExporter exporter = new ExcelExporter();
    var filePath = "h.xlsx";
    var arr = new[] { "Name1", "Name2", "Name3", "Name4", "Name5", "Name6" };
    var sheetName = "Test";
    var result = await exporter.ExportHeaderAsByteArray(arr, sheetName);
    result.ToExcelExportFileInfo(filePath);
}
  • 通过DTO导出
public async Task ExportHeader()
{
    IExporter exporter = new ExcelExporter();
    var filePath = "h.xlsx";
    var result = await exporter.ExportHeaderAsByteArray<Student>( new Student());
    result.ToExcelExportFileInfo(filePath);
}

640.png640.png

3、导出Excel

  • 基础导出

public class Student
{
    /// <summary>
    ///     姓名
    /// </summary>
    public string Name { get; set; }
    /// <summary>
    ///     年龄
    /// </summary>
    public int Age { get; set; }
}
public async Task Export()
{
    IExporter exporter = new ExcelExporter();
    var result = await exporter.Export("a.xlsx", new List<Student>()
        {
            new Student
            {
                Name = "MR.A",
                Age = 18
            },
            new Student
            {
                Name = "MR.B",
                Age = 19
            },
            new Student
            {
                Name = "MR.B",
                Age = 20
            }
        });
}

通过如上代码我们可以将Excel导出,如下图所示


1.png

  • 特性导出示例
public async Task Export()
{
    IExporter exporter = new ExcelExporter();
    var result = await exporter.Export("test.xlsx", new List<Student>()
        {
            new Student
            {
                Name = "MR.A",
                Age = 18,
                Remarks = "我叫MR.A,今年18岁",
                Birthday=DateTime.Now
            },
            new Student
            {
                Name = "MR.B",
                Age = 19,
                Remarks = "我叫MR.B,今年19岁",
                Birthday=DateTime.Now
            },
            new Student
            {
                Name = "MR.C",
                Age = 20,
                Remarks = "我叫MR.C,今年20岁",
                Birthday=DateTime.Now
            }
        });
}
/// <summary>
/// 学生信息
/// </summary>
[ExcelExporter(Name = "学生信息", TableStyle = "Light10", AutoFitAllColumn = true, MaxRowNumberOnASheet = 2))]
public class Student
{
    /// <summary>
    ///     姓名
    /// </summary>
    [ExporterHeader(DisplayName = "姓名")]
    public string Name { get; set; }

    /// <summary>
    ///     年龄
    /// </summary>
    [ExporterHeader(DisplayName = "年龄")]
    public int Age { get; set; }
    /// <summary>
    ///     备注
    /// </summary>
    public string Remarks { get; set; }
    /// <summary>
    ///     出生日期
    /// </summary>
    [ExporterHeader(DisplayName = "出生日期", Format = "yyyy-mm-DD")]
    public DateTime Birthday { get; set; }
}

通过如上代码我们可以将Excel导出,如下图所示

  1. ExcelExporter特性可以设置导出的全局设置,比如表格样式,Sheet名称,自适应列等等具体参照 导出特性
  2. ExporterHeader特性我们可以对表头名称、样式等等进行设置 具体参照 导出特性
  3. ExcelExporter MaxRowNumberOnASheet 属性对数据进行拆分,通过该属性指定Sheet数据长度从而实现自动切割
  • 表头筛选器
/// <summary>
///  学生信息
/// </summary>
[ExcelExporter(Name = "学生信息", TableStyle = "Light10", AutoFitAllColumn = true, MaxRowNumberOnASheet = 2,ExporterHeaderFilter = typeof(ExporterStudentHeaderFilter))]
public class Student
{
    /// <summary>
    ///     姓名
    /// </summary>
    [ExporterHeader(DisplayName = "姓名")]
    public string Name { get; set; }
    /// <summary>
    ///     年龄
    /// </summary>
    [ExporterHeader(DisplayName = "年龄")]
    public int Age { get; set; }
    /// <summary>
    ///     备注
    /// </summary>
    public string Remarks { get; set; }
}
public class ExporterStudentHeaderFilter : IExporterHeaderFilter
{
    /// <summary>
    /// 表头筛选器(修改名称)
    /// </summary>
    /// <param name="exporterHeaderInfo"></param>
    /// <returns></returns>
    public ExporterHeaderInfo Filter(ExporterHeaderInfo exporterHeaderInfo)
    {
        if (exporterHeaderInfo.DisplayName.Equals("姓名"))
        {
            exporterHeaderInfo.DisplayName = "name";
        }
        return exporterHeaderInfo;
    }
}

通过如上代码片段我们实现 IExporterHeaderFilter 接口,IExporterHeaderFilter以便支持多语言、动态控制列展示等场景

源码地址:https://github.com/dotnetcore/Magicodes.IE

原文链接:【https://www.cnblogs.com/yyfh/p/12218673.html

版权声明:若无特殊注明,本文为《奕独客》原创,转载请保留文章出处。
本文链接:ASP.NET 开源导入导出库Magicodes.IE导出Excel [https://www.yiduk.com/美文分享/27.html]
正文到此结束

热门推荐