xls表格导入数据库功能实例代码
xls表格导入数据库功能实例代码
发布时间:2016-12-29 来源:查字典编辑
摘要:复制代码代码如下:protectedvoidButton1_Click(objectsender,EventArgse){if(FileUp...

复制代码 代码如下:

protected void Button1_Click(object sender, EventArgs e)

{

if (FileUpload1.HasFile == false)

{

Page.ClientScript.RegisterStartupScript(this.GetType(), "shifou", "<script>alert('请您选择 Excel文件')</script> ");

return;

}

string Name = FileUpload1.PostedFile.FileName; //获取初始文件名

int j = Name.LastIndexOf("."); //取得文件名中最后一个"."的索引

string newext1 = Name.Substring(j); //获取文件扩展名

if (newext1 != ".xls" && newext1 != ".xlsx")

{

Page.ClientScript.RegisterStartupScript(this.GetType(), "wenjian", "<script>alert('只可以选择 Excel文件')</script>");

return;//当选择的不是Excel文件时,返回

}

SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);

cn.Open();

//savePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);// FileUpload1.PostedFile.FileName.ToString();

if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件

{

Page.ClientScript.RegisterStartupScript(this.GetType(), "wenjian", "<script>alert('Excel路径有 问题')</script>");

return;//当无文件时,返回

}

filename = FileUpload1.FileName; //Path.GetFileNameWithoutExtension (FileUpload1.PostedFile.FileName);

savePath = Server.MapPath(("../../UploadFiles/xls/") + filename);

FileUpload1.SaveAs(savePath); //保存XLS文件

DataSet ds = ExecleDs(savePath, filename); //调用自定义方法

DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组

int rowsnum = ds.Tables[0].Rows.Count;

if (rowsnum == 0)

{

//当Excel表为空时,对用户进行提示

Page.ClientScript.RegisterStartupScript(this.GetType(), "kong", "<script>alert('Excel表为空表, 无数据!')</script>");

}

else

{

for (int i = 0; i < dr.Length; i++)

{

//自定义的model-student

student.belong = "";

student.birthday = Convert.ToDateTime("1990-1-1");

student.classid = 1;

student.id = 1;

student.inschool = Convert.ToDateTime("1990-1-1");

student.phone = "";

student.sex = "";

student.type = "";

student.isaudite = "否";

student.roleid = Convert.ToInt32("22");

try

{

student.stuname = dr[i]["姓名"].ToString();

student.stuid = dr[i]["学号"].ToString();

student.username = dr[i]["姓名"].ToString();

student.userpassword = dr[i]["学号"].ToString();

}

catch(Exception ex) {

Response.Write("<script>alert('"+ex.Message+"')</script>");

return;

}

DataSet ds2 = studentbll.GetList("stuid='" + dr[i]["学号"].ToString() + "'");

if (ds2.Tables[0].Rows.Count == 0)

{

try

{

bool addstudent = Convert.ToBoolean(studentbll.Add(student));

if (addstudent)

Page.ClientScript.RegisterStartupScript(this.GetType(), "daochu", "<script>alert('Excle表导入成功!')</script>");

}

catch (MembershipCreateUserException ex) //捕捉异常

{

Page.ClientScript.RegisterStartupScript(this.GetType(), "chongfu", "<script>alert('导入内容:" + ex.Message + "')</script> ");

}

}

else

{

Page.ClientScript.RegisterStartupScript(this.GetType(), "chongfu", "<script>alert ('内容重复!禁止导入')</script> ");

continue;

}

}

}

}

public DataSet ExecleDs(string filenameurl, string table)

{

string strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + filenameurl + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";//这段字符串注意空格别写错否则会报错

OleDbConnection conn = new OleDbConnection(strConn);

OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet18$]", conn);//【sheet18$】是表格名

DataSet ds = new DataSet();

odda.Fill(ds, table);

return ds;

}

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新asp.net教程学习
热门asp.net教程学习
编程开发子分类