Click here to Skip to main content
15,919,479 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
I have one grid view called grdBeneficiary in that Beneficiary ID column contains very long number
I am trying to export this grid to excel. after exporting to excel the Beneficiary ID is looking like
VB
9.04103E+13

can any one please help me format the number to text in excel using c# code .presently am using below mentioned code.

string attachment = "attachment; filename=Emp.xls";
           Response.ClearContent();
           Response.AddHeader("content-disposition", attachment);
           Response.ContentType = "application/ms-excel";
           Response.ContentType.ToString();
           StringWriter sw = new StringWriter();
           HtmlTextWriter htw = new HtmlTextWriter(sw);
           for (int i = 0; i < grdErrorRecords.Rows.Count; i++)
           {
               GridViewRow row = grdErrorRecords.Rows[i];
               //Apply text style to each Row
               row.Attributes.Add("class", "textmode");
           }
           grdErrorRecords.RenderControl(htw);
           //format the excel cells to text format
           string style = @"<style> .textmode { mso-number-format:\@; } </style>";
           Response.Write(style);
           Response.ContentType = "application/text";
           Response.Write(sw.ToString());
           Response.End()

How to format the excel sheet to text through c# code?

thanks in advance
(Keerthi Kumar)
Posted
Updated 26-Jul-19 0:37am
v3

Add the Style to each item instead of row

Try like

item.Cells[i].Attributes.Add("style", "textmode");


Or

row.Cells[i].Attributes.Add("style", "textmode");
 
Share this answer
 
v2
Comments
Keerthi Kumar(Andar) 24-Jan-14 2:39am    
NO..its not working..
for (int i = 0; i < grdErrorRecords.Rows.Count ; i++)
{
GridViewRow row = grdErrorRecords.Rows[i];
//Apply text style to each Row
// row.Attributes.Add("style", "textmode");
for (int j = 0; j <6; j++)
{
row.Cells[j].Attributes.Add("style", "textmode");
}
}
i manteined legacy code like yours and it may brings you to headache with browser compatibility...

i suggest you using epplus (free) download it with nuget.

https://www.nuget.org/packages/EPPlus/[^]

https://epplus.codeplex.com/wikipage?title=FAQ[^]

you can format cells like you want using code like
C#
worksheet.Cells["A1:B3,D1:E57"].Style.NumberFormat.Format = "#,##0"; //Sets the numberformat for a range containing two addresses.
worksheet.Cells["A:B"].Style.Font.Bold = true; //Sets font-bold to true for column A & B
worksheet.Cells["1:1,A:A,C3"].Style.Font.Bold = true; //Sets font-bold to true for row 1,column A and cell C3
worksheet.Cells["A:XFD"].Style.Font.Name = "Arial"; //Sets font to Arial for all cells in a worksheet.

//Sets the background color for the selected range (default is A1).
//A range is selected using the by using the worksheet.Select method
worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGreen);
 
Share this answer
 
v2
Format by cells worked for me:
I wanted text format for only one column, here's what I have done:

for (int x = 0; x < gv.Rows.Count; x++)
{


//Apply text style to each cell
gv.Rows[x].Cells[3].Attributes.Add("class", "textmode");



}
 
Share this answer
 
Comments
sweety 22 14-May-19 6:00am    
this is work for me
GridView gridView = new GridView();
gridView.DataSource = Session["ExcelMetadata"]; // dtMetadata;
gridView.DataBind();

Response.ClearContent();
Response.Buffer = true;
string strDateFormat = string.Empty;
strDateFormat = string.Format("{0:yyyy-MMM-dd-hh-mm-ss}", DateTime.Now);
Response.AddHeader("content-disposition", "attachment; filename=UserDetails_" + strDateFormat + ".xls");
Response.ContentType = "application/ms-excel";
Response.Charset = "";

StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

for (int i = 0; i < gridView.Rows.Count; i++)
{
GridViewRow row = gridView.Rows[i];
//APPLY TEXT STYLE TO 2ND COLUMN OF ACH ROW
gridView.Rows[i].Cells[1].Attributes.Add("class", "textmode");
}
string style = @" .textmode { mso-number-format:\@; } ";

Response.Write(style);
gridView.RenderControl(htw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
Add apostrophe before the input field
C#
GridViewRow row = "'" + grdErrorRecords.Rows[i];//This will make all input fields in text format.
 
Share this answer
 
v2
Comments
pavan2407 27-Jan-17 4:09am    
what is the i in the rows
foreach (GridViewRow row in gridView.Rows)
    {
        for (int i = 0; i < row.Cells.Count; i++)
        {
            if (row.RowType == DataControlRowType.DataRow)
            {
                row.Cells[i].Attributes.Add("class", "text");
            }
        }
    }
 
Share this answer
 
Comments
CHill60 26-Jul-19 6:52am    
Shouldn't that be "textmode" not "text" … as per the solutions from other members, that work?
Member 10268973 6-Feb-20 5:34am    
YES,textmode

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900