如何写通用的list转Excel表格

如何写通用的list转Excel表格

编译环境

Asp.Net Core \ C#7.0 / WebApi

1.首先来一个核心的扩展类

上代码（核心代码）这里用到了反射

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using OfficeOpenXml;//EPPlus.Core包
using System.Reflection;

namespace ExportExcel
{
    public static class Helper
    {
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="keyValues">key为列名</param>
        /// <returns></returns>
        public static byte[] ToExcel<T>(this List<T> list,Dictionary<string,string> keyValues)
        {
            //EPPlus.Core包
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet");
                int column = 1;//列
                foreach(string key in keyValues.Keys)
                {
                    worksheet.Cells[1, column].Value = key;
                    column++;
                }
                
                int row = 2;//行
                foreach(var date in list)
                {
                    column = 1;
                    //反射获取数据属性
                    PropertyInfo[] pi = date.GetType().GetProperties();
                    foreach(var value in keyValues.Values)
                    {
                        var info = pi.Where(e => e.Name == value).FirstOrDefault();
                        if(null != info)
                        {
                            worksheet.Cells[row, column].Value = info.GetValue(date);
                        }
                        else
                        {
                            worksheet.Cells[row, column].Value = "";
                        }
                        column++;
                    }
                    row++;
                }
                var content = package.GetAsByteArray();
                if(null == content)
                {
                    return null;
                }
                return content;
            }
        }
    }
}

2.其次就是如何调用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace ExportExcel.Controllers
{
    [Route("[controller]")]
    [ApiController]
    public class ExportExcelController : ControllerBase
    {
        [HttpGet(nameof(ExportExcel))]
        public IActionResult ExportExcel2()
        {
            var list = new List<Student>();
            for(var i =0;i<10;i++)
            {
                list.Add(new Student
                {
                    Age = "12",
                    ClassName = "软件1702",
                    IdCard = "2134234234",
                    Name = "中宋"
                });
            }
            var keyValues = new Dictionary<string, string>();
            keyValues.Add("姓名","Name");
            keyValues.Add("班级", "ClassName");
            keyValues.Add("年龄", "Age");
            keyValues.Add("身份证号", "IdCard");
            var content =  list.ToExcel(keyValues);
            return File(content,"application/ms-excel",$"{Guid.NewGuid().ToString("N")}.xlsx");
        }
    }
}

3. 使用到的模型

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace ExportExcel
{
    public class Student
    {
        public string Name { get; set; }
        public string Age { get; set; }
        public string ClassName { get; set; }
        public string IdCard { get; set; }
    }
}