.Net5导入导出execl表格

通过 using OfficeOpenXml 进行实现

using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using OfficeOpenXml;

namespace Sino
{
    /// <summary>
    /// 通用工具类
    /// </summary>
    public static class ExcelHelper
    {
        /// <summary>
        /// 导出
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="path"></param>
        /// <param name="data"></param>
        /// <param name="headers"></param>
        /// <returns></returns>
        public static bool ExportListToExcel<T>(string path, List<T> data, Dictionary<string, string> headers = null)
        {
            FileInfo file = new FileInfo(path);
            if (file.Exists)
            {
                file.Delete();
                file = new FileInfo(path);
            }

            using (var package = new ExcelPackage(file))
            {
                var worksheet = package.Workbook.Worksheets.Add("sheet1");
                worksheet.Cells.LoadFromCollection(data, true);

                if (headers != null)
                {
                    for (int i = 0; i < worksheet.Dimension.End.Column; i++)
                    {
                        var name = worksheet.Cells[1, i + 1]?.Value?.ToString();
                        if (string.IsNullOrEmpty(name) == false && headers.ContainsKey(name))
                        {
                            worksheet.Cells[1, i + 1].Value = headers[name];
                        }
                    }
                }
                worksheet.DefaultColWidth = 20;
                package.Save();
            }
            return true;
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="existingFile"></param>
        /// <param name="headers"></param>
        /// <returns></returns>
        public static List<T> LoadFromExcel<T>(FileInfo existingFile, Dictionary<string, string> headers = null) where T : new()
        {
            List<T> resultList = new List<T>();
            Dictionary<string, int> dictHeader = new Dictionary<string, int>();

            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];

                int colStart = worksheet.Dimension.Start.Column;  //工作区开始列
                int colEnd = worksheet.Dimension.End.Column;       //工作区结束列
                int rowStart = worksheet.Dimension.Start.Row;       //工作区开始行号
                int rowEnd = worksheet.Dimension.End.Row;       //工作区结束行号

                //将每列标题添加到字典中
                for (int i = colStart; i <= colEnd; i++)
                {
                    dictHeader[worksheet.Cells[rowStart, i].Value.ToString()] = i;
                }

                List<PropertyInfo> propertyInfoList = new List<PropertyInfo>(typeof(T).GetProperties());
                string headerName = "";
                for (int row = rowStart + 1; row <= rowEnd; row++)
                {
                    T result = new T();
                    //为对象T的各属性赋值
                    foreach (PropertyInfo p in propertyInfoList)
                    {
                        try
                        {
                            headerName = p.Name;
                            //导入的时候如果替换过header属性则需要替换过来在复给类值
                            if (headers != null)
                            {
                                if (headers.ContainsKey(headerName))
                                    headerName = headers[headerName];
                            }
                            ExcelRange cell = worksheet.Cells[row, dictHeader[headerName]]; //与属性名对应的单元格

                            if (cell.Value == null)
                                continue;
                            switch (p.PropertyType.Name.ToLower())
                            {
                                case "string":
                                    p.SetValue(result, cell.GetValue<String>());
                                    break;
                                case "int16":
                                    p.SetValue(result, cell.GetValue<Int16>());
                                    break;
                                case "int32":
                                    p.SetValue(result, cell.GetValue<Int32>());
                                    break;
                                case "int64":
                                    p.SetValue(result, cell.GetValue<Int64>());
                                    break;
                                case "decimal":
                                    p.SetValue(result, cell.GetValue<Decimal>());
                                    break;
                                case "double":
                                    p.SetValue(result, cell.GetValue<Double>());
                                    break;
                                case "datetime":
                                    p.SetValue(result, cell.GetValue<DateTime>());
                                    break;
                                case "boolean":
                                    p.SetValue(result, cell.GetValue<Boolean>());
                                    break;
                                case "byte":
                                    p.SetValue(result, cell.GetValue<Byte>());
                                    break;
                                case "char":
                                    p.SetValue(result, cell.GetValue<Char>());
                                    break;
                                case "single":
                                    p.SetValue(result, cell.GetValue<Single>());
                                    break;
                                default:
                                    break;
                            }
                        }
                        catch (KeyNotFoundException ex)
                        { }
                    }
                    resultList.Add(result);
                }
            }
            return resultList;
        }



    }
}