C# 使用Epplus导出Excel [2]：导出动态列数据
C# 使用Epplus导出Excel [1]：导出固定列数据
C# 使用Epplus导出Excel [2]：导出动态列数据
C# 使用Epplus导出Excel [3]：合并列连续相同数据
上一篇导出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(); }
完整代码详情请移步我的github：https://github.com/gordongaogithub/ExportDictionaryExcelByEpplus.git
