/// <summary> /// 导入数据库成功或失败都需要记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="FileName"></param> /// <returns></returns> public ActionResult ImportToDb(string fileName) { if (FileIsUsed(fileName)) { return(Json(ResultData.CreateError("文件正在使用中!"), "text/html;charset=utf-8", JsonRequestBehavior.AllowGet)); } //调用通用导入 ExcelImportResult result = ExcelImportCommand.OnExecute(fileName); return(Json(ResultData.Create(result), "text/html;charset=utf-8", JsonRequestBehavior.AllowGet)); }
public ExcelImportResult <T> ImportTo <T>(string path) { var results = new ExcelImportResult <T>(); using (var pck = new OfficeOpenXml.ExcelPackage()) { using (var stream = System.IO.File.OpenRead(path)) { pck.Load(stream); } var workSheet = pck.Workbook.Worksheets.First(); var columnsCount = workSheet.Dimension.End.Column; var rowCount = workSheet.Dimension.End.Row; //var headerRow = workSheet.Cells[1, 1, 1, columnsCount]; //var headList = headerRow.Select(cell => cell).ToList(); var rowModel = new GeneralRowModel <T>(workSheet); results = new ExcelImportResult <T>(rowModel); for (int rowNum = 1; rowNum <= rowCount; rowNum++) { var errors = new List <Error>(); if (rowModel.HeaderRow != rowNum) { var row = workSheet.Cells[rowNum, 1, rowNum, columnsCount]; var values = Activator.CreateInstance <T>(); foreach (var generalCell in rowModel) { var cell = row[rowNum, generalCell.ForColumn]; try { SetValue(generalCell, values, cell); } catch (Exception ex) { var error = new Error(rowNum, generalCell.ForColumn, generalCell.Name, ex); errors.Add(error); } } var newRow = new Row <T>(rowNum, values, errors); results.Rows.Add(newRow); } } } return(results); }
private void progressBackgroundWorker_DoWork( object sender, DoWorkEventArgs e) { Host.ApplyLanguageSettingsToCurrentThread(); var ei = (ExcelImportInformation)e.Argument; var cp = new ExcelImportController(); cp.Prepare(ei); _bwResult = cp.Process((BackgroundWorker)sender); }
/// <summary> /// Public interface for the core DoImport function /// </summary> /// <param name="jsonConfiguration"></param> /// <param name="workbook"></param> /// <returns></returns> public virtual ExcelImportResult Import(JsonImporterConfiguration jsonConfiguration, ExcelWorkbook workbook) { ExcelImportResult importResult = new ExcelImportResult(); ExcelImporterConfiguration importerConfiguration; var configTranslationResult = _configTranslator.Translate(jsonConfiguration); if (!configTranslationResult.IsSuccess) { _logger.Error(configTranslationResult.Message); importResult.AddMessage("Import Configuration Invalid: " + configTranslationResult.Message); return(importResult); } importerConfiguration = configTranslationResult.Configuration; return(DoImport(workbook, importerConfiguration)); }
/// <summary> /// Step by step import. /// 1. Read the excel file with IExcelReader /// 2. Validate all rows wih IExcelValidators /// 3. Get target contents based on content roots and target paget type /// 4. Loop through all rows, find matching content using IRowToContentIndicator, update / insert content /// </summary> /// <param name="workbook"></param> /// <param name="importerConfiguration"></param> /// <returns></returns> public virtual ExcelImportResult DoImport(ExcelWorkbook workbook, ExcelImporterConfiguration importerConfiguration) { ExcelImportResult importResult = new ExcelImportResult(); List <IContent> updatedContents = new List <IContent>(); try { importResult.IsSuccess = true; var excelReader = importerConfiguration.ExcelReader; var rawExcelData = excelReader.Read(workbook, importerConfiguration); importResult.ExcelRowCount = rawExcelData.Count; var validationResults = ValidateData(rawExcelData, importerConfiguration.ExcelValidators); if (validationResults.Any()) { importResult.IsSuccess = false; foreach (var validationResult in validationResults) { importResult.AddMessage(validationResult.Message); } return(importResult); } var targetPageType = importerConfiguration.TargetPageType; var targetContents = GetTargetContents(importerConfiguration.ContentRoots, targetPageType); var rowToContentIndicator = importerConfiguration.RowIndicators; for (int i = 0; i < rawExcelData.Count; i++) { RowImportResult rowImportResult = null; var row = rawExcelData[i]; var rowNumber = importerConfiguration.RowStart + i; IContent contentToModify = null; var decision = rowToContentIndicator.FindContent(row, rowNumber, targetContents, out contentToModify); if (contentToModify == null) { decision = RowToContentFinderResult.ShouldIgnore; } switch (decision) { case RowToContentFinderResult.ShouldIgnore: importResult.AddMessage($"Sheet: {rawExcelData[i].SheetName} - Row {rawExcelData[i].IndexInSheet} ignored. No matching content found"); importResult.NoOfContentIgnored++; continue; case RowToContentFinderResult.ShouldUpdate: rowImportResult = DoUpdate(row, rowNumber, contentToModify, importerConfiguration); break; case RowToContentFinderResult.ShouldInsert: rowImportResult = DoInsert(row, rowNumber, contentToModify, importerConfiguration); break; default: break; } if (rowImportResult != null) { importResult.AddMessage(rowImportResult.Message); if (rowImportResult.IsSuccess) { switch (decision) { case RowToContentFinderResult.ShouldUpdate: updatedContents.Add(rowImportResult.Content); importResult.NoOfContentUpdated++; break; case RowToContentFinderResult.ShouldInsert: importResult.NoOfContentInserted++; break; default: continue; } } else { importResult.NoOfContentErrors++; } } } AfterImport?.Invoke(this, new AfterImportEventArgs() { UpdatedContents = updatedContents, Configuration = importerConfiguration }); } catch (Exception ex) { importResult.IsSuccess = false; importResult.Message.Add(ex.Message); _logger.Error("Office Excel Import Error", ex); } return(importResult); }