c#關於EXCEL匯入資料庫的做法

標籤：style   blog   http   io   color   ar   os   for   sp   

以下例子轉載：互連網

先在類中定義一個方法名為ExecleDs的方法,用於將Excel表裡的資料填充到DataSet中,代碼如下

public DataSet ExecleDs(string filenameurl,string table)
2          {
3             string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" +filenameurl+ ";Extended Properties=‘Excel 8.0; HDR=YES; IMEX=1‘";
4              OleDbConnection conn = new OleDbConnection(strConn);
5        
6              OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]",conn);
7              DataSet ds = new DataSet();
8              odda.Fill(ds,table);
9       
10             return ds;
11 
12          }

然後設計頁面,在此作個簡單的頁面
<table style="width: 395px; height: 84px" border="1">
2        <tr>
3            <td style="width:380px" align="center">
4                 大量匯入使用者資訊</td>
5        </tr>
6        <tr>
7            <td style="width: 100px">
8                <asp:FileUpload ID="FileUpload1" runat="server" Width="380px" /></td>
9        </tr>
10        <tr>
11            <td style="width:380px" align="center">
12                <asp:Button ID="Button1" runat="server" Text="添加" OnClick="Button1_Click" /></td>
13        </tr>
14    </table>
15    <asp:Label ID="Label1" runat="server"   Width="466px"></asp:Label>
當點擊添加按鈕時激發事件,代碼如下
protected void Button1_Click(object sender, EventArgs e)
  {
       if (FileUpload1.HasFile == false)
      {
            Response.Write("<script>alert(‘請您選擇Excel檔案‘)</script> ");
           return;//當無檔案時,返回
        }
       string IsXls=System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
        if (IsXls != ".xls")
      {
            Response.Write("<script>alert(‘只可以選擇Excel檔案‘)</script>");
          return;//當選擇的不是Excel檔案時,返回
        }
       string error = null;
       Access.Class1 ac = new Access.Class1();
       SqlConnection cn = ac.myConnection();
        cn.Open();
       string strpath = FileUpload1.PostedFile.FileName.ToString();   //擷取Execle檔案路徑
       string filename = FileUpload1.FileName;                       //擷取Execle檔案名稱
         DataSet ds = ac.ExecleDs(strpath,filename);
       DataRow[] dr = ds.Tables[0].Select();                        //定義一個DataRow數組
        int rowsnum = ds.Tables[0].Rows.Count;
      if (rowsnum == 0)                                 
      {
            Response.Write("<script>alert(‘Excel表為空白表,無資料!‘)</script>");   //當Excel表為空白時,對使用者進行提示
       }
     else
      {
         for (int i = 0; i < dr.Length; i++)
           {
               string YHMC = dr[i]["YongHuMingCheng"].ToString();
            string YHMM = dr[i]["YongHuMiMa"].ToString();
                string DQRQ = dr[i]["DaoQiRiQi"].ToString();
              string ZT = dr[i]["ZhuangTai"].ToString();
                string TJSJ = dr[i]["TianJiaShiJian"].ToString();
               string JXDM = dr[i]["JiaXiaoDaiMa"].ToString();
               string sqlcheck = "select count(*) from DC_YongHuLieBiao where YongHuMingCheng=‘" + YHMC +"‘And JiaXiaoDaiMa=‘" + JXDM + "‘";  //檢查使用者是否存在
                bool ch = ac.check(sqlcheck);    
             if (ch == true)
              {
                   string insertstr = "insert into DC_YongHuLieBiao(YongHuMingCheng,YongHuMiMa,DaoQiRiQi,ZhuangTai,TianJiaShiJian,JiaXiaoDaiMa) values(‘" +
                                         YHMC + "‘,‘" + YHMM + "‘,‘" + DQRQ + "‘,‘" + ZT + "‘,‘" + TJSJ + "‘,‘" + JXDM + "‘)";
                    SqlCommand cmd = new SqlCommand(insertstr,cn);
              try
                  {
                     cmd.ExecuteNonQuery();
                    }     catch (MembershipCreateUserException ex)       //捕捉異常
                    {
                        Response.Write("<script>alert(‘建立使用者:"+ex.Message+"‘)</script>");
                    }
              }
              else
             {
                    error += "<em style=‘color:red;font-sixe:25px‘>"+YHMC+"</em>使用者已存在,此行記錄無法插入!請修改使用者再進行插入 <br>";   //若使用者存在,將已存在使用者資訊打出,並提示此使用者無法插入
                   continue;
                 }
           }
           Response.Write("<script>alert(‘Excle表匯入成功!‘)</script>");
            Label1.Text = error;
         }
         cn.Close();
    }

c#關於EXCEL匯入資料庫的做法