>> Read Excel file in Java/Selenium using Apache POI

Read Excel file using Apache POI

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

                                               

                        File file = new File("Path of the ExcelFile\\ReadExcelFile.xls"); //import "java.io.File" at this point

                        FileInputStream fis = new FileInputStream(file); //import "java.io.FileInputStream" at this point

                                               

                        //for below codes, you will need jars from Apache POI.

                        //download latest release of Apache POI which will be available in zip file , from https://poi.apache.org

                        //unzip the folder

                        //add the jar files inside poi folder and lib folder(lib folder is inside poi folder) and other jar files if present in other folder.

                           
//If the excel file is .xls, use below statement.                    

                        Workbook workbook = new HSSFWorkbook(fis); // import "org.apache.poi.ss.usermodel.Workbook", "org.apache.poi.hssf.usermodel.HSSFWorkbook" at this point.


//If the excel file is .xlsx, use below statement.
                        //Workbook workbook = new XSSFWorkbook(fis); //import "org.apache.poi.ss.usermodel.Workbook", "org.apache.poi.xssf.usermodel.XSSFWorkbook" at this point

                                               

                        //Mention the excel sheet name which you want to read

                        Sheet excelSheet = workbook.getSheet("Sheet1"); //here sheet name is given as "Sheet1", import "org.apache.poi.ss.usermodel.Sheet" at this point.

                                               

                        //Find last row , number of rows/column , last cell as below:

                        //Find the number of rows in the sheet, ie, last row.

                        int lastRow = excelSheet.getLastRowNum();

                        System.out.println("Last row is = "+lastRow);

                        System.out.println("First row is = "+excelSheet.getFirstRowNum());

                        int numOfRows = lastRow+1;   //because the rows start from 0

                        System.out.println("Number of rows is = "+numOfRows); 

                                               

                      Row row = excelSheet.getRow(lastRow); // import "org.apache.poi.ss.usermodel.Row" at this point.

       

                        //Find the last cell in the sheet.

                        int lastCell = row.getLastCellNum();

                                               

                        System.out.println("Last cell is = "+lastCell);

                        System.out.println("Number of columns is = "+lastCell);

                                                                                               

                        //Get particular cell value

                        System.out.println("cell 3,4 is = "+excelSheet.getRow(3).getCell(4).getStringCellValue());

                                               

                        //To read all the data from the Sheet.

                        for (int i = 0; i <= lastRow; i++) {

                                for (int j = 0; j < lastCell; j++) {

                                    System.out.print(excelSheet.getRow(i).getCell(j).getStringCellValue()+" , ");

                                }

                         }

                           

                        workbook.close();

                                               

}

Read Sheet1

OUTPUT:

When you run above code, you will get below  output:

Last row is = 6

First row is = 0

Number of rows is = 7

Last cell is = 5

Number of columns is = 5

cell 3,4 is = y34

y00 , y01 , y02 , y03 , y04 , y10 , y11 , y12 , y13 , y14 , y20 , y21 , y22 , y23 , y24 , y30 , y31 , y32 , y33 , y34 , y40 , y41 , y42 , y43 , y44 , y50 , y51 , y52 , y53 , y54 , y60 , y61 , y62 , y63 , y64 ,