Selenium, Java, POI: How to read numeric values from Excel file?

Question

I have a table in the Excel file like this:

enter image description here

I would like to read the product ID and I have the code below:

public class ReadDataFromExcel extends Page {
public static void main(String[] args) {
 ReadDataFromExcel rdfe = new ReadDataFromExcel();
 String vOutput = rdfe.ReadCellData(1, 0);
 //System.out.println(vOutput);
}
//method defined for reading a cell
public String ReadCellData(int vRow, int vColumn) {
 String value = null; //variable for storing the cell value
 Workbook wb = null; //initialize Workbook null
 try {
 //reading data from a file in the form of bytes
 FileInputStream fis = new FileInputStream(System.getProperty("user.dir") + "//src//test//resources//executables//bCom pricing.xlsx");
 //constructs an XSSFWorkbook object, by buffering the whole stream into the memory
 wb = new XSSFWorkbook(fis);
 } catch (IOException e) {
 e.printStackTrace();
 }
 Sheet sheet = wb.getSheetAt(0); //getting the XSSFSheet object at given index
 Row row = sheet.getRow(vRow); //returns the logical row
 Cell cell = row.getCell(vColumn); //getting the cell representing the given column
 //value = cell.getStringCellValue(); //getting cell value
 switch (cell.getCellType())
 {
 case Cell.CELL_TYPE_STRING: //field that represents string cell type
 value = cell.getStringCellValue() + "\t\t\t";
 System.out.print(value);
 break;
 case Cell.CELL_TYPE_NUMERIC: //field that represents number cell type
 value = cell.getNumericCellValue() + "\t\t\t";
 System.out.print(value);
 break;
 default:
}
 return value; //returns the cell value
 }
}

Unfortuantely instead of 7199113022 I get the "7.100113022E9" in the console output. How this should be formatted to receive the exact number?

• Alexey R.: What are you going to do with received value? Holding numbers as strings is not a good idea.
• João Farias: Voting to close as this question is not related to testing, but on how to use the Excel reading library. Posting it on StackOverflow would be more appropriate.

Answer 1

Try with below code this will fix the issue

 public String ReadCellData(int vRow, int vColumn) {
 
 String value = null; //variable for storing the cell value
 Workbook wb = null; //initialize Workbook null
 try {
 //reading data from a file in the form of bytes
 FileInputStream fis = new FileInputStream("path to file");
 //constructs an XSSFWorkbook object, by buffering the whole stream into the memory
 wb = new XSSFWorkbook(fis);
 } catch (IOException e) {
 e.printStackTrace();
 }
 Sheet sheet = wb.getSheetAt(0); //getting the XSSFSheet object at given index
 Row row = sheet.getRow(vRow); //returns the logical row
 Cell cell = row.getCell(vColumn); //getting the cell representing the given column
 //value = cell.getStringCellValue(); //getting cell value
 
 
 switch (cell.getCellType())
 {
 case Cell.CELL_TYPE_STRING: //field that represents string cell type
 value = cell.getStringCellValue();
 break;
 case Cell.CELL_TYPE_NUMERIC: //field that represents number cell type 
 value =(long) cell.getNumericCellValue()+"";
 break;
 default:
 
 }
 
 return value; //returns the cell value
 }

Explanation

value =(long) cell.getNumericCellValue()+""; only changes i have made. Applied casting and converted it into a string

I hope this will fix the issue

• Beti: That helped! Thank you :)

Answer 2

The solution that POI API implies is to use DataFormatter class. Using data formatter you can convert cell value to a string independently of what cell type is.

Approximate code should look like (no switch block is required):

Cell cell = row.getCell(vColumn);
DataFormatter dataFormatter = new DataFormatter();
return dataFormatter.formatCellValue(cell) + "\t\t\t";

Theoretically it should take the format that the cell in your spreadsheet has. Give this a try and let me know if it works.

Answer 3

Copy the value to a TEXT cell and get the value , in below code let say you have 5 columns so i am creating 6th column as TEXT and copy the value to this new cell and then retrieve the value.

 package runner;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Locale;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class test {
 public static void main(String[] args) throws IOException {
 // TODO Auto-generated method stub
 
 FileInputStream file = new FileInputStream("src\\files\\test2.xlsx");
 XSSFWorkbook wb = new XSSFWorkbook(file);
 XSSFSheet sheet = wb.getSheet("Sheet1");
 int row_count = sheet.getLastRowNum() + 1;
 System.out.println("no of rows in the data file is:" + (row_count));
 int column_count = sheet.getRow(0).getPhysicalNumberOfCells();
 System.out.println("no of columns in the data file is:" + (column_count));
 DataFormatter dataFormatter = new HSSFDataFormatter(Locale.ENGLISH);
 // Inside loop
 
 for (int row_index = 1; row_index < row_count; row_index++) {
 XSSFRow currentrow = sheet.getRow(row_index);
 
 //Create cell where there will be no data
 currentrow.createCell(6, CellType.STRING);
 
 //get value of the cell
 String val=currentrow.getCell(0).toString();
 
 //get address of the cell
 CellAddress add=currentrow.getCell(0).getAddress();
 
 //set formula on newly created cell to convert the value from the cell to text (=TEXT(A1,"0.00000000000000000000000") 
 currentrow.getCell(6).setCellFormula("TEXT("+add+",\"0.000000000000000000000000000000000000\")"); 
 // set formula doesn't trigger the formula, we have to use evaluate cell to apply the formula
 CreationHelper creationHelper = wb.getCreationHelper();
 FormulaEvaluator evaluator = creationHelper.createFormulaEvaluator();
 Cell cell = evaluator.evaluateInCell(currentrow.getCell(6));
 
 //print the formula
 System.out.println(currentrow.getCell(6).getStringCellValue()) ;
 
 //close the workbook
 wb.close();
// String cellValueStr = dataFormatter.formatCellValue(currentrow.getCell(0) );
 }
 }
}

See if this helps

Note:

you will have more zeros in decimal as you mention in the formula . So if the formula is TEXT(A1,"0.0") the only one decimal place will be there, for TEXT(A1,"0.000") there will be 3 and so on

and

XSSFWorkbook wb = new XSSFWorkbook(file);
XSSFSheet sheet = wb.getSheet("Sheet1");

Answer 4

Please check this code .which may helpful for many :-

public String cellconvertor(Cell cell)
 {
 try {
 String cellvalue=null;
 if(cell.getCellType().equals(CellType.STRING))
 {
 cellvalue= cell.getStringCellValue();
 }
 else if (cell.getCellType().equals(CellType.NUMERIC ))
 {
 if(HSSFDateUtil.isCellDateFormatted(cell))
 {
 cellvalue=String.valueOf(cell.getDateCellValue());
 }
 else
 {
 cellvalue= String.valueOf(cell.getNumericCellValue()); 
 } 
 }
 return cellvalue;
 }
 catch(NullPointerException e)
 {
 throw new NullPointerException();
 }
 }