In our previous post, we have seen basic excel read & write operation in
In this post, we will discuss how to read the excel sheet data based on the column names instead of column index
Use below maven dependencies for the apache POI in pom.xml of your maven project. [currently using java poi version 5.x, all latest versions can be found here]
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>We will be using class WorkbookFactory, which auto detects appropriate kind of Workbook (HSSFWorkbook or XSSFWorkbook) depending on the excel formats .xls or .xlsx.
Set the excel file and sheet name
package excel;
import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.usermodel.*;
public class ExcelUtil {
private FileInputStream fis;
private FileOutputStream fileOut;
private Workbook wb;
private Sheet sh;
private Cell cell;
private Row row;
private CellStyle cellstyle;
private Color mycolor;
private String excelFilePath;
private Map<String, Integer> columns = new HashMap<>();
public 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 = WorkbookFactory.create(fis);
sh = wb.getSheet(SheetName);
//sh = wb.getSheetAt(0); //0 - index of 1st sheet
if (sh == null) {
sh = wb.createSheet(SheetName);
}
this.excelFilePath = ExcelPath;
//adding all the column header names to the map 'columns'
sh.getRow(0).forEach(cell ->{
columns.put(cell.getStringCellValue(), cell.getColumnIndex());
});
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}Explanation –
setExcelFile() will be called from anywhere in your framework if you want to read or write any excel sheet data, You have to 1st call this method by sending the excel file path and sheet name before performing any read / write operation.
columns map will hold all the column headers in that sheet [1st row of the sheet, i.e sh.getRow(0)], this will help the user to read the data based on the column header instead of sending the column index. [provides flexibility to read the data]
Line 28 to 31 & 37 to 29 are optional if you are sure of using correct file path and sheet name.
Now we can add methods related read or write operations under the same ExcelUtil class.
Read the excel data
public String getCellData(int rownum, int colnum) throws Exception{
try{
cell = sh.getRow(rownum).getCell(colnum);
String CellData = null;
switch (cell.getCellType()){
case STRING:
CellData = cell.getStringCellValue();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell))
{
CellData = String.valueOf(cell.getDateCellValue());
}
else
{
CellData = String.valueOf((long)cell.getNumericCellValue());
}
break;
case BOOLEAN:
CellData = Boolean.toString(cell.getBooleanCellValue());
break;
case BLANK:
CellData = "";
break;
}
return CellData;
}catch (Exception e){
return"";
}
}
public String getCellData(String columnName, int rownum) throws Exception {
return getCellData(rownum, columns.get(columnName));
}
Explanation
As you can see we have over loaded methods getCellData()
Use 1st getCellData(), if you know the exact row & column number of the sheet to fetch the data.
cell is specific to row & column number.
Case can be of STRING, NUMERIC, BOOLEAN etc these are nothing but the cell type, what kind of data a cell has, based on the type we are converting the data to string.
Note – When the cell type is NUMERIC, getNumericCellValue returns the data as double i.e returns 123456.0 if the cell has data 123456, that is why we are converting the cell type to long and then using String.valueOf method to remove the .0 from the fetched value.
Use 2nd getCellData(), if you want to fetch the data based on column name and row number.
Note here, here we are using the column map to fetch the column index based on the String columnName parameter & calling / using the 1st getCellData() only to fetch the excel sheet data.
Let’s see the usage of this method
We have an excel sheet testData.xlsx under project directory.
I am using a main method to read the Sheet1 content, but you can call from any where in your framework.
public static void main(String []args) throws Exception {
ExcelUtil excel = new ExcelUtil();
excel.setExcelFile("./testData.xlsx", "Sheet1");
System.out.println(excel.getCellData("UserName", 1));
System.out.println(excel.getCellData("Password", 1));
System.out.println(excel.getCellData("Valid", 1));
}OutPut
Admin
Admin123!
Yes
Get no. of rows data present
This below code will fetch no. of rows data present in each sheet.
public int getRows(){
return sh.getPhysicalNumberOfRows();
}This above code will return the no. of rows including 1st row.
Write data to excel sheet
public 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 = row.createCell(colnum);
}
cell.setCellValue(text);
fileOut = new FileOutputStream(excelFilePath);
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}catch(Exception e){
throw (e);
}
}This above method need 3 parameters, the cell i.e row & column number and the text you want to write to excel sheet by using cell.setCellValue().
Hope this help!
Hi,
I want code for read & write excel data.
But in that i am using some usernames & passwords , write the status also for
webapplication.
Please provide the code.
Hi Siva,
You can refer http://qavalidation.com/2015/03/selenium-excel-read-and-write-apachepoi.html/
for read/write and highlight the cell…
Thank you for taking the time to provide us with your valuable information.
Hello, while executing the ExcelReader class, I’m getting the below error.
Can you please share how to resolve it
Exception in thread “main” java.lang.NullPointerException: Cannot invoke “org.apache.poi.ss.usermodel.Sheet.getPhysicalNumberOfRows()” because “this.sh” is null
at utils.ExcelManager.getNoOfRow(ExcelManager.java:109)
at utils.ExcelManager.to2DArray(ExcelManager.java:120)
at utils.ExcelReader.main(ExcelReader.java:31)
.