Datagrid to Excel Formatting Tip

When I signed up for a blog account here, I thought all my entries would be about Datagrids. But I've been neglecting what is really my favorite topic, so tonight I'll share a fantastic tip that came to me today from a visitor to my site, Jim Cristofono.

I've been doing a lot recently with Datagrids and Excel, and Jim and I have corresponded on this topic a bit. One limitation (or so I thought), of using the RenderControl method to export a Datagrid to Excel is that you have little or no control over the formatting once the data gets to Excel. For instance, large numbers get converted to the (ugly) x.xxxxxxE-yy syntax.

Jim came up with an super clever workaround for this problem--code below:

Response.Cache.SetExpires(DateTime.Now.AddSeconds(1));
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
Response.Write("<html
xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
Response.Write("\r\n");
Response.Write("<style>  .mystyle1 " + "\r\n" + "
{mso-style-parent:style0;mso-number-format:\""+@"\@"+"\""+";} " +
"\r\n" +
"</style>");
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
DataGrid1.RenderControl(hw);
Response.AppendHeader
("content-disposition","attachment;filename=x.xls");
Response.Write(tw.ToString());
Response.End();
private void DataGrid1_ItemCreated(object sender,
System.Web.UI.WebControls.DataGridItemEventArgs e)
{
int i=0;
if (e.Item.ItemType == ListItemType.Item | e.Item.ItemType ==
ListItemType.AlternatingItem)
   {
   foreach (cRecordTemplate MyRec in arrRec)
    {
    if (MyRec.FieldType=="dbChar")
       e.Item.Cells[i].Attributes.Add("class", "mystyle1");
    i++;
    }
   }
}

Happy Datagridding!
Published Monday, April 14, 2003 10:28 PM by datagridgirl

Comments

# re: Datagrid to Excel Formatting Tip

Can you recommend a good site that just simply explains how to export data from a datagrid in VB.net to an excel file from a command button?

Tuesday, July 29, 2003 4:24 PM by jbarber

# re: Datagrid to Excel Formatting Tip

i think this snippet of code would be a lot more helpful if you more of the code, including declaration of delegates and how to iterate through the items in your item_created eventhandler. arrRec is never defined and it's difficult to understand why you're doing that without more information.

thanks!

Tuesday, November 18, 2003 6:56 PM by swingsetacid

# re: Datagrid to Excel Formatting Tip

its has helped me lot in my project

Thursday, December 04, 2003 1:49 AM by shiv

# re: Datagrid to Excel Formatting Tip

How do I center text in an Excel Spreadsheet using VB.net from a regular form. I have the data being populated from my datagrid but am unable to center it or give it a decimal or currency format.

Any help is greatly appreicated.

Sincerely,
Dan Abramson
danvbman@aol.com

Thursday, February 05, 2004 11:50 AM by Dan Abramson

# re: Datagrid to Excel Formatting Tip

for text format use
mso-number-format:"\@";

for currency format use
mso-number-format:"\0022$\0022\#\,\#\#0\.00";

to center text use
text-align:center;

For example, to center and format ALL cells to text
following line should be added using response.write() at top of the page.

<style> td {mso-number-format:"\@"; text-align:center;} </style>

Hopefully that should give you an idea.

GK

Thursday, February 05, 2004 6:52 PM by Gopi Koganti

# re: Datagrid to Excel Formatting Tip

Any thoughts on how to get the results to display in a new browser window (pop-up style)?

thanks!

js

Monday, March 08, 2004 7:50 PM by jserra

# re: Datagrid to Excel Formatting Tip

Monday, March 08, 2004 9:01 PM by Alero

# re: Datagrid to Excel Formatting Tip

just what i have been after

Tuesday, March 16, 2004 2:50 AM by revenge

# re: Datagrid to Excel Formatting Tip

Very useful tips!
Ok, how do you make sure that the cell border is on for all cells?

Friday, July 02, 2004 4:19 AM by matt

# Exporting To Excel

Friday, December 10, 2004 9:29 AM by TrackBack

# Exporting To Excel

Friday, December 10, 2004 9:36 AM by TrackBack

# Exporting To Excel

Thursday, February 03, 2005 8:24 AM by TrackBack

# re: Datagrid to Excel Formatting Tip

Any thoughts on how to get the results to display in a new browser window (pop-up style)?

Friday, May 25, 2007 1:49 AM by Arnab

# re: Datagrid to Excel Formatting Tip

Hi, do you know how to add pivot when exporting datagrid to excel

Tuesday, May 29, 2007 8:58 AM by 2lits

# re: Datagrid to Excel Formatting Tip

Hi this is one cool site.. Can you help me create a pivot table on the create xls? thanks

Tuesday, May 29, 2007 9:19 AM by 2lits

# re: Datagrid to Excel Formatting Tip

Thanks a million . Your code fragment helped me a lot.

Friday, June 22, 2007 7:32 AM by Aparna

# re: Datagrid to Excel Formatting Tip

Thanks for the example. I was in desparate need for this solution.

Thanks aTon!!

God bless you

Monday, June 25, 2007 10:20 AM by Aman Sharma

# re: Datagrid to Excel Formatting Tip

This coding is use full but please explain that arrRec.

Friday, July 20, 2007 2:49 AM by shiv

# Dicontas - Centralized Services for Email Filtering

When you feel discouraged because of the terrifying amount of effort it involves to discover what you are searching for, cheer up because you\'re one step closer to your goal.

Tuesday, September 11, 2007 11:57 PM by Dicontas - Centralized Services for Email Filtering

# re: Datagrid to Excel Formatting Tip

Thanks. This really helped. The comment from Gopi Koganti was important as well

Tuesday, September 18, 2007 11:46 AM by Joe

# re: Datagrid to Excel Formatting Tip

Hi, I don't understand following line:

foreach (cRecordTemplate MyRec in arrRec)

What are cRecordTemplate and arrRec?

Monday, October 01, 2007 3:20 AM by haris101

# re: Datagrid to Excel Formatting Tip

am facing a problem while exporting data from a Datagrid (Infragistics) to excel.

The grid has some dropdowns and data error validations. I want that when I export them to excel the drop downs must be created and the data validation like max length, numeric characters etc must be checked in the excel sheet itself. I am easily able to create the Excel Workbook. But before writing it to a file I want to put drop downs in certain cells.

Wednesday, October 31, 2007 7:13 AM by suresh

# re: Datagrid to Excel Formatting Tip

Hi,

i have a scenario where i am exporting variable having value 3.200, but when it comes to excel it is displayed as 3.2 only. last two zeros are removed.

how to format this?

Thanks in advance for your time.

Monday, December 31, 2007 2:07 AM by vijay

# re: Datagrid to Excel Formatting Tip

am facing a problem while exporting data from a Datagrid (Infragistics) to excel.

The grid has some dropdowns and data error validations. I want that when I export them to excel the drop downs must be created and the data validation like max length, numeric characters etc must be checked in the excel sheet itself. I am easily able to create the Excel Workbook. But before writing it to a file I want to put drop downs in certain cells.

Wednesday, January 02, 2008 5:42 AM by Amol Sontakke

# re: Datagrid to Excel Formatting Tip

i m Ashi Shah and i hope this will help me

Monday, March 17, 2008 3:36 AM by Ashi Shah

# re: Datagrid to Excel Formatting Tip

Hi,

very nice solution, I was desperately looking for this solution.. thanks a million...

Thursday, March 20, 2008 2:50 AM by Prabhu

# re: Datagrid to Excel Formatting Tip

Monday, June 16, 2008 8:33 AM by jack ni

# re: Datagrid to Excel Formatting Tip

i have Gridview on my form i want exactly excel like features on my asp.net page for format data in Gridview. just like Google Documents Excel provides.

Please help

Saturday, September 27, 2008 1:07 AM by Ashish

# re: Datagrid to Excel Formatting Tip

Is it possible to set the currency symbol

Monday, July 06, 2009 6:58 AM by Madhu

# Microsoft Excel Help notes &laquo; code siblings,adventures,songs and excerpts of my daily work

Pingback from  Microsoft Excel Help notes &laquo;  code siblings,adventures,songs and excerpts of my daily work

# help notes &laquo; code siblings,adventures,songs and excerpts of my daily work

Pingback from  help notes &laquo;  code siblings,adventures,songs and excerpts of my daily work

Leave a Comment

(required) 
(required) 
(optional)
(required)