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