Excel is automatically converting a numeric value to scientific notation

Aspose.Cells Product Family
1

Hi,


The attached code is used to save data (CSV format) to XLSX, there can be vast amounts of data so we’re trying to use a workbook that is initialised with a stream instead of writting data row by row, cell by cell.

What’s happening is that numeric values are being translated to scientific notation. I can ‘format’ the values as a string but that just means I get scientific notation value as text. I’ve also tried [loadOptions.ConvertNumericData = false] which works but the entire file is then text and adding filters for numeric columns treats the values as text (numeric filtering doesn’t work).

Any ideas?

Regards
Mark
2

Hi Mark,

Thanks for your posting and using Aspose.Cells.

0011223344556677889900000000 is a very large number and it will not be shown in numeric value. If you copy/type this number in Excel, it will show it in scientific notation only.

So this is not the bug of Aspose.Cells but a limitation of Microsoft Excel and correct behavior.

3

Shakeel,


Thanks for your response, agreed I see that behaviour in Excel but I need to display the whole value, if you look at the example the column contains other alphanumeric values, the column should treat the value as text or at least that’s what I need.

I can see from using Excel directly that if I format a cell as Text and paste the value into the cell it displays it using scientific notion but if I paste the value into the “edit area” at the top of the sheet (just under the menus) it displays it as text and the value is complete, not in scientific notional.

I guess I’m looking for a work-around, do you have an idea of how I can either:
(a) defeat Excel and get my text in as text, or
(b) use [loadOptions.ConvertNumericData = false] but set the formatting on speifric columns to numeric so the automatic filtering works as expect (for numbers).

Regards
Mark
4

Hi Mark,

Thanks for your posting and using Aspose.Cells.

For your requirement, please see the following code.

C#


TxtLoadOptions ooo = new TxtLoadOptions();

loadOptions.Separator = ‘|’;

…

loadOptions.PreferredParsers = new ICustomParser[] { null, null, new CC(), null }; //let the 3rd column use the special parser, others use Cells’ default parser

…

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(csvStream, loadOptions);


class CC : ICustomParser

{

public object ParseObject(string value)

{

return value;

}

public string GetFormat()

{

return null;

}

}




5

Shakeel,


Excellent, works perfectly.

Thanks
Mark
6

Hi,


Good to know that it figures out your issue now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.