C# 使用Epplus导出Excel

最近项目用Epplus导出Excel，感觉挺好用的，现在我梳理一下如何导出，运行项目是.net Framework控制台应用。

首先NuGet上安装Epplus

1、建一个实体Student

Student.cs

 public class Student    {        public String Name { get; set; }        public String Code { get; set; }    }

 

2、建一个导出类

ExcelExportDto.cs

 public class ExcelExportDto<T>    {        public ExcelExportDto(string columnName, Func<T, object> columnValue)        {            ColumnName = columnName;            ColumnValue = columnValue;        }        public string ColumnName { get; set; }        public Func<T, object> ColumnValue { get; set; }    }

 

3、添加表头表体的类

EpplusHelper.cs

 public static class EpplusHelper    {        /// <summary>        /// 添加表头        /// </summary>        /// <param name="sheet"></param>        /// <param name="headerTexts"></param>        public static void AddHeader(ExcelWorksheet sheet, params string[] headerTexts)        {            for (var i = 0; i < headerTexts.Length; i  )            {                AddHeader(sheet, i   1, headerTexts[i]);            }        }        /// <summary>        /// 添加表头        /// </summary>        /// <param name="sheet"></param>        /// <param name="columnIndex"></param>        /// <param name="headerText"></param>        public static void AddHeader(ExcelWorksheet sheet, int columnIndex, string headerText)        {            sheet.Cells[1, columnIndex].Value = headerText;            sheet.Cells[1, columnIndex].Style.Font.Bold = true;        }        /// <summary>        /// 添加数据        /// </summary>        /// <param name="sheet"></param>        /// <param name="startRowIndex"></param>        /// <param name="items"></param>        /// <param name="propertySelectors"></param>        public  static void AddObjects(ExcelWorksheet sheet, int startRowIndex, IList<Student> items, Func<Student, object>[] propertySelectors)        {            for (var i = 0; i < items.Count; i  )            {                for (var j = 0; j < propertySelectors.Length; j  )                {                    sheet.Cells[i   startRowIndex, j   1].Value = propertySelectors[j](items[i]);                }            }        }

4、在Main方法中写代码，导出Excel

class Program    {        static void Main(string[] args)        {            //获得数据            List<Student> studentList = new List<Student>();            for (int i = 0; i < 10; i  )            {                Student s = new Student();                s.Code = "c"   i;                s.Name = "s"   i;                studentList.Add(s);            }            //创建excel            string fileName = @"d:\"   "导出excel"   DateTime.Now.ToString("yyyyMMddHHmmss")   ".xlsx";            FileInfo newFile = new FileInfo(fileName);            using (ExcelPackage package = new ExcelPackage(newFile))            {                List<ExcelExportDto<Student>> excelExportDtoList = new List<ExcelExportDto<Student>>();                excelExportDtoList.Add(new ExcelExportDto<Student>("Code", _ => _.Code));                excelExportDtoList.Add(new ExcelExportDto<Student>("Name", _ => _.Name));                List<string> columnsNameList = new List<string>();                List<Func<Student, object>> columnsValueList = new List<Func<Student, object>>();                foreach (var item in excelExportDtoList)                {                    columnsNameList.Add(item.ColumnName);                    columnsValueList.Add(item.ColumnValue);                }                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Test");                worksheet.OutLineApplyStyle = true;                //添加表头                EpplusHelper.AddHeader(worksheet, columnsNameList.ToArray());                //添加数据                EpplusHelper.AddObjects(worksheet, 2, studentList, columnsValueList.ToArray());                package.Save();            }        }    }

运行控制台应用，在D盘找到导出的excel文件并打开