Click here to Skip to main content
15,867,985 members
Articles / Web Development / ASP.NET
Article

Exporting DataGrid to Excel, Word and Text Files

Rate me:
Please Sign up or sign in to vote.
4.63/5 (41 votes)
21 Jul 20053 min read 820K   11.8K   256   125
How to export DataGrid to Excel, Word and Text files.

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.

C#
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:

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

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.

C#
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:

C#
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:

VB
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.

VB
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!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
United States United States
My name is Mohammad Azam and I have been developing iOS applications since 2010. I have worked as a lead mobile developer for VALIC, AIG, Schlumberger, Baker Hughes, Blinds.com and The Home Depot. I have also published tons of my own apps to the App Store and even got featured by Apple for my app, Vegetable Tree. I highly recommend that you check out my portfolio. At present I am working as a lead instructor at DigitalCrafts.




I also have a lot of Udemy courses which you can check out at the following link:
Mohammad Azam Udemy Courses

Comments and Discussions

 
QuestionExport excel Pin
Member 1432206531-Oct-21 18:31
Member 1432206531-Oct-21 18:31 
Questionexport gridview to excel then found <div> and </div> in my excel sheet..... whats the problem resolved it Pin
Member 1236041518-Mar-16 2:11
Member 1236041518-Mar-16 2:11 
QuestionAnd response is... Pin
Member 117167354-Sep-15 2:12
Member 117167354-Sep-15 2:12 
QuestionHow to export dataset to Excel with Multiple worksheet in ASP.Net Pin
Chad Sesbreno3-Jul-14 23:00
Chad Sesbreno3-Jul-14 23:00 
GeneralMy vote of 5 Pin
gaga blues2-Nov-12 4:04
gaga blues2-Nov-12 4:04 
QuestionError Glasses.Test.DataGridExport Pin
Member 87961717-Apr-12 15:29
Member 87961717-Apr-12 15:29 
QuestionFree library, to export from a DataSet into a real Excel 2007 .xlsx using OpenXML Pin
Michael Gledhill1-Dec-11 1:38
Michael Gledhill1-Dec-11 1:38 
AnswerRe: Free library, to export from a DataSet into a real Excel 2007 .xlsx using OpenXML Pin
Chris G.27-Mar-12 9:43
Chris G.27-Mar-12 9:43 
QuestionLarge data import Pin
nikunjmochi16-Oct-11 22:16
nikunjmochi16-Oct-11 22:16 
Questionexporting gridview along with some text at the top of the sheet Pin
jyoti mahalik10-Feb-11 20:33
jyoti mahalik10-Feb-11 20:33 
GeneralPC vs Mac Pin
Joe Sweeney28-Jul-10 11:06
Joe Sweeney28-Jul-10 11:06 
GeneralExporting Grid to Plain Text Pin
shyjup4-Jun-10 7:27
shyjup4-Jun-10 7:27 
Generaldivision of cell in datagrid Pin
pujabhatia20109-Mar-10 2:17
pujabhatia20109-Mar-10 2:17 
QuestionDo you have a code for VB.NET that do the same thing? Pin
Nire Memu4-May-09 1:48
Nire Memu4-May-09 1:48 
QuestionRe: Do you have a code for VB.NET that do the same thing? Pin
RavinderSingroha15-May-09 3:45
RavinderSingroha15-May-09 3:45 
AnswerRe: Do you have a code for VB.NET that do the same thing? Pin
CikaPero8-Sep-10 2:02
CikaPero8-Sep-10 2:02 
GeneralChange Format without Looping Pin
soni_ace26-Mar-09 20:31
soni_ace26-Mar-09 20:31 
GeneralRe: Change Format without Looping Pin
azamsharp27-Mar-09 4:43
azamsharp27-Mar-09 4:43 
GeneralRe: Change Format without Looping Pin
Member 438356512-May-09 10:46
Member 438356512-May-09 10:46 
I know this a pretty old post but I just found it. I adapted this code to format all cells as a string and didn't do any looping. For my needs, this ended up being much faster... I'm just passing this along in case it helps someone:



Instead of just a normal "Response.Write(sw.ToString());" , I replaced it with this:



<br />
        string StyleAsText = @"<style> .StyleAsText { mso-number-format:\@; } </style> ";<br />
        Response.Write(StyleAsText + sw.ToString().Replace("<td>", "<td class='StyleAsText'>"));<br />
<br />



Good luck!

A. Soong
GeneralHelp Pin
Saranya.Zy1-Mar-09 20:42
Saranya.Zy1-Mar-09 20:42 
Questionproblem in export datagrid to excel Pin
meg2219-Feb-09 23:28
meg2219-Feb-09 23:28 
GeneralHelp.... Pin
Chris O'N22-Jan-09 3:28
Chris O'N22-Jan-09 3:28 
NewsNot the right way Pin
chandan10013-Dec-08 5:07
chandan10013-Dec-08 5:07 
GeneralRe: Not the right way Pin
azamsharp3-Dec-08 8:20
azamsharp3-Dec-08 8:20 
GeneralRe: Not the right way Pin
CikaPero15-Sep-10 22:25
CikaPero15-Sep-10 22:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.