股票程序化接口:基于DotNetCoreNPOI封装特性通用导出excel

基于DotNetCoreNPOI封装特性通用导出excel

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

效果：

文档更新

2023-04-18	第一版
2023-04-21	1、添加反射缓存。2、单元格时间类型样式初始化提取

调用方式

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

// 你的需要导出的数据集合，这里的DownloadResponse就是你自己的数据集合
            List<DownloadResponse> 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
    {
        /// <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
    {
        private static readonly ConcurrentDictionary<Type, Dictionary<string, PropertyGetter>> _gettersCache = new ConcurrentDictionary<Type, Dictionary<string, PropertyGetter>>();

        public static Dictionary<int, List<PropertyValue>> GetPropertyGetters<F>(List<F> dataList)
        {
            var propertyGetters = GetExcelPropertyGetters<F>();

            var values = new Dictionary<int, List<PropertyValue>>();

            int rowIndex = 0;
            foreach (var response in dataList)
            {
                foreach (var getter in propertyGetters.Values)
                {
                    var value = getter.Getter(response) as PropertyValue;
                    if (value == null)
                    {
                        continue;
                    }

                    if (!values.TryGetValue(rowIndex, out var list))
                    {
                        list = new List<PropertyValue>();
                    }
                    list.Add(value);
                    values[rowIndex] = list;
                }
                rowIndex++;
            }
            return values;
        }

        public static Dictionary<string, PropertyGetter> GetExcelPropertyGetters(Type type)
        {
            if (_gettersCache.TryGetValue(type, out var result))
            {
                return result;
            }

            result = new Dictionary<string, PropertyGetter>();
            var properties = type.GetProperties();
            var excelProperties = properties
                .Select(prop => Tuple.Create(prop.GetCustomAttribute<ExcelAttribute>()?.Index ?? int.MaxValue, prop))
                .OrderBy(prop => prop.Item1)
                .ToList();

            foreach (var (_, prop) in excelProperties)
            {
                var attr = prop.GetCustomAttribute<ExcelAttribute>();
                var getter = CreateGetter(prop);
                result[prop.Name] = new PropertyGetter
                {
                    Attribute = attr,
                    Getter = getter
                };
            }

            _gettersCache[type] = result;

            return result;
        }

        public static Dictionary<string, PropertyGetter> GetExcelPropertyGetters<F>()
        {
            return GetExcelPropertyGetters(typeof(F));
        }

        private static Func<object, PropertyValue> CreateGetter(PropertyInfo prop)
        {
            var instance = Expression.Parameter(typeof(object), "instance");
            var castInstance = Expression.Convert(instance, prop.DeclaringType);
            var propertyAccess = Expression.Property(castInstance, prop);
            var castToObject = Expression.Convert(propertyAccess, typeof(object));
            var lambdaBody = Expression.MemberInit(
                Expression.New(typeof(PropertyValue)),
                Expression.Bind(
                    typeof(PropertyValue).GetProperty(nameof(PropertyValue.Description)),
                    Expression.Constant(prop.GetCustomAttribute<ExcelAttribute>()?.Description ?? string.Empty)
                ),
                Expression.Bind(
                    typeof(PropertyValue).GetProperty(nameof(PropertyValue.Index)),
                    Expression.Constant(prop.GetCustomAttribute<ExcelAttribute>()?.Index ?? 0)
                ),
                Expression.Bind(
                    typeof(PropertyValue).GetProperty(nameof(PropertyValue.IsMerge)),
                    Expression.Constant(prop.GetCustomAttribute<ExcelAttribute>()?.IsMerge ?? false)
                ),
                Expression.Bind(
                    typeof(PropertyValue).GetProperty(nameof(PropertyValue.Value)),
                    Expression.TypeAs(castToObject, typeof(object))
                ),
                Expression.Bind(
                    typeof(PropertyValue).GetProperty(nameof(PropertyValue.ValueType)),
                    Expression.Constant(prop.PropertyType.FullName)
                )
            );
            var lambda = Expression.Lambda<Func<object, PropertyValue>>(lambdaBody, instance);
            return lambda.Compile();
        }
    }

    public class PropertyValue
    {
        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 PropertyGetter
    {
        public ExcelAttribute? Attribute { get; set; }
        public Func<object, 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<PropertyValue>> propertyGetters, XSSFWorkbook workbook)
        {
            var dateTimeStyle = workbook.CreateCellStyle();
            dateTimeStyle = workbook.CreateCellStyle();
            dateTimeStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
            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);
                        cell.SetCellValue(value.ToOADate());
                        cell.CellStyle = dateTimeStyle;
                    }
                    // 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);
                        }
                    }
                }
            }
        }
    }

文章为作者独立观点，不代表股票自动交易程序化数据接口观点

股票程序化接口:基于DotNetCoreNPOI封装特性通用导出excel

股票程序化接口:Python数据分析利器

股票程序化接口:IT9862/IT9866/IT9868 代码UI联动基础

股票程序化接口:Interface接口 java

股票程序化接口:torchgeometric安装教程

股票程序化接口:Python数据分析利器

股票程序化接口:IT9862/IT9866/IT9868 代码UI联动 基础

股票程序化接口:Interface接口 java

股票程序化接口:torchgeometric安装教程

股票程序化接口:IT9862/IT9866/IT9868 代码UI联动基础