EPPlus扩展封装

  开发过程中难免会遇到读取Excel的上百个title所有列的数据，而这种我们可以采用简单除暴的方式，直接通过Epplus取Sheet的行和列的位置，但是这种实现可读性非常差。

针对这种情况，我通过可配置Mapping的方式实现，通过Excel的Title与Entity对象做个映射读取到DataTable，然后操作DataTable已读取好的Excel数据。

以下实现支持Excel的Title多行合并配置

1、首先定义接口类

    public interface IProcessExcel
    {
        ProcessExcel SetWorksheetIndex(int positionId);
        DataTable Execute(ExcelPackage excelPackage);
        Task<dynamic> ExcelLoadProcess(IFormFile formFile, Func<ExcelPackage, IProcessExcel, Task<dynamic>> func, CancellationToken cancellationToken);
        void SetProperty<T>(T entity, DataRow dataRow, string prefix = "") where T : class;
    }

2、继承IProcessExcel 实现抽象接口的对应方法

 public class ProcessExcel : IProcessExcel
    {
        private int PositionId { get; set; }
        private int FromRow { get; set; }
        private int FromCol { get; set; }
        private int ToRow { get; set; }
        private bool IsOffset { get; set; }
        private int StartFromRow { get; set; }
        private ConcurrentDictionary<string, string> FieldsMapping { get; set; } = new ConcurrentDictionary<string, string>();
        private string SheetName { get; set; }

        public ProcessExcel SetWorksheetIndex(int positionId)
        {
            PositionId = positionId;
            return this;
        }

        public ProcessExcel SetExcelRange(int fromRow, int fromCol, int toRow)
        {
            FromRow = fromRow;
            FromCol = fromCol;
            ToRow = toRow;
            return this;
        }

        public ProcessExcel SetExcelRange(int startFromRow)
        {
            StartFromRow = startFromRow;
            return this;
        }

        public ProcessExcel SetOffset(bool isOffset)
        {
            IsOffset = isOffset;
            return this;
        }

        public ProcessExcel SetMapping(ConcurrentDictionary<string, string> fieldsMapping)
        {
            FieldsMapping = fieldsMapping;
            return this;
        }

        public ProcessExcel SetSheetName(string sheetName)
        {
            SheetName = sheetName;
            return this;
        }

        public async Task<dynamic> ExcelLoadProcess(IFormFile formFile, Func<ExcelPackage, IProcessExcel, Task<dynamic>> func, CancellationToken cancellationToken)
        {
            await using var stream = new MemoryStream();
            await formFile.CopyToAsync(stream, cancellationToken);
            using var package = new ExcelPackage(stream);
            return await func(package, this);
        }

        public DataTable Execute(ExcelPackage excelPackage)
        {
            var worksheet = excelPackage.Workbook.Worksheets[PositionId];
            if (!string.IsNullOrEmpty(SheetName))
            {
                if (!worksheet.Name.Contains(SheetName)) throw new xxException($"未知的模板：{worksheet.Name}，请检查！");
            }
            if (worksheet.Dimension == null) throw new xxException($"{worksheet.Name}没有数据");

            var column = worksheet.Dimension.End.Column;
            var rows = worksheet.Dimension.Rows;

            if (rows <= 0 || column <= 0) throw new xxException($"{worksheet.Name}没有数据");

            var dt = new DataTable(worksheet.Name);
            var headerName = string.Empty;
            foreach (var cell in worksheet.Cells[FromRow, FromCol, ToRow, column])
            {
                if (IsOffset)
                {
                    var headerValue = cell.Offset(cell.Rows - FromRow, cell.Columns - ToRow).Text;
                    if (!string.IsNullOrEmpty(headerValue))
                    {
                        headerName = headerValue.Trim();
                    }
                }

                var columnName = cell.Text.Trim();
                columnName = string.IsNullOrEmpty(columnName) ? headerName : $"{headerName}-{columnName}";

                if (FieldsMapping.Any() && FieldsMapping.TryGetValue(columnName, out var field) && !string.IsNullOrEmpty(field))
                {
                    columnName = field.ToLower();
                }

                if (!dt.Columns.Contains(columnName) && !string.IsNullOrEmpty(columnName))
                {
                    dt.Columns.Add(columnName);
                }
            }

            for (var i = StartFromRow; i <= worksheet.Dimension.End.Row; i++)
            {
                var row = worksheet.Cells[i, 1, i, column];
                var newRow = dt.NewRow();

                foreach (var cell in row)
                {
                    if ((cell.Style.Numberformat.Format.IndexOf("mm-dd-yy", StringComparison.Ordinal) > -1 || cell.Style.Numberformat.Format.IndexOf("yyyy", StringComparison.Ordinal) > -1) && cell.Value != null)
                    {
                        newRow[cell.Start.Column - 1] = cell.GetValue<DateTime>();
                    }
                    else
                    {
                        newRow[cell.Start.Column - 1] = cell.Text;
                    }
                }

                dt.Rows.Add(newRow);
            }

            return dt;
        }

3、实体字段映射关系，这里采用反射的方式处理的（如果考虑更高性能可以采用Expression表达式树的方式）

 public void SetProperty<T>(T entity, DataRow dataRow, string prefix = "") where T : class
        {
            var type = entity.GetType();
            foreach (var property in type.GetProperties(BindingFlags.Instance | BindingFlags.Public))
            {
                var attributeName = $"{prefix}{property.GetCustomAttribute<ColumnAttribute>()?.Name}".ToLower();
                if (!FieldsMapping.Values.Contains(attributeName)) continue;

                var value = dataRow[attributeName];
                value = value is DBNull ? "" : value;

                if (typeof(string) == property.PropertyType)
                {
                    property.SetValue(entity, value, null);
                    continue;
                }

                if (typeof(bool) == property.PropertyType)
                {
                    property.SetValue(entity, Equals(value, "1") || Equals(value, "是"), null);
                }
            }
        }
    }

4、接口入参部分

 [HttpPost("import")]
        public async Task<dynamic> Import(IFormFile formFile, CancellationToken cancellationToken)
        {
            if (formFile == null || formFile.Length <= 0)
            {
                return BadRequest("formfile is empty");
            }

            if (!Path.GetExtension(formFile.FileName).Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
            {
                return BadRequest("Not Support file extension");
            }
        }

以上代码没有提供完整，仅提供部分核心代码仅供参考