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.
If you would like to learn how to read a simple file in Java, you can check out this tutorial How to Read a File 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 the 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 to your pom.xml file:
<dependency>
<groupid>org.apache.poi</groupid>
<artifactid>poi-ooxml</artifactid>
<version>5.0.0</version>
</dependency>
If you are not familiar with Maven, you can check out this tutorial: Create Java Project with Maven.
Write/Create Excel files 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.