/// <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));
}
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>
/// 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);
}
