Read And Write Excel Data Using Apachi POI

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);
}

}




No comments:

Post a Comment

Copyright © 2017 QALEARNINGGUIDE.COM || ALL RIGHTS RESERVED