ASP.NET EXCEL导入

最近准备总结一些在开发中常用的代码，这是截取在某次开发工程中导入功能的代码

aspx代码

点击下载模板

上传Excel文件

aspx.cs代码

protected void Putin_Click(object,EventArgs e)
{
if(PutinFild.HasFile)
{
if(Path.GetExtension(PutinField.FileName==”.xls”))
{
putinFild.PosteFile.SaveAs(server.MapPath(“~/upfile/Excel/temporary.xls”))
ArrayList al = ExcelSheetName(Server.MapPath(“~/upfile/Excel/temporary.xls”));/获取文档的工作列表

                DataTable dt = ExcelDataSource(Server.MapPath("~/upfile/Excel/temporary.xls"), al[1].ToString()).Tables[0];//将Execl文件读入内存保存在DataSet中
                 PutinAmMeterDatabase(dt);//将内容导入到数据库
  }
  }

}

ArrayList ExcelSheetName(string filepath)//获取文档的工作簿
{
ArrayList al = new ArrayList();
string strConn;
strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + filepath + “;Extended Properties=Excel 8.0;”;
OleDbConnection conn = new OleDbConnection(strConn);
try
{
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, “TABLE” });
conn.Close();

        foreach (DataRow dr in sheetNames.Rows)
        {
            al.Add(dr[2]);
        }
    }
    catch
    {
        conn.Close();
        ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('导入失败！上传的文档格式不对！')</script>");
    }
    return al;
}

public DataSet ExcelDataSource(string filepath, string sheetname)//将文档读取保存到DataSet中
{
    string strConn;
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
    OleDbConnection conn = new OleDbConnection(strConn);
    OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
    DataSet ds = new DataSet();
    oada.Fill(ds);
    return ds;
}

void PutinAmMeterDatabase(DataTable dt)
{

    foreach (DataRow dr in dt.Rows)
    {

        string dept = dr[1].ToString();             //部门
        if (dept!="部门")
        {
            string emp_id = dr[2].ToString();                        //工号
            string emp_name = dr[3].ToString();                      //姓名
            string emp_cid = dr[4].ToString();                       //身份证号
            string emp_password = emp_cid.Substring(12, 6);          //密码
            DataTable empdt = SqlHelper.ExecuteDataset(SqlHelper.connStr, CommandType.Text, string.Format("select emp_id from t_employee where emp_id='" + emp_id + "'  ")).Tables[0];
            if (empdt.Rows.Count==0)
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, "insert into t_employee (emp_id,emp_name,emp_password,emp_cid)VALUES('" + emp_id + "','" + emp_name + "','" + emp_password + "','" + emp_cid + "')");
            }
        }
       }
   }