Exporting datagrid to Excel:
Exporting datagrid to excel might sounds complex but its 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 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.
| I would like to thank Sonu Kapoor for helping me with the Format issue in Exporting DataGrid to Excel and Juss for providing the code. |
Format Issue when Exporting datagrid to Excel:
When you export the datagrid to Excel it looses it format. It means that maybe your datagrid has string field which consisted of numbers say '002345'. But when you export the grid and see it in 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 As String
Dim oStringWriter As New System.IO.StringWriter
Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
Dim objStreamWriter As StreamWriter
Dim strStyle As String = "<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 that what is 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 As String = "<style>.text { mso-number-format:\@; } </style>"
For intTemp As Integer = 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 going opening excel file and typing the 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 that column SSN uses what style. 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 the complete discussion on this issue please visit the following like:
http://forums.asp.net/ShowPost.aspx?PageIndex=2&PostID=893621#893621
I hope you liked the article, happy coding !
download code files
About the Author:
My name is Mohammad Azam. You might know me as "AzamSharp". I started programming at the age of 13 with GW BASIC as my first programming language. Later I switched to C programming language and now I exclusively develop solutions in C#.
I have written several articles for many websites. Some of them are also published on Microsoft Official Website www.asp.net. You will find most of my articles on www.codersource.net.
I spend most my time reading .net articles and posting solutions to questions on asp.net forums. I am also Top 25 poster on Asp.net forums. Currently I am an undergraduate student in University of Houston. I will be graduating in Fall 2005. I am also working as an Asp.net developer for my university.
My hobbies include listening music, playing video games and walk on a sunny day. Recently I got engaged to the most beautiful girl in the world which is the best thing ever happened to me. I would also like to thank my parents for all their support and love they have given me on every step of life.
You can contact me at azamsharp@gmail.com.
