C# .Net6 使用Epplus库作Excel导出，处理一对多数据，合并单元格——202404年
一对多导出方法可大致处理以下需求：
1. 标题行别名设定（如在属性上标注[Display(Name = “编号”)]），存在集合属性（集合属性适配了集合普通类型属性和集合对象类型属性）时自动将标题行设为占据两行
2. 布尔转为是与否，导出时设为下拉
3. 枚举转为Display中的Name名称，可实现将枚举从英文转为中文，导出时设为下拉
4. 导出可处理一对多的数据结果，其中可合并单元格，但不知道性能行不行，自己使用看看
5. 基础样式已经设置好了，自己可根据需求修改适配
6. 检测Display属性是否存在，不存在则忽略该列（属性）的导出
7. 检测导出类模型是否有[Display(Name = “…”, Description = “…”)]特性，Name为表名，Description 为文档说明
8. 兼容普通导出以及一对一对象属性导出
注：工具使用了Magicodes.IE（版本时2.7）内置的低版本Epplus，另外5.0以上的高版本Epplus是需要授权的。大家也可以使用Magicodes.IE封装好的导入导出方法，需注意它的导入操作会需要将Excel文件上传存储到服务器本地作解析。
- [一对多导入 ] 导入暂时还没有一对多导入，后续补充~~~~~~~~
ExcelHelper工具分享：
using OfficeOpenXml.DataValidation;
using OfficeOpenXml.Style;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.DirectoryServices.ActiveDirectory;
using SixLabors.ImageSharp;
namespace DMS.Common.Helper
{
public class ExcelTool
{
/// <summary>
/// 导出Excel,采用流导出文件，单sheet，一对一类型数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="sheetName"></param>
/// <param name="fileName"></param>
/// <param name="customPath">自定义路径</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static string ExportExcel<T>(List<T> list, Stream stream, string fileName, List<string> values = null)
{
try
{
if (list == null || list.Count == 0)
{
list = new List<T>();
return DownloadImportTemplate(list, stream, fileName, values);
}
List<string> titleName = new();
Dictionary<string, Dictionary<object, string>> enumColInfo = new();
string sFileName = $"{fileName}-{DateTime.Now.ToString("yyyyMMdd")}.xlsx";
using (ExcelPackage package = new(new FileInfo(sFileName)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(fileName);
#region 设置导出格式
//设置导出格式
var properties = typeof(T).GetProperties();
foreach (var prop in properties)
{
//标题行处理
DisplayAttribute displayAttribute = prop.GetCustomAttribute<DisplayAttribute>();
if (displayAttribute != null)
{
titleName.Add(displayAttribute.Name ?? prop.Name);
}
else
{
titleName.Add(prop.Name);
}
//时间类型处理
if (prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(DateTime?))
{
var columnIndex = Array.IndexOf(typeof(T).GetProperties(), prop) + 1;
var address = $"{worksheet.Cells[2, columnIndex].Address}:{worksheet.Cells[list.Count + 1, columnIndex].Address}";
worksheet.Cells[address].Style.Numberformat.Format = "yyyy-MM-dd";
}
//枚举类型处理
if (prop.PropertyType.IsEnum)
{
Dictionary<object, string> valueMapping = new Dictionary<object, string>();
var enumType = prop.PropertyType;
var enumValues = Enum.GetValues(prop.PropertyType);
var columnIndex = Array.IndexOf(typeof(T).GetProperties(), prop) + 1;
//下拉选择
var dd = worksheet.Cells[2, columnIndex, 20000, columnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
dd.AllowBlank = true;
foreach (var enumValue in enumValues)
{
if (enumValue != null)
{
var memberInfo = enumType.GetMember(enumValue.ToString());
var enumDisplayAttribute = memberInfo[0].GetCustomAttribute<DisplayAttribute>();
if (enumDisplayAttribute != null)
{
var displayName = enumDisplayAttribute.Name;
valueMapping.Add(enumValue, displayName);
dd.Formula.Values.Add($"{displayName}");
}
else
{
valueMapping.Add(enumValue, enumValue.ToString());
dd.Formula.Values.Add($"{enumValue.ToString()}");
}
}
}
var attributes = prop.GetCustomAttributes(true);
var propDisplayAttribute = attributes.FirstOrDefault(a => a is DisplayAttribute) as DisplayAttribute;
string propertyDisplayName = propDisplayAttribute?.Name ?? prop.Name;
enumColInfo.Add(propertyDisplayName, valueMapping);
}
//布尔类型
if (prop.PropertyType == typeof(bool) || prop.PropertyType == typeof(bool?))
{
var columnIndex = Array.IndexOf(typeof(T).GetProperties(), prop) + 1;
//下拉选择
var dd = worksheet.Cells[2, columnIndex, 20000, columnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
dd.AllowBlank = true;
dd.Formula.Values.Add("是");
dd.Formula.Values.Add("否");
}
}
#endregion 设置导出格式
#region 样式设置
//单元格自动适应大小
worksheet.Cells.Style.ShrinkToFit = true;
//单元格自动换行
worksheet.Cells.Style.WrapText = true;
//全部字段导出
worksheet.Cells.LoadFromCollection(list, true, OfficeOpenXml.Table.TableStyles.Light13);
//获取最后的行数
var endCellRow = worksheet.Dimension.End.Row;
//获取最后的列数
var endCellColumn = worksheet.Dimension.End.Column;
//获得第一行有数据的最后一个单元格
string rowCell = "null";
var lastRowCell = worksheet.Cells.LastOrDefault(c => c.Start.Row == 1);
if (lastRowCell != null) rowCell = lastRowCell.Address;
#region 设置全局样式
for (int i = 1; i <= endCellColumn; i++)
{
worksheet.Column(i).Width = 25;//列宽
}
// 设置样式范围
var globalRange = worksheet.Cells[$"A1:{worksheet.Cells[endCellRow, endCellColumn].Address}"];
// 设置字体大小
globalRange.Style.Font.Size = 12;
// 设置字体粗细
globalRange.Style.Font.Bold = false;
//字体颜色
globalRange.Style.Font.Color.SetColor(SixLabors.ImageSharp.Color.Black);
// 设置水平对齐
globalRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// 设置垂直对齐
globalRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
#endregion 设置全局样式
#region 设置标题行样式
var firstTitle = "A1" + ":" + rowCell;
//字体大小
worksheet.Cells[firstTitle].Style.Font.Size = 14;
//字体粗细
worksheet.Cells[firstTitle].Style.Font.Bold = true;
//字体颜色
worksheet.Cells[firstTitle].Style.Font.Color.SetColor(SixLabors.ImageSharp.Color.White);
//左右居中
worksheet.Cells[firstTitle].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//上下居中
worksheet.Cells[firstTitle].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
//设置第一行行高
worksheet.Row(1).Height = 20;
#endregion 设置标题行样式
#endregion 样式设置
#region 标题行别名设置
//导出标题行别名设置
if (values != null && values.Count > 0)
{
for (int i = 0; i < values.Count; i++)
{
//导出标题行别名设置
worksheet.Cells[1, i + 1].Value = values[i];
//枚举处理
for (int j = 0; j < list.Count; j++)
{
if (enumColInfo.ContainsKey(values[i]))
{
var valueMappings = enumColInfo[values[i]];
worksheet.Cells[2 + j, i + 1].Value = valueMappings[worksheet.Cells[2 + j, i + 1].Value];
}
}
}
}
else
{
if (titleName != null && titleName.Count > 0)
{
for (int i = 0; i < titleName.Count; i++)
{
worksheet.Cells[1, i + 1].Value = titleName[i];
//枚举处理
for (int j = 0; j < list.Count; j++)
{
if (enumColInfo.ContainsKey(titleName[i]))
{
var valueMappings = enumColInfo[titleName[i]];
worksheet.Cells[2 + j, i + 1].Value = valueMappings[worksheet.Cells[2 + j, i + 1].Value];
}
}
}
}
}
#endregion 标题行别名设置
package.SaveAs(stream);
}
return sFileName;
}
catch (Exception ex)
{
throw new Exception("Failed to export:", ex);
}
}
/// <summary>
/// 导出Excel,采用流导出文件，单sheet，一对多类型数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="stream"></param>
/// <param name="fileName"></param>
/// <param name="description"></param>
/// <param name="values"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static string ExportExcel<T>(List<T> list, Stream stream, string fileName = null, string description = null)
{
try
{
if (list == null || list.Count == 0)
{
list = new List<T>();
}
bool hasListProperty = false;
if (fileName == null || description == null)
{
// 获取泛型类型T的类型信息
Type type = typeof(T);
// 获取类级别的Display特性
var typeDisplayAttribute = type.GetCustomAttribute<DisplayAttribute>();
if (typeDisplayAttribute != null)
{
fileName = fileName != null ? fileName : typeDisplayAttribute.Name ?? "未命名";
description = description != null ? description : typeDisplayAttribute.Description ?? "";
}
}
List<string> titleName = new();
Dictionary<string, Dictionary<object, string>> enumColInfo = new();
string sFileName = $"{fileName}-{DateTime.Now.ToString("yyyyMMdd")}.xlsx";
using (ExcelPackage package = new(new FileInfo(sFileName)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(fileName);
#region 数据赋值
if (description == null || description == "")
{
//标题行设置
TitleRowSet<T>(worksheet, ref hasListProperty, 1, 1);
if (hasListProperty)
{
//数据行设置
DataRowSet(worksheet, list, 3);
}
else
{
//数据行设置
DataRowSet(worksheet, list, 2);
}
}
else
{
//描述行
DescriptionRowSet<T>(worksheet, description);
//标题行设置
TitleRowSet<T>(worksheet, ref hasListProperty, 2, 1);
if (hasListProperty)
{
//数据行设置
DataRowSet(worksheet, list, 4);
}
else
{
//数据行设置
DataRowSet(worksheet, list, 3);
}
}
#endregion 数据赋值
#region 全局样式设定
//单元格自动适应大小
worksheet.Cells.Style.ShrinkToFit = true;
//单元格自动换行
worksheet.Cells.Style.WrapText = true;
//获取最后的行数
var endCellRow = worksheet.Dimension.End.Row;
//获取最后的列数
var endCellColumn = worksheet.Dimension.End.Column;
//设置列宽
for (int i = 1; i <= endCellColumn; i++)
{
worksheet.Column(i).Width = 25;
}
#endregion 全局样式设定
#region 设置枚举与布尔类型为下拉
//设置枚举与布尔类型为下拉
if (description == null || description == "")
{
if (hasListProperty)
{
AddDropDownListsForBooleanAndEnum<T>(worksheet, 3, 1, endCellRow);
}
else
{
AddDropDownListsForBooleanAndEnum<T>(worksheet, 2, 1, endCellRow);
}
}
else
{
if (hasListProperty)
{
AddDropDownListsForBooleanAndEnum<T>(worksheet, 4, 1, endCellRow);
}
else
{
AddDropDownListsForBooleanAndEnum<T>(worksheet, 3, 1, endCellRow);
}
}
#endregion 设置枚举与布尔类型为下拉
package.SaveAs(stream);
}
return sFileName;
}
catch (Exception ex)
{
throw new Exception("导出失败");
}
}
/// <summary>
/// 下载导入模板-用于单行数据,全字段导出
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">示例数据</param>
/// <param name="stream"></param>
/// <param name="fileName">下载文件名</param>
/// <param name="values">导出标题行</param>
/// <returns></returns>
public static string DownloadImportTemplate<T>(List<T> list, Stream stream, string fileName, List<string> values = null)
{
try
{
List<string> titleName = new();
Dictionary<string, Dictionary<object, string>> enumColInfo = new();
string sFileName = $"{fileName}-{DateTime.Now.ToString("yyyyMMdd")}.xlsx";
using (ExcelPackage package = new(new FileInfo(sFileName)))
{
//判断该文件是否存在
if (package.File.Exists)
{
package.SaveAs(stream);
return sFileName;
}
// 添加worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(fileName);
//设置导出格式
var properties = typeof(T).GetProperties();
foreach (var prop in properties)
{
//标题行处理
DisplayAttribute displayAttribute = prop.GetCustomAttribute<DisplayAttribute>();
if (displayAttribute != null)
{
titleName.Add(displayAttribute.Name ?? prop.Name);
}
else
{
titleName.Add(prop.Name);
}
if (prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(DateTime?))
{
var columnIndex = Array.IndexOf(typeof(T).GetProperties(), prop) + 1;
var address = $"{worksheet.Cells[2, columnIndex].Address}:{worksheet.Cells[1 + 1, columnIndex].Address}";
worksheet.Cells[address].Style.Numberformat.Format = "yyyy-MM-dd";
}
//枚举类型处理
if (prop.PropertyType.IsEnum)
{
Dictionary<object, string> valueMapping = new Dictionary<object, string>();
var enumType = prop.PropertyType;
var enumValues = Enum.GetValues(prop.PropertyType);
var columnIndex = Array.IndexOf(typeof(T).GetProperties(), prop) + 1;
//下拉选择
var dd = worksheet.Cells[2, columnIndex, 20000, columnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
dd.AllowBlank = true;
foreach (var enumValue in enumValues)
{
if (enumValue != null)
{
var memberInfo = enumType.GetMember(enumValue.ToString());
var enumDisplayAttribute = memberInfo[0].GetCustomAttribute<DisplayAttribute>();
if (enumDisplayAttribute != null)
{
var displayName = enumDisplayAttribute.Name;
valueMapping.Add(enumValue, displayName);
dd.Formula.Values.Add($"{displayName}");
}
else
{
valueMapping.Add(enumValue, enumValue.ToString());
dd.Formula.Values.Add($"{enumValue.ToString()}");
}
}
}
var attributes = prop.GetCustomAttributes(true);
var propDisplayAttribute = attributes.FirstOrDefault(a => a is DisplayAttribute) as DisplayAttribute;
string propertyDisplayName = propDisplayAttribute?.Name ?? prop.Name;
enumColInfo.Add(propertyDisplayName, valueMapping);
}
//布尔类型
if (prop.PropertyType == typeof(bool) && prop.PropertyType == typeof(bool?))
{
var columnIndex = Array.IndexOf(typeof(T).GetProperties(), prop) + 1;
//下拉选择
var dd = worksheet.Cells[2, columnIndex, 20000, columnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
dd.AllowBlank = true;
dd.Formula.Values.Add("是");
dd.Formula.Values.Add("否");
}
}
//单元格自动适应大小
worksheet.Cells.Style.ShrinkToFit = true;
//单元格自动换行
worksheet.Cells.Style.WrapText = true;
//全部字段导出
worksheet.Cells.LoadFromCollection(list, true, OfficeOpenXml.Table.TableStyles.Light13);
#region 样式设置
//获取最后的行数
var endCellRow = worksheet.Dimension.End.Row;
//获取最后的列数
var endCellColumn = worksheet.Dimension.End.Column;
//获得第一行有数据的最后一个单元格
string rowCell = "null";
var lastRowCell = worksheet.Cells.LastOrDefault(c => c.Start.Row == 1);
if (lastRowCell != null) rowCell = lastRowCell.Address;
#region 设置全局样式
for (int i = 1; i <= endCellColumn; i++)
{
//列宽
worksheet.Column(i).Width = 20;
}
// 设置样式范围
var globalRange = worksheet.Cells["A1:Z10000"];
// 设置字体大小
globalRange.Style.Font.Size = 12;
// 设置字体粗细
globalRange.Style.Font.Bold = false;
// 字体颜色
globalRange.Style.Font.Color.SetColor(SixLabors.ImageSharp.Color.Black);
// 设置水平对齐
globalRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// 设置垂直对齐
globalRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
#endregion 设置全局样式
#region 设置标题行样式
var firstTitle = "A1" + ":" + rowCell;
//字体大小
worksheet.Cells[firstTitle].Style.Font.Size = 14;
//字体粗细
worksheet.Cells[firstTitle].Style.Font.Bold = true;
//字体颜色
worksheet.Cells[firstTitle].Style.Font.Color.SetColor(SixLabors.ImageSharp.Color.White);
//左右居中
worksheet.Cells[firstTitle].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//上下居中
worksheet.Cells[firstTitle].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
//设置第一行行高
worksheet.Row(1).Height = 20;
#endregion 设置标题行样式
#endregion 样式设置
//导出标题行别名设置
if (values != null && values.Count > 0)
{
for (int i = 0; i < values.Count; i++)
{
//导出标题行别名设置
worksheet.Cells[1, i + 1].Value = values[i];
//枚举处理
for (int j = 0; j < list.Count; j++)
{
if (enumColInfo.ContainsKey(values[i]))
{
var valueMappings = enumColInfo[values[i]];
worksheet.Cells[2 + j, i + 1].Value = valueMappings[worksheet.Cells[2 + j, i + 1].Value];
}
}
}
}
else
{
if (titleName != null && titleName.Count > 0)
{
for (int i = 0; i < titleName.Count; i++)
{
worksheet.Cells[1, i + 1].Value = titleName[i];
//枚举处理
for (int j = 0; j < list.Count; j++)
{
if (enumColInfo.ContainsKey(titleName[i]))
{
var valueMappings = enumColInfo[titleName[i]];
worksheet.Cells[2 + j, i + 1].Value = valueMappings[worksheet.Cells[2 + j, i + 1].Value];
}
}
}
}
}
package.SaveAs(stream);
}
return sFileName;
}
catch (Exception ex)
{
throw new Exception("Failed to export");
}
}
/// <summary>
/// 导入数据-适用于只有一个sheet
/// </summary>
/// <param name="stream"></param>
/// <param name="entity"></param>
/// <returns></returns>
public static IEnumerable<T> ImportData<T>(Stream stream, T entity) where T : new()
{
try
{
using ExcelPackage package = new(stream);
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];//读取第1个sheet
//获取表格的列数和行数
int colStart = worksheet.Dimension.Start.Column;
int colEnd = worksheet.Dimension.End.Column;
int rowStart = worksheet.Dimension.Start.Row;
int rowEnd = worksheet.Dimension.End.Row;
List<T> resultList = new();
List<PropertyInfo> propertyInfoList = new List<PropertyInfo>(typeof(T).GetProperties());
Dictionary<string, int> dictHeader = new();
for (int i = colStart; i <= colEnd; i++)
{
dictHeader[worksheet.Cells[rowStart, i].Value.ToString()] = i;
}
for (int row = rowStart + 1; row <= rowEnd; row++)
{
T result = new();
foreach (PropertyInfo p in propertyInfoList)
{
try
{
//判断标题行是否正确
var attributes = p.GetCustomAttributes(true);
var displayAttribute = attributes.FirstOrDefault(a => a is DisplayAttribute) as DisplayAttribute;
string propertyName = displayAttribute?.Name ?? p.Name;
if (!dictHeader.ContainsKey(propertyName))
{
throw new Exception($"The imported template is incorrect. The column '{propertyName}' is missing.");
}
ExcelRange cell = worksheet.Cells[row, dictHeader[propertyName]]; //与属性名对应的单元格
if (cell.Value == null)
continue;
//时间类型处理
if (p.PropertyType.Equals(typeof(DateTime)))
{
p.SetValue(result, cell.GetValue<DateTime>());
}
else if (p.PropertyType.Equals(typeof(DateTime?)))
{
p.SetValue(result, cell.GetValue<DateTime?>());
}
//枚举值处理
if (p.PropertyType.IsEnum)
{
string enumDisplayName = cell.GetValue<string>();
var enumType = p.PropertyType;
var enumValues = Enum.GetValues(p.PropertyType);
foreach (var enumValue in enumValues)
{
if (enumValue != null)
{
var memberInfo = enumType.GetMember(enumValue.ToString());
var enumDisplayAttribute = memberInfo[0].GetCustomAttribute<DisplayAttribute>();
if (enumDisplayAttribute != null)
{
var displayName = enumDisplayAttribute.Name;
if (displayName == enumDisplayName)
{
p.SetValue(result, enumValue);
}
}
else
{
p.SetValue(result, enumValue);
}
}
}
}
//布尔类型
if (p.PropertyType == typeof(bool) || p.PropertyType == typeof(bool?))
{
string boolStr = cell.GetValue<string>();
if (boolStr == "是")
{
p.SetValue(result, 1);
}
else
{
p.SetValue(result, 0);
}
}
switch (p.PropertyType.Name.ToLower())
{
case "string":
p.SetValue(result, cell.GetValue<string>());
break;
case "int16":
p.SetValue(result, cell.GetValue<short>());
break;
case "int32":
p.SetValue(result, cell.GetValue<int>());
break;
case "int64":
p.SetValue(result, cell.GetValue<long>());
break;
case "decimal":
p.SetValue(result, cell.GetValue<decimal>());
break;
case "double":
p.SetValue(result, cell.GetValue<double>());
break;
//case "boolean":
// p.SetValue(result, cell.GetValue<bool>());
// break;
case "byte":
p.SetValue(result, cell.GetValue<byte>());
break;
case "char":
p.SetValue(result, cell.GetValue<char>());
break;
case "single":
p.SetValue(result, cell.GetValue<float>());
break;
default:
break;
}
}
catch (KeyNotFoundException ex)
{
throw new Exception("Data import failed");
}
}
resultList.Add(result);
}
return resultList;
}
catch (Exception ex)
{
throw new Exception("Failed to import");
}
}
/// <summary>
/// 导入数据-适用于多sheet
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="stream"></param>
/// <param name="entityDictionary"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public static Dictionary<string, List<T>> ImportData<T>(Stream stream, Dictionary<string, T> entityDictionary)
{
try
{
using ExcelPackage package = new(stream);
Dictionary<string, List<T>> resultListDictionary = new Dictionary<string, List<T>>();
foreach (KeyValuePair<string, T> entityPair in entityDictionary)
{
string sheetName = entityPair.Key;
T entity = entityPair.Value;
ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetName];
if (worksheet == null)
{
throw new Exception($"Sheet '{sheetName}' not found in the Excel file");
}
int colStart = worksheet.Dimension.Start.Column;
int colEnd = worksheet.Dimension.End.Column;
int rowStart = worksheet.Dimension.Start.Row;
int rowEnd = worksheet.Dimension.End.Row;
List<T> resultList = new List<T>();
List<PropertyInfo> propertyInfoList = new List<PropertyInfo>(typeof(T).GetProperties());
Dictionary<string, int> dictHeader = new Dictionary<string, int>();
for (int i = colStart; i <= colEnd; i++)
{
dictHeader[worksheet.Cells[rowStart, i].Value.ToString()] = i;
}
for (int row = rowStart + 1; row <= rowEnd; row++)
{
T result = Activator.CreateInstance<T>();
foreach (PropertyInfo p in propertyInfoList)
{
try
{
var attributes = p.GetCustomAttributes(true);
var displayAttribute = attributes.FirstOrDefault(a => a is DisplayAttribute) as DisplayAttribute;
string propertyName = displayAttribute?.Name ?? p.Name;
if (!dictHeader.Any(x => x.Key.Equals(propertyName, StringComparison.OrdinalIgnoreCase)))
{
continue; // 如果标题行不匹配则跳过
}
var columnHeader = dictHeader.First(x => x.Key.Equals(propertyName, StringComparison.OrdinalIgnoreCase)).Value;
ExcelRange cell = worksheet.Cells[row, columnHeader];
if (cell.Value == null)
continue;
switch (p.PropertyType.Name.ToLower())
{
case "string":
p.SetValue(result, cell.GetValue<string>());
break;
case "int16":
p.SetValue(result, cell.GetValue<short>());
break;
case "int32":
p.SetValue(result, cell.GetValue<int>());
break;
case "int64":
p.SetValue(result, cell.GetValue<long>());
break;
case "decimal":
p.SetValue(result, cell.GetValue<decimal>());
break;
case "double":
p.SetValue(result, cell.GetValue<double>());
break;
case "datetime":
p.SetValue(result, cell.GetValue<DateTime>());
break;
case "boolean":
p.SetValue(result, cell.GetValue<bool>());
break;
case "byte":
p.SetValue(result, cell.GetValue<byte>());
break;
case "char":
p.SetValue(result, cell.GetValue<char>());
break;
case "single":
p.SetValue(result, cell.GetValue<float>());
break;
default:
break;
}
}
catch (KeyNotFoundException ex)
{
throw new Exception("Data import failed");
}
}
resultList.Add(result);
}
resultListDictionary.Add(sheetName, resultList);
}
return resultListDictionary;
}
catch (Exception ex)
{
throw new Exception("Failed to import");
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="worksheet"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <param name="startColumn"></param>
/// <param name="endColumn"></param>
/// <param name="ignoreColumns"></param>
public void MergeRows(ExcelWorksheet worksheet, int startRow, int endRow, int startColumn, int endColumn, List<int> ignoreColumns = null)
{
// 如果ignoreColumns为null，将其转换为一个空列表
ignoreColumns ??= new List<int>();
// 首先，确定需要合并的区域
var mergeAreas = new List<(int StartCol, int EndCol)>();
int currentStartCol = startColumn;
for (int col = startColumn; col <= endColumn; col++)
{
if (ignoreColumns.Contains(col))
{
if (col > currentStartCol)
{
mergeAreas.Add((currentStartCol, col - 1));
}
currentStartCol = col + 1;
}
}
// 如果最后一个合并区域没有被添加，现在添加它
if (endColumn > currentStartCol)
{
mergeAreas.Add((currentStartCol, endColumn));
}
// 遍历所有行，对每个合并区域进行合并操作
for (int row = startRow; row <= endRow; row++)
{
foreach (var (startCol, endCol) in mergeAreas)
{
// 清除合并区域中的所有单元格，除了起始单元格
for (int clearCol = startCol + 1; clearCol <= endCol; clearCol++)
{
worksheet.Cells[row, clearCol].Value = null;
}
// 合并单元格
worksheet.Cells[row, startCol, row, endCol].Merge = true;
}
}
}
/// <summary>
/// 描述行设置
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="worksheet"></param>
/// <param name="startRow"></param>
public static void DescriptionRowSet<T>(ExcelWorksheet worksheet, string description = "测试描述")
{
try
{
int startColumn = 2;
int startRow = 1;
worksheet.Cells[startRow, 1].Value = "文档说明";
var properties = typeof(T).GetProperties();
int currentColumn = startColumn;
foreach (var property in properties)
{
string displayName = GetDisplayName(property);
var propertyType = property.PropertyType;
if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(List<>))
{
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(List<>) &&
!propertyType.GetGenericArguments()[0].IsPrimitive &&
propertyType.GetGenericArguments()[0] != typeof(string))
{
// 集合类型，需要展开子属性
var subProperties = property.PropertyType.GetGenericArguments().First().GetProperties();
foreach (var subProperty in subProperties)
{
string subDisplayName = GetDisplayName(subProperty);
if (subDisplayName != null)
currentColumn++;
}
}
else
{
currentColumn++;
}
}
else if (displayName != null)
{
// 检测是否为对象类型 ,是则获取该属性对象的属性个数
if (propertyType.IsClass && !property.PropertyType.Equals(typeof(string)))
{
currentColumn = currentColumn + propertyType.GetProperties().Length;
}
else
{
currentColumn++;
}
}
}
#region 样式设置
//设置行高
worksheet.Row(startRow).Height = 50;
var descriptionTitileRange = worksheet.Cells[startRow, 1, startRow, 1];
//字体大小
descriptionTitileRange.Style.Font.Size = 14;
//字体粗细
descriptionTitileRange.Style.Font.Bold = true;
//字体颜色
descriptionTitileRange.Style.Font.Color.SetColor(Color.Black);
//左右居中
descriptionTitileRange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//上下居中
descriptionTitileRange.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
//获得第一行有数据的最后一个单元格
var descriptionRange = worksheet.Cells[startRow, startColumn, startRow, currentColumn - 2];
//合并单元格
descriptionRange.Merge = true;
//赋值
descriptionRange.Value = description;
//字体大小
descriptionRange.Style.Font.Size = 12;
//字体粗细
descriptionRange.Style.Font.Bold = false;
//字体颜色
descriptionRange.Style.Font.Color.SetColor(Color.Black);
//左右居中
descriptionRange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
//上下居中
descriptionRange.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
#endregion 样式设置
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 标题行设置
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="worksheet"></param>
public static void TitleRowSet<T>(ExcelWorksheet worksheet, ref bool hasListProperty, int startRow = 1, int startCol = 1)
{
try
{
int endRow = startRow;
int currentColumn = startCol;
hasListProperty = false;
var properties = typeof(T).GetProperties();
// 判断属性是否存在集合类型
foreach (var property in properties)
{
Type propertyType = property.PropertyType;
if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(List<>))
hasListProperty = true;
}
foreach (var property in properties)
{
endRow = hasListProperty ? startRow + 1 : startRow;
string displayName = GetDisplayName(property);
if (displayName != null)
{
var propertyType = property.PropertyType;
if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(List<>))
{
// 检查该类型是否为复杂类型，是则需要展开子属性
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(List<>) &&
!propertyType.GetGenericArguments()[0].IsPrimitive &&
propertyType.GetGenericArguments()[0] != typeof(string))
{
var subProperties = property.PropertyType.GetGenericArguments().First().GetProperties();
worksheet.Cells[startRow, currentColumn].Value = displayName; // 主标题
foreach (var subProperty in subProperties)
{
string subDisplayName = GetDisplayName(subProperty);
worksheet.Cells[endRow, currentColumn].Value = subDisplayName; // 子标题
currentColumn++;
}
// 合并主标题行
worksheet.Cells[startRow, currentColumn - subProperties.Length, startRow, currentColumn - 1].Merge = true;
}
else
{
worksheet.Cells[startRow, currentColumn].Value = displayName;
worksheet.Cells[startRow, currentColumn, endRow, currentColumn].Merge = true;
currentColumn++;
}
}
else
{
// 检查是否为对象类型，是则展开其对象的属性，将属性写入标题
if (propertyType.IsClass && !property.PropertyType.Equals(typeof(string)))
{
// 展开对象的属性
var nestedProperties = propertyType.GetProperties();
foreach (var nestedProperty in nestedProperties)
{
string nestedDisplayName = GetDisplayName(nestedProperty);
worksheet.Cells[startRow, currentColumn].Value = nestedDisplayName;
worksheet.Cells[startRow, currentColumn, endRow, currentColumn].Merge = true;
currentColumn++;
}
}
else
{
// 检查是否为非对象类型，是则直接写入标题
worksheet.Cells[startRow, currentColumn].Value = displayName;
worksheet.Cells[startRow, currentColumn, endRow, currentColumn].Merge = true;
currentColumn++;
}
}
}
}
#region 样式设置
if (startRow == endRow)
worksheet.Row(startRow).Height = 30;
//获得标题行范围
var titleRange = worksheet.Cells[startRow, startCol, endRow, currentColumn - 1];
//字体大小
titleRange.Style.Font.Size = 14;
//字体粗细
titleRange.Style.Font.Bold = true;
//字体颜色
titleRange.Style.Font.Color.SetColor(Color.Black);
//背景
titleRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
titleRange.Style.Fill.BackgroundColor.SetColor(Color.WhiteSmoke);
//左右居中
titleRange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
//上下居中
titleRange.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
// 设置边框样式
titleRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
titleRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
titleRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
titleRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
// 设置边框颜色
titleRange.Style.Border.Top.Color.SetColor(Color.LightGray);
titleRange.Style.Border.Bottom.Color.SetColor(Color.LightGray);
titleRange.Style.Border.Left.Color.SetColor(Color.LightGray);
titleRange.Style.Border.Right.Color.SetColor(Color.LightGray);
#endregion 样式设置
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 数据行设置
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="worksheet"></param>
/// <param name="datas"></param>
/// <param name="startRow"></param>
/// <param name="startColumn"></param>
/// <exception cref="Exception"></exception>
public static void DataRowSet<T>(ExcelWorksheet worksheet, List<T> datas, int startRow, int startColumn = 1)
{
try
{
var properties = typeof(T).GetProperties();
Dictionary<PropertyInfo, bool> isCollectionProperties = new Dictionary<PropertyInfo, bool>();
// 判断属性是否为集合类型
foreach (var property in properties)
{
Type propertyType = property.PropertyType;
bool isList = false;
if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(List<>))
isList = true;
isCollectionProperties[property] = isList;
}
int currentRow = startRow;
int currentColumn = startColumn;
foreach (var item in datas)
{
currentColumn = startColumn;
// 计算集合属性的最大行数
int maxCollectionRowCount = 1;
if (isCollectionProperties.Values.Any())
{
foreach (var property in properties)
{
string displayName = GetDisplayName(property);
if (displayName != null)
{
if (isCollectionProperties[property])
{
IList collection = (IList)property.GetValue(item);
if (collection != null)
{
int collectionCount = collection.Cast<object>().Count();
if (collectionCount > maxCollectionRowCount)
maxCollectionRowCount = collectionCount;
}
}
}
}
}
// 写入属性值
foreach (var property in properties)
{
string displayName = GetDisplayName(property);
if (displayName != null)
{
if (!isCollectionProperties[property])
{
// 检查是否为对象类型
bool isObjectProperty = property.PropertyType.IsClass && !property.PropertyType.IsPrimitive && property.PropertyType != typeof(string);
if (isObjectProperty)
{
// 获取对象的实例
object value = property.GetValue(item);
if (value != null)
{
// 获取对象类型
Type objectType = property.PropertyType;
// 获取对象的所有属性
var objectProperties = objectType.GetProperties();
// 对象属性的每个属性都写入到Excel的不同列
foreach (var objectProperty in objectProperties)
{
// 获取子属性的显示名称
string objectDisplayName = GetDisplayName(objectProperty);
if (objectDisplayName != null)
{
// 获取子属性的值
object objectPropertyValue = objectProperty.GetValue(value);
if (objectPropertyValue != null)
// 如果需要，转换数据类型
objectPropertyValue = DataConvert(objectPropertyValue, objectProperty.PropertyType);
// 写入子属性的值到单元格
worksheet.Cells[currentRow, currentColumn].Value = objectPropertyValue;
// 计算非集合属性的合并单元格范围
int mergeStartRow = currentRow;
int mergeEndRow = currentRow + maxCollectionRowCount - 1;
worksheet.Cells[mergeStartRow, currentColumn, mergeEndRow, currentColumn].Merge = true;
// 移动到下一列
currentColumn++;
}
}
}
}
else
{
// 检查是否为非对象类型
object value = property.GetValue(item);
if (value != null)
value = DataConvert(value, property.PropertyType);
worksheet.Cells[currentRow, currentColumn].Value = value;
// 计算非集合属性的合并单元格范围
int mergeStartRow = currentRow;
int mergeEndRow = currentRow + maxCollectionRowCount - 1;
worksheet.Cells[mergeStartRow, currentColumn, mergeEndRow, currentColumn].Merge = true;
// 移动到下一列
currentColumn++;
}
}
else
{
// 获取集合属性的类型参数，即集合中对象的类型
Type subItemType = property.PropertyType.GetGenericArguments()[0];
// 获取对象的属性个数
int subItemPropertyCount = 1;
// 检查该类型是否为复杂类型，则获取该类型的属性个数
if (!subItemType.IsPrimitive && subItemType != typeof(string))
{
subItemPropertyCount = subItemType.GetProperties().Length;
IList collection = (IList)property.GetValue(item);
if (collection == null)
throw new Exception("导出失败");
int subItemRowOffset = 0;
if (collection.Count == 0)
{
// 如果集合为空，写入空值或占位符
for (int i = 0; i < subItemPropertyCount; i++)
{
// 使用空字符串作为占位符
worksheet.Cells[currentRow, currentColumn + i].Value = "";
}
}
else
{
foreach (var subItem in collection)
{
int subItemColumn = currentColumn;
foreach (var subItemProperty in subItem.GetType().GetProperties())
{
var subValue = subItemProperty.GetValue(subItem);
if (subValue != null)
subValue = DataConvert(subValue, subItemProperty.PropertyType);
worksheet.Cells[currentRow + subItemRowOffset, subItemColumn].Value = subValue;
subItemColumn++;
}
subItemRowOffset++;
subItemPropertyCount = subItem.GetType().GetProperties().Length;
}
}
}
else
{
subItemPropertyCount = 1;
// 如果是基本类型，可以直接将集合转换为字符串并写入单元格
var collection = (IList)property.GetValue(item);
if (collection == null)
throw new Exception("导出失败");
int subItemRowOffset = 0;
if (collection.Count == 0)
{
// 如果集合为空，写入空值或占位符
for (int i = 0; i < subItemPropertyCount; i++)
{
// 使用空字符串作为占位符
worksheet.Cells[currentRow, currentColumn + i].Value = "";
}
}
else
{
foreach (var subItem in collection)
{
int subItemColumn = currentColumn;
worksheet.Cells[currentRow + subItemRowOffset, subItemColumn].Value = subItem;
subItemRowOffset++;
}
}
}
currentColumn += subItemPropertyCount;
}
}
}
// 重置行和列的索引，准备下一条数据
currentRow += maxCollectionRowCount;
}
#region 样式设定
// 设置样式范围
var dataRange = worksheet.Cells[startRow, startColumn, currentRow, currentColumn];
// 设置字体大小
dataRange.Style.Font.Size = 12;
// 设置字体粗细
dataRange.Style.Font.Bold = false;
//字体颜色
dataRange.Style.Font.Color.SetColor(SixLabors.ImageSharp.Color.Black);
// 设置水平对齐
dataRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// 设置垂直对齐
dataRange.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
#endregion 样式设定
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 数据转换
/// </summary>
/// <param name="value"></param>
/// <param name="type"></param>
/// <returns></returns>
private static object DataConvert(object value, Type type)
{
try
{
if (value == null) return value;
// 检查是否为时间类型
if (type == typeof(DateTime) || type == typeof(DateTime?))
{
// 转换为字符串
return ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss");
}
// 检查是否为布尔类型
if (type == typeof(bool) || type == typeof(bool?))
{
// 转换为"是"或"否"
return (bool)value ? "是" : "否";
}
// 检查是否为枚举类型
if (type.IsEnum)
{
var enumValue = (Enum)value;
var memberInfo = type.GetMember(enumValue.ToString());
if (memberInfo.Length > 0)
{
var enumDisplayAttribute = memberInfo[0].GetCustomAttribute<DisplayAttribute>();
if (enumDisplayAttribute != null)
{
// 转换为Display特性中的name值
return enumDisplayAttribute.Name ?? enumValue.ToString();
}
}
// 默认情况下使用枚举的名称
return enumValue.ToString();
}
return value;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 添加下拉框
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="worksheet"></param>
/// <param name="startRow"></param>
/// <param name="startCol"></param>
/// <param name="endRow"></param>
/// <exception cref="Exception"></exception>
private static void AddDropDownListsForBooleanAndEnum<T>(ExcelWorksheet worksheet, int startRow, int startCol, int endRow)
{
try
{
//用于记录集合对象属性造成的列偏移
int colOffset = 0;
var properties = typeof(T).GetProperties();
for (int i = 0; i < properties.Length; i++)
{
var prop = properties[i];
string displayName = GetDisplayName(prop);
if (displayName != null)
{
int columnIndex = i + startCol + colOffset;
// 检查是否为布尔类型
if (prop.PropertyType == typeof(bool) || prop.PropertyType == typeof(bool?))
{
//添加下拉选择
var dd = worksheet.Cells[startRow, columnIndex, endRow, columnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
if (dd != null)
{
dd.AllowBlank = true;
dd.Formula.Values.Add("是");
dd.Formula.Values.Add("否");
}
}
// 检查是否为枚举类型
if (prop.PropertyType.IsEnum)
{
Dictionary<object, string> valueMapping = new Dictionary<object, string>();
var enumType = prop.PropertyType;
var enumValues = Enum.GetValues(prop.PropertyType);
//下拉选择
var dd = worksheet.Cells[startRow, columnIndex, endRow, columnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
dd.AllowBlank = true;
foreach (var enumValue in enumValues)
{
if (enumValue != null)
{
var memberInfo = enumType.GetMember(enumValue.ToString());
var enumDisplayAttribute = memberInfo[0].GetCustomAttribute<DisplayAttribute>();
if (enumDisplayAttribute != null)
{
var enumDisplayName = enumDisplayAttribute.Name;
valueMapping.Add(enumValue, enumDisplayName);
dd.Formula.Values.Add($"{enumDisplayName}");
}
else
{
valueMapping.Add(enumValue, enumValue.ToString());
dd.Formula.Values.Add($"{enumValue.ToString()}");
}
}
}
}
// 检查是否为对象类型
if (prop.PropertyType.IsClass && !prop.PropertyType.IsPrimitive
&& !prop.PropertyType.IsEnum && prop.PropertyType != typeof(string)
&& !prop.PropertyType.IsGenericType && !typeof(System.Collections.IEnumerable).IsAssignableFrom(prop.PropertyType))
{
// 对象类型，需要遍历其公共属性
var objectProperties = prop.PropertyType.GetProperties();
// 遍历对象的所有属性
foreach (var objectProp in objectProperties)
{
string subDisplayName = GetDisplayName(objectProp);
if (subDisplayName != null)
{
int subColumnIndex = i + startCol + colOffset;
// 检查对象的属性是否为布尔类型
if (objectProp.PropertyType == typeof(bool) || objectProp.PropertyType == typeof(bool?))
{
// 添加下拉选择
var dd = worksheet.Cells[startRow, subColumnIndex, endRow, subColumnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
if (dd != null)
{
dd.AllowBlank = true;
dd.Formula.Values.Add("是");
dd.Formula.Values.Add("否");
}
}
// 检查对象的属性是否为枚举类型
else if (objectProp.PropertyType.IsEnum)
{
var enumValues = Enum.GetValues(objectProp.PropertyType);
var dd = worksheet.Cells[startRow, subColumnIndex, endRow, subColumnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
dd.AllowBlank = true;
foreach (var enumValue in enumValues)
{
var enumDisplayName = enumValue.ToString(); // 假设没有DisplayAttribute，或者使用默认的枚举名称
dd.Formula.Values.Add(enumDisplayName);
}
}
colOffset++;
}
}
colOffset--;
}
// 检查是否为集合类型
if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(List<>))
{
// 获取集合的元素类型
var elementType = prop.PropertyType.GetGenericArguments()[0];
// 检查集合中元素是否为复杂对象类型
if (elementType.IsClass && !elementType.IsPrimitive && !elementType.IsEnum && elementType != typeof(string))
{
// 获取对象类型属性
var elementProperties = elementType.GetProperties();
// 遍历对象的属性
foreach (var elementProp in elementProperties)
{
string subDisplayName = GetDisplayName(elementProp);
if (subDisplayName != null)
{
// 检查属性是否为布尔类型
if (elementProp.PropertyType == typeof(bool) || elementProp.PropertyType == typeof(bool?))
{
// 添加下拉选择
var dd = worksheet.Cells[startRow, columnIndex, endRow, columnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
if (dd != null)
{
dd.AllowBlank = true;
dd.Formula.Values.Add("是");
dd.Formula.Values.Add("否");
}
}
// 检查属性是否为枚举类型
if (elementProp.PropertyType.IsEnum)
{
Dictionary<object, string> valueMapping = new Dictionary<object, string>();
var enumType = elementType;
var enumValues = Enum.GetValues(enumType);
var dd = worksheet.Cells[startRow, columnIndex, endRow, columnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
dd.AllowBlank = true;
foreach (var enumValue in enumValues)
{
var memberInfo = enumType.GetMember(enumValue.ToString());
var enumDisplayAttribute = memberInfo.Length > 0 ? memberInfo[0].GetCustomAttribute<DisplayAttribute>() : null;
if (enumDisplayAttribute != null)
{
var enumDisplayName = enumDisplayAttribute.Name;
valueMapping.Add(enumValue, enumDisplayName);
dd.Formula.Values.Add($"{enumDisplayName}");
}
else
{
valueMapping.Add(enumValue, enumValue.ToString());
dd.Formula.Values.Add($"{enumValue.ToString()}");
}
}
}
colOffset++;
columnIndex++;
}
}
}
else if (elementType != typeof(bool) || elementType != typeof(bool?) || !elementType.IsEnum)
{
colOffset++;
}
// 检查集合的元素类型是否为布尔
if (elementType == typeof(bool) || elementType == typeof(bool?))
{
// 添加下拉选择
var dd = worksheet.Cells[startRow, columnIndex, endRow, columnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
if (dd != null)
{
dd.AllowBlank = true;
dd.Formula.Values.Add("是");
dd.Formula.Values.Add("否");
}
}
// 检查集合的元素类型是否为枚举
if (elementType.IsEnum)
{
Dictionary<object, string> valueMapping = new Dictionary<object, string>();
var enumType = elementType;
var enumValues = Enum.GetValues(enumType);
var dd = worksheet.Cells[startRow, columnIndex, endRow, columnIndex].DataValidation.AddListDataValidation() as ExcelDataValidationList;
dd.AllowBlank = true;
foreach (var enumValue in enumValues)
{
var memberInfo = enumType.GetMember(enumValue.ToString());
var enumDisplayAttribute = memberInfo.Length > 0 ? memberInfo[0].GetCustomAttribute<DisplayAttribute>() : null;
if (enumDisplayAttribute != null)
{
var enumDisplayName = enumDisplayAttribute.Name;
valueMapping.Add(enumValue, enumDisplayName);
dd.Formula.Values.Add($"{enumDisplayName}");
}
else
{
valueMapping.Add(enumValue, enumValue.ToString());
dd.Formula.Values.Add($"{enumValue.ToString()}");
}
}
}
colOffset--;
}
}
else
{
colOffset--;
}
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 获取属性的注释
/// </summary>
/// <param name="property"></param>
/// <returns></returns>
private static string GetDisplayName(PropertyInfo property)
{
var displayAttribute = property.GetCustomAttribute<DisplayAttribute>();
return displayAttribute != null ? displayAttribute.Name : null;
}
}
}
部分方法未被调用验证过，可能不能用，那就自己调一下吧
以下为效果图：
这是全貌（已将数据清空）
模型样例
[Display(Name = "导出测试", Description = "暂无")]
public class ExportModel
{
/// <summary>
/// 字符串属性
/// </summary>
[Display(Name = "字符串属性")]
public string AT { get; set; }
/// <summary>
/// 布尔属性
/// </summary>
[Display(Name = "布尔属性")]
public bool BT { get; set; }
/// <summary>
/// 整型属性
/// </summary>
[Display(Name = "整型属性")]
public int CT { get; set; }
/// <summary>
/// 周期单位
/// </summary>
[Display(Name = "枚举属性")]
public PeriodUnit DT { get; set; }
/// <summary>
/// 集合普通属性
/// </summary>
[Display(Name = "集合普通属性")]
public List<string> List1 { get; set; }
/// <summary>
/// 集合对象属性
/// </summary>
[Display(Name = "集合对象属性")]
public List<SpotCheckRecordDetailTemplate> List2 { get; set; }
}
导出样例
string sFileName = ExcelTool.ExportExcel(new List<ExportModel>(), stream);
if (string.IsNullOrEmpty(sFileName))
return BadRequest(ReturnResult.ResError("Failed to export spare parts library"));
return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"{sFileName}");
如有问题，加QQ:1991241337
1300
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
