How to Read Excel File in Java Using POI

In this TechVidvan Java Tutorial, we are going to learn how we can read excel file in java. In Java, reading excel files is not similar to reading word files because of cells in excel files. JDK does not provide a direct API to read or write Microsoft Excel or Word documents. We have to rely on the third-party library that is Apache POI. In this article, we will learn how to create and write to an excel file using Apache POI.

Read & write excel file in java

Keeping you updated with latest technology trends, Join TechVidvan on Telegram

Reading and writing Excel Files in Java

The people all over the world use excel files (spreadsheets) for various tasks related to organization, analysis, and storage of tabular data. Since these excel files are so common, the developers often face some use-cases when we need to read data from an excel file or generate a report in excel format. Apache POI (Poor Obfuscation Implementation) is a Java API for reading and writing Microsoft Documents in both formats .xls and .xlsx. It contains classes and interfaces.

Apache POI Library

The Apache POI library provides two implementations for reading excel files:

1. HSSF (Horrible SpreadSheet Format) Implementation

It denotes an API that is working with Excel 2003 or earlier versions. There are interfaces of HSSF implementations which are HSSFWorkbook, HSSFSheet, HSSFRow, and HSSFCell. We use these interfaces work with excel files of the older binary file format – .xls

2. XSSF (XML SpreadSheet Format) Implementation

It denotes an API that is working with Excel 2007 or later versions. We can use the XSSF implementation to work with the newer XML based file format – .xlsx.

Apache POI Library in java

Interfaces and Classes in Apache POI

Below are various interfaces in POI:

1. Workbook: It represents an Excel Workbook. It is an interface implemented by HSSFWorkbook and XSSFWorkbook.

2. Sheet: It is an interface that represents an Excel worksheet. A sheet is a central structure of a workbook, which represents a grid of cells. The Sheet interface extends java.lang.Iterable.

3. Row: It is also an interface that represents the row of the spreadsheet. The Row interface extends java.lang.Iterable. There are two concrete classes: HSSFRow and XSSFRow.

4. Cell: It is an interface. It is a high-level representation of a cell in a row of the spreadsheet. HSSFCell and XSSFCell implement the Cell interface

Steps to Read Data from XLS file

1. Create a simple Java project in eclipse.

2. Now, create a lib folder in the project.

3. Download and add the following jar files in the lib folder:

  • commons-collections4-4.1.jar Click Here
  • poi-3.17.jar Click Here
  • poi-ooxml-3.17.jar Click Here
  • poi-ooxml-schemas-3.17.jar Click Here
  • xmlbeans-2.6.0.jar Click Here

4. Set the Class-Path:

Right-click on the project ->Build Path ->Add External JARs -> select all the above jar files -> Apply and close.

5. Now create a class file with the name ReadExcelFileDemo and write the following code in the file.

6. Create an excel file with the name “employee.xls” and write some data into it.

Create Excel

Code to read excel file

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.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
public class ReadExcelFileDemo {
  public static void main(String args[]) throws IOException {
    //obtaining input bytes from a file  
    FileInputStream fis = new FileInputStream(new File("C:\\mysheets\\employee.xls"));
    //creating workbook instance that refers to .xls file  
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    //creating a Sheet object to retrieve the object  
    HSSFSheet sheet = wb.getSheetAt(0);
    //evaluating cell type   
    FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
    System.out.println(“The given file is”);
    for (Row row: sheet)
    //iteration over row using for each loop  
    {
      for (Cell cell: row) //iteration over cell using for each loop  
      {
        switch (formulaEvaluator.evaluateInCell(cell).getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
          //field that represents numeric cell type  
          //getting the value of the cell as a number  
          System.out.print(cell.getNumericCellValue() + "\t\t");
          break;
        case Cell.CELL_TYPE_STRING:
          //field that represents string cell type  
          //getting the value of the cell as a string  
          System.out.print(cell.getStringCellValue() + "\t\t");
          break;
        }
      }
      System.out.println();
    }
  }
}

Output:

Open Excel File

Code to read .xlsx file

The xlsx file is same as xls file and looks like:

reading Excel file

import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class XLSXReaderExample {
  public static void main(String[] args) {
    try {
      File file = new File("C:\\mysheets\\student.xlsx");
      //creating a new file instance  
      FileInputStream fis = new FileInputStream(file);
      //obtaining bytes from the file  
      //creating Workbook instance that refers to .xlsx file  
      XSSFWorkbook wb = new XSSFWorkbook(fis);
      XSSFSheet sheet = wb.getSheetAt(0);
      //creating a Sheet object to retrieve object  
      Iterator < Row > itr = sheet.iterator();
      //iterating over excel file
      System.out.println(“The given file is”);
      while (itr.hasNext()) {
        Row row = itr.next();
        Iterator < Cell > cellIterator = row.cellIterator();
        //iterating over each column  
        while (cellIterator.hasNext()) {
          Cell cell = cellIterator.next();
          switch (cell.getCellType()) {
          case Cell.CELL_TYPE_STRING:
            //field that represents string cell type  
            System.out.print(cell.getStringCellValue() + "\t\t\t");
            break;
          case Cell.CELL_TYPE_NUMERIC:
            //field that represents number cell type  
            System.out.print(cell.getNumericCellValue() + "\t\t\t");
            break;
          default:
          }
        }
        System.out.println("");
      }
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output:

Reading a particular cell value from an excel file (.xlsx)

Table: EmployeeData.xlsx

Read cell from excel file

Example

In the following example, we read the value of the 2nd row and the 2nd column. The row and column counting start from 0. So the program returns “John”.

Code to read a particular cell from an excel file

//reading value of a particular cell  
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel. * ;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadCellExample {
  public static void main(String[] args) {
    ReadCellExample rc = new ReadCellExample(); //object of the class  
    //reading the value of 2nd row and 2nd column  
    String vOutput = rc.ReadCellData(2, 2);
    System.out.println(vOutput);
  }
  //method defined for reading a cell  
  public String ReadCellData(int vRow, int vColumn) {
    String value = null; //variable for storing the cell value  
    Workbook wbook = null; //initialize Workbook null  
    try {
      //reading data from a file in the form of bytes  
      FileInputStream fis = FileInputStream("C:\\mysheets\\EmployeeData.xlsx");
      //creates an XSSFWorkbook object by buffering the whole stream into the memory  
      wbook = new XSSFWorkbook(fis);
    }
    catch(FileNotFoundException e) {
      e.printStackTrace();
    }
    catch(IOException e1) {
      e1.printStackTrace();
    }
    Sheet sheet = wbook.getSheetAt(0);
    //getting the XSSFSheet object at given index  
    Row row = sheet.getRow(vRow);
    //returns the logical row  
    Cell cell = row.getCell(vColumn);
    //getting the cell representing the given column  
    value = cell.getStringCellValue();
    //getting cell value  
    return value;
    //returns the cell value  
  }
}

Output:
John

How to Write Excel File in Java

Writing excel using POI is very simple and involves the following steps:

1. Create a workbook
2. Create a sheet in a workbook
3. Create a row in a sheet
4. Add cells in a sheet
5. Now, repeat the steps 3 and 4 to add more data

Code to write excel file in Java using POI

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel. * ;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcelDemo {
  public static void main(String[] args) {
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Product data");

    //This data needs to be written (Object[])
    Map < String,
    Object[] > data = new TreeMap < String,
    Object[] > ();
    data.put("1", new Object[] {
      "ID",
      "NAME",
      "PRICE"
    });
    data.put("2", new Object[] {
      1,
      "Mouse",
      1000
    });
    data.put("3", new Object[] {
      2,
      "Keyboard",
      1200
    });

    //Iterate over data and write to sheet
    Set < String > keyset = data.keySet();
    int rownum = 0;
    for (String key: keyset) {
      Row row = sheet.createRow(rownum++);
      Object[] objArr = data.get(key);
      int cellnum = 0;
      for (Object obj: objArr) {
        Cell cell = row.createCell(cellnum++);
        if (obj instanceof String) cell.setCellValue((String) obj);
        else if (obj instanceof Integer) cell.setCellValue((Integer) obj);
      }
    }
    try {
      //Write the workbook in file system
      FileOutputStream out = new FileOutputStream(new File("Product.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output:

Conclusion

In this article, we learned how to read and write an excel file in Java using the program. There is a library called Apache POI which has many classes and methods that help us to deal with excel files in Java. We studied how to read a xls file and a xlsx file and how to write an excel file. We can also access a particular cell of an excel file.

Do share feedback in the comment section if you liked the article.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.