Excel file data is saved to SQL

1. Get a DataTable

        /// <summary>        ///querying data in an Excel file/// </summary>        /// <param name= "strpath" >file path</param>        /// <param name= "SheetName" >Worksheet name</param>        /// <returns>DataTable</returns>         PublicDataTable Getexcel (stringstrpath,stringsheetname) {            stringMyString ="provider=microsoft.ace.oledb.12.0;data Source = '"+ strpath +"'; Extended properties= ' Excel 8.0; hdr=yes;imex=1; '";
//connection string, hdr=yes means that the first line of Excel is the header row, whereas the first row is not the header rowOleDbConnection Cnnxls =NewOleDbConnection (mystring); Cnnxls.            Open (); OleDbDataAdapter Myda=NewOleDbDataAdapter ("select * FROM ["+ SheetName +"]", Cnnxls);//Querying data in a worksheetDataSet ds =NewDataSet (); Myda.fill (DS,"["+ SheetName +"]"); Cnnxls.            Close (); DataTable DT= ds. tables[0]; returnDT; }

2. Click the button function to save the data to the database

        /// <summary>        ///Save the data in Excel to an SQL database/// </summary>        /// <param name= "Sender" ></param>        /// <param name= "E" ></param>        protected voidBtnsave_click (Objectsender, EventArgs e) {
            if(LaValue.Text.Length >0) {Helper.excelhelper eh=NewHelper.excelhelper ();//public method Class object, encapsulating methods for Excel operationsDataTable dt = Eh.getexcel (Lavalue.text, DropDownList1.Text.ToString ());//Get a DataTabledatarow[] dr = dt. Select ();//Get                intRowsnum = dt. Rows.Count;//number of data rows in the tableList<string> lstmsg =Newlist<string> ();//Save failure Information                if(Rowsnum = =0) {Page.ClientScript.RegisterStartupScript ( This. GetType (),"a","alert (' Excel table is empty table, no data! ');",true); }                Else{String Error="";  for(inti =0; I < Dr. Length; i++)//Loop Insert Data                    {                        //Excel column name cannot be changed                        stringC_oraname = dr[i]["CompanyName"].                        ToString (); stringC_town = dr[i][" Town"].                        ToString (); stringc_trding = dr[i]["Trading"].                        ToString (); stringC_postcode = dr[i]["Postcode"].                        ToString (); stringC_phone = dr[i]["Phone"].                        ToString (); if(String.IsNullOrEmpty (c_oraname)) {error+="The company name cannot be empty"; }                        if(Error.) Equals (""))//{Model.model_companey Company_model=NewModel.model_companey (); Bll. Bll_companey COMPANY_BLL=NewBLL.                            Bll_companey (); Company_model. C_oraname=C_oraname; Company_model. C_town=C_town; Company_model. C_trding=c_trding; Company_model. C_postcode=C_postcode; Company_model. C_phone=C_phone; //The company name did not appear before                            if(Helper.SQLHelper.SqlContains (C_oraname,"Companey","C_oraname") ==false) {Company_bll. ADD (Company_model);//Add Data                            }                            Else{Error= C_oraname +"already exists"; Lstmsg.add ("did not import successfully,"+"Reason:"+ Error +". ");//Save error messageError =""; }                        }                        Else                        {                        }                    }                     This. TXTMESSAGELIST.ITEMS.ADD ("The import is complete. "); if(NULL!=lstmsg) {                         This. TXTMESSAGELIST.ITEMS.ADD ("Total"+ lstmsg.count () +"record did not succeed. <br/><br/>"); foreach(stringSinchlstmsg) {                             This. TXTMESSAGELIST.ITEMS.ADD (s); }                    }                }            }         }

3. Place the sheet name into the DropDownList control for selection

        /// <summary>        ///bind the sheet name to the DropDownList control/// </summary>        /// <param name= "Sender" ></param>        /// <param name= "E" ></param>        protected voidBtnup_click (Objectsender, EventArgs e) {            if(fileupload1.hasfile) {stringext = System.IO.Path.GetExtension (fileupload1.filename);//get file suffix                if(ext.) ToLower ()! =". xls"&& ext. ToLower ()! =". xlsx") {Page.ClientScript.RegisterStartupScript ( This. GetType (),"a","alert (' Please select an Excel file! ');",true); return; } httpfilecollection Files= HttpContext.Current.Request.Files;//get all the FileUpload controls on the page                stringTMP = files[0].                FileName; stringSavepath ="e:/test/"+ DateTime.Now.ToString ("YYMMDD") +". xls";//Save PathLavalue.text =Savepath;                            //save path to a label control that is not visible Fileupload1.saveas (Savepath);                //Save file Dropdownlistbind (Savepath);        //Bind DropDownList } } 

4. Bind DropDownList function

        /// <summary>        ///binding DropDownList/// </summary>        /// <param name= "Savepath" >Path</param>        protected voidDropdownlistbind (stringSavepath) {            stringMyString ="provider=microsoft.ace.oledb.12.0;data Source = '"+ Savepath +"'; Extended properties= ' Excel 8.0; hdr=yes;imex=1; '";//connect an Excel stringOleDbConnection Cnnxls =NewOleDbConnection (mystring);//Create a connectionCnnxls.            Open (); DataSet DS=NewDataSet (); DataTable DT= Cnnxls. GetOleDbSchemaTable (OleDbSchemaGuid.Tables,New Object[] {NULL,NULL,NULL,"TABLE"});//Get worksheet nameDropdownlist1.datasource =DT; foreach(DataRow Drinchdt. Rows) {DropDownList1.Items.Add (String) dr["table_name"]);//to add a worksheet name to a control} cnnxls.        Close (); }

Excel file data is saved to SQL