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

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› c#›C#教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

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

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

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

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

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

上一篇导出excel，是导出已知固定列，有时候我们根本就不知道有几列、列名是什么，因此这些动态列，可以用Dictionary<string,string>接收。

1、实体Student上加上一个字段Dictionarys

Student.cs

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

        public String Code { get; set; }

        public Dictionary<string, string> Dictionarys { get; set; }
    }

2、表头表体类上加上动态列的添加表头与表体

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]);
                }
            }
        }

        /// <summary>
        /// 添加动态表头
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="headerTexts"></param>
        /// <param name="headerTextsDictionary"></param>
        public  static void AddHeader(ExcelWorksheet sheet, string[] headerTexts, string[] headerTextsDictionary)
        {                      
            for (var i = 0; i < headerTextsDictionary.Length; i++)
            {
                AddHeader(sheet, i + 1 + headerTexts.Length, headerTextsDictionary[i]);
            }
        }

        /// <summary>
        /// 添加动态数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="items"></param>
        /// <param name="propertySelectors"></param>
        /// <param name="dictionaryKeys"></param>

        public static void AddObjects(ExcelWorksheet sheet, int startRowIndex, IList<Student> items, Func<Student, object>[] propertySelectors, List<string> dictionaryKeys)
        {           
            for (var i = 0; i < items.Count; i++)
            {
                for (var j = 0; j < dictionaryKeys.Count; j++)
                {
                    sheet.Cells[i + startRowIndex, j + 1 + propertySelectors.Length].Value = items[i].Dictionarys[dictionaryKeys[j]];
                }
            }
                    
        }


        public static List<String> GetDictionaryKeys(Dictionary<string, string> dics)
        {
            List<string> resultList = new List<string>();
            foreach (KeyValuePair<string, string> kvp in dics)
            {
                resultList.Add(kvp.Key);
            }
            return resultList;
        }

    }

3、修改Main方法，导出Excel

主要代码如下：

 //获得数据
            List<Student> studentList = new List<Student>();
            for (int i = 0; i < 10; i++)
            {
                Student s = new Student();
                s.Code = "c" + i;
                s.Name = "n" + i;
                studentList.Add(s);
            }

            //获得不固定数据
            for (int i = 0; i < studentList.Count; i++)
            {
                Dictionary<string, string> dictionarys = new Dictionary<string, string>();
                dictionarys.Add("D1", "d1" + i);
                dictionarys.Add("D2", "d2" + i);
                studentList[i].Dictionarys = dictionarys;
            }


            //创建excel
            string fileName = @"d:\" + "导出excel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            FileInfo newFile = new FileInfo(fileName);
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                #region 固定列
                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);
                }

                #endregion

                #region 不固定列
                List<ExcelExportDto<Dictionary<string, string>>> excelExportDictionaryDtoList = new List<ExcelExportDto<Dictionary<string, string>>>();
                List<string> columnsNameDictionaryList = new List<string>();
                List<string> dictionaryKeys = EpplusHelper.GetDictionaryKeys(studentList[0].Dictionarys);

                if (studentList.Count > 0)
                {
                    for (int i = 0; i < dictionaryKeys.Count; i++)
                    {
                        var index = i;
                        excelExportDictionaryDtoList.Add(new ExcelExportDto<Dictionary<string, string>>(dictionaryKeys[i], _ => _.FirstOrDefault(q => q.Key == dictionaryKeys[i]).Value));
                    }
                    foreach (var item in excelExportDictionaryDtoList)
                    {
                        columnsNameDictionaryList.Add(item.ColumnName);
                    }
                }
                #endregion

                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Test");
                worksheet.OutLineApplyStyle = true;
                //添加表头
                EpplusHelper.AddHeader(worksheet, columnsNameList.ToArray());
                //添加数据
                EpplusHelper.AddObjects(worksheet, 2, studentList, columnsValueList.ToArray());
                if (studentList.Count > 0)
                {
                    //添加动态表头
                    EpplusHelper.AddHeader(worksheet, columnsNameList.ToArray(), columnsNameDictionaryList.ToArray());
                    //添加动态数据
                    EpplusHelper.AddObjects(worksheet, 2, studentList, columnsValueList.ToArray(), dictionaryKeys);
                }
                package.Save();
            }