EPPuls Excel 导入导出

 /// <summary>
    /// Excel导出
    /// </summary>
    public static class ExcelHelper
    {
        /// <summary>
        /// 设置表格样式
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="bgColor">背景色</param>
        /// <param name="fontColor">字体颜色</param>
        /// <param name="bold">/粗体</param>
        /// <returns></returns>
        private static ExcelRange SetStyle(this ExcelRange cell, Color? bgColor = null, Color? fontColor = null, bool bold = false)
        {
            cell.Style.Fill.PatternType = ExcelFillStyle.Solid;                 //边框样式
            cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;   //水平居中对齐
            cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;       //垂直居中对齐
            cell.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); //边框颜色
            cell.Style.Font.Bold = bold;                                        //字体加粗
            cell.Style.Numberformat.Format = "@";                               //单元格格式
            cell.Style.Font.Color.SetColor(fontColor ?? Color.Black);           //文本颜色
            cell.Style.Fill.BackgroundColor.SetColor(bgColor ?? Color.White);   //背景颜色
            return cell;
        }


        private static string GetDescription(this PropertyInfo propertyInfo) => propertyInfo.GetCustomAttribute<DescriptionAttribute>()?.Description.Trim() ?? "";


        private static void ForEach<T>(this List<T> ts, Action<T, int> func)
        {

            for (int i = 0; i < ts.Count; i++) func(ts[i], i);
        
        }


        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ts"></param>
        /// <param name="colunms">自定义列头</param>
        /// <param name="ignoreField">忽略的字段</param>
        /// <returns></returns>
        public static FileContentResult ToExcelFixed<T>(this List<T> data, params string[] ignoreFields)
        {

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial; //使用免费的

            using (var ep = new ExcelPackage())
            {
                var columns = typeof(T).GetProperties().Where(x => x.GetDescription() != "" && !ignoreFields.Contains(x.Name)).ToList();

                var sheet = ep.Workbook.Worksheets.Add("Sheet1");

                data.ForEach((t, i) =>
                {

                    columns.ForEach((y, j) =>
                    {

                        if (j == 0) sheet.Cells[1, j + 1].SetStyle(Color.LightSteelBlue, Color.Black, true).Value = y.GetDescription(); //添加表头

                        sheet.Cells[i + 2, j + 1].SetStyle().Value = y.GetValue(t); //添加数据

                    });

                });

                sheet.Rows.Height = 24;

                sheet.Cells.AutoFitColumns();

                return new FileContentResult(ep.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            }
        }

        

        /// <summary>
        /// Excel导入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="file"></param>
        /// <param name="ignoreField"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static List<T> ToImportFixed<T>(this IFormFile file) where T:new() {

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            var result = new List<T>();

            var props = typeof(T).GetProperties().ToList();

            var colunms = new Dictionary<int, PropertyInfo>();

            using (var ep = new ExcelPackage(file.OpenReadStream()))
            {

                var sheet = ep.Workbook.Worksheets[0];

                for (int r = 1; r <= sheet.Dimension.End.Row; r++)
                {
                    T t = new T();

                    for (int c = 1; c <= sheet.Dimension.End.Column; c++)
                    {
                        if (r == 1)
                        {

                            var prop = props.Find(x => x.GetDescription() == sheet.Cells[1, c].Value + "");

                            if (prop == null) throw new Exception("文件格式不正确！");

                            colunms.Add(c, prop);

                            continue;

                        }
                        //去除空格
                        colunms[c].SetValue(t, (sheet.Cells[r, c].Value + "").Trim());

                    }

                    if (r > 1) result.Add(t);
                    
                }

            }

            return result;
        }
    }