Reading Data from Excel File Using Apache POI:

Reading Data from Excel File Using Apache POI:

To read data from an Excel file, you can use the Apache POI library.

 Apache POI provides predefined classes, interfaces, and methods to interact with Excel files.

           

Download Apache POI.jar :

 

            <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->

                        <dependency>

                                    <groupId>org.apache.poi</groupId>

                                    <artifactId>poi</artifactId>

                                    <version>5.0.0</version>

                        </dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>5.0.0</version>

</dependency>

 

After adding these dependencies, save the pom.xml file and ensure that poi.jar and poi-ooxml.jar are added to your current project.

 

Structure of Excel :

An Excel file (workbook) contains worksheets, which in turn contain rows and cells.

 

  Xl  -Workbook

                  worksheets

              rows  and cells

              

create "test data.xls" file in ur system:

Create a file named test data.xls with the following structure:

ProductName	Qty	Price
Lux	4	200
Shampoo	5	200
Paste	2	100

Java Code to Read Data from Excel File

 

package com.birla;

import java.io.FileInputStream;

import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelReader1 {

           

            public static void main(String[] args) throws EncryptedDocumentException, IOException {

                        // TODO Auto-generated method stub

                        //  Apache POI:

                        String xlFile = "C:\\brahma\\Practise\\SelniumPractiseNew\\March52024MyWorkspace\\MavenProjectThroughCommand\\TestData.xlsx";

                        //create FileInputStream   class object

                        FileInputStream fis = new FileInputStream(xlFile);

                        //create workbook reference var

                        Workbook wb =  WorkbookFactory.create(fis);

                        // Workbook - Predefined Interface in POI

                        //create Sheet reference var  and refer Sheet1

                        Sheet  sh = wb.getSheet("Sheet1");

                        // sheet contains rows and cells

                        // Rows count

                        int rowsCnt =  sh.getLastRowNum();

                       

                        System.out.println("rowscnt="+rowsCnt);

                        // rowscnt=3  But actual = 4 rows  - 0,1,2,3

           

                        // Get columns count

                        //                                 sh.getLastCellNum();-- no method available

                        int colsCnt =  sh.getRow(0).getLastCellNum();

                       

                        System.out.println("colsCnt="+colsCnt);

                        // columnsCnt=3  --  displays exact columns count

                        // Read data from row, cell -  0,0 .toString(); ProductName

                        String  data00 = sh.getRow(0).getCell(0).toString();

                       

                        System.out.println("data00="+data00);//ProductName

                       

                        // Read data from row, cell -  0,1 --Qty

                        String data01 = sh.getRow(0).getCell(1).toString();

                        System.out.println("data01="+data01);

                        // HW Read data from 0,2 - Price

                        // Read data from 1,0  -Lux

                        String data10 = sh.getRow(1).getCell(0).toString();

                        System.out.println("data10="+data10);

                       

                        // Read data from 1,1   - 4     

                        String data11 = sh.getRow(1).getCell(1).toString();

                        System.out.println("data11="+data11);// data11=4.0  ??? data11=4

                        //  Note:   add ' single quotes before number in excel file

                        //   '4 ==   treat as string  else number

                        // Read data from 1,2 cell  -200

                        String data12 = sh.getRow(1).getCell(2).toString();

                        System.out.println("data12="+data12);// data12=200.0  ?? data12=200

            }

}

HW read  all rows data - from 1st column using for loop ?

HW  Read all rows data  from 2nd column using for loop ?

 

Develop generic method to read excel file:

  getRowsCnt(String xlFile,String sheet)

{

}

Call:

     getRowsCnt("xl file name.xlsx", "Sheet1" )

//   3

--------------

//HW Develop some generic method to get columns cnt

getColumnsCnt(String xlFile,String sheet)

{

}

Call:getColumnsCnt("xl file name.xlsx", "Sheet1" );

//      3

 

Develop below method:

readDataFromExcel(String xlFile,String sheet, int rowNo, int cellNo)

{

}

package com.birla;

import java.io.FileInputStream;

import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelReader2 {

            // readDataFromExcel(String xlFile,String sheet, int rowNo, int cellNo)

            public static String readDataFromExcel(String xlFile,String sheet, int rowNo, int cellNo) throws EncryptedDocumentException, IOException

            {

                        FileInputStream fis=  new FileInputStream(xlFile);

                        Workbook wb = WorkbookFactory.create(fis);

                        Sheet sh = wb.getSheet(sheet);

                       

                        String data = sh.getRow(rowNo).getCell(cellNo).toString();

                        System.out.println("Data from row="+ rowNo +",cell no="+ cellNo + " is ="+data);

                        return data;

            }

            public static void main(String[] args) throws EncryptedDocumentException, IOException {

                        // TODO Auto-generated method stub

                        //  Apache POI:

                        String xlFile = "C:\\brahma\\Practise\\SelniumPractiseNew\\March52024MyWorkspace\\MavenProjectThroughCommand\\TestData.xlsx";

                        // call readDataFromExcel from sheet1  0, 0

                        String data00 = readDataFromExcel(xlFile, "Sheet1", 0, 0);

                        System.out.println("data00="+data00);

                        // call readDataFromExcel from sheet1  0, 1

                        String data01 = readDataFromExcel(xlFile, "Sheet1", 0, 1);

                        System.out.println("data01="+data01);

                        // call readDataFromExcel from sheet1  0, 2

                        String data02 =readDataFromExcel(xlFile, "Sheet1", 0, 2);

                        System.out.println("data02="+data02);

            }

}

o/p:

 

Data from row=0,cell no=0 is =ProductName

data00=ProductName

Data from row=0,cell no=1 is =Qty

data01=Qty

Data from row=0,cell no=2 is =Price

data02=Price

//                     HW  Read all rows and and all cells Data  from excel file ?

//                     Note:  use 2 for loops

                        //                     0,0   0,1   0,2

                        //                     1,0   1,1   1,2

                        //                     2,0   2,1   2,2

 

 ---

                        //HW Develop Generic Method to Get Column Number Based on Column Name

                        //

                        //   getColumnNoBasedOnColumnName(ExcelFilename, sheetname,"ProductName") ;

                        //   //  1

                        // getColumnNoBasedOnColumnName(ExcelFilename, sheetname,"Qty") ; //  2

                        //

                        // getColumnNoBasedOnColumnName(ExcelFilename, sheetname,"Price") ; //  3

                        //

                        // getColumnNoBasedOnColumnName(ExcelFilename, sheetname,"QtyInvlaidColumn") ;  //  -1, Display proper msg "Given column name is not found in sheet".

                        //                    

HW Store column data in "arrayList" by passing "column name" ?

pass the column name and it should store  all the data ?

readAllDataFromExcelBasedOnColumnName( String excelfilename, String sheetname, String ColumnName)

{

}

FAQ What jar file (or) Library file is used to read the data from excel ?

Apache POI .jar file                

WAP to check Whether given sheet name is exist in excel (or) not ?

code:

// wb.getSheetIndex             

                                    //  return index no of given sheet name

                                    // sheet 1 index no =0

                                    // sheet 2 index no = 1

                                    // if sheet name is not there in excel, it returns -1

                                   

Method calls:

                        isSheetExist(xlFile, "CreateUsers");

                        isSheetExist(xlFile, "Sheet1");

                        isSheetExist(xlFile, "Sheet2");

                       

                        isSheetExist(xlFile, "CreateUsersInvalid");

----------------------

package com.birla;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelReader3 {

            // develop a method -isSheetExist(String xlFile, String sheetName ) throws

            public static int isSheetExist(String xlFile, String sheetName) throws EncryptedDocumentException, IOException

            {

                        FileInputStream fis=  new FileInputStream(xlFile);

                        Workbook wb = WorkbookFactory.create(fis);

                        int indexNo = wb.getSheetIndex(sheetName);

                       

                        System.out.println("Index no of given sheet="+sheetName +"= "+ indexNo);

                        if(indexNo>=0)

                        {

                                    System.out.println("Given sheet=" +sheetName+" is  available in excel = "+xlFile);

                        }

                        else

                        {

                                    System.out.println("Given sheet=" +sheetName+" is not  available in excel = "+xlFile);

                        }

                                   

                       

                        return indexNo;

            }

 

            public static void main(String[] args) throws EncryptedDocumentException, IOException {

                        String xlFile = "C:\\brahma\\Practise\\SelniumPractiseNew\\Jul2023WorkSpace\\MavenProjectThroughCommand\\TestData.xlsx";

                        // check isSheetExist(xlFile, "Sheet1");

                        int indexNoSheet1 = isSheetExist(xlFile, "Sheet1");

                        System.out.println("indexNoSheet1="+indexNoSheet1);

                       

                        // check isSheetExist(xlFile, "Sheet2");// 1

                        int indexNoSheet2 = isSheetExist(xlFile, "Sheet2");

                        System.out.println("indexNoSheet2="+indexNoSheet2);

                       

                        //HW check isSheetExist(xlFile, "CreateUsers");//2

                        // check isSheetExist(xlFile, "CreateUsersInvalid");// -1

                        int indexNoCreateUsersInvalid = isSheetExist(xlFile, "CreateUsersInvalid");

                        System.out.println("indexNoCreateUsersInvalid="+indexNoCreateUsersInvalid);

            }

}

           

o/p:

Index no of given sheet=Sheet1= 0

Given sheet=Sheet1 is  available in excel = C:\brahma\Practise\SelniumPractiseNew\Jul2023WorkSpace\MavenProjectThroughCommand\TestData.xlsx

indexNoSheet1=0

Index no of given sheet=Sheet2= 1

Given sheet=Sheet2 is  available in excel = C:\brahma\Practise\SelniumPractiseNew\Jul2023WorkSpace\MavenProjectThroughCommand\TestData.xlsx

indexNoSheet2=1

Index no of given sheet=CreateUsersInvalid= -1

Given sheet=CreateUsersInvalid is not  available in excel = C:\brahma\Practise\SelniumPractiseNew\Jul2023WorkSpace\MavenProjectThroughCommand\TestData.xlsx

indexNoCreateUsersInvalid=-1

Other important classes in apache poi:

                        //  Apache POI:

                        // We have some predefined classes and interfaces  and predefined methods which can be used to read excel file

 

Key Classes in Apache POI

1. HSSFWorkbook: Predefined class to read data from .xls format.
2. XSSFWorkbook: Predefined class to read data from .xlsx format.

Key Interfaces in Apache POI

1. Workbook: Interface representing an Excel workbook.
2. Sheet: Interface representing a sheet within a workbook.

Read data from .xlsx:

 

package com.birla;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadDataFromExcelByXssfworkbook {

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

                        String xlFile = "C:\\brahma\\Practise\\SelniumPractiseNew\\March52024MyWorkspace\\MavenProjectThroughCommand\\TestData.xlsx";

                        // to read data from .xlsx  -->   we have to use -XSSFWorkbook

                        // create obj for -XSSFWorkbook class

                        FileInputStream  fis  = new FileInputStream(xlFile);

                        XSSFWorkbook xssfwb =  new XSSFWorkbook(fis);

                       

                        XSSFSheet xssfsh = xssfwb.getSheet("sheet1");

                        // read data from row no, cell no = 0,0

                        String data00=  xssfsh.getRow(0).getCell(0).toString();

                        System.out.println("data00="+data00);

                        // data00=ProductName

                        // HW read data from  row no, cell no = 0,1

                        // HW read data from  row no, cell no = 0,2

                        // HW read data from  row no, cell no =1,0

                        //                     HW read data from  row no, cell no =1,1

                        //HW  Get Rows count

                        //HW  Get columns Cnt

            }

}

                       

// HW  create new excel file with .xls format  and  Get Rows count,  Get columns Cnt ,  read data from .xls format file using 'HSSWorkbook' class?