安装依赖包

Install-Package EPPlus

代码

相关方法

        /// <summary>
        /// Excel内容类型
        /// </summary>
        public static string ExcelContentType => "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        
        #region 导出Excel
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <param name="savePath"></param>
        /// <param name="heading"></param>
        /// <param name="columnsToTake"></param>
        /// <param name="saveNameRule">导出文件命名规则</param>
        /// <returns></returns>
        public static string ExportToExcel<T>(List<T> data, string savePath, string heading, string[] columnsToTake, SaveNameRule saveNameRule = SaveNameRule.Guid)
        {
            if (!Directory.Exists(savePath))
                Directory.CreateDirectory(savePath);
            return ExportExcel(ListToDataTable(data), heading, savePath, columnsToTake, false, saveNameRule);
        } 
        #endregion
        #region List转DataTable
        /// <summary>
        /// List转DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        private static DataTable ListToDataTable<T>(List<T> data)
        {
            DataTable dataTable = new DataTable();
            Type type = typeof(T);
            var properties = type.GetProperties();
            int count = 0;
            foreach (var item in properties)
            {
                if (item.IsDefined(typeof(DescriptionAttribute), true))
                    dataTable.Columns.Add(item.Name, Nullable.GetUnderlyingType(item.PropertyType) ?? item.PropertyType);
                else
                    count++;
            }
            object[] values = new object[properties.Count() - count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                   
                        if (properties[i].IsDefined(typeof(DescriptionAttribute), true))
                            values[i] = properties[i].GetValue(item)??"";
                    
                   
                }
                dataTable.Rows.Add(values);
            }
            return dataTable;
        } 
        #endregion

        #region 导出Excel方法
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dataTable">数据源</param>
        /// <param name="heading">工作簿Worksheet</param>
        /// <param name="savePath">保存路径</param>
        /// <param name="columnsToTake">要导出的列</param>
        /// <param name="isAutoColumnWidth">是否自适应列宽</param>
        /// <param name="saveNameRule">导出文件命名规则</param>
        /// <returns></returns>
        private static string ExportExcel(DataTable dataTable, string heading, string savePath, string[] columnsToTake, bool isAutoColumnWidth = false, SaveNameRule saveNameRule = SaveNameRule.Guid)
        {
            string result = string.Empty;
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            if (!Directory.Exists(savePath))
                Directory.CreateDirectory(savePath);
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(string.IsNullOrWhiteSpace(heading) ? "sheet1" : heading);
                for (int i = 0; i < columnsToTake.Length; i++)
                {
                    var cell = workSheet.Cells[1, i + 1];
                    cell.AutoFitColumns();
                    cell.Style.Font.Bold = true;
                    cell.Value = columnsToTake[i];
                }
                for (int i = 1; i <= dataTable.Rows.Count; i++)
                {
                    for (int j = 0; j < dataTable.Columns.Count; j++)
                    {
                        if (j > columnsToTake.Length-1)
                            continue;
                        workSheet.Cells[i + 1, j + 1].Value = dataTable.Rows[i - 1][j];
                        if (dataTable.Rows[i - 1][j].GetType() == typeof(DateTime))
                            workSheet.Cells[i + 1, j + 1].Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";
                    }
                }
                if (isAutoColumnWidth)
                    workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
                string id = "";
                switch (saveNameRule)
                {
                    case SaveNameRule.Guid:
                        id = Guid.NewGuid().ToString();
                        break;
                    case SaveNameRule.NowTime:
                        id = DateTime.Now.ToString("yyyyMMddHHmmssfff");
                        break;
                    default:
                        break;
                }

                result = savePath + id + ".xlsx";
                package.SaveAs(new FileInfo(result));
            }
            return result;
        }
        #endregion
        
        #region 获取所有属性的描述
        /// <summary>
        /// 获取所有属性的描述
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static string[] GetColNames<T>()
        {
            List<string> lst = new List<string>();

            PropertyInfo[] peroperties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo property in peroperties)
            {

                object[] objs = property.GetCustomAttributes(typeof(DescriptionAttribute), true);
                if (objs.Length > 0)
                {
                    lst.Add(((DescriptionAttribute)objs[0]).Description);
                }
            }
            return lst.ToArray();
        }
        #endregion

        
        public enum SaveNameRule
        {
            /// <summary>
            /// Guid
            /// </summary>
            Guid,
            /// <summary>
            /// 当前时间 yyyyMMddHHmmssfff
            /// </summary>
            NowTime

        }

测试模型类

  public class Student
  {
      [Description("编号")]
      public int Id { get; set; }
      [Description("姓名")]
      public string Name { get; set; }
  }

测试导出

    var res = ExportToExcel<Student>(new List<Student>
        {
            new Student{ Id = 1, Name = "张三" },
            new Student { Id = 2, Name = "李四" }
        }, "D:\\", null, GetColNames<Student>(), SaveNameRule.NowTime);