Trim data while reading excel sheet in Java

Aspose.Cells Product Family
1

To read the contents of a cell, I’ve used the following method:

cell.getStringValue().

If the cell is formatted with currency, it returns the cell contents with spaces around the value.

E.g. cell value = 100. Formatting = currency with ($1,234.10).
cell.getStringValue() gives "$100 ". Notice the trailing spaces.

Some cells might contain data with leading/trailing spaces.

I want “$100” and data without leading/trailing spaces. In java, we can apply the string.trim() method but does the aspose library support any option to return the trimmed data directly?

2

Hi,


Thanks for your posting and using Aspose.Cells.

The space (trim end) occurs because of number format. If you change the number format in such a way that end space does not occur, then Aspose.Cells will return you string value according to your applied format.

Please see the following code, its source excel file and its console output. The console output explains that there was an end space in cell A1 but after applying the custom format, there is no space. Then it prints the first 20 cells and none of them has end space.

Java
Workbook wb = new Workbook(dirPath + “sample.xlsx”);

Worksheet ws = wb.getWorksheets().get(0);

Cell cell = ws.getCells().get(“A1”);
String strValueBefore = cell.getStringValue();

//Apply custom format on entire column
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);

String customFormat = “”$"#,##0.00;\("$"#,##0.00\)";
Style st = wb.createStyle();
st.setCustom(customFormat);

ws.getCells().getColumns().get(0).applyStyle(st, flag);

//Now again read the cell value
cell = ws.getCells().get(“A1”);
String strValueAfter = cell.getStringValue();

//Value after does not have last space
if(strValueBefore.compareTo(strValueAfter + " “)==0)
{
System.out.println(“Value After has no end space.”);
}

//Print the first 20 cells
for(int i=0; i<20; i++)
{
Cell c = ws.getCells().get(i, 0);
System.out.println(c.getStringValue()+”—");
}

Console Output
<span style=“color: rgb(255, 0, 0); font-family: “Courier New”; font-size: small;”>Value After has no end space.
$18.00—
$93.00—
$54.00—
$55.00—
$73.00—
$4.00—
$12.00—
$33.00—
$19.00—
$4.00—
$73.00—
$88.00—
$21.00—
$10.00—
$93.00—
$13.00—
$74.00—
$53.00—
$54.00—
$2.00—
3

Thanks for the detailed explanation.

The code is specific to a format.

If we want to trim the spaces in all the cases, is there any generic solution?

4

Hi,


Thanks for your posting and using Aspose.Cells.

Actually, your requirement is not a general requirement or even valid. It is only specific to you. Because, the root cause of your issue is your own specific format. We do not return you string value with space at the end as a bug but we return you like this because you have specified it yourself with your own format.

However, if you chose a format which does not create space at the end and we return you string value with space at the end, then it will be considered as a bug and we will fix it.