Java Excel Reader

Steps:

1. Create the following variable inside the class.
• It will look like this

public class ExcelReader {
    static XSSFWorkbook workbook;
    static XSSFSheet sheet;
}

1.  
2. Write a constructor – Press Ctrl + n in Windows and Command + n in Mac to get the Menu. Choose Constructor and it is ready.
   • This constructor needs two parameters one is the path of the excel from the resources folder and the name of the sheet that you want to fetch the data from.
   • After that, as we discussed we will first need the Workbook and then Sheet. So, initialize both the variables
   • It will look like this:
     

     public class ExcelReader {
         static XSSFWorkbook workbook;
         static XSSFSheet sheet;
         public ExcelReader(String excelPath, String sheetName) {
             try {
                 workbook = new XSSFWorkbook(excelPath);
                 sheet = workbook.getSheet(sheetName);
             } catch (Exception e) {
                 e.printStackTrace();
             }
         }

   Now we have both WorkBook and Sheet. If you notice
   sheeet
   variable was created with the help of `workbook` which makes sense when we MS Excel manually. We always create a workbook first and then create a sheet inside it.

3. Let find out how many rows are there in the sheet (not workbook). So it will look like this. This method getPhysicalNumberOfRows() is available out of the box with the POI Apache dependency we don’t have to write it.

   • public int getRowCount() {
         return sheet.getPhysicalNumberOfRows();
     }

4. And similarly how many Columns

   • public int getColCount() {
         return sheet.getRow(0).getPhysicalNumberOfCells();
     }

5. As per the flow Workbook > Sheet > Row and Col > Cells Data. Now we need to fetch the data from cells. So let’s imagine that cells can have numerical data.

   • public double getCellDataNumericalValue(int rowNum, int colNum) {
         try {
             return sheet.getRow(rowNum).getCell(colNum).getNumericCellValue();
         } catch (Exception e) {
             System.out.println("There is an exception, so returning the default value as");
             e.printStackTrace();
             return 0;
         }
     }

   • So it is clear that cells are inside the sheet, so obviously we will be needing sheet variables to fetch the cell’s value, and to locate a cell we need to know row and column. In the last part, getNumericCellValue() this method always looks for a value that is double if not it will throw an error. We need this because if by mistake we have entered the wrong value it will use the proper Exception. Please see the video for a better understanding.

6. Just like we fetched a numeric value from a cell. We need a method that can fetch/read a `String` value and that will be:
   •  Basically, if you notice it is the same method (without try-catch block), it is just that we are converting the value to String using .toString() method

   public String getCellDataStringData(int rowNum, int colNum) {
       return sheet.getRow(rowNum).getCell(colNum).toString();
   }

   • That’s it our Java Excel Reader is ready. Now we only want to test it, whether it is working fine or not.
     

      public class ExcelReader {
     
         static XSSFWorkbook workbook;
         static XSSFSheet sheet;
     
         public ExcelReader(String excelPath, String sheetName) {
             try {
                 workbook = new XSSFWorkbook(excelPath);
                 sheet = workbook.getSheet(sheetName);
             } catch (Exception e) {
                 e.printStackTrace();
             }
         }
     
         public int getRowCount() {
             return sheet.getPhysicalNumberOfRows();
         }
     
         public int getColCount() {
             return sheet.getRow(0).getPhysicalNumberOfCells();
         }
     
         public double getCellDataNumericalValue(int rowNum, int colNum) {
             try {
                 return sheet.getRow(rowNum).getCell(colNum).getNumericCellValue();
             } catch (Exception e) {
                 System.out.println("There is an exception, so returning the default value as");
                 e.printStackTrace();
                 return 0;
             }
         }
     
         public String getCellDataStringData(int rowNum, int colNum) {
             return sheet.getRow(rowNum).getCell(colNum).toString();
         }
     }

7. Now for testing, let’s create another class where we will only put our test cases. Go to src/test/java and create one package tests inside com.datadriven.excel
8. Inside this this tests package, create one Java file as FetchData.java which will look like this.
   

   public class FetchData {
   static String excelPath = System.getProperty("user.dir") + "/src/test/resources/Book1.xlsx";
   static ExcelReader excelReader = new ExcelReader(excelPath, "Sheet1");
   
   
   
   public static void main(String[] args) {
       int rowCount = excelReader.getRowCount();
       int colCount = excelReader.getColCount();
       double cellNumValue = excelReader.getCellDataNumericalValue(1, 2);
       String cellStringValue = excelReader.getCellDataStringData(1, 2);
       System.out.println("Row count is " + rowCount);
       System.out.println("Col count is " + colCount);
       System.out.println("Double value is " + cellNumValue);
       System.out.println("String value is " + cellStringValue);
     }
   }