xx.aspx:
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
<asp:Button ID="Button2" runat="server" Text="导入数据库" OnClick="Button2_Click" />
</form>
</body>
xx.aspx.cs: //后台
//连接excel表格
public DataSet ExcelDataSource()//string filepath, string sheetname
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.FileUpload1.PostedFile.FileName.ToString() + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter oada = new OleDbDataAdapter(" SELECT * FROM [Sheet1$]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
return ds;
}
//将excel表格的数据绑定到GridViewli并显示
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//
if (IsXls != ".xls") //
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
this.GridView1.DataSource = ExcelDataSource();
this.GridView1.DataBind();
}
//将GridView数据再导入数据库
protected void Button2_Click(object sender, EventArgs e)
{
int hangshu=this.GridView1.Rows.Count;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
string id = GridView1.Rows[i].Cells[1].Text.ToString().Trim();//["用户ID"]
string name = GridView1.Rows[i].Cells[2].Text.ToString().Trim();//["用户显示名"]
string pwd = GridView1.Rows[i].Cells[7].Text.ToString().Trim();//["密码"]
pwd = FormsAuthentication.HashPasswordForStoringInConfigFile(pwd, "MD5");
string status = GridView1.Rows[i].Cells[5].Text.ToString().Trim();//["状态"]
string UserType = GridView1.Rows[i].Cells[6].Text.ToString().Trim();//["角色"]
string depart = GridView1.Rows[i].Cells[3].Text.ToString().Trim();//["部门"]
int depaetId = 0;
if (Sys_DepartmentManager.GetSys_DepartmentsByname(depart) != null)
depaetId = Sys_DepartmentManager.GetSys_DepartmentsByname(depart).SysNo;
else if (Sys_DepartmentManager.GetSys_DepartmentsByname(depart) == null)
{
Sys_Department de = new Sys_Department();
de.CreateTime = DateTime.Now;
de.CreateUserSysNo = "admin";
de.DepartmentID = " ";
de.DepartmentName = depart;
de.Note = " ";
de.Status = 0;
Sys_Department newde = Sys_DepartmentManager.AddSys_Department(de);
depaetId = newde.SysNo;
}
string company = GridView1.Rows[i].Cells[4].Text.ToString().Trim();//["公司"]
string zhanghao = GridView1.Rows[i].Cells[0].Text.ToString().Trim();//["账户在系统中的唯一号"]
string sql = "insert into Sys_User(UserID,UserName,Pwd,Email,Phone,Note,Status,UserType,DepartmentSysNo,Company,PhotoUrl,Initial) VALUES ('" + id + "','" + name + "','" + pwd + "',' ',' ',' ','" + status + "','" + Convert.ToInt32(UserType) + "','" + depaetId + "','" + company + "','no.jpg','" + zhanghao + "')";
try
{
DBHelper.ExecuteCommand(sql);
}
catch (Exception ex)
{
Response.Write("第" + i + "行数据出错");
}
}
Response.Write("导入OK");
}
1708
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
