怎樣把Excel檔中的工作表內容存入數據庫

 頁面代碼﹕

< form id = " Form1 "  method = " post "  runat = " server " >
    < TABLE id = " Table1 "  cellSpacing = " 0 "  cellPadding = " 0 "  width = " 770 "  border = " 0 " >
       < TR >
          < TD align = " center " >< asp:label id = " Label1 "  runat = " server "  CssClass = " text_16bold "  EnableViewState = " False " > 考勤

資料上傳 </ asp:label ></ TD >
       </ TR >
       < TR >
               < TD >< asp:label id = " Label2 "  runat = " server " > 請選擇要上傳的文件 </ asp:label ></ TD >
       </ TR >
       < TR >
               < TD >< input id = " myFile "  type = " file "  name = " myFile "  runat = " server " ></ TD >
       </ TR >
        < TR >
               < TD >< input id = " btnUpload "  type = " button "  value = " 上傳 "  name = " btnUpload "  runat = " server " ></ TD >
         </ TR >
         < tr >
               < td >< asp:label id = " lblMSG "  runat = " server "  EnableViewState = " False "  ForeColor = " Red " ></ asp:label ></ td >
         </ tr >
         </ TABLE >
</ form >

 

  后台關鍵代碼﹕

/**/ ///把excel檔數據放入DataTable

public  DataTable GetXLSData( string  filename, string  SheetName)
   ... {
   string mystring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +filename + ";Extended Properties='Excel  8.0;;HDR=YES;IMEX=1';"; 
   OleDbConnection cnnxls=new OleDbConnection(mystring);
   OleDbDataAdapter myDa=new OleDbDataAdapter("select * from ["+SheetName+"$]",cnnxls);
   DataSet myDs=new DataSet();
   myDa.Fill(myDs,"["+SheetName+"$]");
   return myDs.Tables[0];
  }

/**/ ///上傳按鈕事件﹐把放入到DataTable的數據存入到數據庫

public   void  btnUpload_ServerClick( object  sender, System.EventArgs e)
   ... {   
   int  CorrectNUM = 0;
   
   if(this.myFile.PostedFile!=null)
   ...{                 
      string strPath=(Server.MapPath("/POS/Upload/"+DateTime.Now.ToString("yyyyMMddhhmmss")+".xls"));
      myFile.PostedFile.SaveAs(strPath);
     try
      ...{
         DataTable dt=this.GetXLSData(strPath,"history data");
         for(int i=0;i<dt.Rows.Count;i++)
          ...{     
            string PNL=dt.Rows[i][2].ToString();   //工號
            string DATETIME=dt.Rows[i][3].ToString();
            DATETIME=System.Convert.ToDateTime(DATETIME).ToString("yyyyMMdd HHmmss");
     
            string YMADY=DATETIME.Substring(0,8).ToString();//刷卡日期
            string CAR_TIME=DATETIME.Substring(9,4).ToString();//刷卡時間
             //數據層調用到后端存儲過程

            string msg=new EmpWorkTimeBL().UploadCardRecord(PNL,YMADY,CAR_TIME);

           if(msg=="true")
             ...{
                CorrectNUM++;
                this.lblMSG.Text = "共有"+ CorrectNUM.ToString()+"資料上傳成功";
                this.Bind_dgUpload();
            }
         else
           ...{
               JSUtil.Alert(msg);
               return;
          }  
       } 
    }
     catch
      ...{
          this.lblMSG.Text="上傳的文件有誤";
          return;
      }
    
    }
   
  }