Export Gridview Data to Excel from WebPage with formatting

pratim gupta

10y2.5k.NET

Hi,

I have a gridview populated and on a button click I am exporting it to an Excel spreadsheet. This is working absolutely fine. The problem is that there are about 8 columns of data that is being exported and what I need to do is that to have the first 5 columns formatted as text and the last 3 columns to be formatted as numbers. I did see using Open XML probably I can format it accordingly but need solution suggestions and some sample code to get it to work. Thanks in advance.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajeev Punhani10y ago
Accepted answer
Hi Pratim,
Just Make a small change in the below code
foreach (TableCell cell in row.Cells)
{
if (cell.Text.ToString() == ColumnText||cell.Text.ToString() == ColumnText||cell.Text.ToString() == ColumnText)
{
e.Row.Cells[5].Attributes.Add("class","textmode1");
e.Row.Cells[6].Attributes.Add("class","textmode1");
e.Row.Cells[7].Attributes.Add("class","textmode1");

}
else
{
e.Row.Cells[0].Attributes.Add("class","textmode");
}

}

In style create one more class as .textmode1
stringstyle=@"";
If the above solution is helpful then,accept the answer.
Rajeev Punhani10y ago
Hi Pratim,
Just save that file as csv and see if leading zeros are present.If zero's are present then you have to
To create a custom number format, follow these steps:
On the Format menu, click Cells.
Click the Number tab.
Click Custom.
In the Type box, type the custom format.
Click OK.
pratim gupta10y ago
Hey Rajeev,
Thanks for the solution. I did something same and it is working as expected. Thanks again.
I need one more tip though. I have a value as '008949' and I am formatting it as Text but when it shows up in the spreadsheet it is truncating the leading 0's (showing as 8949). Is there a specific format that I can use in the style tag to have it printed with the leading 0's?
Rajeev Punhani10y ago
Hi Pratim,
Put the above code in for loop.and provide your Column Names of 6,7,8 column for ColumnText .
pratim gupta10y ago
Hey Rajeev,
Thank you for the code snippet. I tried the code and it seems to be formatting everything as text, which I had accomplished. My need was to format the columns individually like the first 5 as text and the next 3 columns as Number. Thanks.
Rajeev Punhani10y ago
Hi Pratim,
Please find the below code.
stringattachment="attachment;filename=Sample.xls";
Response.ClearContent();
Response.AddHeader("content-disposition",attachment);
Response.ContentType="application/ms-excel";
Response.ContentType.ToString();
StringWritersw=newStringWriter();
HtmlTextWriterhtw=newHtmlTextWriter(sw);
for(inti=0;i
{
GridViewRowrow=grd1.Rows[i];
//ApplytextstyletoeachRow
row.Attributes.Add("class","textmode");
}
grd1.RenderControl(htw);
//formattheexcelcellstotextformat
stringstyle=@"";
Response.Write(style);
Response.ContentType="application/text";
Response.Write(sw.ToString());
Response.End()
For styling option of columns please find the below link.
http://cosicimiento.blogspot.in/2008/11/styling-excel-cells-with-mso-number.html.
If the above solution is helpful then,accept the answer.