Excel Sheet in Selenium

HOW TO READ DATA FROM EXCEL FILE USING APACHE POI

How to Read Data from Excel File Using Apache POI

Java provides us different classes for File Manipulation with Selenium. In this tutorial, we are going to learn how can we read and write on Excel file with the help of Java IO package and Apache POI library.

To read or write an Excel,Apache provides a very famous library POI. This library is capable enough to read and write both XLS and XLSX file format of Excel.

To read XLS files, an HSSF implementation is provided by POI library.

To read XLSX, XSSF implementation of POI library will be the choice.

Before writing program you need to download the POI Jar files and import into your Project.

You can simply download the latest version POI jars from http://poi.apache.org/download.html.

READING AN EXCEL FILE

Reading an excel file is also very simple if we divide this in steps.

  1. Create workbook instance from excel sheet
  2. Get to the desired sheet
  3. Increment row number
  4. iterate over all cells in a row
  5. repeat step 3 and 4 until all data is read

The below code explains how to read an Excel file using Apache POI libraries.

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {

	public static void main(String[] args) throws IOException {
		FileInputStream file = new FileInputStream("D:\\WorkSpace\\Project2\\ExcelFiles\\Test.xlsx");

		//Create Workbook instance holding reference to .xlsx file
		XSSFWorkbook workbook = new XSSFWorkbook(file);

		//Get first/desired sheet from the workbook
		XSSFSheet sheet = workbook.getSheetAt(0);
		//Iterate through each rows one by one
		Iterator < Row > rowIterator = sheet.iterator();
		rowIterator.next(); //Skip the 1st Row while reading
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			//For each row, iterate through all the columns
			Iterator < Cell > cellIterator = row.cellIterator();

			while (cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
				//Check the cell type and format accordingly
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_NUMERIC:
					System.out.print(cell.getNumericCellValue() + "\t");
					break;
				case Cell.CELL_TYPE_STRING:
					System.out.print("\t" + cell.getStringCellValue() + "\t");
					break;
				}
			}
			System.out.println("");
		}
		file.close();

	}
}


How to Write Data into Excel File Using Apache POI

How to Write Data into Excel File Using Apache POI

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

  1. Create a workbook
  2. Create a sheet in workbook
  3. Create a row in sheet
  4. Add cells in sheet
  5. Repeat step 3 and 4 to write more data
package pack2;
import java.io.FileOutputStream;
import java.io.IOException;
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;
public class ExcelWrite {
	public static void main(String[] args) throws IOException {
		XSSFWorkbook wb1 = new XSSFWorkbook();
		XSSFSheet sheet11 = wb1.createSheet("My Sheet");

		XSSFRow row1 = sheet11.createRow(1);
		XSSFCell cell0 = row1.createCell(0);
		cell0.setCellValue(12);
		XSSFCell cell1 = row1.createCell(1);
		cell1.setCellValue(23);
		XSSFCell cell2 = row1.createCell(2);
		cell2.setCellValue(34);
		XSSFCell cell3 = row1.createCell(3);
		cell3.setCellValue(43);
		XSSFCell cell4 = row1.createCell(4);
		cell4.setCellValue(11);
		XSSFCell cell5 = row1.createCell(5);
		cell5.setCellValue(121);

		FileOutputStream fileOut = new FileOutputStream("D:\\MyExcel.xlsx");
		wb1.write(fileOut);

		fileOut.flush();
		fileOut.close();
		wb1.close();
	}
}

WRITING DATA IN EXISTING EXCEL FILE (DON’T CREATE NEW EXCEL FILE)

The below code shows how to write data in Excel file using Apache POI libraries.

package pack7;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
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;

public class WritingExcel {

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

		FileInputStream f1 = new FileInputStream("D:\\WorkSpace\\Project2\\ExcelFiles\\MyExcel.xls");
		HSSFWorkbook wb1 = new HSSFWorkbook(f1);
		HSSFSheet sheet11 = wb1.getSheet("Casualty");

		HSSFRow row1 = sheet11.createRow(1);
		HSSFCell cell0 = row1.createCell(0);
		cell0.setCellValue(12);
		HSSFCell cell1 = row1.createCell(1);
		cell1.setCellValue(23);
		HSSFCell cell2 = row1.createCell(2);
		cell2.setCellValue(34);
		HSSFCell cell3 = row1.createCell(3);
		cell3.setCellValue(43);
		HSSFCell cell4 = row1.createCell(4);
		cell4.setCellValue(11);
		HSSFCell cell5 = row1.createCell(5);
		cell5.setCellValue(121);

		FileOutputStream fileOut = new FileOutputStream("D:\\WorkSpace\\Project2\\ExcelFiles\\ResponseTime.xls");
		wb1.write(fileOut);

		fileOut.flush();
		fileOut.close();
		wb1.close();

	}

}

Writing data into excel sheet using formula

package pack7;

import java.io.FileOutputStream;
import java.io.IOException;
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;

public class WritingExcelFormula{

  public static void main(String[] args) throws IOException {
    
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet sheet11 = wb1.createSheet("My Sheet");
    HSSFRow row0 = sheet11.createRow(0);
    HSSFCell cell00 = row0.createCell(0);
    cell00.setCellValue("Value 1");
    HSSFCell cell01 = row0.createCell(1);
    cell01.setCellValue("Value 2");
    HSSFCell cell02 = row0.createCell(2);
    cell02.setCellValue("Value 3");
    HSSFCell cell03 = row0.createCell(3);
    cell03.setCellValue("Result(Value1*Value2*Value3)");
    HSSFRow row1 = sheet11.createRow(1);
    HSSFCell cell0 = row1.createCell(0);
    cell0.setCellValue(12);
    HSSFCell cell1 = row1.createCell(1);
    cell1.setCellValue(23);
    HSSFCell cell2 = row1.createCell(2);
    cell2.setCellValue(34);
    HSSFCell cell3 = row1.createCell(3);
    cell3.setCellFormula("A2*B2*C2");
    
    
    FileOutputStream fileOut = new FileOutputStream("D:\\WorkSpace\\Project2\\ExcelFiles\\MyExcel.xls");
    wb1.write(fileOut);
  
    fileOut.flush();
    fileOut.close();
    wb1.close();
  
  }
}

READING EXCEL FILE WHICH HAVE FORMULA CELLS

package pack7;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelFormula {

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

		FileInputStream file = new FileInputStream("D:\\WorkSpace\\Project2\\ExcelFiles\\MyExcel.xlsx");

		//Create Workbook instance holding reference to .xlsx file
		XSSFWorkbook workbook = new XSSFWorkbook(file);

		//Get first/desired sheet from the workbook
		XSSFSheet sheet = workbook.getSheetAt(0);
		//Iterate through each rows one by one
		Iterator < Row > rowIterator = sheet.iterator();
		rowIterator.next(); //Skip the 1st Row while reading
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			//For each row, iterate through all the columns
			Iterator < Cell > cellIterator = row.cellIterator();

			while (cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
				//Check the cell type and format accordingly
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_NUMERIC:
					System.out.print(cell.getNumericCellValue() + "\t");
					break;
				case Cell.CELL_TYPE_STRING:
					System.out.print("\t" + cell.getStringCellValue() + "\t");
					break;
				case Cell.CELL_TYPE_FORMULA:
					System.out.print(cell.getNumericCellValue() + "\t");
					break;
				}
			}
			System.out.println("");
		}
		file.close();

	}
}

HOW TO CREATE HYPERLINKS ON CELLS USING APACHE POI

package pack2;

import java.io.File;
import java.io.FileOutputStream;
import org.apache.poi.common.usermodel.Hyperlink;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelHyperlinkEX {
	public static void main(String[] args) throws Exception {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet spreadsheet = workbook.createSheet("Hyperlinks");
		XSSFCell cell;
		CreationHelper createHelper = workbook.getCreationHelper();
		XSSFCellStyle hlinkstyle = workbook.createCellStyle();
		XSSFFont hlinkfont = workbook.createFont();
		hlinkfont.setUnderline(XSSFFont.U_SINGLE);
		hlinkfont.setColor(HSSFColor.BLUE.index);
		hlinkstyle.setFont(hlinkfont);
		//URL Link
		cell = spreadsheet.createRow(1).createCell((short) 1);
		cell.setCellValue("URL Link");
		XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
		link.setAddress("http://www.testingbar.com/");
		cell.setHyperlink((XSSFHyperlink) link);
		cell.setCellStyle(hlinkstyle);
		//Hyperlink to a file in the current directory
		cell = spreadsheet.createRow(2).createCell((short) 1);
		cell.setCellValue("File Link");
		link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_FILE);
		link.setAddress("cellstyle.xlsx");
		cell.setHyperlink(link);
		cell.setCellStyle(hlinkstyle);
		//e-mail link
		cell = spreadsheet.createRow(3).createCell((short) 1);
		cell.setCellValue("Email Link");
		link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
		link.setAddress("mailto:[email protected]?" + "subject=Hyperlink");
		cell.setHyperlink(link);
		cell.setCellStyle(hlinkstyle);
		FileOutputStream out = new FileOutputStream(
		new File("hyperlink.xlsx"));
		workbook.write(out);
		out.close();
		System.out.println("hyperlink.xlsx written successfully");
	}
}

Leave a Reply

Your email address will not be published. Required fields are marked *