如何将Excel导入数据库

先在类中定义一个方法名为ExecleDs的方法,用于将Excel表里的数据填充到DataSet中,代码如下

 1     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          }
13 

然后设计页面,在此作个简单的页面

 1   < 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 >

当点击添加按钮时激发事件,代码如下

 1    protected   void  Button1_Click( object  sender, EventArgs e)
 2      {
 3        if (FileUpload1.HasFile == false)
 4        {
 5            Response.Write("<script>alert('请您选择Excel文件')</script> ");
 6            return;//当无文件时,返回
 7        }
 8        string IsXls=System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
 9        if (IsXls != ".xls")
10        {
11            Response.Write("<script>alert('只可以选择Excel文件')</script>");
12            return;//当选择的不是Excel文件时,返回
13        }
14        string error = null;
15        Access.Class1 ac = new Access.Class1();
16        SqlConnection cn = ac.myConnection();
17        cn.Open();
18        string strpath = FileUpload1.PostedFile.FileName.ToString();   //获取Execle文件路径
19        string filename = FileUpload1.FileName;                       //获取Execle文件名
20        DataSet ds = ac.ExecleDs(strpath,filename);
21        DataRow[] dr = ds.Tables[0].Select();                        //定义一个DataRow数组
22        int rowsnum = ds.Tables[0].Rows.Count;
23        if (rowsnum == 0)                                 
24        {
25            Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
26        }
27        else
28        {
29            for (int i = 0; i < dr.Length; i++)
30            {
31                string YHMC = dr[i]["YongHuMingCheng"].ToString();
32                string YHMM = dr[i]["YongHuMiMa"].ToString();
33                string DQRQ = dr[i]["DaoQiRiQi"].ToString();
34                string ZT = dr[i]["ZhuangTai"].ToString();
35                string TJSJ = dr[i]["TianJiaShiJian"].ToString();
36                string JXDM = dr[i]["JiaXiaoDaiMa"].ToString();
37
38                string sqlcheck = "select count(*) from DC_YongHuLieBiao where YongHuMingCheng='" + YHMC + "'And JiaXiaoDaiMa='" + JXDM + "'";  //检查用户是否存在
39                bool ch = ac.check(sqlcheck);    
40                if (ch == true)
41                {
42                    string insertstr = "insert into DC_YongHuLieBiao(YongHuMingCheng,YongHuMiMa,DaoQiRiQi,ZhuangTai,TianJiaShiJian,JiaXiaoDaiMa) values('" +
43                                        YHMC + "','" + YHMM + "','" + DQRQ + "','" + ZT + "','" + TJSJ + "','" + JXDM + "')";
44
45                    SqlCommand cmd = new SqlCommand(insertstr,cn);
46                    try
47                    {
48                        cmd.ExecuteNonQuery();
49                    }
50                    catch (MembershipCreateUserException ex)       //捕捉异常
51                    {
52                        Response.Write("<script>alert('创建用户:"+ex.Message+"')</script>");
53                    }
54
55                }
56                else
57                {
58                    error += "<em style='color:red;font-sixe:25px'>"+YHMC+"</em>用户已存在,此行记录无法插入!请修改用户再进行插入 <br>";   //若用户存在,将已存在用户信息打出,并提示此用户无法插入
59                    continue;
60                }
61
62                
63
64            }
65
66            Response.Write("<script>alert('Excle表导入成功!')</script>");
67            Label1.Text = error;
68        }
69
70        cn.Close();
71        
72    }
73