Store Excel Data to SQL Server in Asp.net

Operation Diagram

 


 

ExcelWrapper

/// <Summary>
/// Query an EXCEL worksheet and add it to DATASET
/// </Summary>
/// <Param name = "filenameurl"> file path </param>
/// <Param name = "table"> name of the table in dataset (not the same as the table in the database) </param>
/// <Returns> </returns>
Public static DataSet ExecleDs (string filenameurl, string table)
{
String strConn = "Provider = Microsoft. Jet. OleDb.4.0 ;"

+ "Data source =" + filenameurl + "; Extended Properties = 'excel 8.0; HDR = YES; IMEX = 1 '";
OleDbConnection conn = new OleDbConnection (strConn );
Conn. Open ();
DataSet ds = new DataSet ();
OleDbDataAdapter odda = new OleDbDataAdapter ("select * from [Sheet1 $]", conn );
Odda. Fill (ds, table );
Return ds;
}

 

 

. Cs

// Submit button
Protected void imgbtnSubmit_Click (object sender, ImageClickEventArgs e)
{
Try
{
If (! FileUpload1.HasFile)
{
JsHelper. Alert ("Please select an Excel file", this );
Return;
}

// Get the file suffix
String extension = System. IO. Path. GetExtension (FileUpload1.FileName). ToString (). ToLower ();
If (extension! = ". Xls" & extension! = ". Xlsx ")
{
JsHelper. Alert ("only Excel files can be selected", this );
Return;
}

// Construct a file name with Exel in the relative path of the server, and SaveAs saves the uploaded file content on the server.
String filename = DateTime. Now. ToString ("yyyymmddhhMMss") + FileUpload1.FileName;
String savePath = Server. MapPath (("~ \ Upfiles \ ") + filename );
FileUpload1.SaveAs (savePath );

DataSet ds = ExcelWrapper. ExecleDs (savePath, filename );
DataRow [] dr = ds. Tables [0]. Select ();
Int rowsnum = ds. Tables [0]. Rows. Count;
List <String> lstMsg = new List <string> ();
If (rowsnum = 0)
{
JsHelper. Alert ("the Excel table is empty, no data", this );
}
Else
{
For (int I = 0; I <dr. Length; I ++)
{
String error = "";

// The Name Of The excel Column cannot be changed.
String num = dr [I] ["student ID"]. ToString ();
String name = dr [I] ["name"]. ToString ();
String pwd = dr [I] ["password"]. ToString ();
String collegeNum = dr [I] [""]. ToString ();
String birth = dr [I] ["Birthday"]. ToString ();

If (! BLL. M_CollegeBLL.GetAllCollegeNum (). Contains (collegeNum ))
{
Error + = "the school does not store & nbsp ;";
}

If (String. IsNullOrEmpty (collegeNum ))
{
Error + = "select the department where the student is located & nbsp ;";
}

If (String. IsNullOrEmpty (num ))
{
Error + = "student ID cannot be blank & nbsp ;";
}
Else if (! Utility. IsLetterThanSomeLength (num, 25 ))
{
Error + = "the student ID is too long & nbsp ;";
}

If (String. IsNullOrEmpty (name ))
{
Error + = "name cannot be blank & nbsp ;";
}
Else if (! Utility. IsLetterThanSomeLength (name, 25 ))
{
Error + = "the name is too long & nbsp ;";
}

If (String. IsNullOrEmpty (birth ))
{
Error + = "the birthdate cannot be blank & nbsp ;";
}
Else if (! Utility. IsDateTime (birth ))
{
Error + = "Incorrect birthdate format & nbsp ;";
}
If (String. IsNullOrEmpty (sex ))
{
Error + = "gender cannot be blank & nbsp ;";
}
If (String. IsNullOrEmpty (error ))
{
M_Student stu = new M_Student ();
Stu. Num = num;
Stu. Name = name;
Stu. Pwd = pwd;
Stu. CollegeNum = collegeNum;
Stu. Birthday = Convert. ToDateTime (birth );

// This student ID does not exist
If (! BLL. M_StudentBLL.GetAllStuNum (). Contains (num ))
{
BLL. M_StudentBLL.Add (stu );
}
Else
{
BLL. M_StudentBLL.Modify (stu );
}
}
Else
{
LstMsg. Add ("student ID:" + num + "not imported successfully," + "Reason:" + error + ". ");
}
}
}
This. lblHint. Text = "Import completed. ";
If (null! = LstMsg)
{
This. lblHint. Text + = "Total" + lstMsg. Count () + "records failed. <Br/> ";
Foreach (string s in lstMsg)
{
This. lblHint. Text + = s;
}
}
}
Catch
{
This. lblHint. Text = "The program has an error. Check the table to be imported! ";
}
}

 

 





From IT fat Column