Cell.StringValue Automatically Converting to a Number

Aspose.Cells Product Family
1

I am trying to parse the file below. I am running into a problem with the Cusip column. When Aspose sees a value of ‘313380E3’ it thinks it is a numeric value and converts it into a number (Guessing that it is in scientific notation), even though I want it as a string.

I don’t mind that Aspose recognizes it as IsNumeric, not IsString since in my code I know what type to expect. But it’s frustrating that there is no way to access the raw value (I was hoping that the String Value would give me the raw information in the field). I know that there is a LoadOptions.ConvertNumericData that I can set to false, but that then causes other problems in the parsing logic (I end up seeing doubles, i.e. 1.10, parsed as Excel Dates, i.e. 40918, when I access their double value).

Is there anyway to access the actual underlying text in a cell? Is the StringValue really meant to convert the raw text to a number string?

Here is the sample code I am using.

loadOptions.LoadDataOnly = true;

Workbook wb = new Workbook(@"…\Example Files\Sample_E.csv", loadOptions);

Worksheet ws = wb.Worksheets[0];

Cell cell = ws.Cells[0, 2];

Console.WriteLine(cell.Value.ToString());

cell = ws.Cells[1, 2];
Console.WriteLine(cell.Value.ToString());

cell = ws.Cells[3, 2];
Console.WriteLine(cell.Value.ToString());



2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please set LoadOptions.ConvertNumericData = false, it will then not convert the string into numeric and will work fine.

Please also download and use the latest version:
Aspose.Cells for .NET (Latest Version)

Below is the sample test code and screenshot for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Sample_E.csv”;


LoadOptions loadOptions = new LoadOptions();

loadOptions.LoadDataOnly = true;

loadOptions.ConvertNumericData = false;


Workbook wb = new Workbook(filePath, loadOptions);


Worksheet ws = wb.Worksheets[0];


Cell cell = ws.Cells[0, 2];


Console.WriteLine(cell.Value.ToString());


cell = ws.Cells[1, 2];

Console.WriteLine(cell.Value.ToString());


cell = ws.Cells[3, 2];

Console.WriteLine(cell.Value.ToString());



Screenshot:
3

I think you misunderstood the problem. The values in the Cusip column are text values, not numbers.

The correct values to output would be as follows. But instead, Aspose is automatically converting them to Numeric values (including their string representations). I am wondering if there is a way to actually see the raw text value in a Cell after Aspose has parsed the file.

Cusip
313380E3
617446E7

4

Hi,

Thanks for your feedback.

Yes, you are right.

Please see my above post. As you can see the output is correct as expected.

Let me know if there is still an error.

Output:
Cusip
313380E3
617446E7

5

I guess my problem with loadOptions.ConvertNumericData = false is that Aspose then parses 1.10 as a 40918. Running the following code on this new example file gives the following results, which is not what I would expect.

Decimal
40918
40998


LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);

loadOptions.ConvertNumericData = false;
loadOptions.LoadDataOnly = true;

Workbook wb = new Workbook(@"…\Example Files\Sample_E2.csv", loadOptions);

Worksheet ws = wb.Worksheets[0];

Cell cell = ws.Cells[0, 4];

Console.WriteLine(cell.Value.ToString());

cell = ws.Cells[1, 4];
Console.WriteLine(cell.Value.ToString());

cell = ws.Cells[3, 4];
Console.WriteLine(cell.Value.ToString());

6

Hi,

Thanks for your input.

I think, this problem is unavoidable, because both Aspose.Cells and Ms-Excel do not have any way to tell the difference that if it is a numeric data or just a string data.

If you open it Ms-Excel, you will also see that same problem occurs.

You should add some pre-fix or some non-integer letter (alphabet) to distinguish it yourself e.g

Change

313380E3

to

’313380E3

or

x313380E3

or

n313380E3

etc