How to read data from excel sheet using Apache POI in Java?

Here is the complete Java code to read and write from the excel sheet in Java. Please note that you will have to download and add POI library to current project from url http://poi.apache.org/download.html

The package org.apache.poi.hssf contains the xls implementations
The package org.apache.poi.xssf contains the xlsx implementations

package framework;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;

public class Excel {

 /**
  * @param args
  */
 public static void main(String[] args) {
    
FileInputStream file = null;
HSSFWorkbook workbook;

Futil.createHtmlHead(1);


try {
 
 
 
   
 file = new FileInputStream(new File("F:\\selenium\\batch.xls"));
 workbook = new HSSFWorkbook(file);
 HSSFSheet sheet = workbook.getSheetAt(0);
 Iterator<Row> rowIterator = sheet.iterator();
 int k=0;
 while (rowIterator.hasNext())  
    {
    rowIterator.next();
    k++;
    }
 
 System.out.println("Total rows in the sheet " + k);
 int intRowCounter = 1;
 Row row =  sheet.getRow(intRowCounter);
 System.out.println("Data in the excel " +  readcell(row,2));
 
 row.getCell(1).setCellValue("salunke");
 FileOutputStream fos = new FileOutputStream("F:\\selenium\\batch.xls");
    workbook.write(fos);
    fos.close();

} 
catch(Exception ex){
System.out.println("Exception occured" + ex.toString());
}

finally{
 
 try{
 file.close();
 }catch(Exception ex){
  
  System.out.println(ex.toString());
 }
 
 
}

 }//main method ends
 
 
 
//to read the data
 
 public static String readcell(Row rowObj,int colNum)
 {
  String x="";
 try{
  if  (rowObj.getCell(colNum).getCellType() == 1)
   x = rowObj.getCell(colNum).getStringCellValue();
  else if  (rowObj.getCell(colNum).getCellType() == 0)
   x = "" + (int)rowObj.getCell(colNum).getNumericCellValue();
 }
 catch(Exception e){
  x = "";
 //System.out.println(e.toString() + " while reading a cell");
  }
  
  return x;
 }
 
 
 //to write the data
 public static void writeCell(Row rowObj,int colNum, String data)
 {
  
  try{
    
    rowObj.getCell(colNum).setCellValue((String) data);
   
  }
  catch(Exception e){
  
   System.out.println(e.toString() + " while writing a cell");
  }
  
  
 }
 
 

} //class ends

You may also like to read below topics.

1. Press key in Selenium Webdriver in Java
2. Read Table Data in Selenium Webdriver in Java
3. Take the screenshot in Selenium Webdriver in Java
4. Execute JavaScript in Selenium Webdriver in Java
5. Handle multiple browser windows in Selenium Webdriver in Java
6. Double-Click on the Element in Selenium Webdriver in Java
7. Exceptions in Selenium Webdriver in Java
8. Synchronization in Selenium Webdriver in Java
9. Frameworks in Selenium

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

Synchronization in selenium webdriver in python - Example

Synchronization makes selenium to wait for specific elements before performing operation on them. We can use below synchronization methods in selenium. 

1. Page Load Synchrnoization – Implicit Wait

We can set the default page navigation timeout. Below statement will set the navigation timeout as 50. This means that selenium script will wait for maximum 50 seconds for page to load.  If page does not load within 50 seconds, it will throw an exception.

driver.set_page_load_timeout(50)

2. Element Synchronization – Implicit Wait

We can set the default element existance timeout. Below statement will set the default object synchronization timeout as 20. This means that selenium script will wait for maximum 20 seconds for element to exist.  If Web element does not exist within 20 seconds, it will throw an exception.

driver.implicitly_wait(20)

3. Synchronization based upon specific condition – Explicit Waits

We can also insert custom synchronization points in the script using WebDriverWait class. Please remember that you have to import this class before you use it.

from selenium.webdriver.support import expected_conditions as EC

We can also instruct selenium to wait until element is in expected condition.

from selenium.webdriver.support import expected_conditions as EC

wait = WebDriverWait(driver, 10)

element = wait.until(EC.element_to_be_clickable((By.ID,'someid')))

from selenium import webdriver

from selenium.webdriver.common.by import By

from selenium.webdriver.support.ui import WebDriverWait

# available since 2.4.0

from selenium.webdriver.support import expected_conditions as EC

# available since 2.26.0

ff = webdriver.Firefox()

ff.get("http://somedomain/url_that_delays_loading")

try:

    element = WebDriverWait(ff, 10).until(EC.presence_of_element_located((By.ID, "myDynamicElement")))

finally:

    ff.quit()

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

Get the selected value in combobox in selenium webdriver in python.

We can get the data from combobox using get_attribute method as illustrated in the below example.

from selenium import webdriver

from selenium.webdriver.support.ui import Select

driver = webdriver.Firefox()

driver.maximize_window()

driver.get("http://register.rediff.com/register/register.php")

try:

   

 e = driver.find_element_by_name("DOB_Day")

 select = Select(e)

 select.select_by_index(3)

 selOption = select.first_selected_option

   

 print(selOption.get_attribute("value"))

   

 driver.close()

except Exception as e:

    print ("Exception occured", format(e));

finally:

    driver.quit()

    print ("finally")

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

Reading data from the webpage in selenium webdriver in python - Example

Selenium API provides 2 important methods to read data from web elements.

1. value_of_css_property – gets the value of css property of the element
2. get_attribute – gets the value of given attribute.

We can also get the inner text of element using text property.

We can also check if

1. Element is displayed using is_displayed mehtod
2. Element is selected using is_selected method
3. Element is enabled using is_enabled method

x = e.value_of_css_property("width");
x = e.get_attribute("onfocus");

x = e.is_selected();
x = e.is_displayed();
x = e.isEnabled();

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

Working with radiobuttons in Selenium Webdriver in Python - Example

We can select the radiobutton using click method as illustrated in the below example in Python.

from selenium import webdriver

from selenium.webdriver.support.ui import Select

driver = webdriver.Firefox()

driver.maximize_window()

driver.get("http://register.rediff.com/register/register.php")

try:

 driver.find_element_by_xpath("//*[@id='wrapper']/table[2]/tbody/tr[21]/td[3]/input[2]").click()

   

    driver.close()

except Exception as e:

    print ("Exception occured", format(e));

finally:

    driver.quit()

    print ("finally")

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

Working with Checkboxes in Selenium Webdriver in Python - Example

We can first see if the checkbox is selected using is_selected() method. Then using click method we can perform the operations such as selecting or deselecting the checkboxes as illustrated in the below example.

from selenium import webdriver

from selenium.webdriver.support.ui import Select

driver = webdriver.Firefox()

driver.maximize_window()

driver.get("http://register.rediff.com/register/register.php")

try:

   

elem= driver.find_element_by_name("chk_altemail")

  

   if (elem.is_selected()):

       print("Checkbox is selected..now deselecting")

      elem.click()

   else:

       print("Checkbox is not selected..now selecting it")

      elem.click()

   

   driver.close()

except Exception as e:

    print ("Exception occured", format(e));

finally:

    driver.quit()

    print ("finally")

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com

How to click on the buttons in selenium webdriver in Python?

We can click on the buttons using click() method as illustrated in the below example.

from selenium import webdriver

from selenium.webdriver.support.ui import Select

driver = webdriver.Firefox()

driver.maximize_window()

driver.get("http://www.amazon.in")

try:

    driver.find_element_by_xpath("//*[@id='twotabsearchtextbox']").send_keys("Selenium")

    driver.find_element_by_xpath("//*[@id='nav-bar-inner']").click()

   

    driver.close()

except Exception as e:

    print ("Exception occured", format(e));

finally:

    driver.quit()

    print ("finally")

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com