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

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

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

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 = ; i < headerTexts.Length; i++)
{
AddHeader(sheet, i + , 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[, columnIndex].Value = headerText;
sheet.Cells[, 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 = ; i < items.Count; i++)
{
for (var j = ; j < propertySelectors.Length; j++)
{
sheet.Cells[i + startRowIndex, j + ].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 = ; i < headerTextsDictionary.Length; i++)
{
AddHeader(sheet, i + + 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 = ; i < items.Count; i++)
{
for (var j = ; j < dictionaryKeys.Count; j++)
{
sheet.Cells[i + startRowIndex, j + + 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 = ; i < ; i++)
{
Student s = new Student();
s.Code = "c" + i;
s.Name = "n" + i;
studentList.Add(s);
} //获得不固定数据
for (int i = ; 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[].Dictionarys); if (studentList.Count > )
{
for (int i = ; 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, , studentList, columnsValueList.ToArray());
if (studentList.Count > )
{
//添加动态表头
EpplusHelper.AddHeader(worksheet, columnsNameList.ToArray(), columnsNameDictionaryList.ToArray());
//添加动态数据
EpplusHelper.AddObjects(worksheet, , studentList, columnsValueList.ToArray(), dictionaryKeys);
}
package.Save();
}