先在类中定义一个方法名为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
然后设计页面,在此作个简单的页面
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 >
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
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