ASP.net implements methods for importing multiple sheet data from Excel into SQL Server-practical tips

The example in this article describes how the ASP.net implementation imports multiple sheet data from Excel into SQL Server. Share to everyone for your reference, specific as follows:

The public DataSet GetDataSet (string filePath) {string connstr = string. Format ("Provider=Microsoft.Jet.OLEDB.4.0;Data source= '" + FilePath + "); Extended properties= ' Excel 8.0;
  Hdr=yes;imex=1 ' ");
  OleDbConnection Conn = new OleDbConnection (CONNSTR);
  Create ArrayList object to store all sheetname ArrayList sheetnamelist = new ArrayList (); Get the total number of sheet in the configuration Excel (this is configured according to the project requirements) if you need to import all sheet data for the Excel table, delete the code int sheetcount = Convert.ToInt32 ( configurationmanager.appsettings["Sheetcount"].
  ToString ());
  DataSet Dsexcel = new DataSet ();
   try {if (conn.state = = connectionstate.closed) {Conn.Open (); DataTable Dtexcelschema = conn.getoledbschematable (OleDbSchemaGuid.Tables, new object[] {null, NULL, NULL, "Table"}
   ); String sheetname = String.
   Empty; if (DtExcelSchema.Rows.Count > Sheetcount) {page.registerstartupscript ("", "<mce:script type=" Text/javascrip T "><!--alert (' Sorry!) you upload an Excel file sheet The total number cannot be greater than 10 sheet..!!
    ')//--></mce:script> ");
   Returnelse {for (int j = 0; J < DtExcelSchema.Rows.Count J + +) {SheetName = String.Format ("sheet{0}$",
     j + 1);
    Sheetnamelist.add (SheetName); Exception ex) {throw new Exception (ex.
  Message.tostring (), ex);
  finally {conn.close (); try {string strSQL = string.
   Empty; for (int i = 0; i < Sheetnamelist.count i++) {strSQL = ' select * FROM [' + sheetnamelist[i].
    ToString () + "]";
    OleDbDataAdapter da = new OleDbDataAdapter (strSQL, Conn); DataTable dtexcel = new DataTable (Sheetnamelist[i).
    ToString ()); Da.
    Fill (Dtexcel);
   DSEXCEL.TABLES.ADD (Dtexcel);
  return dsexcel; The catch (Exception ex) {throw new Exception (ex).
  Message.tostring (), ex); Insert data from an Excel table into the database public void InsertData (DataSet ds) {string strsql=string.
   Empty; if (ds. Tables[0]. Rows.Count > 0) {for (int j = 0; J < ds.) Tables.count; J + +) {for (int i=0;i<ds. TABLES[J].rows.count;i++) {DataRow dr=ds. TABLES[J].
     Rows[i]; Group name string groupname = dr["group name". ToString ().
     Trim (); Contact string ContactName = dr["Contacts". ToString ().
     Trim (); Mobile number String mobile = dr["mobile number"]. ToString ().
     Trim (); Company Name String companyName = dr["Company Name". ToString ().
     Trim (); Public number String officenum = dr["office number"]. ToString ().
     Trim (); Family Number String homenum = dr["Home number"]. ToString ().
     Trim (); Mailbox String email = dr["Mailbox"]. ToString ().
     Trim (); Contact string address = dr["Contact address"]. ToString ().
     Trim (); Create time string createtime = dr["Creation time". ToString ().
     Trim (); Gender string Sex = dr["gender"]. ToString ().
     Trim (); Mobile package Type String mobiletype = dr["mobile package Type". ToString ().
     Trim (); Whether to open communication assistant string IsOpen = dr["Open communication Assistant"]. ToString ().
     Trim (); SQL statement strSQL = "INSERT INTO Msm_excel" (Groupname,mobile,name,companyname,officenum,homenum,emial,address,createtim E,sex,mobiletype,isopen) VALUES (' "+ gRoupname + "', '" + Mobile + "', '" + ContactName + "', '" + CompanyName + "', '" + Officenum + "', '" + Homenum + "', '" + emai
     L + "', '" + Address + "', '" + Createtime + "', '" + Sex + "', '" + Mobiletype + "', '" + IsOpen + "";
      try {int n = sqlhelper.sqldataexecute (strSQL); if (n > 0) {page.registerstartupscript ("" "," <mce:script type= "Text/javascript" ><!--alert (' data inserted into
Work! ')
       --></mce:script> "); Label1.Text = "Altogether successfully inserts" + DS. TABLES[J].
      Rows.Count.ToString () + "strip data"; else {page.registerstartupscript ("", "<mce:script type=" Text/javascript "><!--alert (' Server busy!
Please try again later.
      --></mce:script> ");
     The catch (Exception ex) {throw ex; else {page.registerstartupscript ("", "<mce:script type=" Text/javascript "><!--alert (' This
No data!!! In Excel file ')
  --></mce:script> "); }//Invoke//Get upload filename string filename = Fileupload1.filenamE Determine if there is an upload file if (FileUpload1.PostedFile.FileName.Length = 0) {page.registerstartupscript ("", "<mce:script type="
  Text/javascript "><!--alert (' Please select the Excel file you want to upload!! ')//--></mce:script>"); //Determine if the uploaded file type is correct else if (! Path.getextension (FileUpload1.PostedFile.FileName). ToLower (). Equals (". xls") &&! Path.getextension (FileUpload1.PostedFile.FileName). ToLower (). Equals (". xlsx")) {Page.registerstartupscript (")" <script>alert (' Sorry!) the file type you uploaded is not correct! Only upload files of Excel type! '
  </script. ");}
   else {//Get uploaded file path FilePath = Server.MapPath ("txtfiles//") + DateTime.Now.ToString ("Yyyymmddhhmmss") + fileName; This.
   FileUpload1.PostedFile.SaveAs (FilePath);
   ds = GetDataSet (FilePath);
  InsertData (DS);

 }

I hope this article will help you with ASP.net programming.