导出GridView数据到Excel

//带格式导出
            string style = @"<style> .text { mso-number-format:/@; } </script>";
            Response.ClearContent();
            Response.Charset = "GB2312";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.AddHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(FileName, Encoding.GetEncoding("GB2312")).ToString());
            Response.ContentType = "application/excel";
            gv.Page.EnableViewState = false;  
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
           
            ///导出所有数据
            gv.AllowPaging = false;
            gv.Columns[0].Visible = false; //这一列便不会显示
            gv.Columns[18].Visible = false; //这一列便不会显示
            gv.Columns[19].Visible = false; //这一列便不会显示
            Bind(this.txtFromDate.Value.Trim(), this.txtToDate.Value.Trim(), this.drpStatus.SelectedValue.Trim(), this.txtRSNNO.Text.Trim().ToUpper(), this.txtCustomerName.Text.Trim(), this.drpShipVia.SelectedValue.ToString());

            gv.RenderControl(htw);
            Response.Write(style);
            Response.Write(sw.ToString());
            Response.End();

            gv.AllowPaging = true;
            gv.Columns[0].Visible = true;
            gv.Columns[18].Visible = true;
            gv.Columns[19].Visible = true;
            Bind(this.txtFromDate.Value.Trim(), this.txtToDate.Value.Trim(), this.drpStatus.SelectedValue.Trim(), this.txtRSNNO.Text.Trim().ToUpper(), this.txtCustomerName.Text.Trim(), this.drpShipVia.SelectedValue.ToString());

 

 

 

 

 

  // 根据模板文件创建副本
        string filePath = Server.MapPath("~/" + Guid.NewGuid().ToString() + ".xls");
        File.Copy(Server.MapPath("demo.xls"), filePath);
        // 使用OleDb驱动程序连接到副本
        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");
        using (conn)
        {
            conn.Open();
            // 增加记录
            foreach(DataRow dr in dt.Rows)
            {
            OleDbCommand cmd = new OleDbCommand("INSERT INTO [Sheet1$] VALUES(@name, @chinese, @english,@math,@note,@total)", conn);
            cmd.Parameters.AddWithValue("@name", dr["name"]);
            cmd.Parameters.AddWithValue("@chinese",dr["chinese"]);
            cmd.Parameters.AddWithValue("@english", dr["english"]);
            cmd.Parameters.AddWithValue("@math", dr["math"]);
            cmd.Parameters.AddWithValue("@note", dr["note"]);
            cmd.Parameters.AddWithValue("@total", "");
            cmd.ExecuteNonQuery();
            }
        }
        // 输出副本的二进制字节流
     
        Response.ContentType = "application/ms-excel";
        Response.AppendHeader("Content-Disposition", "attachment;filename=info.xls");
        Response.BinaryWrite(File.ReadAllBytes(filePath));
        // 删除副本
        File.Delete(filePath);