Exporting details from GridView into Excel 2010 and formet Cell format using ASP.NET

Question

0.00/5 (No votes)

See more:

HI everyone!

I need help.
I am trying to export data from a gridview into MS Excel 2007 or 2010 file.
I am able to export but then i would like to format all the cell in that file to be Text as i have data like 1E1, 4E1.
Currently when export, instead of 1E1, it shows 1E+001.

Anyone can help me?
Thanks!!

Posted 15-Apr-12 2:19am

xbolslock

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wes Aday · Answer 1 · 2012-04-15T02:48:00

Solution 1

Apparently there are a couple of ways to do this. Maybe this will help you[^]

Posted 15-Apr-12 2:48am

Wes Aday

Comments

xbolslock 15-Apr-12 9:36am

i seen this. but is using vb. i only know how to program in c# ._.

[no name] 15-Apr-12 9:42am

So? Setting the NumberFormat in VB is the same in C#. Prepending a ' to a string is the same. If all else fails use a VB to C# converter. You didn't post any code with your question and I am not going to write a ton of code for you.

xbolslock 15-Apr-12 9:45am

i am currently using this code:

Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.AllowPaging = false;
GridView1.DataBind();
//Change the Header Row back to white color
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Applying stlye to gridview header cells
for (int i = 0; i < GridView1.HeaderRow.Cells.Count; i++)
{
GridView1.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
}
int j = 1;
//This loop is used to apply stlye to cells based on particular row
foreach (GridViewRow gvrow in GridView1.Rows)
{
//gvrow.BackColor = Color.White;
if (j <= GridView1.Rows.Count)
{
if (j % 2 != 0)
{
for (int k = 0; k < gvrow.Cells.Count; k++)
{
gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
}
}
}
j++;
}
GridView1.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}

[no name] 15-Apr-12 9:50am

And this has anything to do with Excel exactly how? This is formatting the cells in a gridview NOT an Excel sheet.

xbolslock 15-Apr-12 9:52am

oh! thx

xbolslock 15-Apr-12 10:05am

i have solve it already.

i replace this code

foreach (GridViewRow gvrow in GridView1.Rows) { //gvrow.BackColor = Color.White; if (j <= GridView1.Rows.Count) { if (j % 2 != 0) { for (int k = 0; k < gvrow.Cells.Count; k++) { gvrow.Cells[k].Style.Add("background-color", "#EFF3FB"); } } } j++; }

with

foreach (GridViewRow item in GridView1.Rows)
{
for (int i = 0; i < item.Cells.Count; i++)
{
item.Cells[i].Attributes.Add("style", "mso-number-format:\\@");
}
}