Exporting data from gridview to excel
Posted on October 19, 2007 - Filed Under Uncategorized |
Sometimes, we come across to a point where we need to export all items displayed in our gridview to excel spreadsheet or to a csv file for reporting purposes or as an attachements for emails, etc.
So in our html page, we should have a gridview and a button named grdSample and btnSavetoExcel.
And put the following code in the code behind page…
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
grdSample.DataSource = BindData();
grdSample.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;
}
On page upload, we bind our gridview with the datatable so it will display the proper data on the grid. In this sample,we used the categories table of the Northwind database.
protected void btnSavetoExcel_Click(object sender, EventArgs e)
{
String filename = “Sample” + DateTime.Now.ToShortDateString() + “.xls”;
Response.Clear();
Response.AddHeader(”content-disposition”, “attachment; filename=” + filename);
Response.Charset = “”;
Response.ContentType = “application/vnd.xls”;
stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
grdSample.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
Let’s dissect the code above, to better understand the logic in it.
In the event click of the btnSavetoExcel, we first create the filename for the excel file. In this sample, we created a file named sample_[current short date].xls
Usually, I put a date when naming a filename for easier retrieval and references in the future. But then it up toyou on how you would like to name your file.
Movin on, we then erases any buffered HTML output via the Response.Clear method. By the way, we used the Response object to send output to the client.
The Response.AddHeader sets the HTML header name to value. While the Response.Charset appends the name of the character set to the content-type header. The character set specifies for the browser on how to display characters.
The ContentType property specifies the HTTP content type for the response. If no ContentType is specified, the default is text/HTML.
Then we Constructs and initializes a new instance of the StringWriter class. This automatically created and associated with the new StringWriter instance.
The RenderControl will outputs server control content to a provided HtmlTextWriter object and stores tracing information about the control if tracing is enabled. The HtmlTextWriter object in this sample is the htmlWrite.
Since this will output the content back to the server, the form will require the gridview tobe inside an HTML form with runat=server…so if your page is in a contentpage…then you’ll be face with this error..
Error: Control ‘GridView1′ of type ‘GridView’ must be placed inside a form tag with runat=server
The solution for this it to have the next code:
public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the
//specified ASP.NET server control at run time.
}
For further reading on this error, check this… http://msdn2.microsoft.com/en-us/library/system.web.ui.page.verifyrenderinginserverform.aspx
Comments
Leave a Reply