Cannot get a text value from a numeric cell “Poi”

Question

I'm trying to consume data from a spreadsheet in Excel, but always of this error, already tried formatting the worksheet to text and number and still the error persists.

I saw a person using it resolved cell.setCellType ( Cell.CELL_TYPE_STRING ) ; but I do not know where I fit this passage in my code.

WebElement searchbox = driver.findElement(By.name("j_username")); WebElement searchbox2 = driver.findElement(By.name("j_password"));           try {     FileInputStream file = new FileInputStream(new File("C:\\paulo.xls"));      HSSFWorkbook workbook = new HSSFWorkbook(file);     HSSFSheet sheet = workbook.getSheetAt(0);      for (int i=1; i <= sheet.getLastRowNum(); i++){         String j_username = sheet.getRow(i).getCell(0).getStringCellValue();         String j_password = sheet.getRow(i).getCell(0).getStringCellValue();          searchbox.sendKeys(j_username);         searchbox2.sendKeys(j_password);         searchbox.submit();            driver.manage().timeouts().implicitlyWait(10000, TimeUnit.MILLISECONDS);     }      workbook.close();     file.close(); } catch (FileNotFoundException fnfe) {     fnfe.printStackTrace(); } catch (IOException ioe) {     ioe.printStackTrace(); 

Answer 1

Formatter will work fine in this case.

import org.apache.poi.ss.usermodel.DataFormatter;  FileInputStream fis = new FileInputStream(workbookName); Workbook workbook = WorkbookFactory.create(fis); Sheet sheet = workbook.getSheet(sheetName); DataFormatter formatter = new DataFormatter(); String val = formatter.formatCellValue(sheet.getRow(row).getCell(col)); list.add(val);   //Adding value to list 

Answer 2

    Cell cell = sheet.getRow(i).getCell(0);     cell.setCellType ( Cell.CELL_TYPE_STRING );     String j_username = cell.getStringCellValue(); 

UPDATE

Ok, as have been said in comments, despite this works it isn't correct method of retrieving data from an Excel's cell.

According to the manual here:

If what you want to do is get a String value for your numeric cell, stop!. This is not the way to do it. Instead, for fetching the string value of a numeric or boolean or date cell, use DataFormatter instead.

And according to the DataFormatter API

DataFormatter contains methods for formatting the value stored in an Cell. This can be useful for reports and GUI presentations when you need to display data exactly as it appears in Excel. Supported formats include currency, SSN, percentages, decimals, dates, phone numbers, zip codes, etc.

So, right way to show numeric cell's value is as following:

 DataFormatter formatter = new DataFormatter(); //creating formatter using the default locale  Cell cell = sheet.getRow(i).getCell(0);  String j_username = formatter.formatCellValue(cell); //Returns the formatted value of a cell as a String regardless of the cell type.