Remove scientific notations shown when a csv file is opened using excel in .net

Question

As per my requirement, I would like to generate an csv with csvhelper and need to open the same using Microsoft excel. But in that case values, for example "123456789455454111" is getting converted to 1.23457E+17.

I have tried using the below code, but still no luck.

using (var csv = new CsvWriter(writer, config))
{
    csv.Context.TypeConverterOptionsCache.GetOptions<string>().Formats = new[] { "0" };

I have defined the variable as string but still it gets converted to scientific notation.

var data = new List<MyData>
        {
            new MyData { Value1 = "12345678945545411341" },
            new MyData { Value1 = "9876543214545454222"}
        };

class MyData
{
    public string Value1 { get; set; }
}

Is there anyway through which I can achieve this ?

Note : I know we can avoid scientific notation conversion by adding ' or any other character which is not a number, but as per my requirement I need to display the numeric value as it is.

Answer 1

CSV files are just text files. They have no styling. It's not possible to how other applications display text files.

If the file is meant to be opened using Excel, a better idea would be to create an actual Excel file using one of the many Excel libraries like Epplus, ClosedXML, NPOI. Excel has native support for dates and numbers, with the display format controlled by a cell's style.

Most libraries can save to a stream, whether a MemoryStream or directly to the ASP.NET Response stream, allowing the generation of well formatted Excel files in web applications

Let's assume this is the data you want to export:

class MyData
{
    public string Name {get;set;}
    public decimal Value {get;set;}
    public DateTime Recorded {get;set;}
}


var data = new List<MyData>{
            new MyData("Measure1, 12345678945545411341m,DateTime.Now()),
            new MyData("Measure2,  9876543214545454222m,DateTime.Now())
};

Using EPPlus

Using EPPlus you can load data into an Excel sheet with a single LoadFromCollection, LoadFromDataTable or LoadFromDataReader.

using(var package = new ExcelPackage(@"c:\temp\myWorkbook.xlsx"))
{
    var sheet = package.Workbook.Worksheets.Add("My Sheet");
    var table=sheet.Cells.LoadFromCollection(data, true, TableStyles.Medium9);;

    // Save to file
    package.Save();
}

This will load the data into an Excel sheet, preserving the types and generate a table with headers and the Medium9 table style

The way dates and numbers are displayed is controlled by the cell's style. The style format string is the same used in Excel. The following line specifies a number without decimals :

sheet.Cells["A1:A25"].Style.Numberformat.Format = "#";

Recent versions of EPPlus allow specifying the header names, number formats etc through attributes :

class MyData
{
    public string Name {get;set;}

    [EpplusTableColumn(NumberFormat = "#")]
    public decimal Value {get;set;}

    [EpplusTableColumn(NumberFormat = "yyyy-MM-dd")]
    public DateTime Recorded {get;set;}
}

You can check the Loading Data Samples in the library's Github repo

Using ClosedXML

ClosedXML is another popular open source library. This snippet will create an Excel file with list data :

var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Data Sheet");

var tableWithData = ws.Cell(1, 1).InsertTable(data.AsEnumerable());

wb.SaveAs("MyFile.xlsx");

The library's documentation shows various other ways to insert and format data

Styling works in a similar way :

ws.Column("E").Style.NumberFormat.Format = "#";

The library docs go into depth on how to create tables, style cells and columns etc

Answer 2

The problem is Excel can't handle numbers larger than 15 digits. learn.microsoft.com

Excel follows the IEEE 754 specification for how to store and calculate floating-point numbers. Excel therefore stores only 15 significant digits in a number and changes digits after the fifteenth place to zeros

I tried adding those numbers to Excel as text and then converted the cells to numbers. Excel still considered them to be text. On line 3 where I tried to sum A1 and A2 I got 0.00

The only way to show those numbers in their entirety in Excel is to get Excel to format them as text instead of as a number.

You can either import the CSV file into Excel using the Data tab and tell Excel "Do not detect data types" or you can try some of the hacks for CSV data like putting a tab character in front of your data or enclosing the data in double quotes with an equal sign before that ="<long number>" with the ultimate output being field1,"=""<long number>""",field3.

void Main()
{
    var data = new List<MyData>
    {
        new MyData { Value1 = "12345678945545411341" },
        new MyData { Value1 = "9876543214545454222"}
    };

    using (var writer = new StreamWriter(@"C:\Temp\TestExcelScientific.csv"))
    using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
    {
        csv.Context.RegisterClassMap<MyDataMap>();

        csv.WriteRecords(data);
    }
}

public class MyDataMap : ClassMap<MyData>
{
    public MyDataMap()
    {
        Map(x => x.Value1).TypeConverter<ExcelTextConverter>();
    }
}

public class ExcelTextConverter : DefaultTypeConverter
{
    public override string ConvertToString(object value, IWriterRow row, MemberMapData memberMapData)
    {
        value = "\t" + value;

        return base.ConvertToString(value, row, memberMapData);
    }
}

public class MyData
{
    public string Value1 { get; set; }
}