Exporting Data Grid to Excel Sheet –ASP.Net,c#

Exporting datagrid to Excel:

Exporting datagrid to excel might sounds complex but its pretty simple. Let’s see how this can be done.

1st Option

2nd Option

Response.Clear(); Response.AddHeader(“content-disposition”, “attachment;filename=FileName.xls”); Response.Charset = “”; 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); myDataGrid.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End();

      #region Create Excel Sheet
                               string strFileName = @”Report.xls”;                   // create the DataGrid and perform the databinding
                   System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
                   grid.HeaderStyle.Font.Bold = true;
                   grid.DataSource = dtTable;
                   grid.DataBind();
                   grid.DataMember = dtTable.TableName;                   using (StreamWriter sw = new StreamWriter(strFileName))
                   {
                       using (HtmlTextWriter hw = new HtmlTextWriter(sw))
                       {
                           grid.RenderControl(hw);
                       }
                   }
               #endregion

The code given above is the complete code to export the datagrid to excel file.

Response.AddHeader is letting Asp.net know that we are exporting a file which is named FileName.xls
Response.ContentType denotes the type of the file being exported
myDataGrid.RenderControl(htmlWrite) which writes the data to the HtmlTextWriter
Response.Write(stringWrite.ToString()); which send the request to the response stream.

As you can see exporting the datagrid to excel is pretty simple.

Exporting the datagrid to word file:

You can also export the datagrid to the word file. You might ask a question that why would anyone like to do that. If you have a word document which needs table than you can simple export the table from the datagrid to the word document. The code is similar to the above with little minor changes.

Response.Clear(); Response.AddHeader(“content-disposition”, “attachment;filename=FileName.doc“); Response.Charset = “”; Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = “application/vnd.word“; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); myDataGrid.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End();

The only changes we made is in bold.

Exporting the datagrid to a Text File:

Sometimes you need to export the whole datagrid to a text file. In this case you need to iterate through the dataset and concatenate the text to string or more precisely StringBuilder object. Let’s see how this can be done.

Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper selectCommandWrapper = db.GetStoredProcCommandWrapper(“sp_GetLatestArticles”); DataSet ds = db.ExecuteDataSet(selectCommandWrapper); StringBuilder str = new StringBuilder(); for(int i=0;i<=ds.Tables[0].Rows.Count – 1; i++) { for(int j=0;j<=ds.Tables[0].Columns.Count – 1; j++) { str.Append(ds.Tables[0].Rows[i][j].ToString()); } str.Append(“<BR>”); } Response.Clear(); Response.AddHeader(“content-disposition”, “attachment;filename=FileName.txt“); Response.Charset = “”; Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = “application/vnd.text“; System.IO.StringWriter stringWrite = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); Response.Write(str.ToString()); Response.End();

The important thing to note is the two for loops that iterates through the dataset and append the rows into the StringBuilder object.