Click here to Skip to main content
14,936,036 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi all,

In my ASP.NET application, I need to convert Dataset to EXCEL to the customer. Everything is working fine except a column which has a numbers.

Ex: 7002136138603600000

But when I open in Excel, it is showing in exponential format.

Something like this: 7E+18

What needs to be done in order to show these values as text instead in exponential format? here is what am using.

C#
HttpResponse response = HttpContext.Current.Response;

          // first let's clean up the response.object
          response.Clear();

          response.Charset = string.Empty;
          // set the response mime type for excel
          response.ContentType = "application/vnd.ms-excel";
          response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + ".xls" + "\"");

          // create a string writer
          using (StringWriter sw = new StringWriter())
          {
              using (HtmlTextWriter htw = new HtmlTextWriter(sw))
              {
                  // instantiate a datagrid
                  DataGrid dg = new DataGrid();
                  dg.DataSource = ds.Tables[0];
                  dg.DataBind();
                  dg.RenderControl(htw);
                  response.Output.Write(sw.ToString());
                  response.Flush();
                  response.End();
              }
          }
Posted
Updated 31-Jul-12 1:38am
v2
Comments
Kenneth Haugland 31-Jul-12 7:36am
   
is this were you print the read dataset, or? I need to see the excel writing if im going to help you. Basically each cell has somehting called cell.format were you set how you want the numbers or text to appear.

Check this article.

I have explained how to write to excel from dataset.
Export DataTable to Excel with Formatting in C#[^]
   
Comments
qwerty 2 31-Jul-12 12:56pm
   
Hi Santhosh,

You have posted only the style in relation to font and border but no where i could find the formatting of number to text.Could you please provide me that.
Hi,
Try this:
This below function used to generate excel file based on datatable.
C#
public void ExportToExcel(DataTable dt)
{
    if (dt.Rows.Count > 0)
    {
        string filename = "DownloadMobileNoExcel.xls"; 
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        DataGrid dgGrid = new DataGrid();
        dgGrid.DataSource = dt;
        dgGrid.DataBind();
        
        //Get the HTML for the control.
        dgGrid.RenderControl(hw);
        //Write the HTML back to the browser.
        //Response.ContentType = application/vnd.ms-excel;
        Response.ContentType = "application/vnd.ms-excel";
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
        this.EnableViewState = false;
        Response.Write(tw.ToString());
        Response.End();
    }
}


Call the above function in button click event
C#
protected void btnSave_Click(object sender, EventArgs e)
{
    ExportToExcel((DataTable)ViewState["gvMobile"]);  
}



--Amit
   

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