c# - 搜索数据库中的现有数据后如何将数据gridview导出到excel？我正在使用 asp.net c#

Question

搜索条形码编号后，我在将数据网格视图导出到 excel 时遇到问题。如果我不使用搜索条形码编号，我可以将数据导出到 Excel。但我想过滤数据，以便我可以通过我搜索的特定条形码编号将其导出到 excel。

这是 test.aspx ：

    <div class="container">
        <asp:Image ID="Image3" ImageUrl="~/Images/LabelBarcode.png" runat="server" />
        <asp:TextBox ID="BarcodeSearch" runat="server" Width="300px" Height="35px" Font-Bold="true" CssClass="form-control" 
            AutoPostBack="true" OnTextChanged="btnSearch_Click" ></asp:TextBox> 
        <br />

    </div>
    <br />

    <div style="margin-left:50px;">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns ="false" Font-Names ="Arial" Font-Size ="11pt" AlternatingRowStyle-BackColor ="#C2D69B"  
            HeaderStyle-BackColor ="green" AllowPaging ="true" OnPageIndexChanging ="OnPaging" PageSize="25" >
            <Columns>
               <asp:TemplateField HeaderText="No." ItemStyle-HorizontalAlign="Center" ItemStyle-Width="30px">
                   <ItemTemplate>
                       <%# Container.DataItemIndex + 1 %>
                   </ItemTemplate>
               </asp:TemplateField>
               <asp:BoundField ItemStyle-Width = "150px" DataField = "BarcodeNumber" HeaderText = "BarcodeNumber" />
               <asp:BoundField ItemStyle-Width = "150px" DataField = "CycleDate" HeaderText = "CycleDate"/>
               <asp:BoundField ItemStyle-Width = "150px" DataField = "ProductDesc" HeaderText = "ProductDesc"/>
               <asp:BoundField ItemStyle-Width = "150px" DataField = "AccountNumber" HeaderText = "AccountNumber"/>
               <asp:BoundField ItemStyle-Width = "150px" DataField = "ReasonDesc" HeaderText = "ReasonDesc"/>
            </Columns> 
        </asp:GridView>
    </div>
    <br />
    <div style="margin-left:50px;">
        &nbsp;<asp:Button ID="btnExportExcel" runat="server" Text="Export To Excel" CssClass="btn-danger" Visible="false" onclick="btnExportExcel_Click" />&nbsp;

    </div>
</form>

这是我的 test.aspx.cs 代码：

protected void Page_Load(object sender, EventArgs e) {

        if (!IsPostBack)
        {

            using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ICMPCRTN"].ToString()))

                try
                {
                    cn.Open();
                    DataTable dtX = new DataTable();
                    SqlCommand cmd = new SqlCommand("select TOP 10 * from [PRORETURN].[dbo].[tblMainData] ", cn);

                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dtX);
                    GridView1.DataSource = dtX;
                    GridView1.DataBind();
                    GridView1.Visible = true;

                    btnExportExcel.Visible = true;

                }
                catch (System.Exception err)
                {
                    string display = err.Message;
                    ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + display + "');", true);
                }
                finally
                {
                    cn.Close();
                }
        }

    }


    protected void btnSearch_Click(object sender, EventArgs e)
    {
        using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ICMPCRTN"].ToString()))

            try
            {
                cn.Open();
                DataTable dtX = new DataTable();
                SqlCommand cmd = new SqlCommand("select * from [PRORETURN].[dbo].[tblMainData] where BarcodeNumber = @bcode ", cn);
                cmd.Parameters.Add(new SqlParameter("@bcode", BarcodeSearch.Text.ToString()));

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dtX);
                GridView1.DataSource = dtX;
                GridView1.DataBind();
                GridView1.Visible = true;

                BarcodeSearch.Text = "";
                BarcodeSearch.Focus();

                btnExportExcel.Visible = true;

            }
            catch (System.Exception err)
            {
                string display = err.Message;
                ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('" + display + "');", true);
            }
            finally
            {
                cn.Close();
            }
    }

    protected void OnPaging(object sender, GridViewPageEventArgs e)
    {

        GridView1.PageIndex = e.NewPageIndex;
        btnSearch_Click(sender, e);
        GridView1.DataBind();
    }

    protected void btnExportExcel_Click(object sender, EventArgs e)
    {
        
        Response.Clear();
        Response.Buffer = true;

        Response.AddHeader("content-disposition", "attachment;filename=TestExport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        
        GridView1.DataBind();
        btnSearch_Click(sender, e);

        //Change the Header Row back to white color
        GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

        //Apply style to Individual Cells
        GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[4].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[5].Style.Add("background-color", "green");

        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            GridViewRow row = GridView1.Rows[i];

            //Change Color back to white
            row.BackColor = System.Drawing.Color.White;

            //Apply text style to each Row
            row.Attributes.Add("class", "textmode");

            //Apply style to Individual Cells of Alternating Row
            if (i % 2 != 0)
            {
                row.Cells[0].Style.Add("background-color", "#C2D69B");
                row.Cells[1].Style.Add("background-color", "#C2D69B");
                row.Cells[2].Style.Add("background-color", "#C2D69B");
                row.Cells[3].Style.Add("background-color", "#C2D69B");
                row.Cells[4].Style.Add("background-color", "#C2D69B");
                row.Cells[5].Style.Add("background-color", "#C2D69B");
            }
        }
        GridView1.RenderControl(hw);

        GridView1.AllowPaging = false;
        GridView1.AllowSorting = false;

        //style to format numbers to string
        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
        HttpContext.Current.ApplicationInstance.CompleteRequest(); 
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        
    }

标签： c#asp.netexcel