public ExcelData Import(byte[] excelFile, ExcelConfig config)
{
// 判断上传的Excel数据文件是否采用了最新的模版
if (!config.IsValidExcel(excelFile))
{
throw new Exception("您上传的文件不能导入，因为不是采用最新的模板，请先下载右上角的最新模板！");
}
var data = new ExcelData();
data.InitConfig(config);
var workbook = new Workbook(new MemoryStream(excelFile));
var cells = workbook.Worksheets[0].Cells;
var comments = workbook.Worksheets[0].Comments;
// 填充表格数据，需要循环导入的数据
foreach (var table in data.Tables)
{
var startRowIndex = table.Structure.StartRowIndex;
// 循环行
for (int i = startRowIndex; i <= cells.MaxDataRow + 1; i++)
{
// 判断是否结束
if (IsEndRow())
{
break;
}
var row = new ExcelRowInfo(i);
// 循环列
foreach (var cell in table.Structure.Cells)
{
// 获取导入单元格的值
var value = cells[i, cell.ColIndex].StringValue.Trim();
if (!string.IsNullOrWhiteSpace(value))
{
var cellInfo = new ExcelCellInfo(cell);
cellInfo.Value = value;
row.Cells.Add(cellInfo);
}
}
if (row.Cells.Count > 0)
{
table.Rows.Add(row);
}
}
}
// 填充一次导入的数据
foreach (var cell in data.Variables)
{
var value = cells[cell.Structure.RowIndex, cell.Structure.ColIndex].StringValue.Trim();
if (string.IsNullOrEmpty(value))
{
cell.Value = value;
}
}
return(data);
}
/// <summary>
/// 验证Excel导入的数据是否有效
/// </summary>
/// <param name="data">Excel的数据信息</param>
/// <param name="CellValidation">单元格的验证事件</param>
/// <returns></returns>
public static IEnumerable <CellErrorInfo> Vaildate(this ExcelData data, Action <CellValidationArgs> CellValidation)
{
var errors = new List <CellErrorInfo>();
// 循环表格
foreach (var table in data.Tables)
{
// 循环表格行
var dt = table.ToDataTable();
for (int i = 0; i < table.Rows.Count; i++)
{
// 循环表格列
var row = table.Rows[i];
foreach (var cell in row.Cells)
{
// 构造验证单元格事件的参数
var args = new CellValidationArgs
{
ColIndex = cell.Structure.ColIndex,
EnumKey = cell.Structure.EnumKey,
FieldName = cell.FieldName,
Record = dt.Rows[i],
RowIndex = row.RowIndex,
Value = cell.Value,
};
// 触发验证单元格事件
CellValidation(args);
// 如果验证不通过，则反写Excel，红色背景白色字体加书签说明
if (!args.IsValid)
{
errors.Add(new CellErrorInfo
{
ColIndex = args.ColIndex,
RowIndex = args.RowIndex,
ErrorText = args.ErrorText,
});
}
}
}
}
// 检查变量
foreach (var cell in data.Variables)
{
// 构造验证单元格事件的参数
var args = new CellValidationArgs
{
ColIndex = cell.Structure.ColIndex,
EnumKey = cell.Structure.EnumKey,
FieldName = cell.FieldName,
Record = null,
RowIndex = cell.Structure.RowIndex,
Value = cell.Value,
};
// 触发验证单元格事件
CellValidation(args);
// 如果验证不通过，则反写Excel，红色背景白色字体加书签说明
if (!args.IsValid)
{
errors.Add(new CellErrorInfo
{
ColIndex = args.ColIndex,
RowIndex = args.RowIndex,
ErrorText = args.ErrorText,
});
}
}
return(errors);
}