Reading Excel File Using apache poi

Reading an excel file

Reading an excel file is also very simple if we divide this in steps

• Create workbook instance from excel sheet
• Get to the desired sheet
• Increment row number
• Iterate using for loop and get the desired row and column data

Maven Dependencies:

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

Sample Program:

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.chrome.ChromeDriver;

public class URLS {

public static FileOutputStream outFile;

public static HSSFWorkbook wb;

public static HSSFSheet sh;

static FileInputStream fis;

static List<String> urls = new ArrayList<String>();

static void excelSetup(String sheetName) throws IOException {

/*

 * Reading the Excel Files

 */

fis = new FileInputStream(System.getProperty("user.dir") + "/ExcelWorkbook.xls");

wb = new HSSFWorkbook(fis);

sh = wb.getSheet(sheetName);

}

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

excelSetup("URLS");

System.setProperty("webdriver.chrome.driver",

System.getProperty("user.dir") + File.separator + "chromedriver_mac64");

WebDriver driver = new ChromeDriver();

int rows = sh.getLastRowNum();

for (int i = 1; i <=rows; i++) {

String url = sh.getRow(i).getCell(0).toString();

//Reading the URL from the Excel Sheet

                        driver.get(url);

if (driver.getPageSource().contains("404 Page Not Found")) {

System.out.println(sh.getRow(i).getCell(0).toString());

}else{

System.out.println(driver.getTitle()+"=="+driver.getCurrentUrl());

}

}

driver.quit();

}

}