file.CopyTo(msSource);
msSource.Seek(0, SeekOrigin.Begin);
DataTable sourceExcel = ReadStreamToDataTable(msSource, "", true);
//模板数据
string dataDir = _hosting.WebRootPath;//获得当前服务器程序的运行目录
dataDir = Path.Combine(dataDir, "ExcelTemplate");
var path = dataDir + "//档案模版.xlsx";
MemoryStream msModel = new MemoryStream();
FileStream stream = new FileStream(path, FileMode.Open);
stream.CopyTo(msModel);
msModel.Seek(0, SeekOrigin.Begin);
DataTable templateExcel = ReadStreamToDataTable(stream, "", true);
//验证是否同模板相同
string columnName = templateExcel.Columns[0].ColumnName;
if (columnName != sourceExcel.Columns[0].ColumnName)
{
throw new UserFriendlyException(501, "上传的模板文件不正确");
}
int sucessCount = 0;
int errorCount = 0;
// 处理后台逻辑 执行 插入操作
uploadResult.SuccessCount = sucessCount;
uploadResult.ErrorCount = errorCount;
uploadResult.uploadErrors = errorList;
return uploadResult;
}
catch (Exception ex)
{
throw new UserFriendlyException(501, "上传的模板文件不正确");
}
}
将文件流转化为Datable
public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)
{
//定义要返回的datatable对象
DataTable data = new DataTable();
//excel工作表
ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 0;
try
{
//根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fileStream);
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
