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 ImportCompanyEmployees(Stream stream, bool isCoverData) { var companyEmployeeSheet = new ImportSheetModel() { SheetName = "员工信息", HeaderRowIndex = 1, StartRowIndex = 2, ColCount = 24, ParseMain = ParseToEntity, }; var result = _importExportService.Import(stream, companyEmployeeSheet, isCoverData); result.Title = "员工信息"; return(result); }
public ImportResultModel ImportEmployeeWorkHistories(Stream stream) { var sheet = new ImportSheetModel() { SheetName = "职业史", HeaderRowIndex = 1, StartRowIndex = 2, ColCount = 11, ParseMain = ParseToEntity, }; var result = _importExportService.Import(stream, sheet, false); result.Title = "职业史"; return(result); }
public ImportResultModel Import(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) { ProcessExtraData(sheet, isCoverData, result, worksheet, extraCols); if (result.ErrorRows.Count > 0) { result.Result = ImportResult.Failed; return(result); } } ProcessMainData(sheet, isCoverData, result, worksheet, extraCols); } if (result.ErrorRows.Count > 0) { result.Result = ImportResult.Failed; } else { result.Result = ImportResult.Successful; } return(result); }
public ImportResultModel ImportHealthResult(Stream inputStream, bool isCoverData) { var extraCols = new List <ImportExtraColModel>() { new ImportExtraColModel() { RowIndex = 2, ColIndex = 2 }, new ImportExtraColModel() { RowIndex = 3, ColIndex = 2 }, new ImportExtraColModel() { RowIndex = 4, ColIndex = 2 }, new ImportExtraColModel() { RowIndex = 5, ColIndex = 2 }, new ImportExtraColModel() { RowIndex = 6, ColIndex = 2 }, }; var healthResultSheet = new ImportSheetModel() { SheetName = "体检结果", HeaderRowIndex = 8, StartRowIndex = 9, ColCount = 13, ExtraCols = extraCols, ParseMain = ParseToEntityMain, ParseExtra = ParseToEntityExtra, }; var result = _importExportService.ImportWithExtra(inputStream, healthResultSheet, isCoverData); result.Title = "体检结果"; return(result); }
private static void ProcessMainData(ImportSheetModel sheet, bool isCoverData, ImportResultModel result, ExcelWorksheet worksheet, List <string> extraCols) { 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++; } }
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); }