How to prevent scientific notation in an event when exporting SQL Server data into an Excel sheet

Question

How do we prevent from scientific notation when exporting SQL Server data into an Excel sheet.

A 20 digit column value gets convert into scientific notation instead of the exact value. e.g. in SQL Server the value is 31812121800006173492.

But when I export the data into Excel, the value gets turn into the 3.18121E+19.

How do I get the exact value in Excel?

I have written the below code for export the data into the excel: Please suggest where and how do we set style attribute.

string filename = filename + "COC Report" + ".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 = dsexcel;
        dgGrid.DataBind();
        dgGrid.ShowHeader = true;
        dgGrid.CellPadding = 2;
        dgGrid.CellSpacing = 2;
        dgGrid.RenderControl(hw);
        Response.ContentType = "application/vnd.ms-excel";
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
        this.EnableViewState = false;
        Response.Write(tw.ToString());
        Response.End();

https://stackoverflow.com/questions/42685878/scientific-notation-when-exporting-sql-to-excel — Dale K, Apr 11 '19 at 04:59
if you export to csv format, you might want to mark this field as text (so you can put a ' in front of the string you write down to the csv); if you export to xlsx format, you might want to format the excel field/column to have the correct numeric format — Rex, Apr 11 '19 at 05:00
i have done this with adding a ' in front of string it's works for me .xls format but only issue is that a ' notation is also display with value in excel format e.g: '31812121800006173492. i want to display only numbers without ' notation in front of values. — Kunal, Apr 11 '19 at 05:15
And also whenever i am trying to fetch the data into .CSV format. i am getting the data but in table format. why..? — Kunal, Apr 11 '19 at 05:17
You'd have to show your code for someone to be able to answer that. — Dale K, Apr 11 '19 at 05:56
This doesn't create an Excel file, it creates an HTML table and sends it to the browser with a fake content type. Excel isn't fooled though and imports the HTML table as that - an HTML table. This means you can't control the cell style because there's no Excel file to begin with — Panagiotis Kanavos, Apr 16 '19 at 07:13

Kunal · Answer 1 · 2019-04-15T14:55:35.133

0

I got the solution but in alternative way, i have done it using asp Report Viewer control.first show the data in the report viewer and as report viewer has by default functionality to export the data into the excel/PDF/doc format without changing anything or writing export excel code.after exporting the data into the excel sheet,a 20 digit values does not gets turn into the scientific notation. i got the data as it is.because in report viewer number values consider as text.Please try this if some one is encountering the same problem.

edited Apr 15 '19 at 14:55

answered Apr 15 '19 at 14:49

Kunal

7
3

2

You don't need to do any of this. The exact value is there. The way it's displayed is controlled by the cell's style. You didn't post any code so it's not possible to explain how to set the style. Did you use Epplus? Did you create a CSV? Excel interop? You don't need to go through a reporting tool to export an Excel file. – Panagiotis Kanavos Apr 15 '19 at 14:59
Using a library like Epplus for example you could create an Excel sheet easily with `sheet.Cells.LoadFromDataTable(table)` or `sheet.Cells.LoadFromCollection(someList)`. Epplus can handle basic types and you can also modify the styles used to display cells or entire tables. – Panagiotis Kanavos Apr 15 '19 at 15:00
Thank you for your suggestion. I have posted code below. please suggest where and how do we set the style attribute for particular column. – Kunal Apr 16 '19 at 04:16

How to prevent scientific notation in an event when exporting SQL Server data into an Excel sheet

1 Answers1