【资料图】

目前根据项目中的要求，支持列名定义，列索引排序，行合并单元格，EXCEL单元格的格式也是随着数据的类型做对应的调整。

效果图：

可以看到时非常容易的能够导出数据，实际调用可能就三四句话

// 你的需要导出的数据集合，这里的DownloadResponse就是你自己的数据集合            List dataList = GetDownloadList(data);             // 导出逻辑            var workbook = new XSSFWorkbook();            var sheet = workbook.CreateSheet("Sheet1");            sheet.SetValue(PropertyHelper.GetPropertyGetters(dataList), workbook);             string path = Path.Combine(@"D:\", $"{Guid.NewGuid()}.xlsx");            // 输出 Exce 文件            using (var fs = new FileStream(path, FileMode.Create, FileAccess.Write))            {                workbook.Write(fs);            }

public class DownloadResponse    {        /// 
        /// 第一个参数：列名        /// 第二个参数：索引(顺序)        /// 第三个参数：是否合并单元格        /// 后期可以添加一些样式，比如宽度，颜色等，暂时先这样吧        /// 
        [Excel("Customs Area", 0, true)]        public string? CustomsArea { get; set; }        [Excel("Vender", 1, true)]        public string? VendorCode { get; set; }    }

public static class PropertyHelper    {        /// 
        /// 获取RemarkAttribute的值,并按index正序排序。        /// 
        ///         ///         ///         public static Dictionary> GetPropertyGetters(List dataList)        {            var propertyGetters = GetExcelPropertyGetters();            var values = new Dictionary>();            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();                        }                        list.Add(value);                        values[rowIndex] = list;                    }                }                rowIndex++;            }            return values;        }        /// 
        /// 获取ExcelAttribute的值。        /// 
        ///         ///         public static Dictionary> GetExcelPropertyGetters()        {            var result = new Dictionary>();            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();                if (attr != null)                {                    var getter = CreateGetter(prop);                    result[prop.Name] = new PropertyGetterInfo                    {                        Attribute = attr,                        Getter = getter                    };                }            }            return result;        }        private static Func CreateGetter(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()?.Description ?? string.Empty)                ),                Expression.Bind(                    typeof(PropertyGetterInfo).GetProperty(nameof(PropertyGetterInfo.Index)),                    Expression.Constant(prop.GetCustomAttribute()?.Index ?? 0)                ),                Expression.Bind(                    typeof(PropertyGetterInfo).GetProperty(nameof(PropertyGetterInfo.IsMerge)),                    Expression.Constant(prop.GetCustomAttribute()?.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>(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    {        public ExcelAttribute? Attribute { get; set; }        public Func Getter { get; set; }    }     public class ExcelAttribute : Attribute    {        /// 
        /// 列描述        /// 
        private string _description;        /// 
        /// 列索引        /// 
        private int _index;        /// 
        /// 是否合并        /// 
        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> 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);                        }                    }                }            }        }    }