How to remove exponent notation coming for large numbers while exporting grid to excel in ASP.NET with C#?

Question

0.00/5 (No votes)

See more:

I am getting exponential notation for large numbers while exporting gridview binded with a dataset to export. I am using asp.net with c#.

What I have tried:

Code is as below:

DataSet ds = objadmin.getAuditTrailReport(FromDate, ToDate, drdUserId.SelectedValue.ToString());

                DataTable dt = new DataTable();
                if (ds.Tables[0].Rows.Count > 0)
                {
                    dt = ds.Tables[0];
                    grdExport.DataSource = dt;
                    grdExport.DataBind();
                    Response.Clear();

                    Response.AddHeader("content-disposition", "attachment;   filename=Audittrail_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");

                    Response.Charset = "";

                    // If you want the option to open the Excel file without saving than

                    // comment out the line below

                    // Response.Cache.SetCacheability(HttpCacheability.NoCache);

                    Response.ContentType = "application/vnd.xls";

                    System.IO.StringWriter stringWrite = new System.IO.StringWriter();

                    System.Web.UI.HtmlTextWriter htmlWrite =
                    new HtmlTextWriter(stringWrite);
                    //for (int i = 0; i < grdExport.Items.Count; i++)
                    //{
                    //    DataGridItem item = grdExport.Items[i];
                    //    //Apply text style to each Row
                    //    item.Cells[i].Attributes.Add("style", "textmode");
                    //}
                    grdExport.RenderControl(htmlWrite);

                    string strHead = strReportHeading + " FROM " + ViewState["FDOB"].ToString() + " TO " + ViewState["TDOB"].ToString();
                    StringBuilder strbn = new StringBuilder();
                    strbn.Append("<div style='text-align:center;font-weight:bold;background-color:White; width:100%;font-size:large'> " + strHead + "</div>");
                    strbn.Append(stringWrite.ToString());

                    Response.Write(strbn);

                    Response.End();

                }

Posted 2-Dec-21 6:30am

Member 15421351

Updated 2-Dec-21 22:31pm

Add a Solution

Comments

Richard MacCutchan 2-Dec-21 12:44pm

That is a feature of Excel, not how you are exporting. If you want fixed notation then you need to add the formatting to the relevant cells or ranges.

1 solution

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)

Richard Deeming · Answer 1 · 2021-12-02T22:31:00

You are not exporting the grid to Excel. You are "exporting" it to HTML, whilst lying to the browser to make it think your content is an Excel file.

Excel will do its best to import the HTML. But you will have extremely limited control over the formatting, and you will always get a warning message telling you that the file content doesn't match the expected file type.

If you want to export to Excel, then use a library to generate a real Excel file. For example, ClosedXML[^], or the OpenXML SDK[^].