ASP.NET实现Excel导入数据库
1、在页面拖入所需控件分别为：FileUpload和Button
2、进入后台编写代码，首先编写导入方法：
/// <param name="filenameurl">服务器路径</param>
/// <param name="table">表名</param>
public DataSet ExecleDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
odda.Fill(ds, table);
return ds;
}
3、继续编辑导入按钮：
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)//检查FileUpload是否有文件
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//获取文件扩展名
if (IsXls != ".xlsx")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;
}
string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName; //获取Execle文件名 DateTime日期函数
string savePath = Server.MapPath(("~\\Files\\") + filename);//获得虚拟服务器相对路径(注：在项目中建立Files文件夹)
FileUpload1.SaveAs(savePath); //将上传文件保存
DataSet ds = ExecleDs(savePath, filename); //调用自定义方法
DataRow[] dr = ds.Tables[0].Select();
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表!')</script>");
}
else
{
for (int i = 0; i < dr.Length; i++)
{
string name = dr[i]["姓名"].ToString();
string sex = dr[i]["性别"].ToString();
string age = dr[i]["年龄"].ToString();
//其中姓名，性别，年龄为Excel表头
//此处为添加数据库的insert into
}
Response.Write("<script>alert('Excle表导入成功!');</script>");
}
}
