使用NPOI导入导出excel,已经封装好Action可以直接调用

导出

效果图

使用方法

定义导出实体

class Student
{
    public int Id { get; set; }

    public string Name { get; set; }

    public bool IsBanZhang { get; set; }

    public static IEnumerable<Student> GetStudents()
    {
        return new List<Student>
        {
                new Student{Name="小强",Id=1,IsBanZhang=false},
        new Student{Name="小文",Id=2,IsBanZhang=true},
        new Student{Name="小黄",Id=3,IsBanZhang=false},
        new Student{Name="小刚",Id=3,IsBanZhang=false},
        };
    }
}

导出Action

public IActionResult DownExcel()
{
    var list = Student.GetStudents();
    var excelHeper = new ExcelHelper();
    var config = new List<ExcelGridModel> {
            new ExcelGridModel{name="Id",label="学号", align="left",},
            new ExcelGridModel{name="Name",label="姓名", align="left",},
            new ExcelGridModel{name="IsBanZhang",label="是否班长", align="left",},
    };

    var fileName = "a.excel";

    return excelHeper.ExcelDownload(list, config, fileName);
}

主要代码

导入导出代码

/// <summary>
/// 描 述：NPOI Excel DataTable操作类
/// </summary>
public class ExcelHelper
{
    #region Excel导出方法 ExcelDownload

    /// <summary>
    /// Excel导出下载
    /// </summary>
    /// <typeparam name="T">数据类型</typeparam>
    /// <param name="list">数据源</param>
    /// <param name="columnJson">导出列</param>
    /// <param name="fileName">下载文件名称</param>
    public ActionResult ExcelDownload<T>(IEnumerable<T> list, IEnumerable<ExcelGridModel> columnList, string fileName)
    {
        var excelConfig = ConvertExcelGridModelToConfig(columnList, fileName);

        var rowData = list.ToDataTable(columnList.Select(i => i.name));

        var stream = ExportMemoryStream(rowData, excelConfig);
        return new FileStreamResult(stream, MIMEType.xls) { FileDownloadName = JointXls(fileName) };

    }

    /// <summary>
    /// Excel导出下载
    /// </summary>
    /// <param name="dtSource">DataTable数据源</param>
    /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
    public ActionResult ExcelDownload(DataTable dtSource, IEnumerable<ExcelGridModel> columnList, string fileName)
    {
        var excelConfig = ConvertExcelGridModelToConfig(columnList, fileName);
        var stream = ExportMemoryStream(dtSource, excelConfig);
        return new FileStreamResult(stream, MIMEType.xls) { FileDownloadName = JointXls(fileName) };
    }

    /// <summary>
    /// Excel导出下载
    /// </summary>
    /// <param name="dtSource">DataTable数据源</param>
    /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
    public ActionResult ExcelDownload(DataTable dtSource, ExcelConfig excelConfig, string fileName)
    {
        var stream = ExportMemoryStream(dtSource, excelConfig);
        return new FileStreamResult(stream, MIMEType.xls) { FileDownloadName = JointXls(fileName) };
    }

    #endregion Excel导出方法 ExcelDownload

    #region DataTable导出到Excel文件excelConfig中FileName设置为全路径

    /// <summary>
    /// DataTable导出到Excel文件 Export()
    /// </summary>
    /// <param name="dtSource">DataTable数据源</param>
    /// <param name="excelConfig&quo