XSSFWorkbook in java

Question

I am using the XSSFWorkbook for reading the excel sheet data. When i entered the numbers with space after 10 digits and want to remove the space after numbers before saving. But trim(),replaceAll() - all these are not removing the spaces. How can i remove the space from the string "99999999 " after fetching using currentCell.getStringCellValue().

tried with trim(),replaceAll() methods and also stringUtils.trim()

phoneNumber = currentCell.getStringCellValue().replaceAll(" ",""); System.out.println(phoneNumber);

Expected is : want to remove the space after the string "99999 " as "99999"

No error message but i am expecting "99999" but getting "99999 " after using all the mentioned methods.

What makes you believe there is a space after your numbers? Excel often displays numbers with space for a negative sign after them just so the last zero will line up in the cells. But there is no space at the end, it is just a format thing for display. — jmarkmurphy, Aug 09 '19 at 17:40

score 2 · Answer 1 · answered Aug 09 '19 at 12:53

Maybe the character behind your 99999 is not a default white space but some other horizontal white-space character, like non-breaking space for example.

You can trim all such horizontal white-space characters as described in this answer: https://stackoverflow.com/a/28295733/3915431.

Complete example using apache poi for reading Excel:

import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;

class ReadExcel {

 public static void main(String[] args) throws Exception {

  Workbook workbook  = WorkbookFactory.create(new FileInputStream("SAMPLE.xlsx"));
  DataFormatter dataFormatter = new DataFormatter();
  CreationHelper creationHelper = workbook.getCreationHelper();
  FormulaEvaluator formulaEvaluator = creationHelper.createFormulaEvaluator();

  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) {
   for (Cell cell : row) {
    String cellContent = dataFormatter.formatCellValue(cell, formulaEvaluator);
    System.out.println("|"+cellContent+"|");
    cellContent = cellContent.trim(); // trims only default white-space
    System.out.println("|"+cellContent+"|");
    cellContent = cellContent.replaceAll("(^\\h*)|(\\h*$)",""); // trims all horizontal white-space characters
    System.out.println("|"+cellContent+"|");
   }
  }

  workbook.close();

 }
}

score 1 · Answer 2 · answered Aug 09 '19 at 03:18

I tried reproduce your problem using a simple java class, but failed. The trim() method for string is working fine. I believe there was something else in your code. Here my simple test.

class Scratch {
    public static void main(String[] args) {
        String phoneNumber = "99999999999 ";
        System.out.println("before trim = " + phoneNumber.length());

        phoneNumber = phoneNumber.trim();
        System.out.println("after trim = " + phoneNumber.length());
    }
}

Result

before trim = 12
after trim = 11

Me too maybe he need to add String variable first – I'm Limit Aug 09 '19 at 03:33 — I'm Limit, Aug 09 '19 at 03:33

score 0 · Answer 3 · answered Aug 09 '19 at 04:01

0

Try using tostring, and try the bellow code.

Cell currentCell;

if(currentCell.getCellTypeEnum() == CellType.STRING) {
    String phoneNumber = currentCell.toString().trim();
    System.out.println(phoneNumber);
}

answered Aug 09 '19 at 04:01

frianH

7,295
6
20
45

No `currentCell` is `XSSFCell` Object cannot to string but can call with `currentCell.getStringCellValue()` – I'm Limit Aug 09 '19 at 08:26
i mean, you try `currentCell.toString().trim()` – frianH Aug 09 '19 at 08:36

XSSFWorkbook in java

3 Answers3