Asp.net中關於excel檔案大量匯入SQL Server資料表

 using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Configuration;
namespace WebApplication1
{
 /// <summary>
 /// tantest 的摘要說明。
 /// </summary>
 public class tantest : System.Web.UI.Page
 {
  protected System.Web.UI.WebControls.Label labelUpResult;
  protected System.Web.UI.WebControls.Label labelFileExt;
  protected System.Web.UI.WebControls.Label labelFileSize;
  protected System.Web.UI.WebControls.Button Button1;
  protected System.Web.UI.WebControls.Button Button2;
  protected System.Web.UI.WebControls.Label Label1;
  protected System.Web.UI.HtmlControls.HtmlInputFile inputFile;
  protected System.Web.UI.WebControls.Label labelFileName;
   //屬性
  private string Excelsource
  {
   get
   {
    return (ViewState["Excelsource"]==null)?"":(string)(ViewState["Excelsource"]);
   }
   set
   {
    ViewState["Excelsource"] = value;
   }
  }

 
  private void Page_Load(object sender, System.EventArgs e)
  {
   
  }

  #region Web Form設計器產生的程式碼
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: 該調用是 ASP.NET Web Form設計器所必需的。
   //
   InitializeComponent();
   base.OnInit(e);
  }
  
  /// <summary>
  /// 設計器支援所需的方法 - 不要使用代碼編輯器修改
  /// 此方法的內容。
  /// </summary>
  private void InitializeComponent()
  {   
   this.Button1.Click += new System.EventHandler(this.Button1_Click);
   this.Button2.Click += new System.EventHandler(this.Button2_Click);
   this.Load += new System.EventHandler(this.Page_Load);

  }
  #endregion

  private void Button1_Click(object sender, System.EventArgs e)
  {
   //檢查上傳檔案不為空白
   if(inputFile.PostedFile.ContentLength > 0)
   {
    //設定上傳檔案的儲存路徑
    string strSaveDir = "./WHHRREPORT/";
    string strName = inputFile.PostedFile.FileName;
    //取得檔案名稱(抱括路徑)裡最後一個"."的索引
    int intExt = strName.LastIndexOf(".");
    //取得副檔名
    string strExt = strName.Substring(intExt);
    //這裡我自動根據日期和檔案大小不同為檔案命名,確保檔案名稱不重複
    /*
    DateTime datNow = DateTime.Now;
    string strNewName = datNow.DayOfYear.ToString() + inputFile.PostedFile.ContentLength.ToString() + strExt; */
    //取得檔案名稱(包括路徑)裡最後一個"\"的索引
    int intPath = strName.LastIndexOf("\\");
    //取得檔案名稱(不包括路徑)
    string strNewName = strName.Substring(intPath);
    //儲存檔案到你所要的目錄,這裡是IIS根目錄下的upload目錄.你可以改變.
    //注意: 我這裡用Server.MapPath()取當前檔案的絕對目錄.在asp.net裡"\"必須用"\\"代替
    inputFile.PostedFile.SaveAs(Server.MapPath(strSaveDir + strNewName));
    Excelsource=Server.MapPath(strSaveDir + strNewName).ToString();
    //得到這個檔案的相關屬性:檔案名稱,檔案類型,檔案大小
    labelUpResult.Text = "上傳成功！";
    labelFileName.Text = "檔案源：" + strName;
    labelFileExt.Text = "檔案類型：" + inputFile.PostedFile.ContentType + "( " + strExt + " )";
    labelFileSize.Text = "檔案大小：" + (inputFile.PostedFile.ContentLength / 1024).ToString() + " K Byte(s)";
   }
   else
   {
    labelUpResult.Text = "請選擇你要上傳的檔案！";
    labelFileName.Text = "";
    labelFileExt.Text = "";
    labelFileSize.Text = "";
   }

  
  }

  private void Button2_Click(object sender, System.EventArgs e)
  {
   SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
  
   //串連excel資料來源
   string excelconnstring=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Excelsource+"";
   excelconnstring+=@";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" ;
   System.Data.OleDb.OleDbConnection excelconn=new System.Data.OleDb.OleDbConnection(excelconnstring);
   string sql="select * from [sheet1$]";
   System.Data.OleDb.OleDbDataAdapter mycomm=new System.Data.OleDb.OleDbDataAdapter(sql,excelconn)  ;
   DataSet myds=new DataSet();
   mycomm.Fill(myds,"ss");
   
   //
   SqlCommand cm= new SqlCommand();
   cm.Connection=conn;
   conn.Open();
   for(int i=0;i<myds.Tables[0].Rows.Count;i++)
   { 
string updateSql1="insert into tantest(ghid,xjhao,ylao,yliao,gjj)values('"+myds.Tables[0].Rows[i]["工號"]+"','"+myds.Tables[0].Rows[i]["薪金號"]+"','"+myds.Tables[0].Rows[i]["養老基金"]+"','"+myds.Tables[0].Rows[i]["醫學基金"]+"','"+myds.Tables[0].Rows[i]["公積金"]+"')";
    cm.CommandText=updateSql1;
    try
    {   
     cm.ExecuteNonQuery();
    }
    catch
    {

    }
    finally
    {
     this.Label1.Visible=true;
     this.Label1.Text="資料匯入成功！";
            
    }
    
   } 
   cm.Dispose();
   conn.Close();       
 
  }
 }
}