Working with Excel sheet using POI apache
In this tutorial, we will learn how to integrate Microsoft Excel sheet with Selenium WebDriver. Selenium does not provide an inbuilt feature to work with excel. So, we will take help of some 3rd party tool like POI from Apache.
Apache POI is an open source library provided by Apache Software Foundation to read and write Excel files.
Essential classes and Interface in Apache POI API:
There are two formats of Excel files – .xls and .xlsx format.
To read .xls format POI has HSSF implementation and for .xlsx format POI has XSSF implementation.
Workbook – It’s an interface implement by HSSFWorkbook and XSSFWorkbook to work with xls and xlsx format workbook respectively.
Sheet – It’s an interface implemented by HSSFSheet and XSSFSheet to work with sheets of xls and xlsx format workbook respectively.
Row – It’s an interface implemented by HSSFRow and XSSFRow, which represent a ROW of a sheet of xls and xlsx format respectively.
Cell – It’s an interface implemented by HSSFCell and XSSFCell, which represent a CELL in a row of a sheet of xls and xlsx format respectively.
Now let us list down all the methods required to work with an Excel:
- Creating a workbook.
- Opening a Workbook.
- Creating a Sheet.
- Saving a Workbook.
- Saving a Workbook with a new name.
- Closing a Workbook.
- Getting Row number from a Sheet.
- Getting Cell number from a Row of a Sheet.
- Reading Data from a Cell.
- Writing Data into a Cell.
Now let us learn all these methods one-by-one. These are most frequently used methods while working with an excel sheet. You can directly use these methods in your project. There may be few changes required to cover up your requirement.
Creating a Workbook:
In the below method we will learn how to create a new workbook using POI Apache.
//A variable to read from a file
private InputStream oFileReader;
//To write in a file
private OutputStream oFileWriter;
//To work with workbook
private Workbook oExcelWorkbook;
public void createExcelWorkbook(String sFilename){
try {
// To remove white space from a filename
sFilename = sFilename.trim();
//Check to verify if user passed empty value
if(sFilename.isEmpty()){
throw new Exception("Invalid file name..");
}
//Check if file exists or not
if(new File(sFilename).exists()){
throw new Exception("File already exists");
}
if(sFilename.endsWith("xlsx")) {
//To initialise for .xlsx format
oExcelWorkbook = new XSSFWorkbook();
} else if(sFilename.endsWith("xls")){
//To initialise for .xls format
oExcelWorkbook = new HSSFWorkbook();
} else {
throw new Exception("Invalid File Extension...");
}
oFileWriter = new FileOutputStream(sFilename);
oExcelWorkbook.write(oFileWriter);
oFileWriter.close();
oExcelWorkbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Opening a Workbook:
In this method, we will learn how to open a workbook using POI API’s.
public void openExcelSheet(String sFileName){
try {
//Trim check
sFileName = sFileName.trim();
//Empty Check
if(sFileName.isEmpty()){
throw new Exception("File Name not specified");
}
//To verify if the file exist or not
if(! (new File(sFileName)).exists()){
throw new Exception("File doesnot exist.. ");
}
//Reading a file
oFileReader = new FileInputStream(sFileName);
sExcelFileName = sFileName;
//Reading a file and converting it in excel format
oExcelWorkbook = WorkbookFactory.create(oFileReader);
} catch (Exception e) {
e.printStackTrace();
}
}
Saving a Workbook:
In this method, we will learn how to save a workbook using POI files
public void save(){
try {
oFileWriter = new FileOutputStream(sExcelFileName);
oExcelWorkbook.write(oFileWriter);
oFileWriter.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Saving a Workbook with new name:
In this method, we will learn how to save a workbook with a new name using POI fiiles.
public void saveAs(String sFileNewName){
try {
sFileNewName = sFileNewName.trim();
if(sFileNewName.isEmpty()){
throw new Exception("File name does not exists");
}
if((new File(sFileNewName)).exists()){
throw new Exception("File already exists");
}
oFileWriter = new FileOutputStream(sFileNewName);
oExcelWorkbook.write(oFileWriter);
oFileWriter.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Closing a Workbook:
In this method, we will learn how to close a workbook.
public void close(){
try {
oExcelWorkbook.close();
oFileReader.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Create a Sheet:
In this method, we will learn how to create a sheet in a workbook.
public void createSheet(String sSheetName){
try {
sSheetName = sSheetName.trim();
if(sSheetName.isEmpty()){
throw new Exception("Sheet name not specified..");
}
Sheet oSheet;
oSheet = oExcelWorkbook.getSheet(sSheetName);
if(oSheet != null){
throw new Exception("Sheet Already exist...");
}
oExcelWorkbook.createSheet(sSheetName);
} catch (Exception e) {
e.printStackTrace();
}
}
Getting number of Row from a Sheet:
In this method, we will learn how to get number of rows which has data from a sheet.
public int getRowCountoFSheet(String sSheetName){
try {
sSheetName = sSheetName.trim();
if(sSheetName.isEmpty()){
throw new Exception("Sheet name not specified..");
}
Sheet oSheet;
oSheet = oExcelWorkbook.getSheet(sSheetName);
if(oSheet == null){
throw new Exception("Sheet doesnot exist...");
}
return oSheet.getLastRowNum();
} catch (Exception e) {
e.printStackTrace();
return -1;
}
}
Get a Cell Count from a Row in a Sheet:
In this method, we will learn how to get cell number of a row from a sheet.
public int getCellCount(String sSheetName, int iRow){
try {
sSheetName = sSheetName.trim();
if(sSheetName.isEmpty()){
throw new Exception("Sheet name not specified..");
}
Sheet oSheet;
oSheet = oExcelWorkbook.getSheet(sSheetName);
if(oSheet == null){
throw new Exception("Sheet doesnot exist...");
}
if(iRow < 1){
throw new Exception("Row Index start from 1");
}
Row oRow;
oRow = oSheet.getRow(iRow);
if(oRow == null ){
return 0;
} else {
return oRow.getLastCellNum();
}
} catch (Exception e) {
e.printStackTrace();
return -1;
}
}
Reading data from a Cell of a sheet:
In this method, we will learn how to read data from a cell of a sheet.
public String getCellData(String sSheetName, int iRow, int iCell){
try {
sSheetName = sSheetName.trim();
if(sSheetName.isEmpty()){
throw new Exception("Sheet name not specified..");
}
Sheet oSheet;
oSheet = oExcelWorkbook.getSheet(sSheetName);
if(oSheet == null){
throw new Exception("Sheet doesnot exist...");
}
if(iRow < 1 || iCell < 1){
throw new Exception("Row and cell Index start from 1");
}
Row oRow;
oRow = oSheet.getRow(iRow-1);
if(oRow == null ){
return "";
}
Cell oCell;
oCell = oRow.getCell(iCell-1);
if(oCell == null ){
return "";
} else {
if(oCell.getCellType() == Cell.CELL_TYPE_NUMERIC){
return String.valueOf((int) oCell.getNumericCellValue());
} else {
return oCell.getStringCellValue();
}
}
} catch (Exception e) {
e.printStackTrace();
return "";
}
}
Writing Data in a Cell of a Sheet:
In this method, we will learn how to write in a cell of a sheet.
public void setCellData(String sSheetName, int iRow, int iCell, String sValue){
try {
sSheetName = sSheetName.trim();
if(sSheetName.isEmpty()){
throw new Exception("Sheet name not specified..");
}
Sheet oSheet;
oSheet = oExcelWorkbook.getSheet(sSheetName);
if(oSheet == null){
throw new Exception("Sheet doesnot exist...");
}
if(iRow < 1 || iCell < 1){
throw new Exception("Row and cell Index start from 1");
}
Row oRow;
oRow = oSheet.getRow(iRow-1);
if(oRow == null){
oSheet.createRow(iRow-1);
oRow = oSheet.getRow(iRow-1);
}
Cell oCell;
oCell = oRow.getCell(iCell-1);
if(oCell == null ){
oRow.createCell(iCell-1);
oCell = oRow.getCell(iCell-1);
}
oCell.setCellValue(sValue);
}
catch (Exception e) {
e.printStackTrace();
}
}
Now let us test the above code with a scenario:
Scenario:
- Create a new workbook with name say testdata.xlsx
- Open the workbook
- Create a new sheet with name say TestData
- Write some data in the sheet.
- Save the workbook.
package test;
import commonLibs.ExcelDriver;
public class DemoExcel {
public static void main(String[] args) {
String sFileName = "C:\\Users\\sdhingra\\Desktop\\TestData.xlsx";
String sSheetName = "TestData";
ExcelDriver excel = new ExcelDriver();
excel.createExcelWorkbook(sFileName);
excel.openExcelSheet(sFileName);
excel.setCellData(sSheetName, 1, 1, "saurabh");
excel.setCellData(sSheetName, 1, 2, "Dhingra");
excel.setCellData(sSheetName, 2, 1, "Amrita");
excel.setCellData(sSheetName, 2, 2, "Joshi");
excel.save();
}
}
Here, is the complete code:
package commonLibs;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelDriver {
private InputStream oFileReader;
private OutputStream oFileWriter;
private Workbook oExcelWorkbook;
private String sExcelFileName;
public void createExcelWorkbook(String sFilename){
try {
sFilename = sFilename.trim();
if(sFilename.isEmpty()){
throw new Exception("Invalid file name..");
}
if(new File(sFilename).exists()){
throw new Exception("File already exists");
}
if(sFilename.endsWith("xlsx")) {
oExcelWorkbook = new XSSFWorkbook();
} else if(sFilename.endsWith("xls")){
oExcelWorkbook = new HSSFWorkbook();
} else {
throw new Exception("Invalid File Extension...");
}
oFileWriter = new FileOutputStream(sFilename);
oExcelWorkbook.write(oFileWriter);
oFileWriter.close();
oExcelWorkbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//----------------------------------------------------------------------------
public void openExcelSheet(String sFileName){
try {
//Trim check
sFileName = sFileName.trim();
//Empty Check
if(sFileName.isEmpty()){
throw new Exception("File Name not specified");
}
//To verify if the file exist or not
if(! (new File(sFileName)).exists()){
throw new Exception("File doesnot exist.. ");
}
//Reading a file
oFileReader = new FileInputStream(sFileName);
sExcelFileName = sFileName;
//Reading a file and converting it in excel format
oExcelWorkbook = WorkbookFactory.create(oFileReader);
} catch (Exception e) {
e.printStackTrace();
}
}
//----------------------------------------------------------------------------
public void save(){
try {
oFileWriter = new FileOutputStream(sExcelFileName);
oExcelWorkbook.write(oFileWriter);
oFileWriter.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//----------------------------------------------------------------------------
public void saveAs(String sFileNewName){
try {
sFileNewName = sFileNewName.trim();
if(sFileNewName.isEmpty()){
throw new Exception("File name does not exists");
}
if((new File(sFileNewName)).exists()){
throw new Exception("File already exists");
}
oFileWriter = new FileOutputStream(sFileNewName);
oExcelWorkbook.write(oFileWriter);
oFileWriter.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//----------------------------------------------------------------------------
public void close(){
try {
oExcelWorkbook.close();
oFileReader.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//----------------------------------------------------------------------------
public void createSheet(String sSheetName){
try {
sSheetName = sSheetName.trim();
if(sSheetName.isEmpty()){
throw new Exception("Sheet name not specified..");
}
Sheet oSheet;
oSheet = oExcelWorkbook.getSheet(sSheetName);
if(oSheet != null){
throw new Exception("Sheet Already exist...");
}
oExcelWorkbook.createSheet(sSheetName);
} catch (Exception e) {
e.printStackTrace();
}
}
//----------------------------------------------------------------------------
public int getRowCountoFSheet(String sSheetName){
try {
sSheetName = sSheetName.trim();
if(sSheetName.isEmpty()){
throw new Exception("Sheet name not specified..");
}
Sheet oSheet;
oSheet = oExcelWorkbook.getSheet(sSheetName);
if(oSheet == null){
throw new Exception("Sheet doesnot exist...");
}
return oSheet.getLastRowNum();
} catch (Exception e) {
e.printStackTrace();
return -1;
}
}
//----------------------------------------------------------------------------
public int getCellCount(String sSheetName, int iRow){
try {
sSheetName = sSheetName.trim();
if(sSheetName.isEmpty()){
throw new Exception("Sheet name not specified..");
}
Sheet oSheet;
oSheet = oExcelWorkbook.getSheet(sSheetName);
if(oSheet == null){
throw new Exception("Sheet doesnot exist...");
}
if(iRow < 1){
throw new Exception("Row Index start from 1");
}
Row oRow;
oRow = oSheet.getRow(iRow);
if(oRow == null ){
return 0;
} else {
return oRow.getLastCellNum();
}
} catch (Exception e) {
e.printStackTrace();
return -1;
}
}
//----------------------------------------------------------------------------
public String getCellData(String sSheetName, int iRow, int iCell){
try {
sSheetName = sSheetName.trim();
if(sSheetName.isEmpty()){
throw new Exception("Sheet name not specified..");
}
Sheet oSheet;
oSheet = oExcelWorkbook.getSheet(sSheetName);
if(oSheet == null){
throw new Exception("Sheet doesnot exist...");
}
if(iRow < 1 || iCell < 1){
throw new Exception("Row and cell Index start from 1");
}
Row oRow;
oRow = oSheet.getRow(iRow-1);
if(oRow == null ){
return "";
}
Cell oCell;
oCell = oRow.getCell(iCell-1);
if(oCell == null ){
return "";
} else {
if(oCell.getCellType() == Cell.CELL_TYPE_NUMERIC){
return String.valueOf((int) oCell.getNumericCellValue());
} else {
return oCell.getStringCellValue();
}
}
} catch (Exception e) {
e.printStackTrace();
return "";
}
}
//----------------------------------------------------------------------------
public void setCellData(String sSheetName, int iRow, int iCell, String sValue){
try {
sSheetName = sSheetName.trim();
if(sSheetName.isEmpty()){
throw new Exception("Sheet name not specified..");
}
Sheet oSheet;
oSheet = oExcelWorkbook.getSheet(sSheetName);
if(oSheet == null){
throw new Exception("Sheet doesnot exist...");
}
if(iRow < 1 || iCell < 1){
throw new Exception("Row and cell Index start from 1");
}
Row oRow;
oRow = oSheet.getRow(iRow-1);
if(oRow == null){
oSheet.createRow(iRow-1);
oRow = oSheet.getRow(iRow-1);
}
Cell oCell;
oCell = oRow.getCell(iCell-1);
if(oCell == null ){
oRow.createCell(iCell-1);
oCell = oRow.getCell(iCell-1);
}
oCell.setCellValue(sValue);
}
catch (Exception e) {
e.printStackTrace();
}
}
//----------------------------------------------------------------------------
}
PS: For any questions, queries and feedback, feel free to write us at saurabh@qatechhub.com or support@qatechhub.com. Happy Learning ?
