点击提交按钮，将网格视图导出到excel表格
本文关键字：excel 表格 视图 网格 提交 按钮 | 更新日期: 2023-09-27 18:20:15
我正在创建一个c#应用程序。在这个应用程序中，我从用户那里获取输入，并使用这些条目触发sql查询，并在Gridview中显示结果。所以当我们点击提交按钮时，这些操作就会发生。之后，我想给用户一个选项，通过点击另一个提交按钮，将网格视图结果导出到excel表中。这些东西的代码是：
aspx代码：
<body>
<form id="form1" runat="server">
<div>
<b>Enter Value 1 :</b>
<asp:TextBox ID="p1" runat="server" />
<br />
<b>Enter value 2 :</b>
<asp:TextBox ID="p2" runat="server" /><br />
<asp:Button ID="btn1" runat="server" OnClick="Button1_Click" Text="Start Search" />
<asp:Button ID="btn2" runat="server" OnClick="Button2_Click" Text="Export Data to Excel" />
<hr />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true"
OnPageIndexChanging="gridView_PageIndexChanging"
ShowFooter="false"
CssClass="gridstyle"
EnableViewState="false"
AllowPaging="true">
<AlternatingRowStyle CssClass="altrowstyle" />
<HeaderStyle CssClass="headerstyle" />
<RowStyle CssClass="rowstyle" />
<RowStyle Wrap="false" />
<HeaderStyle Wrap="false" />
</asp:GridView>
</div>
</form>
</body>
此代码的代码文件为：
public partial class pSearch : System.Web.UI.Page
{
SqlConnection sqlconn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);
DataSet dsldata;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button2_Click(object sender, EventArgs e)
{
DataSet ds0 = new DataSet();
ds0 = (DataSet)Session["data"];
DataView dataview_ldata = dsldata.Tables[0].DefaultView;
DataTable dt = dsldata.Tables[0];
GridView1.DataSource = dataview_ldata;
GridView1.DataBind();
ExportToExcel(GridView1);
}
private void ExportToExcel(GridView GrdView)
{
try
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
Response.Charset = "";
// If you want the option to open the Excel file without saving than
// comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GrdView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string RName = Page.Request.QueryString["RName"];
string typeofquery = "mycommand";
string pv1 = p1.Text ;
string pv2 = p2.Text ;
string abc = null;
abc = "" + typeofquery + " @RName=" + RName + ",@P1='" + pv1 + "',@P2='" + pv2 + "'";
SqlDataAdapter cmdldata = new SqlDataAdapter(abc, sqlconn);
GridView1.PageSize = 1000;
cmdldata.SelectCommand.CommandTimeout = 600;
dsldata = new DataSet();
ErrorHandling errhandle = new ErrorHandling();
try
{
cmdldata.Fill(dsldata);
Session["data"] = dsldata;
DataView dataview_ldata = dsldata.Tables[0].DefaultView;
DataTable dt = dsldata.Tables[0];
GridView1.DataSource = dataview_ldata;
GridView1.DataBind();
}//end of try
catch (Exception ex)
{
String errorMessage = errhandle.displayException(ex);
Response.Write(errorMessage);
}//end of catch
finally
{
if (errhandle != null)
{
errhandle = null;
}
}//end of finally
}
public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time.
}
protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
}
我现在得到一张空的excel表作为我的输出。
根据我的分析，我认为问题如下：单击按钮1时，查询将生成网格视图。当点击按钮2时，它之前的所有数据都会丢失（尽管我们仍然可以在屏幕上看到它）。这就是为什么我现在得到一个空的excel表。
其次，由于我是在点击按钮而不是在页面加载时创建网格视图，因此网格视图的数据在protected void Button2_Click（对象发送方，EventArgs e）｛｝作用
现在唯一能让它工作的选项是，我应该在button2_Click函数上再次执行查询的相同操作。
但我再次认为这不是一个有效的方式。此外，我正在运行一个复杂的查询，有时可能需要3-4分钟才能给出输出。因此，运行两次查询是不可能的。我还尝试缓存数据集，但没有成功。甚至在不工作的情况下创建会话。
从上一天起，我就在这件事上伤透了脑筋。请帮帮我！非常感谢。
Cache听起来是一个很好的解决方案，你能发布你的缓存代码吗？
否则，我会在ViewState中执行此操作。获取长查询的结果，将其存储在ViewState中，将结果绑定到GridView，然后单击按钮后，您可以访问ViewState存储的数据。对于大型数据集来说，这不是最好的方法，因为ViewState是通过每个PostBack发送的，并存储在客户端。它确实会降低应用程序的速度，并导致意外错误。
这是我在本地工作的编辑：
将其存储在会话中应该可以正常工作。以下是我对一些伪数据所做的操作。仅供参考，您可以将GridView直接绑定到DataSet，而不必深入到DataTable或类似的东西。
protected void Button1_Click(object sender, EventArgs e)
{
string RName = Page.Request.QueryString["RName"];
string typeofquery = "mycommand";
string pv1 = p1.Text;
string pv2 = p2.Text;
string abc = null;
abc = "" + typeofquery + " @RName=" + RName + ",@P1='" + pv1 + "',@P2='" + pv2 + "'";
SqlDataAdapter cmdldata = new SqlDataAdapter(abc, sqlconn);
GridView1.PageSize = 1000;
cmdldata.SelectCommand.CommandTimeout = 600;
var dummyDt = new DataTable();
dummyDt.Columns.Add("Sup");
dummyDt.Columns.Add("Bro");
dummyDt.Rows.Add("Test1", "test2");
dummyDt.Rows.Add("Test1", "test2");
dummyDt.Rows.Add("Test1", "test2");
dummyDt.Rows.Add("Test1", "test2");
dummyDt.Rows.Add("Test1", "test2");
dsldata = new DataSet();
dsldata.Tables.Add(dummyDt);
//ErrorHandling errhandle = new ErrorHandling();
try
{
//cmdldata.Fill(dsldata);
Session["data"] = dsldata;
//DataView dataview_ldata = dsldata.Tables[0].DefaultView;
//DataTable dt = dsldata.Tables[0];
GridView1.DataSource = dsldata;
GridView1.DataBind();
}//end of try
catch (Exception ex)
{
//String errorMessage = errhandle.displayException(ex);
Response.Write(ex.Message);
}//end of catch
finally
{
//if (errhandle != null)
//{
// errhandle = null;
//}
}//end of finally
}
protected void Button2_Click(object sender, EventArgs e)
{
//DataSet ds0 = new DataSet();
//ds0 = ;
//DataView dataview_ldata = dsldata.Tables[0].DefaultView;
//DataTable dt = dsldata.Tables[0];
GridView1.DataSource = (DataSet)Session["data"];
GridView1.DataBind();
ExportToExcel(GridView1);
}
private void ExportToExcel(GridView GrdView)
{
try
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
Response.Charset = "";
// If you want the option to open the Excel file without saving than
// comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GrdView.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
catch (Exception ex)
{
Response.Write("<script>alert('" + ex.Message + "')</script>");
}
}