Retrieve String values of cells (without formatting)

Aspose.Cells Product Family
1

I have a double value stored in a cell: 9.99

Its formatted and displayed in Excel as $9.99 (with commas separating thousands if needed).

Calling cell.getStringValue() on this cell returns me the formatted String value, not just “9.99” which is what the user entered.
Call cell.getDisplayStringValue() on this cell returns the formatted String value, which is what I expect of this method.

How do I obtain the value of an Excel cell as the user entered it i.e. as a String - Im trying to prevent inaccuracies introduced via double conversion.

It seems that getStringValue() should not be applying the format in this case (aspose.cells 7.7.2) - since there is already a method to obtain the formatted value of the cell.


2

Hi,


Well, you may use Cell.getValue() or getDoubleValue() method to get the complete value in the cell without formattings.

Thank you.
3

That doesnt prevent the inaccuracies - I want the value as entered by the user, not the double value that Aspose converts the cell value in to.


Excel remembers what I typed in - for example, if I enter 0.001, then no matter how I format the cell, or reference it in other cells, Excel knows I typed in 0.001. If you convert this to a double, then you introduce inaccuracies in the conversion.

Could you make available the sequence of characters entered by the user as a getXYZ method on Cell?
4

Hi,


Thanks for providing us some details.

I am not entirely sure about your needs here, could you provide some sample Excel file and screen shots to demonstrate what you are looking for in Aspose.Cells APIs, so we could evaluate it on our end to help you accordingly.

Thank you.
5

Open up a new Excel workbook.

Enter the following values in to cells:
1, 1.0, 0.1, 0.01, 0.001, 0.0001
Now retrieve those values using Java Aspose Cells v7.7.2, and put the values into a BigDecimal using Cell.getDoubleValue().
Print out all the values retrieved via the BigDecimal.toString()

Cell.getStringValue() returns the value of the Cell with all its formats applied - that is redundant and not required, since there’s already a specific method to obtain the formatted String value of a Cell - Cell.getDisplayStringValue().

Can you make Cell.getStringValue() return just the characters entered by the user as a String - whether it happens to be alphanumeric, or numeric doesn’t matter - if the user entered 0.001 and hit return on a cell in Excel, I’d like to get a String back as 0.001.


6

Hi Nick,

Thank you for further elaboration.

We have evaluated your presented scenario on our end with latest version of Aspose.Cells for Java 7.7.2.3. Cell.getStringValue() method returns the original string inserted as the cell value. In our tests, we have manually created an Excel spreadsheet with the values provided by you, and read them using the Cell.getStringValue() method. Please note, in this case we have set the format of the cell as text. In second test, we input the values dynamically using Aspose.Cells for Java API and read them correctly with the same. Please check the below provided code snippet and attached input spreadsheet.

Java


Workbook book = new Workbook(myDir + “book1.xlsx”);
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();
System.out.println(cells.get(“A1”).getStringValue());
System.out.println(cells.get(“A2”).getStringValue());
System.out.println(cells.get(“A3”).getStringValue());
System.out.println(cells.get(“A4”).getStringValue());
System.out.println(cells.get(“A5”).getStringValue());
System.out.println(cells.get(“A6”).getStringValue());

cells.get(“B1”).putValue(“1”, false);
cells.get(“B2”).putValue(“1.0”, false);
cells.get(“B3”).putValue(“0.1”, false);
cells.get(“B4”).putValue(“0.01”, false);
cells.get(“B5”).putValue(“0.001”, false);
cells.get(“B6”).putValue(“0.0001”, false);
System.out.println(cells.get(“B1”).getStringValue());
System.out.println(cells.get(“B2”).getStringValue());
System.out.println(cells.get(“B3”).getStringValue());
System.out.println(cells.get(“B4”).getStringValue());
System.out.println(cells.get(“B5”).getStringValue());
System.out.println(cells.get(“B6”).getStringValue());


In case you still have any questions, please feel free to write back any time.