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
然后设计页面,在此作个简单的页面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>
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
当点击添加按钮时激发事件,代码如下
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![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
55
}
56
else
57
{
58
error += "<em style='color:red;font-sixe:25px'>"+YHMC+"</em>用户已存在,此行记录无法插入!请修改用户再进行插入 <br>"; //若用户存在,将已存在用户信息打出,并提示此用户无法插入
59
continue;
60
}
61![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
62
63![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
64
}
65![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
66
Response.Write("<script>alert('Excle表导入成功!')</script>");
67
Label1.Text = error;
68
}
69![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
70
cn.Close();
71
72
}
73![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)