/// <summary>加载源文件</summary>
private DataTable LoadMergeSource(MergeContext context)
{
var workbook = WorkbookFactory.Create(context.MergeSourceFile);
var excelSheet = workbook.GetSheetAt(0);
return(excelSheet.ToDataTable(context.MergeSourceFieldRow - 1, context.MergeSourceDataRow - 1));
}
/// <summary>合并</summary>
public void Merge(frmIndexSubmissionPresenter presenter, MergeContext context)
{
LoadFromApiService(false, false);
var souceTable = LoadMergeSource(context);
MergeSourceToTarget(context, souceTable);
}
/// <summary>合并</summary>
private void MergeSourceToTarget(MergeContext context, DataTable sourceTable)
{
IWorkbook workbook = null;
using (var stream = new FileStream(context.MergeTargetFile, FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(stream);
}
var targetSheet = workbook.GetSheetAt(0);
var targetFieldRowNum = 1;
var targetFieldRow = targetSheet.GetRow(targetFieldRowNum);
var targetProductFileColIndex = targetFieldRow.Cells.First(cell => cell != null && cell.StringCellValue == SubmissionConsts.ST_ProductFile).ColumnIndex;
var mapper = new Dictionary <int, DataColumn>();
foreach (var cell in targetFieldRow.Cells)
{
mapper[cell.ColumnIndex] = sourceTable.Columns.Cast <DataColumn>().FirstOrDefault(col => col.ColumnName == cell.StringCellValue);
}
var sourcePTFileTagColumn = sourceTable.Columns.Cast <DataColumn>().FirstOrDefault(col => col.ColumnName == SubmissionConsts.ST_ProductFile);
var sourceTitleTagColumn = sourceTable.Columns.Cast <DataColumn>().First(col => col.ColumnName == MetadataConsts.Title);
var targetStartRowIndex = 2;
var targetLastRowNum = targetSheet.LastRowNum;
var targetRows = new List <IRow>();
for (int rowIndex = targetStartRowIndex; rowIndex <= targetLastRowNum; rowIndex++)
{
var row = targetSheet.GetRow(rowIndex);
if (row == null)
{
continue;
}
targetRows.Add(row);
}
MergeSourceToTarget(context, targetRows, mapper, targetProductFileColIndex, sourceTable, sourcePTFileTagColumn, FindFullMerge);
MergeSourceToTarget(context, targetRows, mapper, targetProductFileColIndex, sourceTable, sourceTitleTagColumn, FindFullMerge);
MergeSourceToTarget(context, targetRows, mapper, targetProductFileColIndex, sourceTable, sourceTitleTagColumn, FindFuzzyMerge);
if (sourceTable.Rows.Count > 0)
{
targetLastRowNum++;
var resourceKeyColIndex = targetFieldRow.Cells.First(cell => cell != null && cell.StringCellValue == MetadataConsts.ResourceKey).ColumnIndex;
var resourceKeyTag = this.Config.MetadataTags.Find(e => e.Name == MetadataConsts.ResourceKey);
resourceKeyTag.CheckDefaultValue(this.Config.Variables);
for (int i = 0; i < sourceTable.Rows.Count; i++)
{
var row = sourceTable.Rows[i];
var cellData = BuildExcelMergeDataRow(mapper, row);
cellData[resourceKeyColIndex].Value = resourceKeyTag.BuildVariablesValue(null);
targetSheet.AddRow(targetLastRowNum + i, cellData, false);
}
}
using (var stream = new FileStream(context.MergeTargetFile, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
workbook.Close();
}
/// <summary>合并文件名完全匹配的数据</summary>
private void MergeSourceToTarget(MergeContext context, List <IRow> targetRows, Dictionary <int, DataColumn> mapper, int targetProductFileColIndex, DataTable sourceTable, DataColumn sourceTitleTagColumn, Func <string, DataTable, DataColumn, DataRow> mergeValidator)
{
for (int rowIndex = 0; rowIndex < targetRows.Count; rowIndex++)
{
var row = targetRows[rowIndex];
if (row == null)
{
continue;
}
var targetFileCell = row.Cells.First(cell => cell != null && cell.ColumnIndex == targetProductFileColIndex);
var targetFileName = Path.GetFileName(targetFileCell.StringCellValue);
var sourceRow = mergeValidator(targetFileName, sourceTable, sourceTitleTagColumn);
if (sourceRow == null)
{
continue;
}
foreach (var pair in mapper)
{
var dataColumn = pair.Value;
if (dataColumn == null || dataColumn.ColumnName == MetadataConsts.Title)
{
continue;
}
var sourceValue = sourceRow[dataColumn];
if (sourceValue == null || sourceValue == DBNull.Value)
{
continue;
}
var cell = row.GetCell(pair.Key);
if (cell == null)
{
cell = row.CreateCell(pair.Key);
}
if (sourceValue is double)
{
cell.SetCellValue((double)sourceValue);
}
else if (sourceValue is bool)
{
cell.SetCellValue((bool)sourceValue);
}
else if (sourceValue is DateTime)
{
cell.SetCellValue((DateTime)sourceValue);
}
else
{
cell.SetCellValue(sourceValue.ToString());
}
}
sourceTable.Rows.Remove(sourceRow);
targetRows[rowIndex] = null;
}
sourceTable.AcceptChanges();
}