Trim() not working with excel read apache poi in Java:

Trim() not working with excel read apache poi in Java:

While reading from the excel, you need to make sure you are considering this unicode value as well \u00A0 along with white spaces to handle all the possible inputs.

Trim() does not help us for this, and below are the tries I made and got the solution, posted all here to help you with n number of possible use cases and solutions.

Not working:

Try 1:

[java]
row.getCell(0).getStringCellValue().replaceAll(“\u00A0”, “”);

[/java]

 

Try 2:

[java]

row.getCell(0).getStringCellValue().trim();

[/java]

 

Try 3:

Also after converting all the numbers to string also it does not work:
[java]

DataFormatter objDefaultFormat = new DataFormatter();

String value = objDefaultFormat.formatCellValue(row.getCell(0), objFormulaEvaluator).replaceAll(“\u00A0″,””);

[/java]

 

Try 4:

[java]
String value = objDefaultFormat.formatCellValue(row.getCell(0), objFormulaEvaluator).trim();

[/java]

 

This too not Working:

Try 5:

[java]

FormulaEvaluator eValuator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
cityArg = org.apache.commons.lang3.StringUtils.trimAllWhitespace(objDefaultFormat.formatCellValue(row.getCell(0), eValuator));

[/java]

 

Finally working:

[java]
FormulaEvaluator eValuator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
DataFormatter objDefaultFormat = new DataFormatter();

String value = StringUtils.trimTrailingWhitespace((StringUtils.trimLeadingWhitespace(objDefaultFormat.formatCellValue(row.getCell(0), eValuator))));
}
country = StringUtils.replace(country,”\u00A0″, “”);

[/java]

Note: StringUtils is from spring core’s one only,  not from apache commons library.