Automating a Script to Get The Prices for Travelling between Different Cities Using Apache POI

In the previous article we learnt how to compare two travel prices in order to get the lowest price between two different cities. In that example, we wrote everything in the script only, because it was only two cities, but what if you want to know the travel prices of different cities like Bangalore-Delhi, Bangalore-Lucknow, Bangalore-Dellas, Bangalore-New York…and 100 other cities. It will be difficult for to write 1000 lines of code repeating the same line of code with different values. So instead of doing it, we will create an excel file in which there will be two columns “Departure City”  and “Arrival City”. And list down all the Departure and arrival cities.

Now launch Eclipse, create a package and a class inside it.  Now the question is how will you fetch the values from the excel file to your script. So here we have Apache POI. Apache provides this library POI which will help us to read the values from excel file.

So we have to fetch the values from excel file and pass it to our script in order to get the automated result. Let us see how to do it :

1. Go to any of your local drive and create an excel sheet named “abcd.xlsx”.

2. Inside this file, list down all the departure and arrival cities.

Departure Arrival
BLR ORD
BLR LKO
BLR DEL

aaa

Keep the sheet name as Sheet1. Select the values on the sheet, right click and click on Format cells.

aaa

Under Number tab, chose the type Text and click OK. We are doing it because, if we don’t do it, we may get a NullPointerException. In window, there is no need to give the border to the values, then also if you want you can give.

bbb

Once it is done, go to your script and get this file there. How to get it there, that we are going to see here :

1. In your script, create a method called excelRead() and in this method we will get the values of the excel file.

public static String[][] excelRead() throws IOException
{
FileInputStream fis=new FileInputStream(“E:\\abcd.xlsx”);
XSSFWorkbook wb=new XSSFWorkbook(fis);
XSSFSheet ws=wb.getSheet(“Sheet1”);

This is just half of the method. Here the first line FileInputStream creates a pipeline between our excel file and script. Then we need to create workbook and pass the fis object to it. The next thing we need to do is get the sheet, which we can get using a method getSheet(sheetname) present inside Workbook class.

int rowNum=ws.getLastRowNum()+1;
int colNum=ws.getRow(0).getLastCellNum();
String[][] data=new String[rowNum][colNum];

for(int i=1; i<rowNum ; i++)
{
XSSFRow row=ws.getRow(i);
for(int j=0; j<colNum; j++)
{
XSSFCell cell=row.getCell(j);
String value=cellToString(cell);
data[i][j]=value;
System.out.println(cell);
}
}
return data;
}

To read all the values one by one we need to create one loop. The first thing is to find the length of row and column, then we can set the loop according to the row number and column. In the above code, the first line gets us the Last row number, as the row number starts with zero, we have written +1 to get the exact number. Once we get the row number, using the getRow(0) method we can get the last column number.

In rest of the code we are passing the row number and the column number, and we are getting the cell value. But another problem here is that, whatever value we get is in the form of cell object, we need to convert it into string or number, however we want. To convert this cell object value to string, we have again created a method called cellToString() and we are passing the cell value as argument.

public static String cellToString(XSSFCell cell)
{
int type;
Object res;
type=cell.getCellType();
switch(type)
{
case 0://Numeric value in excel
res=cell.getNumericCellValue();
break;
case 1://String value in excel
res=cell.getStringCellValue();
break;
default:
throw new RuntimeException(“There are no support for this type of cell.”);
}
return res.toString();

}

Now we need to write the automation steps which is required to perform the main operations. In the previous article, we created a method called findPrice, there we passed one argument “dest”, but here we will pass two argumenst src and dest.

public static double findPrice(String src,String dest) throws InterruptedException
{
WebDriver driver=new FirefoxDriver();
driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
driver.get(“http://www.orbitz.com/”);
Thread.sleep(1000);
//Click on the Flights Only radio button
driver.findElement(By.xpath(“//input[@name=’search.type’]”)).click();
//Click on From city
driver.findElement(By.name(“ar.rt.leaveSlice.orig.key”)).sendKeys(src);
//Click on To city
driver.findElement(By.name(“ar.rt.leaveSlice.dest.key”)).sendKeys(dest);
//Leave date
driver.findElement(By.name(“ar.rt.leaveSlice.date”)).sendKeys(“03/20/2015”);
//Return Date
driver.findElement(By.name(“ar.rt.returnSlice.date”)).sendKeys(“03/27/2015”);
//Click on Search Flights Button
driver.findElement(By.name(“search”)).click();
Thread.sleep(1000);
//Select the lowest price from the list
driver.findElement(By.xpath(“//*[@id=’main’]/div[9]/div[2]/div[1]/div[1]/div/div[2]/a”)).click();
//Get the price
String price=driver.findElement(By.className(“mirrorCash”)).getText().replace(“$”, “”).replace(“,”,””);
double dprice=Double.parseDouble(price);
return dprice;

}

We created three separate methods excelRead(), cellToString(HSSFCell cell)  and findPrice(String src,String dest). All we have to do now is call these methods into main method whenever required. In main method we will call findPrice(src,dest) which will take different values from the excel sheet which will come through excelRead() method and then we will get all the travel prices for the cities listed in excel sheet.

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

String[][] data;
data=excelRead();
double price;
for(int i=1 ; i<data.length ; i++)
{
price=findPrice(data[i][0],data[i][1]);
System.out.println(“Price For Flight From ” +data[i][0]+ ” To ” +data[i][1]+ ” is ” +price);
}
}

The whole code will look something like this :

package home;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.concurrent.TimeUnit;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;

public class Demo1 {

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

String[][] data;
data=excelRead();
double price;
for(int i=1 ; i<data.length ; i++)
{
price=findPrice(data[i][0],data[i][1]);
System.out.println(“Price For Flight From ” +data[i][0]+ ” To ” +data[i][1]+ ” is ” +price);
}
}
public static double findPrice(String src,String dest) throws InterruptedException
{
WebDriver driver=new FirefoxDriver();
driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
driver.get(“http://www.orbitz.com/”);

Thread.sleep(1000);

//Click on the Flights Only radio button
driver.findElement(By.xpath(“//input[@name=’search.type’]”)).click();

//Click on From city
driver.findElement(By.name(“ar.rt.leaveSlice.orig.key”)).sendKeys(src);

//Click on To city
driver.findElement(By.name(“ar.rt.leaveSlice.dest.key”)).sendKeys(dest);

//Leave date
driver.findElement(By.name(“ar.rt.leaveSlice.date”)).sendKeys(“03/20/2015”);

//Return Date
driver.findElement(By.name(“ar.rt.returnSlice.date”)).sendKeys(“03/27/2015”);

//Click on Search Flights Button
driver.findElement(By.name(“search”)).click();

Thread.sleep(1000);

//Select the lowest price from the list
driver.findElement(By.xpath(“//*[@id=’main’]/div[9]/div[2]/div[1]/div[1]/div/div[2]/a”)).click();

//Get the price
String price=driver.findElement(By.className(“mirrorCash”)).getText().replace(“$”, “”).replace(“,”,””);
double dprice=Double.parseDouble(price);
return dprice;
}
public static String[][] excelRead() throws IOException
{
FileInputStream fis=new FileInputStream(“E:\\abcd.xlsx”);
XSSFWorkbook wb=new XSSFWorkbook(fis);
XSSFSheet ws=wb.getSheet(“Sheet1”);

int rowNum=ws.getLastRowNum()+1;
int colNum=ws.getRow(0).getLastCellNum();
String[][] data=new String[rowNum][colNum];

for(int i=1; i<rowNum ; i++)
{
XSSFRow row=ws.getRow(i);
for(int j=0; j<colNum; j++)
{
XSSFCell cell=row.getCell(j);
String value=cellToString(cell);
data[i][j]=value;
System.out.println(cell);
}
}
return data;
}
public static String cellToString(XSSFCell cell)
{
int type;
Object res;
type=cell.getCellType();
switch(type)
{
case 0://Numeric value in excel
res=cell.getNumericCellValue();
break;
case 1://String value in excel
res=cell.getStringCellValue();
break;
default:
throw new RuntimeException(“There are no support for this type of cell.”);
}
return res.toString();
}}

Now run the script, you will get all the travel cost of the cities listed in the excel sheet…!!!

Add Comment