getDisplayStringValue() returns "#" for thin columns

Aspose.Cells Product Family
1
Hello
I have found trouble with method getDisplayStringValue() for thin columns (please see following example)

@Test
public void displayValueTest() {
try {
Workbook wb = new Workbook("D:\\in.xlsx");
Cells cells = wb.getWorksheets().get(0).getCells();

Cell cell = cells.get("C4");

String value = cell.getDisplayStringValue();

System.out.println(value);
} catch (Exception e) {
e.printStackTrace();
assertNull(e);
}
}

System out returns symbol "#". For another column's width it returns "2E+06", but i needed to receive my number (1921233.5), that formatted by cell's rules. For example "1 921 233.5" (with right group separator). There is exists a way to receive this string?

Best regards. Alexey
2

Hi Alexey,


Thank you for contacting Aspose support.

We have evaluated your presented scenario while using the latest version of Aspose.Cells for Java (Latest Version). Please note, the getDisplayStringValue method will return the cell value as it is displayed in the spreadsheet when loaded with MS Excel application. In your provided spreadsheet, you have narrowed the column width so when you retrieve the value for cell B4 using the method getDisplayStringValue, it returns the string “2E+06”, that is a correct behavior. You should be using the same method if you wish to retrieve the cell value with formatting applied. Although you have first set the column width so that actual value is completely displayed. Best approach would be to call the Worksheet.AutoFitColumn or Worksheet.AutoFitColumns method before retrieving the cell value.

Please have a look at the below provided code snippet, as well as the attached spreadsheet. Please note, for testing purposes, we have set the comma as thousand separator for cell B4 in the attached sample.

Java

Workbook wb = new Workbook(myDir + “in-2.xlsx”);
Worksheet sheet = wb.getWorksheets().get(0);
sheet.autoFitColumn(1);
Cells cells = sheet.getCells();
Cell cell = cells.get(“C4”);
System.out.println("DisplayStringValue " + cell.getDisplayStringValue());
System.out.println("Value " + cell.getValue());


Output

DisplayStringValue 1,921,234
Value 1921233.5
3

Hi Babar Raza.


Thanks for your reply, but it is not acceptable way. We needed to receive right formatted value without auto fit columns. Probably you have not yet this method, but it should not be very complex. Could your developers estimate this feature for develop?

Best regards. Alexey
4

Hi Alexey,

Thanks for using Aspose.Cells.

Please use Cell.getStringValue() instead. For numeric values, Cell.StringValue will give the formatted result.

Hopefully, it should fulfill your requirements. Let us know if you encounter any other issue. We will look into it and help you asap.

5

Hi Shakeel Faiz.


We have tried to use getStringValue(), but now have found following error - when we set up a setting “setDisplayZeros” to “FALSE” - we still receving a zeros. Could you provide some method, that will returns display string value without width or height affects?

Best regards. Alexey
6

Hi Alexey,

Thanks for your feedback and using Aspose.Cells.

Kindly explain in detail how Cell.getStringValue() does not fit your needs. You can provide us sample xls file, screenshot, sample code and expected output. It will help us look into your requirements precisely and we will see if your requested feature could be implemented in future releases.

7

Hello there is example. A Flag “display zeros” is ignored by library. Anyway i need some method, that will return “15464654” for first case and “” for second case. I think it should be very easy to return string value before width of column applying


public void testDisplayValue() throws Exception {
Workbook wb = new Workbook(“D://2.xlsx”);

Worksheet worksheet = wb.getWorksheets().get(0);
Cells cells = worksheet.getCells();

System.out.println(worksheet.getDisplayZeros());

outAssert(“15464654”, cells, “D3”, false);
outAssert("", cells, “D5”, false);
outAssert("", cells, “D6”, false);

//but following is error too
outAssert("", cells, “D5”, true);
outAssert("", cells, “D6”, true);
}

private void outAssert(String expected, Cells cells, String cell, boolean useDisplay) {
System.out.println(“Expected :” + expected + " \t Received:" + getStringValue(cells, cell, useDisplay));
}

private String getStringValue(Cells cells, String cell, boolean useDisplay) {
if (useDisplay) {
return cells.get(cell).getDisplayStringValue();
} else {
return cells.get(cell).getStringValue();
}
}


Best regards. Alexey
8

Hi Alexey,

Thanks for your sample code and using Aspose.Cells.

After running your code, I think, Cell.getStringValue() is returning correct results but Cell.getDisplayStringValue() are not returning correct results, they should return “” instead of 0 or 0.00 values.

I got the following output results.

Console Output:
Cell.getStringValue() results
Expected :15464654 Received:15464654 //returning correct result
Expected : Received:0 //returning correct result
Expected : Received:0.00 //returning correct result
Cell.getDisplayStringValue() results
Expected : Received:0 // should return ""
Expected : Received:0.00 //should return ""

Let me know if got your requirements correct.

Please also explain what do you mean by “return string value before width of column applying”

9

Hi Shakeel Faiz.


In current model getStringValue works right, but we needed some mixed method, that will return formatted value (like getDisplayStringValue) (as it will be displayed by Excel), but without part of your code, that applying a width of cell (like in a infinity wide cell) (like getStringValue). Could you please add this method in your API?

Best regards. Alexey
10

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

We have logged your requirements in our database for investigation. We will look into it and see if your requested feature could be implemented. Please spare us some time, once, there is some update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40845.

11

Hi Shakeel. Yes i need a new method in your API (or parameter for getDisplayStringValue), that will returns formatted cells value, as it will be displayed in infinity wide cell.


Best regards. Alexey
12

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

We have already logged your requirements. Please spare us some time. Once, we will have some update for you, we will let you know asap.

13

Hi Alexey,

Thanks for using Aspose.Cells.

We have evaluated this issue further. To get the same behavior with MS Excel, Cell.StringValue also should
give “” for zeros when DisplayZeros is false.

So from the coming new
fix, Cell.StringValue/DisplayStringValue will both return “” for this case.

14

Hi Alexey,

Thanks for using Aspose.Cells for Java.

We have fixed the issue relating to DisplayZeros set as false.

Please download and try this fix: Aspose.Cells for Java (Latest Version) and let us know your feedback.

15

The issues you have found earlier (filed as CELLSJAVA-40845) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.