How to Read Excel Files in Java using Apache POI




Apache POI is one of the trusted java package to handle MS office document. Using POI we can read write data in MS Excel using java, Even we can read write in MS word, power point.

In this post, I am discussing some common activities required to do in real life application for MS excel operations.

Some useful classes which are required to handle excel file:

Apache POI main classes usually start with either HSSF, XSSF or SXSSF.
   1.       HSSF is used for Excel 97-2000 file format. E.g. HSSFWorkbook, HSSFSheet.
   2.       XSSF is used for Excel 2007 OOXML (.xlsx) file format. e.g. XSSFWorkbook, XSSFSheet.  
   3.       SXSSF (since 3.8-beta3) is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited. e.g. SXSSFWorkbook, SXSSFSheet. SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document.

XSSF (XML SpreadSheet Format) – Used to reading and writing Open Office XML (XLSX) format files.   
HSSF (Horrible SpreadSheet Format) – Use to read and write Microsoft Excel (XLS) format files.


How to Read Excel File (XLSX) in Java


Writing a file using POI is very simple and involve following steps:

a.       Opening workbook
b.      Point to the sheet in workbook
c.       Traversing row in sheet
d.      Read cells in sheet

     a.       Opening Workbook:
In this example I am using xlsx format means I am using XSSF class.
File fileName = new File("C://testprokect/dataSheet.xlsx"); 
FileInputStream fis = new FileInputStream(fileName );
// Finds the workbook instance for XLSX file
 XSSFWorkbook myWorkBook = new XSSFWorkbook (fis);
Now myWorkBook contains all sheets which are available for the workbook.
// Return first sheet from the XLSX
     b.      Point to the sheet in workbook

workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0);
Here I am using only fist sheet of the workbook. Now mySheet pointing only 1st sheet of the workbook.

// Get iterator to all the rows in current sheet 
      c.       Traversing row in sheet

Iterator<Row> rowIterator = mySheet.iterator();

     d.      Read cells in sheet
   

// Traversing over each row of XLSX file
while (rowIterator.hasNext()) {
Row row = rowIterator.next(); // For each row, iterate through each
// columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue() + "\t");
break;
default:
}
}
System.out.println("");
}
}

How can I add the Apache POI Library in Eclipse


How can I add the Apache POI Library in Eclipse


Many times application requires excel to generate report or some time application expect excel file to read required data.  Example to design a testing framework we need several  excel file to read input data and expected output for validation purpose.

installation of POI in javaAny programs who wants to access MS office files like excel, word , power point as a output or input must be used predefined APIs.  Apache POI is one of them and it is very powerful and popular java API to access MS office files.

For better understanding I am going write down one program which will read one excel file using poi and print the value, I will explain each line .


Download & Installation process 


Before the code we need to download the latest version on the POI from http://poi.apache.org/download.html and unzip its contains to a folder.  After extracting the folder we need to add all jars in eclipse project build path.

Required jars in POI




Required jars which we need to add in our project build path




Required jars for POI





Required jars which we need to add in our project build path
Required jars for POI





Required jars which we need to add in our project build path






After adding jars in project build path

Add poi jars in eclipse


After adding all jars in project build path project is read to use Apache POI.

How to Identify Web Elements Using Selenium Xpath and name

How to find element by Name in selenium

Elements do not necessarily have ID attributes on all of them. Elements can be have names that we can use to locate them uniquely. 



how to use chrome XPath isnpector
In the  image you will find value of the name attribute  of the google search text box is "q".
name is unique for this page.

Now we will write down java selenium code to uniquely identify the web element

WebDriver driver = new FirefoxDriver();
WebElement we = driver.findElement(By.name("q"));

Now we can write down the values in the search text box like below method.

we.sendKeys("selenium:);

How to find element by Xpath in selenium

Before start this topic we need to know what is xpath and how to build the xpath. Most of the time we are using xpath generator tool like Firebug , chrome developer tool ,but sometime we can not solely depend on those tool . We need to design xpath manually to overcome the complex situation.

If we look the the below link we will see there is a situation where we cant not use xapth generator tool directly because need to to use different function to handle the situation.

https://blogs.oracle.com/rajeshthekkadath/entry/xpath_searching_for_a_text


Please study xpath tutorial to learn what is xpath and how to build the xpath . I am providing few links which will give you a snapshot of the xpath.

i. http://www.w3schools.com/xsl/xpath_intro.asp
ii. http://www.tutorialspoint.com/xpath/
iii. https://www.seleniumeasy.com/selenium-tutorials/xpath-tutorial-for-selenium

How to use xpath in scrip


Sample code

WebDriver driver = new FirefoxDriver();

driver.findElement(By.xpath("//*[@id='BLUE_BAR_ID_DO_NOT_USE']/div/div/div[1]/div/div/ul/li[12]/a/span/span")).click();




Selenium Locators

Very basic question what is the locator in selenium, Locators allows us to find elements on a page that can be used in our test.  Actually we need to uniquely identify one element from HTML page where we can do operation like click, select, sendKeys etc.
Selenium webdriver provide us various mechanism to uniquely identify one element from HTML.

1.  Locate elements by ID
2.  Locate elements by Name
3.  Locate elements by Link
4.  Locate elements by Xpath
5.  Locate elements by Css
6.  Locate elements by DOM
7.  Locate elements by PartialLink

Before starting we should begain by making sure that we have all the relevant application installed. While there are not fullproof , they will give us clue how to construct the locator for our test page.


Firefox browser:

1. Firebug: This is a Firefox addon and it allows developer to find elements on the page.

Firebug is available in firefox addon section


firebug install


2. Firepath :

Firepath is available in Firefox addon section.

IE browser:

1. IE Developer tool: This is in build feature for IE ,we can launch by pressing F12.

Chrome:

1. Google chrome developer tool: Like IE this is also in build tool for chrome. By  pressing F12 we can use this tool. We can generate Xpath ,css path using this tool.


find xpath in chrome



copy xpath in chrome
Xapth or css path generation









Now start with locate element by ID

On web application element should have an ID for all there controls on the page. ID should be the unique for that page.

How to find element by ID in selenium 


selenium locators
In the  image you will find the ID of the google search text box.  id="lst-ib".
ID is unique for this page.

Now we will write down java selenium code to uniquely identify the web element (In selenium all web component is a web element) and we will operate on this web element.

Here we are assuming we using Firefox browser
WebDriver driver = new FirefoxDriver();
WebElement we = driver.findElement(By.id("st-ib"));
we.sendKeys("selenium:);

In my next post I will cover below items

1.  Locate elements by Name
2.  Locate elements by Link
3.  Locate elements by Xpath

Selenium Installation process

Hi this is my first post on selenium . lets start with definition of selenium and then the installation process. My complete tutorial based on selenium java.  
how to install selenium

What is Selenium?

Selenium is a free (open source) automated testing suite for web applications across different browsers like google chrome, firefox IE,  opera and safari .It also run on different OS like windows, Linux, mac. It is quite similar to UFT (HP) previously it was QTP, but selenium works only on web application


Installation : 

1.  Install java sdk  from oracle  website on your computer. 

Download java sdk based on your OS and the architecture like 32 bit or 64 bit . 

2. Install the java.  

3. Set the java path.  Please follow the instruction how to set the java path in your system. 

4.  Download selenium jars from here

      ii. java

5.  Extract java jars.  

6 . Download JAVA IDE like eclipse . In my selenium tutorial I will use eclipse because it is easy to use.  You can download eclipse from here

7. Create a new project through File > New > Java Project. Name the project as "newproject".

8.A new pop-up window will open enter details as follow

     i. Project Name
     ii. Location to save project
     iii. Select an execution JRE
     iv. Select layout project option
     v. Click on finish button

9. Now selenium WebDriver's into Java Build Path

In this step,

i.   Right-click on "newproject" and select Properties.
ii.  On the Properties dialog, click on "Java Build Path".
iii. Click on the Libraries tab, and then
iiv. Click on "Add External JARs.."

Now your project is ready to use as a selenium test.

Thanks to read my blog . Please let me know if you have any query on selenium installation process . I will try to solve the query.


In my next tutorial I will explain selenium locators and how to user different locator in script.