NPOI import excel, npoiexcel

NPOI import excel, npoiexcel

1. excel Import

Public override string CheckAndImportFile (ref DataTable errorInfoDataTable)
{

PorgressBar. SetProgress (5, "start uploading files to the server .....");
// Upload a file to the server
File. SaveAs (filePath );
PorgressBar. SetProgress (10, "the file has been uploaded to the server .....");

// Save the check result
ErrorInfoDataTable = new DataTable ();
ErrorInfoDataTable. Columns. Add ("Data row number ");
ErrorInfoDataTable. Columns. Add ("error message ");

List <Proctor> excelData;
Dictionary <int, string> dicErrorInfo;
PorgressBar. SetProgress (30, "prepare to read data in the file ........");

// Obtain the data uploaded to excel
Var importProctorHelper = new ImportProctorHelper ();

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

PorgressBar. SetProgress (90, "after reading the data in the file, prepare to insert the data ......");

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 (transactionscospontion. Required ))
{
Var updateCount = 0;
Var insertCount = 0;
Var alreadyCount = 0;
// Query the names and SID of all invigilators in the Institution
Var proctorDictionary =
ProctorDAL. Instance. GetProctorNameByTestIdAndTestCampusCode (testID, testCampusCode );

Foreach (var item in excelData)
{
Item. TestID = testID;
// Whether the invigilator exists
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 ("Total number of Excel entries: {0}, number of updates: {1}, number of inserted entries: {2}, no number of entries allowed by the orchestration: {3}", excelData. count, updateCount,
InsertCount, alreadyCount );
}
Else
{
ErrorInfoDataTable. Rows. Add ("", result );
}
}
}

Return result;

}

// This class processes the excel information, returns the correct list data, and prepares the data for the above insertion.

Public class ImportProctorHelper
{
Private const string PROCTOR_SHEET_NAME = "test ";

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;
}
// Obtain the column name (the fourth behavior column name in the template)
Var cIndex = 0;
Var columnNumber = LoadCell (sheet, 3, cIndex ++). ToString (). Trim (); // serial number
Var columntestCampusCode = LoadCell (sheet, 3, cIndex ++). ToString (). Trim (); // campus code (required)
Var columnName = LoadCell (sheet, 3, cIndex ++). ToString (). Trim (); // invigilator (required)
Var columnProctorType = LoadCell (sheet, 3, cIndex ++). ToString (). Trim (); // invigilation type (required)
Var columnCollege = LoadCell (sheet, 3, cIndex ++). ToString (). Trim (); // Faculty (required)
Var columnSeq = LoadCell (sheet, 3, cIndex ++). ToString (). Trim (); // use the sequence
Var columnIsUsed = LoadCell (sheet, 3, cIndex). ToString (). Trim (); // whether to enable

If (columnNumber = "no." & columntestCampusCode = "campus code (required )"&&
ColumnProctorType = "invigilation type (required)" & columnName = "invigilator (required )"&&
ColumnCollege = "Faculty (required)" & columnSeq = "order of use (required)" & columnIsUsed = "enable (required )")
{
For (var I = 4; I <= sheet. LastRowNum; I ++)
{
If (I % 1000 = 3)
{
ShowProgress (30 + I * 60/sheet. LastRowNum,
String. Format ("read excel file: {0}/{1}", I-3, sheet. LastRowNum-3 ));
}
If (error infodic. Count> 1000)
{
Result = "too many Excel file errors. Please check ";
Break;
}
CIndex = 1;
Var testCampusCode = LoadCell (sheet, I, cIndex ++). ToString (). Trim (); // campus code (required)
Var name = LoadCell (sheet, I, cIndex ++). ToString (). Trim (); // invigilator (required)
Var proctorType = LoadCell (sheet, I, cIndex ++). ToString (). Trim (); // invigilation type (required)
Var college = LoadCell (sheet, I, cIndex ++). ToString (). Trim (); // Department (required)
Var seq = LoadCell (sheet, I, cIndex ++). ToString (). Trim (); // use the sequence
Var isUsed = LoadCell (sheet, I, cIndex). ToString (). Trim (); // whether to enable

// Empty rows are not processed
If (testCampusCode. _ IsNullOrEmpty ()&&
Name. _ IsNullOrEmpty () & proctorType. _ IsNullOrEmpty ()&&
College. _ IsNullOrEmpty ()&&
Seq. _ IsNullOrEmpty ()&&
IsUsed. _ IsNullOrEmpty ()
)
{
Continue;
}

// Required field
If (! TestCampusCode. _ IsNullOrEmpty ()&&
! Name. _ IsNullOrEmpty ()&&! ProctorType. _ IsNullOrEmpty ()&&! College. _ IsNullOrEmpty ()&&
! Seq. _ IsNullOrEmpty ()&&
! IsUsed. _ IsNullOrEmpty ())
{
// Check whether the school district code is consistent
If (! TestCampusCodeList. Contains (testCampusCode ))
{
ErrorInfoDic. Add (I + 1,
String. Format ("the school district code is inconsistent. The selected school district code is: {0}, the school district code in Excel is {1}", testCampusCodeList. First (),
TestCampusCode ));
Continue;
}

If (! Regex. IsMatch (proctorType, @ "^ [1-2] $ "))
{
ErrorInfoDic. Add (I + 1, "invigilation type incorrect ");
Continue;
}
If (! Regex. IsMatch (seq, @ "^ [0-9] + $ "))
{
ErrorInfoDic. Add (I + 1, "Incorrect order filling ");
Continue;
}
If (! Regex. IsMatch (isUsed, @ "^ [0-1] $ "))
{
ErrorInfoDic. Add (I + 1, "Whether to enable Filling error ");
Continue;
}
If (logExistDicName. ContainsKey (name ))
{
ErrorInfoDic. Add (I + 1, string. Format ("the instructor with the same name exists in row {0}", logExistDicName [name]);
Continue;
}
LogExistDicName. Add (name, I + 1 );

Var tmpEntity = new Proctor
{
Name = name,
TestCampusCode = testCampusCode,
ProctorType = int. Parse (proctorType) = 1? ProctorType. invigilator 1: ProctorType. invigilator 2,
College = college,
Seq = int. Parse (seq ),
IsUsed = int. Parse (isUsed) = 0? UsedType. Unused: UsedType. Used
};
ProctorList. Add (tmpEntity );
}
Else
{
// The required field is not specified.
ErrorInfoDic. Add (I + 1, "required item not filled ");
}
}
}
Else
{
Result = "Excel does not comply with the specifications. Please fill in the data according to the specifications in the template! ";
}

If (result. _ IsNullOrEmpty () & errorInfoDic. Count> 0)
{
Result = "Excel file error. Check ";
}

Return result;
}