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