Read and write Excel files in Java

In this post, you will learn to read and write Excel files in Java.  We will use the Apache POI library, the most commonly used library for working with MS Excel files in Java.

Apache POI classes

The Apache POI contains classes for working with different types and formats of Excel files.

Some of the classes are:

  • HSSFWorkbook and HSSFSheet – for working with the Excel ’97(-2007) file format.
  • XSSFWorkbook and XSSFSheet – for working with the Excel 2007 OOXML file format.
  • SXSSFWorkbook and SXSSFSheet – These classes are used for writing very large files without running out of memory as only a configurable portion of the rows are kept in memory at any one time.
    These two classes are used for working with the MSI Excel files with extension xlsx.
  • Row – High-level representation of a row of a spreadsheet.
  • Cell – High-level representation of a cell in a row of a spreadsheet.

Using the Apache POI library

To be able to use the library, you need to add the following dependency into your pom.xml file:

<dependency>
    <groupid>org.apache.poi</groupid>
    <artifactid>poi-ooxml</artifactid>
    <version>5.0.0</version>
</dependency>

Write/Create Excel file in Java

First, let’s create a Java POJO with fields that will represent columns in our Excel file:

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Product {

  private int id;
  private String color;
  private String size;
  private int cost;
  private String description;

}


Here, we are using the Lombok annotations, since we don’t want to write boilerplate code such as constructors, getters, and setters. These annotations are doing all that for us, behind the scene.

Now, when we have the Product class, let’s create an Excel file.

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class WriteExcelFile {

  // let's create a list of Products that we want to write to Excel file
  private static List <Product> getListOfProducts() {
    List <Product> products = new ArrayList<>();
    products.add(new Product(1, "Blue", "XL", 12.5, "Mens T-shirt"));
    products.add(new Product(2, "Red", "S", 11.5, "Womens T-shirt"));
    products.add(new Product(3, "Green", "XS", 8.2, "T-shirt for kids"));

    return products;
  }

  public static void main(String[] args) {

    // Create a blank Workbook
    try (Workbook workbook = new XSSFWorkbook()) {

      // Create a blank Sheet
      Sheet sheet = workbook.createSheet("Products");

      // column names
      List <String> columns = new ArrayList<>();
      columns.add("ID");
      columns.add("Colour");
      columns.add("Size");
      columns.add("Cost");
      columns.add("Product Description");

      Row headerRow = sheet.createRow(0);

      // Create columns/first row in a file
      for (int i = 0; i < columns.size(); i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(columns.get(i));
      }

      int rowNum = 1;

      // iterate over the list of products and for each product write its values to the excel row
      for (Product product : getListOfProducts()) {
        Row row = sheet.createRow(rowNum++);

        // populate file with the values for each column
        row.createCell(0).setCellValue(product.getId());
        row.createCell(1).setCellValue(product.getColour());
        row.createCell(2).setCellValue(product.getSize());
        row.createCell(3).setCellValue(product.getCost());
        row.createCell(4).setCellValue(product.getDescription());

      }

      // format columns
      for (int i = 0; i < columns.size(); i++) {
        sheet.autoSizeColumn(i);
      }

      // create file
      FileOutputStream out = new FileOutputStream(new File("products.xlsx"));

      // write data to file
      workbook.write(out);

      // close the output stream
      out.close();

    } catch (IOException e) {
      e.printStackTrace();
    }
  }

}

And when we open the saved products.xlsx file, we can see the following:

ID	Colour	Size	Cost	Product Description
1	Blue	XL	12.5	Mens T-shirt
2	Red	S	11.5	Womens T-shirt
3	Green	XS	8.2	T-shirt for kids


Read an Excel file

Now, let’s read the file from the previous example.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;

import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class ReadExcelFile {

  public static void main(String[] args) throws FileNotFoundException {

    FileInputStream file = new FileInputStream("products.xlsx");

    //Create Workbook instance that will contain content from the file
    try (XSSFWorkbook workbook = new XSSFWorkbook(file)) {

      //Get first sheet
      XSSFSheet sheet = workbook.getSheetAt(0);

      //Iterate through rows
      for (Row row : sheet) {

        //For each row, iterate through all the columns
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext()) {
          Cell cell = cellIterator.next();

          // Check the cell type and extract accordingly
          if (cell.getCellType().equals(CellType.NUMERIC)) {
            System.out.print(cell.getNumericCellValue() + " ");
          } else if (cell.getCellType().equals(CellType.STRING)) {
            System.out.print(cell.getStringCellValue() + " ");

          }

        }
        System.out.println("");
      }
      file.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Output:

ID Colour Size Cost Product Description 
1.0 Blue   XL   12.5 Mens   T-shirt 
2.0 Red    S    11.5 Womens T-shirt 
3.0 Green  XS   8.2 T-shirt for kids 


That was all about how to read and write Excel files in Java.

Leave a Reply

Your email address will not be published.