Get cell contents as String without formatting

Id like to be able to obtain the value of a floating point number, as typed in Excel by a user, as a String, without any formatting. This doesnt seem to be possible at this time -


getStringValue() is documented as:










Gets the string value contained in the cell. If the type of this cell is string, then return the string value itself. For other cell types, the formatted string value(formatted with the specified style of this cell) will be returned. The formatted cell value is same with what you can get from excel when copying a cell as text(such as copying cell to text editor or exporting to csv).

getDisplayStringValue() is documented as:

Gets the formatted string value of this cell.

These two API calls effectively give me the same value for floating point numbers which is of no use to me when trying to parse entered values precisely (being provided numeric values as a Double by the Aspose.Cells API is too late to maintain correctness)

What I want is an API call on Cell that gives me the characters typed in by the user, regardless of formatting, regardless of cell type.

Please could you add such a method ( getStringValueWithoutFormat() ) or remove the functionality from the method highlighted in red above (since I can always obtain the formatted String value by using getDisplayStringValue() )

Regards

Nick


1 Like

Hi Nick,


I think you may try to use Cell.getValue() method to retrieve the un-formatted value contained in the cell.

Thank you.

I dont want the value as interpreted by Aspose.Cells - getValue() returns a Double for floating point cells. I want to obtain the value in the cell as typed by the user - this would be as a String, not a Double.


Once Aspose.cells has put the value entered by the user into a java.lang.Double it’s too late - I can no longer avoid IEEE floating point conversion issues (like 10.2 being interpreted as 10.19999999999193723781).

After some manual inspection of BIFF8 and the zipped xlsx files, I see that Excel is storing the characters as typed by the user (no IEEE number munging) so the data must be able to be retrieved in character/string form.

Hi,


We think Aspose.Cells works the same way as MS Excel does as after all we follow MS Excel standards and specifications. Anyways we will get back to you soon to give you further details on this matter/ issue.

Thank you.

Hi,

Thanks for your posting and using Aspose.Cells.

When user input value for a cell, excel will convert the input string to proper value and type automatically. Such as when you input one numeric value in excel for a cell, in the saved excel file, the data for this cell saved is a numeric value, not the original string you input. So it is impossible to get the original input string directly even for ms excel itself.

Would you please provide us the BIFF8 file in which the cell value is saved as string and aspose.cells takes it as numeric value? We will check it soon.

After having worked on an Excel library several months ago, Im fairly sure that Excel stores what the user typed in both the BIFF8 file and the Zipped XML. This is super easy to see - just make an empty worksheet, set cell A1 to ‘10.2’ and save the file and then inspect.


Excel does not store the binary floating point representation of ‘10.2’ in the file (it uses its own format in BIFF8 and stores the value directly in XML) - it stores ‘10.2’. When working with cell A1 in Excel, if I multiply that value by 10,000,000 I get 10.2 x 10MM. Not 10MM x IEEE floating point representation of 10.2 (which is 10,199,9999,xxxxx). Again - this is easy to validate both in Excel (do that calculation and display as many DPs as you can), and then do the same thing with a simple Java program accessing the data through Aspose.cells.

Right now, as of the latest version of Aspose.cells, I can only retrieve the value of that numeric cell as a java.lang.double. This forces a conversion in to IEEE floating point format, and 10.2 cannot be represented accurately. Thus, multiplying such a number by a large power of 10 results in significant errors in Java apps. I need to be able to retrieve the characters (thus a String) that the user typed in to Excel, so that I can work with BigDecimal(String) to retain accuracy.

The current methods to retrieve String values (of which there are 2) both return the value plus any formatting (commonly, cells could be formatted in Excel by the user with suffixes such as ‘bps’, ‘years’, ‘millions’, ‘billions’ etc.). I dont want to have to filter this out - its just not a safe and reliable mechanism (the user could change the prefix and/or suffix at any time and the prefix and/or suffix could contain numbers)

Im just asking for a method to return the characters (either as a String or char[]) of the cell that the user typed without any formatting due to the cell style (prefixes, suffixes etc).

Hi Nick,

Thanks for your posting and using Aspose.Cells.

We have logged your feature request in our database for investigation. We will look into it and see if such a feature could be implemented. Once, there is some fix or other update/advice for you, we will let you know asap.

This issue has been logged as CELLSNET-42667.

This problem also extends to setting floating values as well as reading them.


For example, I want to set a value in a cell of ‘10.2’. This should be numeric, but I want the exact value of 10.2 to be set in the cell, not 10.199999999999999… which is what I get if I convert my BigDecimal(10.2) to a double IEEE floating point value before invoking cell.setValue().

If I set my BigDecimal value as a String (via BigDecimal.toPlainString() ) in order to keep the correct value, Aspose.cells seems to mark the cell as a String cell, and I can no longer read the cell contents back in to my application using cell.getDoubleValue().

The inability to get and set accurate floating point values in Excel sheets is rendering Aspose useless for us since we are working in the financial services domain, and encounter large power-of-ten multipliers on a regular basis. Working directly in Excel does not have such issues since Excel keeps track of what was typed.

Hi,

We have evaluated your issue further.

For the cell value saved in BIFF8 records, if the value is integer or decimal value with less significance(such as your example 10.2), then it can be represented by “RK” structure, which can be formatted as the original value exactly without possible precision lost of IEEE floating point numbers. However, for most numeric values the RK structure is not enough to represent them, MS Excel saves them as IEEE floating point numbers too. For RK value saved in the BIFF8 record, we may consider to provide an API such as Cell.getStringValueWithoutFormat() to get the original value exactly, but we don’t think it can solve your problem when common double values were saved as IEEE floating point numbers in BIFF8 records too. And it may have no help to your newly found issue of setting floating values to cells.

By our test, when assigning double value 10.2 to one cell(by MS Excel or by Aspose.Cells APIs), we can always get “10.2” when fetching it from the cell by using:
e.g.

cell.getValue().toString()

Please create one sample console application (runnable) and send it to us to reproduce your issue, so we could check whether we can solve it at our end. And, for your situation about setting value to a cell, maybe you can consider to save the string of the value to a cell and then get it back from the cell and parse it as BigDecimal by yourself later.

Thank you.

Most values entered by a user in Excel are going to be 0-2, maybe 4 decimal places. Users rarely like to type more than that in, and usually the longer precision numbers are calculated via formula. Most common, by far, is 0 or 2. If a user enters numbers like this they expect to see the exact same, non IEEE munged values in the output downstream (i.e. in our application). After all, this is what they see in Excel.


As for BIFF8 records - also take a look at the value as stored in .XLSX files. Those numbers, at least one the ones I checked, are stored as entered by the user - not the IEEE munged form. Check out longer precision numbers as well as shorter ones - the text values in the XML seem to be as-entered-values.

So, we’re simply asking for a way to retrieve that value in String form (without any of the formatting). The two methods provided on the API do the exact same thing for non-String values, which is redundant - we’d like one method to give us the fully formatted entered value + formatting as entered/set by the user, and we’d like the other method on the API to give us the value as entered by the user in String form, without any formatting - just the characters typed in by the user. You do this for String values (have the two API methods behave differently) - why not do it for non-String values?

Notwithstanding the above, I can enter into Excel a fractional decimal number that cannot be precisely represented by binary floating point (2dp, 10dp, whatever), save it, close Excel, reopen, and then view that cell contents out to as many decimal places as Excel can display, and Excel still doesn’t show the munged IEEE form. I can take that cell value and multiply it by 100,000,000 (and more) and show that value to as many decimal places as Excel can handle, and I still don’t see the IEEE “noise”. It certainly looks to me like Excel is keeping track of the characters typed by the user and not relying on IEEE floating point representation in all the cases I tried.

Hi,


Thanks for providing us further details.

I have logged your findings/ comments against your issue “CELLSNET-42667” into our database. We will evaluate and look into it soon.

Once we have any update on it, we will let you know here.

Thank you.

Hi,

Thanks for your posting and using Aspose.Cells.

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

1. We have added Cell.StringValueWithoutFormat for getting string format of the numeric value without custom formatting being applied.

2. To try to keep original input precision for numeric value, user can try Cell.PutValue(String, true).

Hello,

I still have a problem with the last version reading a csv file

Here is the real value: 2015451106632800010

and in Aspose:
StringValue: 2,01545E+18
StringValueWithoutFormat: 2.0154511066328E+18

We can’t simply have what is written in the file ?

David

Edit:
To add more info, “StringValueWithoutFormat” is working well with 451106632800010 for instance. the string above is too long ?

Edit2:
Just saw this thread is tagged Java. My problem is in .Net

Hi David Rousselle,


Please try using TxtLoadOptions.setConvertNumericData() method to set to false, so the big numeric data should be loaded as string, see the sample code below that works fine (it extract original long value) with the attached file:
e.g
Sample code:

TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.CSV);
opts.setConvertNumericData(false);

Workbook workbook = new Workbook(“bk_getcell1.csv”, opts);
Worksheet worksheet = workbook.getWorksheets().get(0);

Cell cell = worksheet.getCells().get(“A1”);


System.out.println(cell.getStringValue());

System.out.println(cell.getStringValueWithoutFormat());

Let us know if you still have any issue.

Thank you.

Thank you for your answer.

Indeed it’s working for this column now, but I also have numeric columns that I want to get easily with “DoubleValue”. do you have a solution for me ?

Thanks

Hi,


Thanks for providing us further details.

Well, setConvertNumericData() affects the whole data in the template CSV file. I am afraid, there is no better way to cope with your custom needs. I think as a workaround, you may try to re-convert your desired data to numeric data and then obtain the double value of the cell(s) accordingly, see the following sample code with the updated template file for your reference:
e.g
Sample code:

TxtLoadOptions opts = new TxtLoadOptions(LoadFormat.CSV);
opts.setConvertNumericData(false);
Workbook workbook = new Workbook(“bk_getcell1.csv”, opts);
Worksheet worksheet = workbook.getWorksheets().get(0);

Cell cell = worksheet.getCells().get(“A1”);


System.out.println(cell.getStringValue());

System.out.println(cell.getStringValueWithoutFormat());

cell = worksheet.getCells().get(“B1”); // now you need to convert the string data to numeric data to re-enter into itself.
cell.putValue(cell.getStringValue(), true);
System.out.println(cell.getDoubleValue());
System.out.println(cell.getStringValue());


Hope, this helps a bit.

Thank you.

I used opts.setConvertNumericData(false); and converted to double

Thanks for your answer

Hi,


Hopefully the workaround suits your requirements. Feel free to contact us any time if you have further queries or have some other issue, we will be happy to assist you soon.

Thank you.


Hi , I’m using aspose 8.2.1.0 and I can not use StringValueWithoutFormat, but I need exactly same string which is in cell, but aspose make format for date by current culture. How can I get date without formatting liek cells[row,column].StringValue??

Hi sargsyanvalentina,


I think you may try to use Cell.getValue() method if it works for your needs. If you still find any issue, kindly elaborate your issue in details and do provide your sample code (runnable) with your template file as per the thread post, we will check it soon:
https://forum.aspose.com/t/78432

Thank you.