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

Question

I have a table in the Excel file like this:

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?

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

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();
        }
    }