We will see the use of apache POI open source library in java to read and write excel file.
If you are using Apache POI > 4.x version, then refer Read write excel using Java 4.x
Download
We have 2 ways to download the Apache POI library
- If using normal java project, download POI jar files from here.
Add these below jar files along with selenium into your build of project [in eclipse / intelliJ IDE]
- poi-x.xx-*.jar
- poi-ooxml-x.xx*.jar
- xmlbeans-x.x.x.jar
- poi-ooxml-schemas-x.xx-*.jar
- If using maven project, then add below dependency in pom.xml
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>Unlike jexcel api, apache POI handles both the types of excel file, .xls and .xlsx file as well.
For handling .xls files, we use HSSF (Horrible SpreadSheet Format) related POI classes.
For handling .xlsx files, we use XSSF (XML SpreadSheet Format) related POI classes.
If you are using XSSF classes, then you can deal with both the excel format files, so no worries!
We will be only discussing the operations that are needed for our selenium framework and the operations are as listed below.
- Create an excel file if it’s not existed.
- Enter (update) data in a cell.
- Read data from particular cell.
- Highlight particular cell.
Create an excel file and sheet if not existed
Here in this below code, I am importing all the required libraries for the rest of the code snippets, so it will be easy for reference.
package pkg.poi;
import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class excelutil {
private static FileInputStream fis;
private static FileOutputStream fileOut;
private static XSSFWorkbook wb;
private static XSSFSheet sh;
private static XSSFCell cell;
private static XSSFRow row;
private static XSSFCellStyle cellstyle;
private static XSSFColor mycolor;
public static void setExcelFile(String ExcelPath,String SheetName) throws Exception
{
try{
File f = new File(ExcelPath);
if(!f.exists())
{
f.createNewFile();
System.out.println("File doesn't exist, so created!");
}
fis=new FileInputStream(ExcelPath);
wb=new XSSFWorkbook(fis);
sh = wb.getSheet(SheetName);
//sh = wb.getSheetAt(0); //0 - index of 1st sheet
if (sh == null)
{
sh = wb.createSheet(SheetName);
}
}catch (Exception e){System.out.println(e.getMessage());}
}Note :
There is another way to set the workbook, by using WorkbookFactory class (From ss.usermodel package, so you need not to write logic to handle both .xls and .xlsx file, refer post workbookfactory usage)
setExcelFile method takes 2 argument, path of excel file and sheet name, that we will be working on, we are checking if the excel file/sheet exist, if not, create it.
Enter(update) data in a cell
setCellData method takes 3 arguments, string text we want to enter into cell with rownum and colnum, this method enters data into an empty cell and also updates if data already existed.
public static void setCellData(String text, int rownum, int colnum) throws Exception
{
try{
row = sh.getRow(rownum);
if(row ==null)
{
row = sh.createRow(rownum);
}
cell = row.getCell(colnum);
if (cell != null)
{
cell.setCellValue(text);
}
else
{
cell = row.createCell(colnum);
cell.setCellValue(text);
}
fileOut = new FileOutputStream(ExcelPath);
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}catch(Exception e){throw (e);} }Read data from particular cell
getCellData method takes the rownum and colnum to fetch the data from excel sheet,excel path and sheet name are already taken care by the setExcelFile method.There are different ways to fetch the data from cell, like we need to fetch the cell_type1st and then fetch the data accordingly.
public static String getCellData(int rownum, int colnum) throws Exception
{
try{
cell = sh.getRow(rownum).getCell(colnum);
String CellData = null;
switch (cell.getCellType()){
case Cell.CELL_TYPE_STRING:
CellData = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell))
{
CellData = cell.getDateCellValue().toString();
}
else
{
CellData = Double.toString(cell.getNumericCellValue());
if (CellData.contains(".0"))//removing the extra .0
{
CellData = CellData.substring(0, CellData.length()-2);
}
}
break;
case Cell.CELL_TYPE_BLANK:
CellData = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
CellData = Boolean.toString(cell.getBooleanCellValue());
break;
}
return CellData;
}catch (Exception e){return"";}
}
Highlight particular cell
highlightCell method takes 3 argument, which color to highlight the cell(row and col).
public static void highlightCell(String color, int rownum, int colnum)throws Exception
{
try{
cell = sh.getRow(rownum).getCell(colnum,Row.RETURN_BLANK_AS_NULL);
}catch(Exception e){System.out.println("cell is null");}
if (cell == null)
{
cell = row.createCell(colnum);
}
cellstyle = wb.createCellStyle();
color = color.toUpperCase();
switch(color)
{
case "GREEN":
mycolor = new XSSFColor(Color.GREEN);
break;
case "RED":
mycolor = new XSSFColor(Color.RED);
break;
default:
mycolor = new XSSFColor(Color.BLACK);
break;
}
cellstyle.setFillForegroundColor(mycolor);
cellstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell.setCellStyle(cellstyle);
FileOutputStream fileOut = new FileOutputStream(ExcelPath);
wb.write(fileOut);
fileOut.flush();
fileOut.close();
System.out.print("color done");
}You can call the above 4 methods based on your requirement from the main() method of the same class or can import the class into another class (import pkg.poi.excelutil) and call these methods
Let me know of any other requirements that needs to be added in this post, so everyone can leverage the code,Thanks!
Good information about read excel. Can you please provide information about how to read data from a JSON file. Because JSON is commonly used for data exchange.
Really good article on POI. But I have a situation where I need to read data based on the flag as yes under runmode and then just write results to the blank column nex to that rows only.
E.g
Sheet has 10 rows, only 5 are marked as YEs under runmode so i am putting if statement as the first statement in my test case to read that data only but my write function takes rownum and colnum as parameters but I don’t know how to pass two number dynamically based on row numbers that are executing so that I can write data only to few not all. Any rows can have yes irrespective of the order data is IN.
Any help will be really appreciated