C# 使用Epplus导出Excel [1]：导出固定列数据

 

C# 使用Epplus导出Excel [1]：导出固定列数据

C# 使用Epplus导出Excel [2]：导出动态列数据

C# 使用Epplus导出Excel [3]：合并列连续相同数据

C# 使用Epplus导出Excel [4]：合并指定行

C# 使用Epplus导出Excel [5]：样式

 

最近项目用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文件并打开

完整代码详情请移步我的github：https://github.com/gordongaogithub/ExportExcelByEpplus.git