Read data from excel file using Apachi POI-
public static String readData(String Filepath, String sheetName, int rowNum, int cellNum) throws InvalidFormatException, IOException{
FileInputStream fis = new FileInputStream(Filepath);
Workbook wb = WorkbookFactory.create(fis);
int type = wb.getSheet(sheetName).getRow(rowNum).getCell(cellNum).getCellType();
String value = "";
if(type==Cell.CELL_TYPE_STRING){
value = wb.getSheet(sheetName).getRow(rowNum).getCell(cellNum).getStringCellValue();
}else if(type==Cell.CELL_TYPE_NUMERIC){
int numValue = (int) wb.getSheet(sheetName).getRow(rowNum).getCell(cellNum).getNumericCellValue();
value = ""+numValue;
}else if(type==Cell.CELL_TYPE_BOOLEAN){
boolean boolValue = wb.getSheet(sheetName).getRow(rowNum).getCell(cellNum).getBooleanCellValue();
value = ""+boolValue;
}
return value;
}
write the data into file
public static void writeDataToFile(String Filepath, String sheetName, int rowNum, int cellNum, String value) {
FileInputStream fis = new FileInputStream(Filepath);
Workbook wb = WorkbookFactory.create(fis);
wb.getSheet(sheetName).getRow(rowNum).createCell(cellNum).setCellValue(value);
//wb.getSheet(sheetName).createRow(rowNum).createCell(cellNum).setCellValue(value); //use this if you are going to write in new row.
FileOutputStream fos = new FileOutputStream(Filepath);
wb.write(fos);
}
If you are writing generic function for your framework, Please follow below program
package com.automation.genericLib;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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;
public class ExcelLib {
String excelPath = "C:\\Users\\Sunil\\Desktop\\selenium\\TestData\\Test_Data.xlsx";
public String getExcelData(String sheetName , int rowNum , int colNum) throws InvalidFormatException, IOException{
FileInputStream fis = new FileInputStream(excelPath);
Workbook wb = WorkbookFactory.create(fis);
Sheet sh = wb.getSheet(sheetName);
Row row = sh.getRow(rowNum);
String data = row.getCell(colNum).getStringCellValue();
return data;
}
public int getRowCount(String sheetName) throws InvalidFormatException, IOException{
FileInputStream fis = new FileInputStream(excelPath);
Workbook wb = WorkbookFactory.create(fis);
Sheet sh = wb.getSheet(sheetName);
int rowCount = sh.getLastRowNum();
return rowCount;
}
public void setExcelData(String sheetName , int rowNum , int colNum , String data) throws InvalidFormatException, IOException{
FileInputStream fis = new FileInputStream(excelPath);
Workbook wb = WorkbookFactory.create(fis);
Sheet sh = wb.getSheet(sheetName);
Row row = sh.getRow(rowNum);
Cell cel = row.getCell(colNum);
cel.setCellType(cel.CELL_TYPE_STRING);
cel.setCellValue(data);
FileOutputStream fos= new FileOutputStream(excelPath);
wb.write(fos);
}
}