CODEWITHSUNDEEP
c#asp.net
Anand
Anand

Reputation: 81

How to format exponent to number from Export to excel?

I am exporting data table to excel and one column is having Phone number in the data table but after export in Excel the phone number coloumn is displaying as exponent.

I need as number, How to fix this ?

 string fileName = "File" + DateTime.Now.ToString("MMddyyyy_HHmmss") + ".xls";
        Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
        //Response.AddHeader("content-disposition", "attachment;filename=File.xls");
        Response.ContentType = "application/vnd.ms-excel";

        StringWriter stringWriter = new StringWriter();
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
        DataGrid dataExportExcel = new DataGrid();
        dataExportExcel.ItemDataBound += new DataGridItemEventHandler(dataExportExcel_ItemDataBound);
        dataExportExcel.DataSource = dt;
        dataExportExcel.DataBind();
        dataExportExcel.RenderControl(htmlWrite);
        System.Text.StringBuilder sbResponseString = new System.Text.StringBuilder();
        sbResponseString.Append("<html xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:xlExcel8\" xmlns=\"http://www.w3.org/TR/REC-html40\"> <head></head> <body>");
        sbResponseString.Append(stringWriter + "</body></html>");
        Response.Write(sbResponseString.ToString());
        Response.End();

Upvotes: 0

Views: 6942

Answers (4)

Pandian
Pandian

Reputation: 9126

Add the below STYLE to your HTML tag, It will help you..

<style> table { mso-number-format:'0'; } </style>

like this : 

sbResponseString.Append("<html xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:xlExcel8\" xmlns=\"http://www.w3.org/TR/REC-html40\"> <head><style> td { mso-number-format:'0'; } </style></head> <body>");

Full Code:

   protected void btnExcel_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt.Clear();
        dt.Columns.Add("Phone");
        dt.Columns.Add("Name");
        DataRow Sample = dt.NewRow();
        Sample["Phone"] = 125316245612456124;
        Sample["Name"] = "Pandian";
        dt.Rows.Add(Sample);
        GetExcel(dt);            
    }
    public void GetExcel(DataTable dt)
    {
        string fileName = "File" + DateTime.Now.ToString("MMddyyyy_HHmmss") + ".xls";
        Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
        Response.ContentType = "application/vnd.ms-excel";
        StringWriter stringWriter = new StringWriter();
        HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
        DataGrid dataExportExcel = new DataGrid();
        dataExportExcel.DataSource = dt;
        dataExportExcel.DataBind();
        dataExportExcel.RenderControl(htmlWrite);
        System.Text.StringBuilder sbResponseString = new System.Text.StringBuilder();
        sbResponseString.Append("<html xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:xlExcel8\" xmlns=\"http://www.w3.org/TR/REC-html40\"> <head><style> table { mso-number-format:'0'; } </style></head> <body>");
        sbResponseString.Append(stringWriter + "</body></html>");
        Response.Write(sbResponseString.ToString());
        Response.End();
    }

Excel Output :

Excel Output

Upvotes: 4

Venki
Venki

Reputation: 68

use this td { mso-number-format:"@"; }

Upvotes: 2

Rakesh
Rakesh

Reputation: 299

you can prepend a single quote to the phone number before writing it to the cell of the spreadsheat.

like "'1234567890" instead of "1234567890"

thanks

Upvotes: 1

Aghilas Yakoub
Aghilas Yakoub

Reputation: 29000

You can use NumberValue and NumberFormat properties

Here a sample, set your range

yourSheet.Range[".."].NumberValue = 1234.5678;
yourSheet.Range[".."].NumberFormat = "0.00";

Upvotes: 2

Related Questions