0

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?

2
  • What are you going to do with received value? Holding numbers as strings is not a good idea.
    – Alexey R.
    Jul 8 '20 at 13:52
  • 1
    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. Jul 8 '20 at 22:09
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

1
  • 1
    That helped! Thank you :)
    – Beti
    Jul 10 '20 at 18:11
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.

1

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");
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.