NPOI导入excel

1.excel导入

public override string CheckAndImportFile(ref DataTable errorInfoDataTable)
{

porgressBar.SetProgress(5, "开始上传文件到服务器.....");
//上传文件到服务器
file.SaveAs(filePath);
porgressBar.SetProgress(10, "文件已上传到服务器.....");

//用于保存检查结果
errorInfoDataTable = new DataTable();
errorInfoDataTable.Columns.Add("数据行号");
errorInfoDataTable.Columns.Add("错误信息");

List<Proctor> excelData;
Dictionary<int, string> dicErrorInfo;
porgressBar.SetProgress(30, "准备读取文件中数据........");

//获取上传excel中的数据
var importProctorHelper = new ImportProctorHelper();

var result = importProctorHelper.GetAndCheckUpLoadExcelData(filePath, new List<string> {testCampusCode},
porgressBar.SetProgress, out excelData, out dicErrorInfo);

porgressBar.SetProgress(90, "读取文件中数据完毕,准备插入数据......");

if (!result._IsNullOrEmpty())
{
foreach (var item in dicErrorInfo)
{
errorInfoDataTable.Rows.Add(item.Key, item.Value);
}
if (errorInfoDataTable.Rows.Count == 0)
{
errorInfoDataTable.Rows.Add("", result);
}
}
else
{
using (var ts = new TransactionScope(TransactionScopeOption.Required))
{
var updateCount = 0;
var insertCount = 0;
var alreadyCount = 0;
//查询该机构下所有的监考教师姓名和SID
var proctorDictionary =
ProctorDAL.Instance.GetProctorNameByTestIdAndTestCampusCode(testID, testCampusCode);

foreach (var item in excelData)
{
item.TestID = testID;
//该监考教师是否存在
if (proctorDictionary._ContainsKey(item.Name))
{
item.SID = proctorDictionary[item.Name];
if (ProctorDAL.Instance.Update(item))
updateCount++;
}
}
}
else
{
item.SID = CommonHelper.CreateNewGuid();
if (ProctorDAL.Instance.Add(item))
{
insertCount++;
}
}
}

if (result._IsNullOrEmpty())
{
ts.Complete();
result = string.Format("Excel总条数：{0},更新条数:{1},插入条数:{2},已被编排不能修改条数:{3}", excelData.Count, updateCount,
insertCount,alreadyCount);
}
else
{
errorInfoDataTable.Rows.Add("", result);
}
}
}

return result;

}

//该类处理了excel信息，并返回正确list数据，为上面插入准备数据

public class ImportProctorHelper
{
private const string PROCTOR_SHEET_NAME = "测试";

private string LoadProctors(List<string> testCampusCodeList, XSSFWorkbook xssfworkbook, Action<int, string> showProgress, ref List<Proctor> proctorList, ref Dictionary<int, string> errorInfoDic)
{
var result = string.Empty;
var sheet = xssfworkbook.GetSheet(PROCTOR_SHEET_NAME);
if (sheet == null)
{
return result;
}
//获取列名(模版中第四行为列名)
var cIndex = 0;
var columnNumber = LoadCell(sheet, 3, cIndex++).ToString().Trim(); //序号
var columntestCampusCode = LoadCell(sheet, 3, cIndex++).ToString().Trim(); //校区代码(必填)
var columnName = LoadCell(sheet, 3, cIndex++).ToString().Trim(); //监考教师(必填)
var columnProctorType = LoadCell(sheet, 3, cIndex++).ToString().Trim(); //监考类型(必填)
var columnCollege = LoadCell(sheet, 3, cIndex++).ToString().Trim(); //所属院系(必填)
var columnSeq = LoadCell(sheet, 3, cIndex++).ToString().Trim(); //使用顺序
var columnIsUsed = LoadCell(sheet, 3, cIndex).ToString().Trim(); //是否启用

if (columnNumber == "序号" && columntestCampusCode == "校区代码(必填)" &&
columnProctorType == "监考类型(必填)" && columnName == "监考教师(必填)" &&
columnCollege == "所属院系(必填)" && columnSeq == "使用顺序(必填)" && columnIsUsed == "是否启用(必填)")
{
for (var i = 4; i <= sheet.LastRowNum; i++)
{
if (i%1000 == 3)
{
showProgress(30 + i*60/sheet.LastRowNum,
string.Format("读取excel文件:{0}/{1}", i - 3, sheet.LastRowNum - 3));
}
if (errorInfoDic.Count > 1000)
{
result = "Excel文件错误过多，请检查";
break;
}
cIndex = 1;
var testCampusCode = LoadCell(sheet, i, cIndex++).ToString().Trim(); //校区代码(必填)
var name = LoadCell(sheet, i, cIndex++).ToString().Trim(); //监考教师(必填)
var proctorType = LoadCell(sheet, i, cIndex++).ToString().Trim(); //监考类型(必填)
var college = LoadCell(sheet, i, cIndex++).ToString().Trim(); //所属院系(必填)
var seq = LoadCell(sheet, i, cIndex++).ToString().Trim(); //使用顺序
var isUsed = LoadCell(sheet, i, cIndex).ToString().Trim(); //是否启用

//空行不处理
if (testCampusCode._IsNullOrEmpty() &&
name._IsNullOrEmpty() && proctorType._IsNullOrEmpty() &&
college._IsNullOrEmpty() &&
seq._IsNullOrEmpty() &&
isUsed._IsNullOrEmpty()
)
{
continue;
}

//必填字段
if (!testCampusCode._IsNullOrEmpty() &&
!name._IsNullOrEmpty() && !proctorType._IsNullOrEmpty() && !college._IsNullOrEmpty() &&
!seq._IsNullOrEmpty() &&
!isUsed._IsNullOrEmpty())
{
//检查校区代码是否一致
if (!testCampusCodeList.Contains(testCampusCode))
{
errorInfoDic.Add(i + 1,
string.Format("校区代码不一致，选择的校区代码为:{0},Excel中的校区代码为{1}", testCampusCodeList.First(),
testCampusCode));
continue;
}

if (!Regex.IsMatch(proctorType, @"^[1-2]$"))
{
errorInfoDic.Add(i + 1, "监考类型填写错误");
continue;
}
if (!Regex.IsMatch(seq, @"^[0-9]+$"))
{
errorInfoDic.Add(i + 1, "使用顺序填写错误");
continue;
}
if (!Regex.IsMatch(isUsed, @"^[0-1]$"))
{
errorInfoDic.Add(i + 1, "是否启用填写错误");
continue;
}
if (logExistDicName.ContainsKey(name))
{
errorInfoDic.Add(i + 1, string.Format("第{0}行存在一样姓名的教师", logExistDicName[name]));
continue;
}
logExistDicName.Add(name, i + 1);

var tmpEntity = new Proctor
{
Name = name,
TestCampusCode = testCampusCode,
ProctorType = int.Parse(proctorType) == 1 ? ProctorType.监考老师一 : ProctorType.监考老师二,
College = college,
Seq = int.Parse(seq),
IsUsed = int.Parse(isUsed) == 0 ? UsedType.未使用 : UsedType.使用
};
proctorList.Add(tmpEntity);
}
else
{
//必填字段没填写
errorInfoDic.Add(i + 1, "必填项未填写");
}
}
}
else
{
result = "Excel不符合规范，请根据模版中规范填写数据！";
}

if (result._IsNullOrEmpty() && errorInfoDic.Count > 0)
{
result = "Excel文件存在错误，请检查";
}

return result;
}