如何将Gridview中的所有行导出为excel? [英] How to export all rows in a Gridview to excel ?
问题描述
我是asp.net的初学者；我的数据网格中有35条记录；并将GridView的pagesize设置为10.这是我的问题，当我尝试将数据导出到Excel时，只能获得当前显示在Grid中的前10条记录.
如何将所有35条记录导出到Excel.请提出建议.
Hi,
I''m a beginer of asp.net; I have 35 records in my datagrid; and I set pagesize of my GridView as 10. Here my problem is, when I try to export data to Excel, am able to get only first 10 records that are currenlty displayed in the Grid.
how can I export all the 35 records into Excel. Please advice.
推荐答案
t was perfectly alright but following Article has added Advantage over the Previous Post.
You can able to Hide Few Fields and Display desired fields in Exported Excel Sheet, bydefault all the fields which are displayed in datagrid was displayed in Excel, but let say we want to hide "Edit" and "Delete" field while exporting datagrid data to excel. (Note: You can also use datagrid1.Columns[4].Visible = false;)
Format Datagrid Data. Full control over formatting of data as compare to previous method.
Replace Control before exporting to excel. eg: Removing Hyperlink, dropdownlist, checkbox and other controls before exporting data to excel.
Can Export All data @ once, bydefault you can only export page full of data.
Lets understand by example, consider following datagrid, which contain "Employee Name" and "Email" as Hyperlink, but while exporting data i want Hyperlink from email should be removed.
Utility Code: It is self explanatory
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
DataTable dtOriginal = new DataTable();
dtOriginal = ReturnTable(); //Return Table consisting data
//Create Tempory Table
DataTable dtTemp = new DataTable();
//Creating Header Row
dtTemp.Columns.Add("<b>Employee Name</b>");
dtTemp.Columns.Add("<b>Email</b>");
dtTemp.Columns.Add("<b>Join Date</b>");
dtTemp.Columns.Add("<b>Salary</b>");
double dSalary;
DateTime dtDate;
DataRow drAddItem;
for (int i = 0; i < dtOriginal.Rows.Count; i++)
{
drAddItem = dtTemp.NewRow();
drAddItem[0] = dtOriginal.Rows[i][0].ToString();//Name
drAddItem[1] = dtOriginal.Rows[i][1].ToString();//Email
//Join Date
dtDate = Convert.ToDateTime(dtOriginal.Rows[i][2].ToString());
drAddItem[2] = dtDate.ToShortDateString();
//Salary
dSalary = Convert.ToDouble(dtOriginal.Rows[i][3].ToString());
drAddItem[3] = dSalary.ToString("C");
dtTemp.Rows.Add(drAddItem);
}
//Temp Grid
DataGrid dg = new DataGrid();
dg.DataSource = dtTemp;
dg.DataBind();
ExportToExcel("BudgeReport.xls", dg);
dg = null;
dg.Dispose();
}
private void ExportToExcel(string strFileName, DataGrid dg)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
另一种方式
这是将GridView导出到Excel的完整代码:
使用系统；
使用System.Data;
使用System.Configuration;
使用System.Collections;
使用System.Web;
使用System.Web.Security;
使用System.Web.UI;
使用System.Web.UI.WebControls;
使用System.Web.UI.WebControls.WebParts;
使用System.Web.UI.HtmlControls;
使用System.Data.SqlClient;
公共局部类ExportGridView:System.Web.UI.Page
{
受保护的void Page_Load(对象发送者，EventArgs e)
{
如果(！Page.IsPostBack)
{
GridView1.DataSource = BindData();
GridView1.DataBind();
}
}
私有字符串ConnectionString
{
获取{return @"Server = localhost; Database = Northwind;
Trusted_Connection = true;}
}
私有数据集BindData()
{
//进行查询
字符串查询="SELECT * FROM类别";
SqlConnection myConnection =新的SqlConnection(ConnectionString);
SqlDataAdapter广告=新的SqlDataAdapter(query，myConnection);
DataSet ds = new DataSet();
ad.Fill(ds，"Categories");
返回ds;
}
受保护的void Button1_Click(对象发送者，EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition"，"attachment;
filename = FileName.xls);
Response.Charset =";
//如果您要选择不保存以下内容即可打开Excel文件
//注释掉下面的行
//Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType ="application/vnd.xls";
System.IO.StringWriter stringWrite =新的System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite =
新的HtmlTextWriter(stringWrite);
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
公共重写void VerifyRenderingInServerForm(Control control)
{
//确认为
呈现了HtmlForm控件
在运行时指定ASP.NET服务器控件.
}
}
another way
Here is the complete code to Export GridView to Excel:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class ExportGridView : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
GridView1.DataSource = BindData();
GridView1.DataBind();
}
}
private string ConnectionString
{
get { return @"Server=localhost;Database=Northwind;
Trusted_Connection=true"; }
}
private DataSet BindData()
{
// make the query
string query = "SELECT * FROM Categories";
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlDataAdapter ad = new SqlDataAdapter(query, myConnection);
DataSet ds = new DataSet();
ad.Fill(ds, "Categories");
return ds;
}
protected void Button1_Click(object sender, EventArgs e)
{
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);
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the
specified ASP.NET server control at run time.
}
}
请参见
代码项目-常见问题系列1:ASP.Net GridView [ ASP.NET中的所有导出数据 [
see
Code Project - Frequently Asked Questions Series 1: The ASP.Net GridView[^]
and
All in One Export Data in ASP.NET[^]
您可以研究以下链接^^^ 这是很好的链接之一.
希望对您有所帮助，
Theingi Win
You can study these link ^^^It is the one of good links.
Hope be helpful,
Theingi Win
这篇关于如何将Gridview中的所有行导出为excel?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
