asp.net_Excel数据导入数据库

      之前做过一个这样的项目，需要把Excel的数据导入到数据库，并实时在gridview上面分页显示出来(数据大于gridview一页显示的数量的时候分页,这里是显示4条)，现在先收集起来。

    DB db = new DB();
    static int num,fy; //excel数据条数,分页
    //批量添加
    protected void txtMoreadd_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
        {
            ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('请您选择Excel文件')</script> ");
            return;//当无文件时,返回
        }
        string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
        if (IsXls != ".xls")
        {
            ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('只可以选择Excel文件')</script>");
            return;//当选择的不是Excel文件时,返回
        }
        string filename = FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数
        string savePath = Server.MapPath(("upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径
        FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上
        DataSet ds = ExcelSqlConnection(savePath, filename);           //调用自定义方法
        DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
        int rowsnum = ds.Tables[0].Rows.Count;
        num = dr.Length;
        if (rowsnum == 0)
        {
            ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
        }
        else
        {
            for (int i = 0; i < dr.Length; i++)
            {
                //前面除了你需要在建立一个“upfiles”的文件夹外，其他的都不用管了，你只需要通过下面的方式获取Excel的值，然后再将这些值用你的方式去插入到数据库里面
                string user_name = dr[i]["user_name"].ToString();
                string dept_id = dr[i]["dept_id"].ToString();
                string spc_id = dr[i]["spc_id"].ToString();
                string class_id = dr[i]["class_id"].ToString();
                string room = dr[i]["room"].ToString();
                string pic = dr[i]["pic"].ToString();

                string strsql = "insert into users(user_name,dept_id,spc_id,class_id,room,pic)values('" + user_name + "','" + dept_id + "','" + spc_id + "','" + class_id + "','" + room + "','" + pic + "')";  //添加到数据库
                db.sqlEx(strsql);
            }
            
            ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('Excle表成功导入" + num + "条数据!')</script>");
            upfilecheck();
        }
    }


    #region 连接Excel  读取Excel数据   并返回DataSet数据集合
    /// <summary>
    /// 连接Excel  读取Excel数据   并返回DataSet数据集合
    /// </summary>
    /// <param name="filepath">Excel服务器路径</param>
    /// <param name="tableName">Excel表名称</param>
    /// <returns></returns>
    public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName)
    {
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
        OleDbConnection ExcelConn = new OleDbConnection(strCon);
        try
        {
            string strCom = string.Format("SELECT * FROM [Sheet1$]");
            ExcelConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, ExcelConn);
            DataSet ds = new DataSet();
            myCommand.Fill(ds, "[" + tableName + "$]");
            ExcelConn.Close();
            return ds;
        }
        catch
        {
            ExcelConn.Close();
            return null;
        }
    }
    #endregion

    //gridview显示批量上传的数据
    public void upfilecheck()
    {
        string strsql2 = @"SELECT top " + num + @" users.user_name, department.dept_name, special.spc_name, class.class_name, 
        users.room FROM users INNER JOIN
        department ON users.dept_id = department.dept_id INNER JOIN
        special ON users.spc_id = special.spc_id INNER JOIN
        class ON users.class_id = class.class_id order by user_id desc";
        DataTable dt = db.reDt(strsql2);
        GridView1.DataSource = dt;
        GridView1.DataBind();
        Label3.Text = "共有：" + num + "条记录。";
        fy = 2;
    }

表的格式如下:  9条数据

一按批量添加按钮,就有在gridview上面显示,而且自动分好页:

付DB.cs文件代码:

using System;
.......

/// <summary>
///DB 的摘要说明
/// </summary>
public class DB
{
    public DB()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }

    public SqlConnection GetCon()
    {
        string strcon = ConfigurationManager.AppSettings["sql"];
        return new SqlConnection(strcon);
    }

    ///<summary>
    ///执行SQL语句
    ///</summary>
    ///<param name="cmdstr">SQL语句</param>
    ///<returns>返回值为int型：成功返1，失败返0</returns>
    public int sqlEx(string cmdstr)
    {
        SqlConnection con = GetCon(); //连接数据库
        con.Open();//打开连接
        SqlCommand cmd = new SqlCommand(cmdstr, con);
        try
        {
            cmd.ExecuteNonQuery();//执行SQL语句并返回受影响的行数
            return 1;//成功返回1
        }
        catch
        {
            return 0;//失败返回0
        }
        finally
        {
            con.Dispose();//释放连接对象资源，比close彻底
        }
    }

    ///<summary>
    ///执行SQL查询语句
    ///</summary>
    ///<param name="cmdstr">查询语句</param>
    ///<returns>返回DataTable数据表</returns>
    public DataTable reDt(string cmdstr)
    {
        SqlConnection con = GetCon();
        SqlDataAdapter da = new SqlDataAdapter(cmdstr, con);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return (ds.Tables[0]);

    }

    ///<summary>
    ///执行SQL查询语句
    ///</summary>
    ///<param name="cmdstr">查询语句</param>
    ///<returns>返回SqlDataReader对象dr</returns>
    public SqlDataReader reDr(string str)
    {
        SqlConnection con = GetCon();//连接数据库
        con.Open();//并打开了连接
        SqlCommand cmd = new SqlCommand(str, con);
        SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return dr;//返回SqlDataReader对象dr
    }
}