Excel text value converted to number with Scientific Notation issue


#1

7114E4 is converted to 71140000. we have loadoptions as Auto, Xlsx and Tab delimited.


#2

@Thomas.Morrison,

Could you provide your sample files (input file, output file, etc.) and sample code (runnable) to show the issue, we will check it soon.

PS. you may zip the files prior attaching.


#3

Hi Amjad, Any update?


#4

I sent the file(s) via email 3 days ago?


#5

Amjad, I uploaded the file(s) to this thread as well.asposecells.zip (2.7 KB)


#6

@Thomas.Morrison,

I did not receive your email.

I have now evaluated your issue a bit using your template file. See the following sample code where the data in the exported table’s column remained as displayed string (scientific notation oriented):
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\temp\MriX\GL_Total_201909_09202019.xlsOct 4 2019 438PM.xls”, new LoadOptions(LoadFormat.TabDelimited));

        Worksheet worksheet = workbook.Worksheets[0];


        DataTable dtCloned = new DataTable();
        ExportTableOptions opts = new ExportTableOptions();
        opts.CheckMixedValueType = true;
        opts.ExportColumnName = true;
        opts.ExportAsString = true;

opts.FormatStrategy = CellValueFormatStrategy.DisplayStyle;

        dtCloned = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, opts);

        int i = workbook.Worksheets.Add();

        workbook.Worksheets[i].Cells.ImportData(dtCloned, 0, 0, new ImportTableOptions());

        workbook.Save("e:\\test2\\out1.xls");

Also, your provided sample code has missing objects/variables with data and external references that cannot be compiled to test the issue.


#7

It is still not working. The output data coming as 7E+7 instead 7114E4.


#8

@Thomas.Morrison,

Please try the following sample code, it will work for your needs:
e.g
Sample code:

TxtLoadOptions options = new TxtLoadOptions(LoadFormat.TabDelimited);
            options.ConvertNumericData = false;


            Workbook workbook = new Workbook("e:\\test2\\temp\\MriX\\GL_Total_201909_09202019.xlsOct  4 2019  438PM.xls", options);

            Worksheet worksheet = workbook.Worksheets[0];

            DataTable dtCloned = new DataTable();
            ExportTableOptions opts = new ExportTableOptions();
            opts.CheckMixedValueType = true;
            opts.ExportColumnName = true;
            opts.ExportAsString = true;
            opts.FormatStrategy = CellValueFormatStrategy.DisplayStyle;

            dtCloned = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, opts);

            int i = workbook.Worksheets.Add();

            workbook.Worksheets[i].Cells.ImportData(dtCloned, 0, 0, new ImportTableOptions());

            workbook.Save("e:\\test2\\out1.xls"); 

Hope, this helps a bit.