Handling Excel

Read Excel:
package excelExportAndFileIO;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class ReadExcelFile {


public void readExcel(String filePath,String fileName,String sheetName) throws IOException{

    //Create a object of File class to open xlsx file
    File file =    new File(filePath+"\\"+fileName);

    //Create an object of FileInputStream class to read excel file
    FileInputStream inputStream = new FileInputStream(file);

    Workbook Workbook = null;

    //Find the file extension by spliting file name in substring and getting only extension name
    String fileExtensionName = fileName.substring(fileName.indexOf("."));

    //Check condition if the file is xlsx file
    if(fileExtensionName.equals(".xlsx")){
    //If it is xlsx file then create object of XSSFWorkbook class
    Workbook = new XSSFWorkbook(inputStream);
    }
    //Check condition if the file is xls file
    else if(fileExtensionName.equals(".xls")){
        //If it is xls file then create object of XSSFWorkbook class
        Workbook = new HSSFWorkbook(inputStream);
    }

    //Read sheet inside the workbook by its name
    Sheet Sheet = Workbook.getSheet(sheetName);
   
    //Find number of rows in excel file
    int rowCount = Sheet.getLastRowNum()-Sheet.getFirstRowNum();

    //Create a loop over all the rows of excel file to read it
    for (int i = 0; i< rowCount+1; i++) {
        Row row = Sheet.getRow(i);

        //Create a loop to print cell values in a row
    for (int j = 0; j         //Print excel data in console
    System.out.print(row.getCell(j).getStringCellValue()+" || ");
        }

    System.out.println();
    }

    }

    //Main function is calling readExcel function to read data from excel file
public static void main(String...strings) throws IOException{

    //Create a object of ReadExcelFile class
ReadExcelFile objExcelFile = new ReadExcelFile();

    //Prepare the path of excel file
    String filePath = "C:\\Selenium";

    //Call read file method of the class to read data
    objExcelFile.readExcel(filePath,"ExportExcel.xlsx","ExcelDemo");
    }
}

Write Excel:

public class WriteExcelFile {

 

    public void writeExcel(String filePath,String fileName,String sheetName,String[] dataToWrite) throws IOException{

 

        //Create a object of File class to open xlsx file 

        File file = new File(filePath+"\\"+fileName);

 

        //Create an object of FileInputStream class to read excel file 

        FileInputStream inputStream = new FileInputStream(file);

 

        Workbook Workbook = null;

 

        //Find the file extension by spliting file name in substing and getting only extension name 

        String fileExtensionName = fileName.substring(fileName.indexOf("."));

 

        //Check condition if the file is xlsx file 

        if(fileExtensionName.equals(".xlsx")){

 

        //If it is xlsx file then create object of XSSFWorkbook class 

        Workbook = new XSSFWorkbook(inputStream);

 

        }

 

        //Check condition if the file is xls file 

        else if(fileExtensionName.equals(".xls")){

 

            //If it is xls file then create object of XSSFWorkbook class 

            Workbook = new HSSFWorkbook(inputStream);

 

        }

 

         

 

    //Read excel sheet by sheet name  

    Sheet sheet = Workbook.getSheet(sheetName);

 

    //Get the current count of rows in excel file 

    int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();

 

    //Get the first row from the sheet

    Row row = sheet.getRow(0);

 

    //Create a new row and append it at last of sheet

    Row newRow = sheet.createRow(rowCount+1);

 

    //Create a loop over the cell of newly created Row 

    for(int j = 0; j < row.getLastCellNum(); j++){

 

        //Fill data in row

    System.out.println(row.getLastCellNum());

   

        Cell cell = newRow.createCell(j);

 

        cell.setCellValue(dataToWrite[j]);

 

    }

 

    //Close input stream

 

    inputStream.close();

 

    //Create an object of FileOutputStream class to create write data in excel file

     FileOutputStream outputStream = new FileOutputStream(file);

 

    //write data in the excel file 

    Workbook.write(outputStream);

 

    //close output stream 

    outputStream.close();     

 

    }

 

     

 

    public static void main(String...strings) throws IOException{

 

        //Create an array with the data in the same order in which you expect to be filled in excel file 

        String[] Write = {"Mr. F","Hyderabad"};

 

        //Create an object of current class 

        WriteExcelFile objExcelFile = new WriteExcelFile();

 

        //Write the file using file name , sheet name and the data to be filled 

        objExcelFile.writeExcel("C:\\Selenium","ExportExcel.xlsx","Excel",Write);

 

    }

 

}