Reading and Writing data to excel file using Apache POI

Refer to the below Links for Practise

Collections in Java
Complete List of Selenium Basics
50 Real Time Interview Questions
Real Time Scenario’s
Java Platform Standard Edition 8 Documentation
Complete List of Java Basics
Java Quiz
Java OOPS Quiz
Selenium Advanced Quiz
Selenium Quiz
Selenium WebDriver Resume

What is Apache POI?
Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. It is an open source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java program. It contains classes and methods to decode the user input data or a file into MS Office documents.

• HSSF (Horrible Spreadsheet Format) : It is used to read and write xls format of MS-Excel files.
• XSSF (XML Spreadsheet Format) : It is used for xlsx file format of MS-Excel.

Step 1 Download the apache poi jar file as below
Access the below link for Downloading the Apache POI.
http://poi.apache.org/download.html

Step 2Click on the Latest Binary Distribution zip file to download.

Apache POI[Poor Obfuscation Implementation File System]

Latest Binary of Apache POI for Reading XLSX

Step 3 Add the Highlighted Jars to the project build path.

Apache POI Jars are added to the build path.

ooxml-lib jars added to the project

Program: Reading xlsx using Apache POI (“Poor Obfuscation Implementation”)

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ReadExcelHSSF {
 public static void main(String[] args) {
  try {
   FileInputStream fis = new FileInputStream("inputData.xls");

   HSSFWorkbook workbook = new HSSFWorkbook(fis);

   HSSFSheet worksheet = workbook.getSheetAt(0);
   
   System.out.println(worksheet.getRow(0).getCell(0).getStringCellValue());

   Iterator<Row> ite = worksheet.rowIterator();
   
   while(ite.hasNext())
   {
    Row row =  ite.next();

    Iterator<Cell> cellIterator = row.cellIterator();

    while (cellIterator.hasNext())
    {
     Cell cell = cellIterator.next();
     //Check the cell type and format accordingly
     switch (cell.getCellType())
     {
     case Cell.CELL_TYPE_NUMERIC:
      System.out.print(cell.getNumericCellValue());
      break;
     case Cell.CELL_TYPE_STRING:
      System.out.print(cell.getStringCellValue()+"--");
      break;
     }
    }
    System.out.println();
   }
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }
 }

}

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.util.Iterator;

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

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

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

public class ReadExcelHSSF {

public static void main(String[] args) {

try {

FileInputStream fis = new FileInputStream("inputData.xls");

HSSFWorkbook workbook = new HSSFWorkbook(fis);

HSSFSheet worksheet = workbook.getSheetAt(0);

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

Iterator<Row> ite = worksheet.rowIterator();

while(ite.hasNext())

{

Row row = ite.next();

Iterator<Cell> cellIterator = row.cellIterator();

while (cellIterator.hasNext())

{

Cell cell = cellIterator.next();

//Check the cell type and format accordingly

switch (cell.getCellType())

{

case Cell.CELL_TYPE_NUMERIC:

System.out.print(cell.getNumericCellValue());

break;

case Cell.CELL_TYPE_STRING:

System.out.print(cell.getStringCellValue()+"--");

break;

}

System.out.println();

}

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

Utility Methods-loadXLSX(),writeCellData()

//Loading the Excel file before reading the Data
public static void loadXLSX() throws IOException
{
                   
		ExcelFileToRead = new FileInputStream(file);
              //Fetch the workbook stored in the Excel file
		excelWBook = new XSSFWorkbook(ExcelFileToRead);
              

}
//Writing Data to the Cell into specified row and column
public static void writeCellData(int rowNum, int colNum,String content,String sheetName) throws Exception{

     fos=new FileOutputStream(file);
     System.out.println("Writing the Data to the Excel in rownum:" + rowNum +"::colNum:: "+colNum+"::content::" + content );
    cell = excelWBook.getSheet(sheetName).getRow(rowNum).createCell(colNum);
    cell.setCellType(cell.CELL_TYPE_STRING);
    cell.setCellValue(content);
    excelWBook.write(fos);
    fos.close();

}

//Loading the Excel file before reading the Data

public static void loadXLSX() throws IOException

{

ExcelFileToRead = new FileInputStream(file);

//Fetch the workbook stored in the Excel file

excelWBook = new XSSFWorkbook(ExcelFileToRead);

}

//Writing Data to the Cell into specified row and column

public static void writeCellData(int rowNum, int colNum,String content,String sheetName) throws Exception{

fos=new FileOutputStream(file);

System.out.println("Writing the Data to the Excel in rownum:" + rowNum +"::colNum:: "+colNum+"::content::" + content );

cell = excelWBook.getSheet(sheetName).getRow(rowNum).createCell(colNum);

cell.setCellType(cell.CELL_TYPE_STRING);

cell.setCellValue(content);

excelWBook.write(fos);

fos.close();

}

Program: Reading xls using Apache POI (“Poor Obfuscation Implementation”)

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ReadExcelPoi {

 public static void main() throws InvalidFormatException, FileNotFoundException, IOException{

  Workbook wb=WorkbookFactory.create(new FileInputStream(new File("inputData.xlsx")));

  Sheet sh1=wb.getSheetAt(0);

  int rows=sh1.getLastRowNum();

  for(int i=0;i<rows+1;i++){

   String data1=sh1.getRow(i).getCell(0).getStringCellValue();
   String data2=sh1.getRow(i).getCell(1).getStringCellValue();

   System.out.println("Data from First column and from row "+i+" is "+data1);
   System.out.println("Data from Second column and from row "+i+" is "+data2);
   System.out.println("===========================================");
  }

 }
}

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ReadExcelPoi {

public static void main() throws InvalidFormatException, FileNotFoundException, IOException{

Workbook wb=WorkbookFactory.create(new FileInputStream(new File("inputData.xlsx")));

Sheet sh1=wb.getSheetAt(0);

int rows=sh1.getLastRowNum();

for(int i=0;i<rows+1;i++){

String data1=sh1.getRow(i).getCell(0).getStringCellValue();

String data2=sh1.getRow(i).getCell(1).getStringCellValue();

System.out.println("Data from First column and from row "+i+" is "+data1);

System.out.println("Data from Second column and from row "+i+" is "+data2);

System.out.println("===========================================");

}

Total-QA

Automation Tools

Reading and Writing data to excel file using Apache POI

Leave a Reply Cancel reply