Need to convert scientific value to text

Question

I am trying to convert csv to xls in which there is Bank Account No (16 digit) need to convert it to text

I tried with EPPlus package by which xls is getting generated easily but Bank Account column gets converted to scientific value.Also tried to convert column to numeric and text but thats not working

    private void csvToXls(string source,string destination,string fileName)
    {
        string csvFileName = source;
        string excelFileName = destination+"/"+fileName+".xls";

        string worksheetsName = "sheet 1";

        bool firstRowIsHeader = false;

        var format = new ExcelTextFormat();
        format.Delimiter = '|';
        format.EOL = "\n";              // DEFAULT IS "\r\n";
                                        // format.TextQualifier = '"';

        using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
            worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.None, firstRowIsHeader);

            foreach (var cell in worksheet.Cells["C:C"])
            {
                cell.Value = Convert.ToString(cell.Value);

            }

            foreach (var cell in worksheet.Cells["AC:AC"])
            {
                cell.Value = Convert.ToString(cell.Value); 
            }
            worksheet.Cells["C:C"].Style.Numberformat.Format = "#0";
            worksheet.Cells["C:C"].Style.Numberformat.Format = "@";
            worksheet.Cells["AC:AC"].Style.Numberformat.Format = "@";


            package.Save();
        }
    }

Need to convert columns to text which should not be scientific value.Please suggest

Input : In test.csv below input is there

IMDATA||12345678910124567895274|1|NAME|||||||||TEST|||||||||||TESTING||||3301003726558|TDATASTING|TESTING|27-09-2019

Getting Output :

Expected Output :

Answer 1

You need to provide eDataTypes to LoadFromText method for each column, if no types provide for the column it will set to the General by default

So, provide the `eDataTypes to ExcelTextFormat.

I just added for 3 columns and its worked well.

public static void csvToXls(string source, string destination, string fileName)
{
    string csvFileName = source;
    string excelFileName = destination + "/" + fileName + ".xls";

    string worksheetsName = "sheet 1";
    bool firstRowIsHeader = false;

    var format = new ExcelTextFormat();
    var edataTypes = new eDataTypes[] { eDataTypes.String, eDataTypes.String, eDataTypes.String };
    format.DataTypes = edataTypes;
    format.Delimiter = '|';
    format.EOL = "\n";              // DEFAULT IS "\r\n";
                                    // format.TextQualifier = '"';
    using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);
        worksheet.Cells["A1"].LoadFromText(new FileInfo(csvFileName), format, OfficeOpenXml.Table.TableStyles.None, firstRowIsHeader);

        package.Save();
    }
}

OR

You can try to read the text manually

public static  void csvToXls(string source, string destination, string fileName)
{
    string csvFileName = source;
    string excelFileName = destination + "/" + fileName + ".xls";

    string worksheetsName = "sheet 1";

    using (ExcelPackage package = new ExcelPackage(new FileInfo(excelFileName)))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);

        var text = File.ReadAllText(source);
        var rows = text.Split('\n');

        for (int rowIndex = 0; rowIndex < rows.Length; rowIndex++)
        {
            var excelRow = worksheet.Row(rowIndex+1);

            var columns = rows[rowIndex].Split('|');

            for (int colIndex = 0; colIndex < columns.Length; colIndex++)
            {
                worksheet.Cells[rowIndex +1, colIndex +1].Value = columns[colIndex];
            }
        }

        package.Save();
    }
}