Read Excel data from .xls and .xlsx file using POI
minutes read
We all know the importance of a file and how frequently we use it, for example, either we create a new file, update or delete something from it. Selenium helps to automate file manipulation, so in this blog, we will learn how to install the POI jar file and how to perform the read operation on excel with the help of JAVA IO package and APACHE POI library.
Note : For .XLS file read write, we can use .JXL jar file. However, it will not support .xlsx file.
Step 1. If you do not use Eclipse, then the first and foremost step is to install the latest version of Eclipse.
Step 2. If you haven’t added WebDriver, then download the webdriver jar file and add to the library.
Step 3. If you are using Maven, then use the following dependencies.
<!– https://mvnrepository.com/artifact/org.apache.poi/poi –>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
Or else you can directly download the latest POI jar file from https://poi.apache.org/download.html
Step 4. Once the jar file is downloaded, then unzip the file and add library file into your project.
Right click on your project -> Build Path -> Configure Build Path-> Library -> Add External Jar-> select the below listed jar file from unzip file -> ok
Below are Java interfaces and classes that we will use for reading/write xls and xlsx file in POI
- XSSFWorkbook : – Is a class representation of XLSX file.
- HSSFWrokbook : – Is a class represent of XLS file.
- XSSFSheet :- XSSFSheet classes implement this interface represent XLSX file’s sheet.
- HSSFSheet : – HSSFSheet classes implement this interface XLS file’s sheet.
- XSSFRow :- XSSFSheet classes implement this interface represent a row of XLSX file.
- HSSFRow : – HSSFSheet classes implement this interface represent a row of XLS file.
- XSSFCell :- XSSFSheet classes implement this interface represent a cell in a row of XLSX file.
- HSSFCell : – HSSFSheet classes implement this interface represent a cell in a row of XLS file.
Read Operation
Consider below excel to be input for our program to login into Salesforce
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
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.apache.poi.xssf.usermodel.extensions.XSSFCellFill;
import org.apache.poi.ss.usermodel.Workbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
public class excelSheetReadWrite {
WebDriver driver;
public static void main(String[] args)throws IOException {
// TODO Auto-generated method stub
String file=”D://Selenium Projects//Credential.xlsx”;
FileInputStream fis= new FileInputStream(file);
XSSFWorkbook workbook= new XSSFWorkbook(fis);
XSSFSheet sheet= workbook.getSheet(“Sheet1”);
/*Reading data from Excel
System.out.println(sheet.getSheetName());
System.out.println(sheet.getLastRowNum());
String userName=sheet.getRow(1).getCell(1).toString();
System.out.println(sheet.getRow(2).getCell(0));
System.out.println(userName);
*/
//Open Chrome browser
WebDriver driver;
System.setProperty(“webdriver.chrome.driver”,”D:\\SeleniumDrivers\\Chrome\\
chromedriver.exe”);
driver = new ChromeDriver();
driver.manage().window().maximize();
//Login With Salesforce using Webdriver
driver.get(“https://login.salesforce.com/”);
//XSSFRow r= sheet.getRow(1);
int noOfColumns = sheet.getRow(1).getLastCellNum();
String[] Headers = new String[noOfColumns];
for(int i=0;i < noOfColumns ;i++){
Headers[i]=sheet.getRow(0).getCell(i).getStringCellValue();
}
for(int j=0; j< noOfColumns;j++){
if(Headers[j].equals(“UserName”)){
driver.findElement(By.id(“username”)).sendKeys(sheet.getRow(1).getCell(j).getStringCellValue());
}
if(Headers[j].equals(“Password”)){ driver.findElement(By.id(“password”)).sendKeys(sheet.getRow(1).getCell(j).getStringCellValue());
break;
}
}
driver.findElement(By.id(“Login”)).click();
fis.close();
}
}
Once above code is run using eclipse, the browser will open automatically and user would be naviagated to Login URL and then username and password will be fetch from excel file to login into salesforce.
Note: Similarly you may create account record in bulk by using below excel file
