示例#1
0
        /// <summary>
        /// 获取表头单元格和属性
        /// </summary>
        /// <param name="headerCellInfo">表头单元格信息集合</param>
        private ExcelHeaderCellPropertyInfo GetHeaderCellProperties <TImportDto>(ExcelHeaderCellInfo headerCellInfo) where TImportDto : class, new()
        {
            if (headerCellInfo.HeaderCells?.Any() != true)
            {
                throw new Exception($"表头行不能为空");
            }

            var cellProperties = new ExcelHeaderCellPropertyInfo
            {
                SheetName  = headerCellInfo.SheetName,
                SheetIndex = headerCellInfo.SheetIndex
            };

            //属性名称
            var properties = ExcelHelper.GetProperties <TImportDto>();

            foreach (var p in properties)
            {
                var name = p.GetDisplayNameFromProperty()?.Trim();
                var cell = headerCellInfo.HeaderCells.FirstOrDefault(a => a.Name.Trim() == name);
                if (cell != null)
                {
                    cellProperties.HeaderCells.Add(new ExcelHeaderCellProperty
                    {
                        Name         = cell.Name,
                        ColumnIndex  = cell.ColumnIndex,
                        RowIndex     = cell.RowIndex,
                        PropertyInfo = p
                    });
                }
            }

            return(cellProperties);
        }
示例#2
0
        /// <summary>
        /// 获取工作表数据
        /// </summary>
        /// <typeparam name="TImportDto"></typeparam>
        /// <param name="workbook">工作册</param>
        /// <param name="sheetIndex">工作表下标(其实下表:0)</param>
        /// <param name="options">选项配置</param>
        /// <returns></returns>
        private ExcelSheetDataOutput <TImportDto> ProcessWorksheet <TImportDto>(TWorkbook workbook, int sheetIndex, ExcelImportOptions options) where TImportDto : class, new()
        {
            //获取工作表
            TSheet worksheet = GetWorksheet(workbook, sheetIndex);

            //工作表名称
            string sheetName = GetWorksheetName(workbook, worksheet);

            try
            {
                //获取表头行
                TRow headerRow = GetHeaderRow(workbook, worksheet, options);

                //获取表头单元格集合
                List <ExcelHeaderCell> headerCells = GetHeaderCells(workbook, worksheet, headerRow);

                //表头单元格信息
                ExcelHeaderCellInfo headerCellInfo = new ExcelHeaderCellInfo(sheetName, sheetIndex, headerCells);

                //验证表头行
                ValidateHeaderRow <TImportDto>(headerCellInfo);

                //获取表头单元格属性信息
                ExcelHeaderCellPropertyInfo headerCellProperties = GetHeaderCellProperties <TImportDto>(headerCellInfo);

                //设置工作表数据
                return(new ExcelSheetDataOutput <TImportDto>
                {
                    SheetName = sheetName,
                    SheetIndex = sheetIndex + 1,
                    Rows = ProcessWorksheetData <TImportDto>(workbook, worksheet, headerCellInfo, headerCellProperties, options),
                });
            }
            catch (Exception e)
            {
                throw new Exception($"工作表【{sheetName}】存在以下错误:{e.Message}", e);
            }
        }
示例#3
0
        /// <summary>
        /// 验证表头行
        /// </summary>
        /// <param name="headerCellInfo">表头单元格信息集合</param>
        private void ValidateHeaderRow <TImportDto>(ExcelHeaderCellInfo headerCellInfo) where TImportDto : class, new()
        {
            if (headerCellInfo.HeaderCells?.Any() != true)
            {
                throw new Exception($"表头行不能为空");
            }

            //属性名称
            var propertyNames = ExcelHelper.GetDisplayNameListFromProperty <TImportDto>();

            if (!propertyNames.Any())
            {
                throw new Exception($"类 {typeof(TImportDto).Name} 对应的表头不能为空");
            }
            var propertyDuplicate = propertyNames.GroupBy(a => a).Where(a => a.Count() > 1).Select(a => a.Key).ToList();

            if (propertyDuplicate.Any())
            {
                throw new Exception($"类 {typeof(TImportDto).Name} 中 Display Name 重复(或与属性名称重复):{string.Join(",", propertyDuplicate)}");
            }

            //excel表头名称
            var headerNames     = headerCellInfo.HeaderCells.Select(a => a.Name).ToList();
            var headerDuplicate = headerNames.GroupBy(a => a).Where(a => a.Count() > 1).Select(a => a.Key).ToList();

            if (headerDuplicate.Any())
            {
                throw new Exception($"表头名称重复:{string.Join(",", headerDuplicate)}");
            }

            var except = propertyNames.Except(headerNames).ToList();

            if (except.Any())
            {
                throw new Exception($"工作表中不存在以下表头名称:{string.Join(",", except)}");
            }
        }
示例#4
0
        /// <summary>
        /// 处理工作表
        /// </summary>
        /// <param name="workbook">工作册</param>
        /// <param name="worksheet">工作表</param>
        /// <param name="headerCellInfo"></param>
        /// <param name="headerCellProperties">表头单元格信息集合</param>
        /// <param name="options">配置选项</param>
        /// <returns></returns>
        private List <ExcelImportRowInfo <TImportDto> > ProcessWorksheetData <TImportDto>(TWorkbook workbook, TSheet worksheet, ExcelHeaderCellInfo headerCellInfo, ExcelHeaderCellPropertyInfo headerCellProperties, ExcelImportOptions options) where TImportDto : class, new()
        {
            var rows = new List <ExcelImportRowInfo <TImportDto> >();

            //获取数据行区域索引
            var rowRangeIndex = GetDataRowStartAndEndRowIndex(workbook, worksheet, options);

            for (var i = rowRangeIndex.StartIndex; i <= rowRangeIndex.EndIndex; i++)
            {
                //获取数据行
                TRow row = GetDataRow(workbook, worksheet, i);

                //获取行数据
                TImportDto entity = GetRowData <TImportDto>(workbook, worksheet, headerCellProperties, row);
                if (entity != null)
                {
                    //验证数据
                    var valid = ExcelHelper.GetValidationResult(entity);

                    var rowInfo = new ExcelImportRowInfo <TImportDto>
                    {
                        SheetName  = headerCellInfo.SheetName,
                        SheetIndex = headerCellInfo.SheetIndex,
                        Row        = entity,
                        Errors     = valid,
                        RowNum     = i + 1,
                        IsValid    = valid == null
                    };

                    rows.Add(rowInfo);

                    if (!rowInfo.IsValid)
                    {
                        if (options.ValidateMode.Equals(ExcelValidateModeEnum.StopRow))
                        {
                            break;
                        }

                        if (options.ValidateMode.Equals(ExcelValidateModeEnum.ThrowRow))
                        {
                            rowInfo.CheckError();
                        }
                    }
                }
            }
            return(rows);
        }