asp. net implements the method of importing multiple sheet data from Excel into SQLSERVER

This article illustrates how asp. net implements the method of importing multiple sheet data from Excel into SQLSERVER. Share it for your reference, as follows:

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   Store all sheetname 
  ArrayList sheetNamelist = new ArrayList();
  // Get configuration Excel Medium sheet Total ( This is configured according to project requirements )  If you need to import Excel Form all sheet Data, this code is deleted 
  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/javascript"><!--
alert(' I'm sorry ! You upload Excel Documents sheet Too much total cannot be greater than 10 A sheet..!! ')
// --></mce:script>");
    return;
   }
   else
   {
    for (int j = 0; j < dtExcelSchema.Rows.Count; j++)
    {
     sheetName = String.Format("Sheet{0}$", j + 1);
     sheetNamelist.Add(sheetName);
    }
   }
  }
  catch (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;
  }
  catch (Exception ex)
  {
   throw new Exception(ex.Message.ToString(), ex);
  }
 }
 // From Excel  Fetch data from a table   Insert the fetched data 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 person 
     string contactName = dr[" Contact person "].ToString().Trim();
     // Mobile phone number 
     string mobile = dr[" Mobile phone number "].ToString().Trim();
     // Company name 
     string companyName = dr[" Company name "].ToString().Trim();
     // Public number 
     string officeNum = dr[" Office number "].ToString().Trim();
     // Home number 
     string homeNum = dr[" Home number "].ToString().Trim();
     // Mailbox 
     string Email = dr[" Post   Box "].ToString().Trim();
     // Contact address 
     string address = dr[" Contact address "].ToString().Trim();
     // Creation time 
     string createtime = dr[" Creation time "].ToString().Trim();
     // Gender 
     string Sex = dr[" Gender "].ToString().Trim();
     // Type of mobile phone package 
     string mobileType = dr[" Type of mobile phone package "].ToString().Trim();
     // Do you want to enable a communication assistant 
     string isOpen = dr[" Do you want to enable a communication assistant "].ToString().Trim();
     //SQL  Statement 
     strSQL = "insert into msm_Excel(groupName,Mobile,Name,companyName,officeNum,homeNum,Emial,address,Createtime,Sex,mobileType,isOpen)values('" + groupname + "','" + mobile + "','" + contactName + "','" + companyName + "','" + officeNum + "','" + homeNum + "','" + Email + "','" + address + "','" + createtime + "','" + Sex + "','" + mobileType + "','" + isOpen + "')";
     try
     {
      int n = SQLHelper.SqlDataExecute(strSQL);
      if (n > 0)
      {
       Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
alert(' Data insertion succeeded !')
// --></mce:script>");
       Label1.Text = "1 Co-successful insert " + ds.Tables[j].Rows.Count.ToString() + " Bar data ";
      }
      else
      {
       Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
alert(' Server busy! Please try again later ..!')
// --></mce:script>");
      }
     }
     catch (Exception ex)
     {
      throw ex;
     }
    }
   }    
  }
  else {
   Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
alert(' This Excel No data in file !!!')
// --></mce:script>");
  }
 }
// Call 
// Get the upload file name 
  string fileName = FileUpload1.FileName;
   // Determine whether there is an uploaded file 
  if (FileUpload1.PostedFile.FileName.Length == 0) {
   Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
alert(' Please select what you want to upload Excel Documents !!')
// --></mce:script>");
  }
   // Determine whether 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(' I'm sorry ! The file type you uploaded is incorrect ! Upload only Excel File of type !')</script.");
  }
  else
  {
   // Get the 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 is helpful to everyone's asp. net programming.