private static void ProcessExtraData(ImportSheetModel sheet, bool isCoverData, ImportResultModel result, ExcelWorksheet worksheet, List <string> extraCols) { foreach (var ex in sheet.ExtraCols) { extraCols.Add(worksheet.Cells[ex.RowIndex, ex.ColIndex].Value == null ? "" : worksheet.Cells[ex.RowIndex, ex.ColIndex].Value.ToString().Trim()); } var modelExtra = new ImportErrorRowModel(); var errorsExtra = new List <ImportErrorColModel>(); try { sheet.ParseToEntityExtra(extraCols, ref errorsExtra, isCoverData); if (errorsExtra.Count > 0) { modelExtra.RowIndex = 0; modelExtra.Values = extraCols; modelExtra.ErrorCols = errorsExtra; result.ErrorRows.Add(modelExtra); } } catch (Exception e) { modelExtra.RowIndex = 0; modelExtra.Values = extraCols; modelExtra.ErrorCols.Add(new ImportErrorColModel() { ColName = "", Message = e.ToString(), }); result.ErrorRows.Add(modelExtra); } }
public ImportResultModel ImportWithExtra(Stream stream, ImportSheetModel sheet, bool isCoverData) { ImportResultModel result = new ImportResultModel(); using (var xlPackage = new ExcelPackage(stream)) { var worksheet = xlPackage.Workbook.Worksheets[sheet.SheetName]; if (worksheet == null) { throw new Exception("未找到名为 " + sheet.SheetName + " 的Sheet"); } for (var i = 1; i <= sheet.ColCount; i++) { result.HeaderRow.Add(worksheet.Cells[sheet.HeaderRowIndex, i].Value == null ? "" : worksheet.Cells[sheet.HeaderRowIndex, i].Value.ToString().Trim()); } var extraCols = new List <string>(); if (sheet.ExtraCols != null && sheet.ExtraCols.Count > 0) { foreach (var ex in sheet.ExtraCols) { extraCols.Add(worksheet.Cells[ex.RowIndex, ex.ColIndex].Value == null ? "" : worksheet.Cells[ex.RowIndex, ex.ColIndex].Value.ToString().Trim()); } var modelExtra = new ImportErrorRowModel(); var errorsExtra = new List <ImportErrorColModel>(); try { sheet.ParseToEntityExtra(extraCols, ref errorsExtra, isCoverData); if (errorsExtra.Count > 0) { modelExtra.RowIndex = 0; modelExtra.Values = extraCols; modelExtra.ErrorCols = errorsExtra; result.ErrorRows.Add(modelExtra); } } catch (Exception e) { modelExtra.RowIndex = 0; modelExtra.Values = extraCols; modelExtra.ErrorCols.Add(new ImportErrorColModel() { ColName = "", Message = e.ToString(), }); result.ErrorRows.Add(modelExtra); } } //如果extra出错,则读取所有单元格到error里,以便原样输出 if (result.ErrorRows.Count > 0) { int iRow = sheet.StartRowIndex; while (true) { bool allColumnsAreEmpty = true; for (var i = 1; i <= sheet.ColCount; i++) { if (worksheet.Cells[iRow, i].Value != null && !String.IsNullOrEmpty(worksheet.Cells[iRow, i].Value.ToString())) { allColumnsAreEmpty = false; break; } } if (allColumnsAreEmpty) { break; } List <string> values = new List <string>(); for (var i = 1; i <= sheet.ColCount; i++) { values.Add(worksheet.Cells[iRow, i].Value == null ? "" : worksheet.Cells[iRow, i].Value.ToString().Trim()); } var modelRow = new ImportErrorRowModel() { RowIndex = iRow, Values = values, ErrorCols = new List <ImportErrorColModel>() { new ImportErrorColModel() { ColIndex = -1, } }, }; result.ErrorRows.Add(modelRow); iRow++; } } //extra没有错,转实体 else { int iRow = sheet.StartRowIndex; while (true) { bool allColumnsAreEmpty = true; for (var i = 1; i <= sheet.ColCount; i++) { if (worksheet.Cells[iRow, i].Value != null && !String.IsNullOrEmpty(worksheet.Cells[iRow, i].Value.ToString())) { allColumnsAreEmpty = false; break; } } if (allColumnsAreEmpty) { break; } List <string> values = new List <string>(); for (var i = 1; i <= sheet.ColCount; i++) { values.Add(worksheet.Cells[iRow, i].Value == null ? "" : worksheet.Cells[iRow, i].Value.ToString().Trim()); } if (extraCols.Count > 0) { values.AddRange(extraCols); } var model = new ImportErrorRowModel(); var errors = new List <ImportErrorColModel>(); try { sheet.ParseToEntityMain(values, ref errors, isCoverData); if (errors.Count > 0) { model.RowIndex = iRow; if (extraCols.Count > 0) { model.Values = values.Take(sheet.ColCount).ToList(); } else { model.Values = values; } model.ErrorCols = errors; result.ErrorRows.Add(model); } } catch (Exception ex) { model.RowIndex = iRow; if (extraCols.Count > 0) { model.Values = values.Take(sheet.ColCount).ToList(); } else { model.Values = values; } model.ErrorCols.Add(new ImportErrorColModel() { ColName = "", Message = ex.ToString(), }); result.ErrorRows.Add(model); } iRow++; } } } if (result.ErrorRows.Count > 0) { result.Result = ImportResult.Failed; } else { result.Result = ImportResult.Successful; } return(result); }