先在类中定义一个方法名为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上面那代码是在社区里面看的,有两点我想不明白的,想向各位大是求教下,里面类的有两个过程他没写出来,一个是:SqlConnection cn = ac.myConnection(); 另一个是: bool ch = ac.check(sqlcheck); 本人是刚刚用C#的,想问下各位那两个过程的代码是什么,谢谢
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上面那代码是在社区里面看的,有两点我想不明白的,想向各位大是求教下,里面类的有两个过程他没写出来,一个是:SqlConnection cn = ac.myConnection(); 另一个是: bool ch = ac.check(sqlcheck); 本人是刚刚用C#的,想问下各位那两个过程的代码是什么,谢谢
2.返回bool值的检查函数至于干什么使,得看具体代码
这个可能是把连接数据库的字符串封装到这个方法里面了
bool ch = ac.check(sqlcheck);
这个是检测用户名是否已经存在的方法
就是一个连接数据库 然后查询数据库中的数据 如果没有这条数据返回 true
有就返回FALSE
==========================
ac.myConnection()是他自己定义的,应该是返回一个连接实例另一个是: bool ch = ac.check(sqlcheck); 本人是刚刚用C#的,想问下各位那两个过程的代码是什么,谢谢
=========================
string sqlcheck = "select count(*) from DC_YongHuLieBiao where YongHuMingCheng='" + YHMC + "'And JiaXiaoDaiMa='" + JXDM + "'";
bool ch = ac.check(sqlcheck); //这个方法应该是执行sqlcheck这条sql语句.返回一个bool值,
判断YongHuMingCheng='" + YHMC + "'And JiaXiaoDaiMa='" + JXDM + "'"; 是否存在
insert [ODBC;Driver=SQL Server;Server=SQL服务器名;Uid=用户名;Pwd=密码;database=数据库名].接受数据导入的表名(字段列表)
SELECT 导入的字段列表
FROM [Excel 5.0;HDR=NO;DATABASE=c:\test.xls].[sheet1$]
protected void Up_Btn_Click(object sender, EventArgs e)
{
if (Up_File.PostedFile.FileName == "")
{
Response.Write("<script language=javascript>alert('请选择要上传的文件!');</script>");
return;
}
else
{
string str = Up_File.PostedFile.FileName.ToString();
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + str + ";Extended Properties=Excel 8.0";
OleDbConnection thisconnection = new OleDbConnection(conn);
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
DataSet mydataset = new DataSet();
mycommand.Fill(mydataset, "[Sheet1$]");
Up_Grid.DataSource= mydataset;
Up_Grid.DataMember= "[Sheet1$]";
Up_Grid.DataBind();
int count = mydataset.Tables["[Sheet1$]"].Rows.Count;
for (int j = 0; j < count; j++)
{
string id_1, id_2, id_3;
id_1 = mydataset.Tables["[Sheet1$]"].Rows[j]["学号"].ToString().Trim();
id_2 = mydataset.Tables["[Sheet1$]"].Rows[j]["姓名"].ToString().Trim();
id_3 = mydataset.Tables["[Sheet1$]"].Rows[j]["成绩"].ToString().Trim();
SqlConnection con1 = new SqlConnection(ConfigurationManager.AppSettings["strcnn"]);
con1.Open();
SqlCommand scd1 = new SqlCommand("select count(*) from Text where Name='" + id_1 + "'And Num='" + id_2 + "'", con1);
int count1 = Convert.ToInt32(scd1.ExecuteScalar());
con1.Close();
if (count1 == 0)
{
SqlConnection thisconnection1 = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["strcnn"]);
thisconnection1.Open();
string excelsql = "insert into Text(Name,Num,Content) values ('" + id_1 + "','" + id_2 + "','" + id_3 + "') ";
SqlCommand mycommand1 = new SqlCommand(excelsql, thisconnection1);
mycommand1.ExecuteNonQuery();
thisconnection1.Close();
}
else
{
Response.Write("管理员用户名: " + id_1 + " 管理员类型: " + id_2 + " 管理员单位: ");
}
}
Response.Write("更新成功");
}
}