Excel报表

1.Excel报表导入到GridView

 protected void Page_Load(object sender, EventArgs e)

        {

            string path = Server.MapPath("~/Book1.xls");

            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + path);

            conn.Open();

            OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", conn);

            DataSet ds = new DataSet();

            da.Fill(ds);

            this.GridView1.DataSource = ds;

            this.GridView1.DataBind();

            string path = Server.MapPath("~/Book1.xls");

            this.GridView1.DataSource = GetExcel(path);

            this.GridView1.DataBind();

        }

        public DataSet GetExcel(string filepath)

        {

            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";Data Source=" + filepath);

            conn.Open();

            OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", conn);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

            string sql = string.Empty;

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

            {

                sql += string.Format("insert into class values({0},'{1}','{2}','{3}')",ds.Tables[0].Rows[i][0].ToString(),ds.Tables[0].Rows[i][1].ToString(),ds.Tables[0].Rows[i][2].ToString(),ds.Tables[0].Rows[i][3].ToString());

            }

        }

2.Excel数据导入到数据库

 protected void Page_Load(object sender, EventArgs e)

        {

            string path = Server.MapPath("~/Book1.xls");

            DataSet ds = GetExcel(path);

            string sql = string.Empty;

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

            {

                sql += string.Format("insert into class values({0},'{1}','{2}','{3}')", ds.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][2].ToString(), ds.Tables[0].Rows[i][3].ToString());

            }

            SqlHelper.ExecuteNonQuery(sql);

        }

        public DataSet GetExcel(string filepath)

        {

            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";Data Source=" + filepath);

            conn.Open();

            OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", conn);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }