ASP.NET WebForm 导出 Excel

前端代码：SqlToExcel.aspx

<div>
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 
            OnPageIndexChanging="GridView1_PageIndexChanging" AutoGenerateColumns="False" 
            EnableModelValidation="True">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="序号" />
                <asp:BoundField DataField="title" HeaderText="产品名称" />
                <asp:BoundField DataField="BarCode" HeaderText="条形码" />
                <asp:BoundField DataField="code" HeaderText="产品编号" />
                <asp:BoundField DataField="MarketPrice" HeaderText="市场价" />
                <asp:BoundField DataField="PerfectPrice" HeaderText="商城价" />
                <asp:BoundField DataField="SavePrice" HeaderText="三级分类" />
                <asp:BoundField DataField="WebconfigPic" HeaderText="品牌" />
                <asp:BoundField DataField="CompanyID" HeaderText="单位ID" />
                <asp:BoundField DataField="CompanyName" HeaderText="单位名称" />
            </Columns>
        </asp:GridView>
        <asp:RadioButtonList ID="RadioButtonList1" runat="server" RepeatDirection="Horizontal"
            Width="185px">
            <asp:ListItem Selected="True">全部数据</asp:ListItem>
            <asp:ListItem>当前页</asp:ListItem>
        </asp:RadioButtonList>
        <br />
        <asp:Button ID="Button1" runat="server" Text="导出Excel" OnClick="Button1_Click" />
    </div>

SqlToExcel.aspx.cs代码

public partial class SqlToExcel : System.Web.UI.Page
    {
        private System.Data.DataTable dbt = new System.Data.DataTable("tableInfo");
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                DisplayList();
            }
        }

        //导出Excel数据事件
        protected void Button1_Click(object sender, EventArgs e)
        {
            if (this.GridView1.Rows.Count > 0)
            {
                creatTable();
                DataTabletoExcel(dbt);
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('没有数据，无法导出……');</script>");
            }
        }

        public void DisplayList()
        {
            BLLProductNew bll = new BLLProductNew();
            GridView1.DataSource = bll.GetListToExcel();
            GridView1.DataBind();
        }

        //分页
        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            DisplayList();
        }
        private void creatTable()
        {
            DataColumn dbcol = new DataColumn();
            dbcol.ColumnName = "序号";
            dbcol.DataType = Type.GetType("System.String");
            dbt.Columns.Add(dbcol);

            DataColumn dbper = new DataColumn();
            dbper.ColumnName = "商品名称";
            dbper.DataType = Type.GetType("System.String");
            dbt.Columns.Add(dbper);

            DataColumn dbBarCode = new DataColumn();
            dbBarCode.ColumnName = "条形码";
            dbBarCode.DataType = Type.GetType("System.String");
            dbt.Columns.Add(dbBarCode);
            
            DataColumn dbpwd = new DataColumn();
            dbpwd.ColumnName = "商品编号";
            dbpwd.DataType = Type.GetType("System.String");
            dbt.Columns.Add(dbpwd);

            DataColumn dbMarketPrice = new DataColumn();
            dbMarketPrice.ColumnName = "市场价";
            dbMarketPrice.DataType = Type.GetType("System.String");
            dbt.Columns.Add(dbMarketPrice);

            DataColumn dbpPrice = new DataColumn();
            dbpPrice.ColumnName = "商城价";
            dbpPrice.DataType = Type.GetType("System.String");
            dbt.Columns.Add(dbpPrice);

            DataColumn dbpC = new DataColumn();
            dbpC.ColumnName = "三级分类";
            dbpC.DataType = Type.GetType("System.String");
            dbt.Columns.Add(dbpC);

            DataColumn dbBrand = new DataColumn();
            dbBrand.ColumnName = "品牌";
            dbBrand.DataType = Type.GetType("System.String");
            dbt.Columns.Add(dbBrand);

            DataColumn dbpCompanyID = new DataColumn();
            dbpCompanyID.ColumnName = "单位ID";
            dbpCompanyID.DataType = Type.GetType("System.String");
            dbt.Columns.Add(dbpCompanyID);

            DataColumn dbpCompanyName = new DataColumn();
            dbpCompanyName.ColumnName = "单位名称";
            dbpCompanyName.DataType = Type.GetType("System.String");
            dbt.Columns.Add(dbpCompanyName);

            if (RadioButtonList1.Text == "当前页")
            {
                //导出数据库“当前页”数据
                this.GridView1.AllowPaging = true;
            }
            if (RadioButtonList1.Text == "全部数据")
            {
                //导出数据库“全部”数据
                this.GridView1.AllowPaging = false;
            }
            DisplayList();
            foreach (GridViewRow grv in GridView1.Rows)
            {
                DataRow dr = dbt.NewRow();
                dr["序号"] = grv.Cells[0].Text;
                dr["商品名称"] = grv.Cells[1].Text;
                dr["条形码"] = grv.Cells[2].Text;
                dr["商品编号"] = grv.Cells[3].Text;
                dr["市场价"] = grv.Cells[4].Text;
                dr["商城价"] = grv.Cells[5].Text;
                dr["三级分类"] = grv.Cells[6].Text;
                dr["品牌"] = grv.Cells[7].Text;
                dr["单位ID"] = grv.Cells[8].Text;
                dr["单位名称"] = grv.Cells[9].Text;
                dbt.Rows.Add(dr);

            }
            this.GridView1.AllowPaging = true;
            DisplayList();
        }

        public void DataTabletoExcel(System.Data.DataTable db)
        {
            string hour = DateTime.Now.Hour.ToString();
            string min = DateTime.Now.Minute.ToString();
            string sec = DateTime.Now.Second.ToString();

            string path = DateTime.Now.ToString("yyyyMMddhhmmss");
            //path += "aaaa";
            //string Info = "C:\\" + path + "统计.xls";

            string Info = HttpContext.Current.Server.MapPath("") + "/CreateExcel/" + path + "统计.xls";
            Microsoft.Office.Interop.Excel.Application ex = new Microsoft.Office.Interop.Excel.Application();
            ex.Visible = false;
            object ms = Type.Missing;
            Microsoft.Office.Interop.Excel.Workbook wk = ex.Workbooks.Add(ms);
            Microsoft.Office.Interop.Excel.Worksheet ws = wk.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
            //循环列名
            for (int i = 0; i < db.Columns.Count; i++)
            {
                ws.Cells[1, i + 1] = db.Columns[i].ColumnName;
            }
            //循环数据
            for (int i = 0; i < db.Rows.Count; i++)
            {
                for (int j = 0; j < db.Columns.Count; j++)
                {
                    ws.Cells[i + 2, j + 1] = db.Rows[i][j].ToString();
                }
            }
            wk.SaveAs(Info, ms, ms, ms, ms, ms, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, ms, ms, ms, ms, ms);
            ex.Quit();
         }
    }

dal层方法

 public List<ProductNewModel> GetListToExcel()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT a.id as aid,a.title as atitle,a.BarCode as aBarCode,a.Code as aCode,a.MarketPrice as aMarketPrice,a.PerfectPrice as aPerfectPrice,a.CompanyName as aCompanyName,b.title as btitle,c.title as ctitle ");
            sql.Append("FROM WR_ProductNew as a ");
            sql.Append("LEFT JOIN WR_Brand as b ");
            sql.Append("ON a.brandid = b.id ");
            sql.Append("LEFT JOIN WR_Product_Category3 as c ");
            sql.Append("ON a.Category3ID = c.id ");
            sql.Append("ORDER BY a.id");
            DataSet ds = QueryDataSet(sql.ToString());
            List<ProductNewModel> list = new List<ProductNewModel>();
            ProductNewModel model;
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                model = new ProductNewModel();
                model.ID = DataConvert.ToInt32(ds.Tables[0].Rows[i]["aid"].ToString());
                model.Title = DataConvert.ToString(ds.Tables[0].Rows[i]["atitle"].ToString());
                model.BarCode = DataConvert.ToString(ds.Tables[0].Rows[i]["aBarCode"].ToString());
                model.Code = DataConvert.ToString(ds.Tables[0].Rows[i]["aCode"].ToString());
                model.MarketPrice = DataConvert.ToString(ds.Tables[0].Rows[i]["aMarketPrice"].ToString());
                model.PerfectPrice = DataConvert.ToString(ds.Tables[0].Rows[i]["aPerfectPrice"].ToString());
                model.CompanyName = DataConvert.ToString(ds.Tables[0].Rows[i]["acompanyName"].ToString());
                model.SavePrice = DataConvert.ToString(ds.Tables[0].Rows[i]["ctitle"].ToString());
                model.WebconfigPic = DataConvert.ToString(ds.Tables[0].Rows[i]["btitle"].ToString());
                list.Add(model);
            }
            return list;
        }