Skip to main content
Email Password   helpLost your password?

Introduction

DataGrid is one of the most coolest controls in ASP.NET. One thing that all developers need is to put the DataGrid data into an Excel sheet. In this article I will show you how you can export your DataGrid data to an Excel file, a Word file and also a text file.

Exporting DataGrid to Excel

Exporting DataGrid to Excel might sound complex but it's pretty simple. Let's see how this can be done.

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();

The code given above is the complete code to export a DataGrid to an Excel file:

As you can see, exporting a DataGrid to an Excel file is pretty simple.

Exporting the DataGrid to a Word file

You can also export a DataGrid to a Word file. You might ask a question that why would anyone like to do that. If you have a Word document which needs a table, then you can simply export the table from the DataGrid to the Word document. The code is similar to the above with 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();

Exporting a DataGrid to a Text File

Sometimes you need to export a DataGrid to a Text file. In this case you need to iterate through the DataSet and concatenate the text to a string or more precisely a 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 iterate through the DataSet and append the rows into the StringBuilder object.

Format Issue when Exporting DataGrid to Excel

I would like to thank Sonu Kapoor for helping me with the Format issue in exporting a DataGrid to Excel and Juss for providing the code.

When you export a DataGrid to Excel it loses its format. This means that maybe your DataGrid has a string field which consisted of numbers, say '002345'. But when you export the grid and see it in an Excel file you will find that the number changed to '2345'.

You can solve this problem using Cascading Style Sheets.

Code provided by Juss:

Dim strFileName, strFilePath AsString
Dim oStringWriter AsNew System.IO.StringWriter
Dim oHtmlTextWriter AsNew System.Web.UI.HtmlTextWriter(oStringWriter)
Dim objStreamWriter As StreamWriter
Dim strStyle AsString = "<style>.text { mso-number-format:\@; } </style>"
objStreamWriter = File.AppendText(strFilePath)
DataGrid1.RenderControl(oHtmlTextWriter)
objStreamWriter.WriteLine(strStyle)
objStreamWriter.WriteLine(oStringWriter.ToString())
objStreamWriter.Close()

Most of you might be thinking about that "mso-number-format" stuff in between the code. This is the style in which the column will be exported. For this reason, you need to inject the attribute into the DataGrid column for which you want to change the display format.

DataGrid1.DataBind()
Dim strStyle AsString = "<style>.text { mso-number-format:\@; } </style>"
For intTemp AsInteger = 1 To ds.Tables(0).Rows.Count - 1
   DataGrid1.Items(intTemp).Cells(0).Attributes.Add("class", "text")
Next

You can export in many formats. All you need to know is the mso-number-format:\@;. You can easily find the format by opening an Excel file and typing a number in one of the cells. Now if you want to save this number as a Social Security Pattern (xxx-xx-xxxx), right click on the cell and select a pattern that saves it as a Social Security Number. Next save the Excel file to XML format. Open the XML file in Notepad and see what style the column SSN uses. The SSN style is something like this: mso-number-format:000\-00\-0000.

Simply substitute the new style in the strStyle variable and that's it.

For a complete discussion on this issue, please visit this link.

I hope you liked the article, happy coding!

You must Sign In to use this message board.
 
 
Per page   
 FirstPrevNext
QuestionDo you have a code for VB.NET that do the same thing? Pin
Nire Memu		2:48 4 May '09  
QuestionRe: Do you have a code for VB.NET that do the same thing? Pin
RavinderSingroha		4:45 15 May '09  
GeneralChange Format without Looping Pin
Member 2603267		21:31 26 Mar '09  
GeneralRe: Change Format without Looping Pin
azamsharp		5:43 27 Mar '09  
GeneralRe: Change Format without Looping Pin
Member 4383565		11:46 12 May '09  
GeneralHelp Pin
Saranya.Zy		21:42 1 Mar '09  
Questionproblem in export datagrid to excel Pin
meg22		0:28 20 Feb '09  
GeneralHelp.... Pin
Chris O'N		4:28 22 Jan '09  
NewsNot the right way Pin
chandan1001		6:07 3 Dec '08  
GeneralRe: Not the right way Pin
azamsharp		9:20 3 Dec '08  
GeneralProblem exporting datagrid to excel Pin
Anu787		0:43 2 Dec '08  
GeneralThanks Pin
nellairahul		18:40 26 Feb '09  
GeneralExport to word Pin
Member 3679733		1:09 13 Nov '08  
GeneralExport table cell to Excel - Unicode characters are not being displayed correctly Pin
Member 3950401		12:13 18 Sep '08  
GeneralRe: Export table cell to Excel - Unicode characters are not being displayed correctly Pin
gg4237		3:08 23 Oct '09  
GeneralResponse. XXXXXXX Pin
Atis		12:15 16 Sep '08  
GeneralGrid to Excel Export in ASP.Net Pin
sukanya_rathinam		0:40 7 Aug '08  
GeneralExport multiple datagrids on single sheet side by side Pin
ilja g		22:15 16 Jul '08  
GeneralServer cannot append header after HTTP headers have been sent Pin
Member 2268194		21:49 16 Jun '08  
Generalwhat about numbers being stored as text? Pin
Member 3926312		11:27 16 May '08  
GeneralHow to Export all data from grid having custom paging Pin
sameer_s		19:49 16 Apr '08  
QuestionDatatable export problem..0's are getting ommitted at few places.. Pin
prashobkumar		0:28 8 Apr '08  
QuestionError in Export Excell Pin
sanjsy		21:52 19 Mar '08  
AnswerRe: Error in Export Excell [modified] Pin
Frankie4u		23:38 15 May '08  
QuestionHow to export gridveiw to excel with multiple sheet Pin
Shrikanth Saripella		23:24 10 Mar '08  
Last Visit: 10:33 23 Nov '09     Last Update: 10:33 23 Nov '09 12345 Next »


Last Updated 21 Jul 2005 | Advertise | Privacy | Terms of Use | Copyright © CodeProject, 1999-2009