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