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

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.

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