Practical tips for storing Excel data in SQL Server in asp.net

Excelwrapper

Copy Code code as follows:

<summary>
Query Excel spreadsheet Add to DataSet
</summary>
<param name= "Filenameurl" > File path </param>
<param name= The table name in the "table" >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

Copy Code code as follows:

Submit button
protected void Imgbtnsubmit_click (object sender, ImageClickEventArgs e)
{
Try
{
if (! Fileupload1.hasfile)
{
Jshelper.alert ("Please select Excel file", this);
Return
}

               //Get file suffix name
                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;
               }

Constructs exel the file name of the server relative path and SaveAs the uploaded file contents 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 ("Excel table is empty table, no data", this);
}
Else
{
for (int i = 0; i < Dr. Length; i++)
{
String error = "";

Excel column name cannot be changed
String num = dr[i]["School Number"]. ToString ();
String name = dr[i]["Name". ToString ();
string pwd = dr[i]["password"]. ToString ();
String collegenum = dr[i]["College number"]. ToString ();
string birth = dr[i]["Birthday". ToString ();

if (! Bll. M_collegebll.getallcollegenum (). Contains (Collegenum))
{
Error + = "Affiliated college does not exist";
}

if (String.IsNullOrEmpty (Collegenum))
{
Error + = "Please select the student's department";
}

if (String.IsNullOrEmpty (num))
{
Error + = "School number can not be empty";
}
else if (! Utility.isletterthansomelength (NUM, 25))
{
Error + = "The length of the study number is too long";
}

if (string.isnullorempty (name))
{
Error + = "Name can not be empty";
}
else if (! Utility.isletterthansomelength (name, 25))
{
Error + = "The length of the name is too long";
}

if (String.IsNullOrEmpty (birth))
{
Error + = "Date of birth can not be empty";
}
else if (! Utility.isdatetime (birth))
{
Error + = "Birth date format is not correct";
}
if (String.IsNullOrEmpty (Sex))
{
Error + = "Sex can not be empty";
}
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);

The school number does not exist
if (! Bll. M_studentbll.getallstunum (). Contains (num))
{
Bll. M_studentbll.add (Stu);
}
Else
{
Bll. M_studentbll.modify (Stu);
}
}
Else
{
Lstmsg.add ("+ num +" has not been imported successfully, "+" Reason: "+ error +".) ");
}
}
}
This.lblHint.Text = "Import complete. ";
if (null!= lstmsg)
{
This.lblHint.Text + = "Total" + lstmsg.count () + "record not successful." <br/><br/> ";
foreach (string s in lstmsg)
{
This.lblHint.Text = s;
}
}
}
Catch
{
This.lblHint.Text = "program error, please check the table you want to import!" ";
}
}

Effect chart