How to format excel cells to text while exporting data grid to excel?

Question

4.00/5 (1 vote)

See more:

, +

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 23-Jan-14 19:59pm

Keerthi Kumar(Andar)

Updated 26-Jul-19 0:37am

v3

Add a Solution

5 solutions

Solution 5

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");
            }
        }
    }

Posted 26-Jul-19 0:38am

Member 12927626

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

Solution 4

Add apostrophe before the input field

C#

GridViewRow row = "'" + grdErrorRecords.Rows[i];//This will make all input fields in text format.

Posted 29-Dec-16 1:28am

Member 12630889

Updated 29-Dec-16 1:32am

v2

Comments

pavan2407 27-Jan-17 4:09am

what is the i in the rows

Solution 3

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");

}

Posted 2-Feb-16 10:11am

Vishal_netTech

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();

Solution 1

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");

Posted 23-Jan-14 20:21pm

JoCodes

Updated 23-Jan-14 20:24pm

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");
}
}

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)

nrgjack · Accepted Answer · 2014-06-28T05:25:00

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);

How to format excel cells to text while exporting data grid to excel?

5 solutions

Solution 5

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us