Create Excel file with Apache POI in Java

To create an Excel file with apache in Java, we’ll use the Apache POI libraries, so we’ll add the Maven dependency to our project.

–Read a text file in Java –Create an XML file in Java –Create ZIP files in Java –Create a PDF with Apache FOP in Java

The dependency that you must add in your POM file is the following:

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.16</version>
</dependency>

The code is the following:

package com.geekole; // Name of your java package

import java.io.File;
import java.io.FileOutputStream;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;

/**
 *
 * @author geekole.com
 */
public class CreateExcel {

    public static void main(String args[]) {
        //Create blank workbook
        Workbook workbook = new HSSFWorkbook();

        //Create new sheet
        Sheet sheet = workbook.createSheet("Data sheet");

        //For each line, an array of objects is created (Object[])
        Map<String, Object[]> data = new TreeMap<String, Object[]>();
        data.put("1", new Object[]{"Identifier", "Name", "Surnames"});
        data.put("2", new Object[]{1, "Emma", "Whatson"});
        data.put("3", new Object[]{2, "Paul", "Hopkins"});
        data.put("4", new Object[]{3, "Tom", "Pattinson"});
        data.put("5", new Object[]{4, "Antony", "Smith"});

        //Iterate over data to write to the sheet
        Set<String> keyset = data.keySet();
        int rowNumber = 0;
        for (String key : keyset) {
            Row row = sheet.createRow(rowNumber++);
            Object[] objectsArrangement = data.get(key);
            int cellNumber = 0;
            for (Object obj : objectsArrangement) {
                Cell cell = row.createCell(cellNumber++);
                if (obj instanceof String) {
                    cell.setCellValue((String) obj);
                } else if (obj instanceof Integer) {
                    cell.setCellValue((Integer) obj);
                }
            }
        }
        try {
            //The document is generated
            FileOutputStream out = new FileOutputStream(new File("/path/example.xls"));
            workbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

As you can see first we create the container of all the information of our document.

//Create blank workbook
Workbook workbook = new HSSFWorkbook();

If you remember, an excel document can have multiple worksheets. In the next line we create at least one from the previously created workbook object and name it «Datasheet».

//Create new sheet
Sheet sheet = workbook.createSheet("Data sheet");

The next thing is to create the information that will be contained in the data sheet.

//For each line, an array of objects is created (Object[])
Map<String, Object[]> data = new TreeMap<String, Object[]>();
data.put("1", new Object[]{"Identifier", "Name", "Surnames"});
data.put("2", new Object[]{1, "Megan", "Whatson"});
data.put("3", new Object[]{2, "Paul", "Hopkins"});
data.put("4", new Object[]{3, "Tom", "Pattinson"});
data.put("5", new Object[]{4, "Antony", "Smith"});

All the information will be contained in an object called data, which is a map of object arrays that we will iterate over in the next block of code.

//Iterate over data to write to the sheet
Set<String> keyset = data.keySet();
int rowNumber = 0;
for (String key : keyset) {
    Row row = sheet.createRow(rowNumber++);
    Object[] objectsArrangement = data.get(key);
    int cellNumber = 0;
    for (Object obj : objectsArrangement) {
        Cell cell = row.createCell(cellNumber++);
        if (obj instanceof String) {
            cell.setCellValue((String) obj);
        } else if (obj instanceof Integer) {
            cell.setCellValue((Integer) obj);
        }
    }
}

From the previous block you will notice that we iterate over a set of keys created from our initial array, in this iteration we also create the rows of our data sheet with the createRow method and in a nested way we create each of the cells that correspond to each column via the createCell method.

The data with which we create our spreadsheet can come from another data source such as a database, a text file or any data array.

Finally we create the document in excel.

try {
    //The document is generated
    FileOutputStream out = new FileOutputStream(new File("/path/example.xls"));
    workbook.write(out);
    out.close();
} catch (Exception e) {
    e.printStackTrace();
}

The result will be a spreadsheet like the following:

And that’s it all to Create an Excel file with Apache in Java. We hope this example will be useful.