How to Read and Write data to excel sheet with Apache POI and NPOI

Reading and writing data to excel sheet is one of the most used feature of your Selenium framework
Here in have shown the example with Java

But remember the Steps, then you can implement the same in different languages

If you are using C#, then you can use NPOI, you can follow similar steps, it is just the .NET version of POI, here's the link https://npoi.codeplex.com/ 

Step 1) Download Apache poi jars from http://poi.apache.org/
Step 2) Add unzip the jar files and add them to your project

How to read data from excel sheet with POI API 

Algorithm
1)Open file with FileinputStream
2)Open workbook, go to sheet using sheet name
3)In the sheet go to row num and go to the cell
4)Once you reach the cell get the celltype

5)Based on what type of content is held in cell
6)Use the standard methods from POI API to read them and save the value

7)Finally return the value
-----------------------------------------------------------------------------------------

public static String getCellValue(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;
    }
 

How to write data to Excel sheet with POI API 

1)Open File
2)Go to work book, go sheet, row and cell
3)Instead of get'Type'CellValue use setCellValue method and write the content to file

----------------------------------------------------------------------------------------------------

public static void writeData(String filePath, String sheetName, int rowNum, int cellNum, String value) throws InvalidFormatException, IOException{
        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 writing in new row.
        FileOutputStream fos = new FileOutputStream(filePath);
        wb.write(fos);
    }


If you need more information on Apache POI or ou want to try some new features, go developers page of POI
It contains code for all your needs.. Here's the link http://poi.apache.org/spreadsheet/quick-guide.html