基于DotNetCoreNPOI封装特性通用导出excel
基于DotNetCoreNPOI封装特性通用导出excel
目前根据项目中的要求，支持列名定义，列索引排序，行合并单元格，EXCEL单元格的格式也是随着数据的类型做对应的调整。
效果图：
调用方式
可以看到时非常容易的能够导出数据
// 你的需要导出的数据集合，这里的DownloadResponse就是你自己的数据集合
List<DownloadResponse> dataList = GetDownloadList(data);
var workbook = new XSSFWorkbook();
#region 表格自定义样式
var cellStyle = workbook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
var sheet = workbook.CreateSheet("Sheet1");
#endregion
#region 赋值
sheet.SetValue(PropertyHelper.GetPropertyGetters(dataList), workbook);
#endregion
#if DEBUG
string path = Path.Combine(@"D:\", $"{Guid.NewGuid()}.xlsx");
// 输出 Exce 文件
using (var fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
#endif
public class DownloadResponse
{
/// <summary>
/// 第一个参数：列名
/// 第二个参数：索引(顺序)
/// 第三个参数：是否合并单元格
/// 后期可以添加一些样式，比如宽度，颜色等，暂时先这样吧
/// </summary>
[Excel("Customs Area", 0, true)]
public string? CustomsArea { get; set; }
[Excel("Vender", 1, true)]
public string? VendorCode { get; set; }
}
实现代码
public static class PropertyHelper
{
/// <summary>
/// 获取RemarkAttribute的值,并按index正序排序。
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataList"></param>
/// <returns></returns>
public static Dictionary<int, List<PropertyGetterInfo>> GetPropertyGetters<T>(List<T> dataList)
{
var propertyGetters = GetExcelPropertyGetters<T>();
var values = new Dictionary<int, List<PropertyGetterInfo>>();
int rowIndex = 0;
foreach (var response in dataList)
{
foreach (var getter in propertyGetters)
{
string propertyName = getter.Key;
var attr = getter.Value.Attribute;
if (attr != null)
{
var value = getter.Value.Getter(response) as PropertyGetterInfo;
if (!values.TryGetValue(rowIndex, out var list))
{
list = new List<PropertyGetterInfo>();
}
list.Add(value);
values[rowIndex] = list;
}
}
rowIndex++;
}
return values;
}
/// <summary>
/// 获取ExcelAttribute的值。
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static Dictionary<string, PropertyGetterInfo<T>> GetExcelPropertyGetters<T>()
{
var result = new Dictionary<string, PropertyGetterInfo<T>>();
var properties = (from property in typeof(T).GetProperties()
//where Attribute.IsDefined(property, typeof(RemarkAttribute))
orderby ((ExcelAttribute)property
.GetCustomAttributes(typeof(ExcelAttribute), false)
.Single()).Index
select property).ToArray();
foreach (var prop in properties)
{
var attr = prop.GetCustomAttribute<ExcelAttribute>();
if (attr != null)
{
var getter = CreateGetter<T>(prop);
result[prop.Name] = new PropertyGetterInfo<T>
{
Attribute = attr,
Getter = getter
};
}
}
return result;
}
private static Func<T, PropertyGetterInfo> CreateGetter<T>(PropertyInfo prop)
{
var instance = Expression.Parameter(typeof(T), "instance");
var propertyAccess = Expression.Property(instance, prop);
var castToObject = Expression.Convert(propertyAccess, typeof(object));
var lambdaBody = Expression.MemberInit(
Expression.New(typeof(PropertyGetterInfo)),
Expression.Bind(
typeof(PropertyGetterInfo).GetProperty(nameof(PropertyGetterInfo.Description)),
Expression.Constant(prop.GetCustomAttribute<ExcelAttribute>()?.Description ?? string.Empty)
),
Expression.Bind(
typeof(PropertyGetterInfo).GetProperty(nameof(PropertyGetterInfo.Index)),
Expression.Constant(prop.GetCustomAttribute<ExcelAttribute>()?.Index ?? 0)
),
Expression.Bind(
typeof(PropertyGetterInfo).GetProperty(nameof(PropertyGetterInfo.IsMerge)),
Expression.Constant(prop.GetCustomAttribute<ExcelAttribute>()?.IsMerge ?? false)
),
Expression.Bind(
typeof(PropertyGetterInfo).GetProperty(nameof(PropertyGetterInfo.Value)),
Expression.TypeAs(castToObject, typeof(object))
),
Expression.Bind(
typeof(PropertyGetterInfo).GetProperty(nameof(PropertyGetterInfo.ValueType)),
Expression.Constant(prop.PropertyType.FullName)
)
);
var lambda = Expression.Lambda<Func<T, PropertyGetterInfo>>(lambdaBody, instance);
return lambda.Compile();
}
}
public class PropertyGetterInfo
{
public string Description { get; set; }
public int Index { get; set; }
public bool IsMerge { get; set; }
public object? Value { get; set; }
public string ValueType { get; set; }
}
public class PropertyGetterInfo<T>
{
public ExcelAttribute? Attribute { get; set; }
public Func<T, object?> Getter { get; set; }
}
public class ExcelAttribute : Attribute
{
/// <summary>
/// 列描述
/// </summary>
private string _description;
/// <summary>
/// 列索引
/// </summary>
private int _index;
/// <summary>
/// 是否合并
/// </summary>
private bool _isMerge;
public ExcelAttribute(string desc)
{
_description = desc;
}
public ExcelAttribute(string desc, int index)
{
_description = desc;
_index = index;
}
public ExcelAttribute(string desc, int index, bool isMerge)
{
_description = desc;
_index = index;
_isMerge = isMerge;
}
public string Description
{
get
{
return _description;
}
}
public int Index
{
get
{
return _index;
}
}
public bool IsMerge
{
get
{
return _isMerge;
}
}
}
public static class ExcelHelper
{
static readonly string? _intType = typeof(int).FullName;
static readonly string? _intNullType = typeof(int?).FullName;
static readonly string? _longType = typeof(long).FullName;
static readonly string? _longNullType = typeof(long?).FullName;
static readonly string? _doubleType = typeof(double).FullName;
static readonly string? _doubleNullType = typeof(double?).FullName;
static readonly string? _decimalType = typeof(decimal).FullName;
static readonly string? _decimalNullType = typeof(decimal?).FullName;
static readonly string? _stringType = typeof(string).FullName;
static readonly string? _dateTimeType = typeof(DateTime).FullName;
static readonly string? _dateTimeNullType = typeof(DateTime?).FullName;
static readonly string? _boolType = typeof(bool).FullName;
static readonly string? _boolNullType = typeof(bool?).FullName;
static readonly string? _guidType = typeof(Guid).FullName;
static readonly string? _guidNullType = typeof(Guid?).FullName;
public static void SetValue(this ISheet sheet, Dictionary<int, List<PropertyGetterInfo>> propertyGetters, XSSFWorkbook workbook)
{
bool isHead = true;
int sheetRowIndex = 0;
for (int i = 0; i < propertyGetters.Count; i++)
{
var item = propertyGetters[i];
// 创建表头
if (isHead)
{
var headerRow = sheet.CreateRow(sheetRowIndex);
for (int j = 0; j < item.Count; j++)
{
headerRow.CreateCell(j).SetCellValue(item[j].Description);
}
isHead = false;
i--;
continue;
}
// 创建行
sheetRowIndex++;
var row = sheet.CreateRow(sheetRowIndex);
for (int k = 0; k < item.Count; k++)
{
var thisValue = item[k];
var cell = row.CreateCell(thisValue.Index);
if (thisValue.Value == null)
{
cell.SetCellType(CellType.String);
cell.SetCellValue(string.Empty);
}
if (thisValue.Value != null && thisValue.ValueType == _stringType)
{
cell.SetCellType(CellType.String);
cell.SetCellValue(thisValue.Value?.ToString());
}
// 数值类型
else if (thisValue.Value != null && (thisValue.ValueType == _intNullType
|| thisValue.ValueType == _intType
|| thisValue.ValueType == _decimalNullType
|| thisValue.ValueType == _decimalType
|| thisValue.ValueType == _longNullType
|| thisValue.ValueType == _longType
|| thisValue.ValueType == _doubleType
|| thisValue.ValueType == _doubleNullType
))
{
cell.SetCellType(CellType.Numeric);
double.TryParse(thisValue.Value?.ToString(), out double value);
cell.SetCellValue(value);
}
// 时间类型
else if (thisValue.Value != null && (thisValue.ValueType == _dateTimeNullType
|| thisValue.ValueType == _dateTimeType))
{
DateTime.TryParse(thisValue.Value?.ToString(), out var value);
// 时间类型比较特殊，需要设置特定的单元格样式
var style = workbook.CreateCellStyle();
cell.SetCellValue(value.ToOADate());
style = workbook.CreateCellStyle();
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
cell.CellStyle = style;
}
// bool类型
else if (thisValue.Value != null && (thisValue.ValueType == _boolNullType
|| thisValue.ValueType == _boolType))
{
cell.SetCellType(CellType.Boolean);
bool.TryParse(thisValue.Value?.ToString(), out bool value);
cell.SetCellValue(value);
}
// 合并单元格
if (thisValue.IsMerge && thisValue.Value != null)
{
int nextIndex = i + 1;
if (nextIndex >= propertyGetters.Count)
{
continue;
}
var nextValue = propertyGetters[nextIndex];
var e = nextValue.FirstOrDefault(x => x.Description == thisValue.Description && (x.Value?.Equals(thisValue.Value) ?? false));
if (e != null)
{
// 合并当前行和下一行
var range = new CellRangeAddress(sheetRowIndex, sheetRowIndex + 1, e.Index, e.Index);
sheet.AddMergedRegion(range);
}
}
}
}
}
}