12. Read/Write excel files in Selenium using Apache POI

Step 1- Download apache poi jar file as below

http://www.apache.org/dyn/closer.lua/poi/dev/bin/poi-bin-3.14-beta1-20151223.zip

All jar files will come in zip files, Extract it and you will get final jar folder

Step 2- How to add Jar files

Select project then Right click on project > Build path > Configure build path > select jar Files

Note: Do not forget to add the jar file inside of the “ooxml-lib” folder.

Precondition- Create a xlsx file and enter some data to read and save file at perticuler location.

package Others;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

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

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

public class excel_poi {

            public static void main(String []args){

                        try {

                                    // Specify the path of file

                                    File src=new File("D:\\sample.xlsx");

                                    // load file

                                    FileInputStream fis=new FileInputStream(src);

                                    // Load workbook

                                   

                                    XSSFWorkbook wb=new XSSFWorkbook(fis);

                                    // Load sheet- Here we are loading first sheetonly

                                    XSSFSheet sh1= wb.getSheetAt(0);

                                    // getRow() specify which row we want to read.

                                    // and getCell() specify which column to read.

                                    // getStringCellValue() specify that we are reading String data.

                                    System.out.println(sh1.getRow(0).getCell(0).getStringCellValue());

                                    System.out.println(sh1.getRow(0).getCell(1).getStringCellValue());

                                    System.out.println(sh1.getRow(1).getCell(0).getStringCellValue());

                                    System.out.println(sh1.getRow(1).getCell(1).getStringCellValue());

                                    System.out.println(sh1.getRow(2).getCell(0).getStringCellValue());

                                    System.out.println(sh1.getRow(2).getCell(1).getStringCellValue());

                                   

                                    // here createCell will create column

                                     

                                    // and setCellvalue will set the value

                                   

                                     sh1.getRow(0).createCell(2).setCellValue("2.41.0");

                                     

                                     sh1.getRow(1).createCell(2).setCellValue("2.5");

                                     

                                     sh1.getRow(2).createCell(2).setCellValue("2.39");

                                     

                                    // here we need to specify where you want to save file

                                     

                                     FileOutputStream fout=new FileOutputStream(new File("D:\\sample.xlsx"));

                                     

                                    // finally write content

                                     wb.write(fout);

                                     

                                    // close the file

                                     fout.close();

                                     

                        } catch (Exception e) {

                                    System.out.println(e.getMessage());

                        }

            }

}