Export Gridview Data to Excel from WebPage with formatting

Question

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.

Rajeev Punhani · 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(classtextmode1); e.Row.Cells[6].Attributes.Add(classtextmode1); e.Row.Cells[7].Attributes.Add(classtextmode1); } else { e.Row.Cells[0].Attributes.Add(classtextmode); } } In style create one more class as .textmode1 string style=@  .textmode{mso-number-format:\@;}.textmode1{mso-number-format: 0 ;}  ; If the above solution is helpful then accept the answer.

Rajeev Punhani · Answer

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 gupta · Answer

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 Punhani · Answer

Hi Pratim Put the above code in for loop.and provide your Column Names of 678 column for ColumnText .

pratim gupta · Answer

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 Punhani · Answer

Hi Pratim Please find the below code. string attachment= attachment;filename=Sample.xls ; Response.ClearContent(); Response.AddHeader( content-disposition attachment); Response.ContentType= application/ms-excel ; Response.ContentType.ToString(); StringWritersw= new StringWriter(); HtmlTextWriterhtw= new HtmlTextWriter(sw); for ( int i=0;i { GridViewRowrow=grd1.Rows[i]; //ApplytextstyletoeachRow row.Attributes.Add( class  textmode ); } grd1.RenderControl(htw); //formattheexcelcellstotextformat string style=@  .textmode{mso-number-format:\@;}  ; 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.