It’s always interesting to be able to generate Microsoft Excel XLSX files in an application to propose reports to users for example. In that tutorial, you are going to learn how to generate Microsoft Excel files in a Java application.

For that, we are going to use the great Apache POI API available just there : https://poi.apache.org/download.html

Note that you can watch this tutorial as a video on YouTube :

 

Adding Apache POI as a dependency

First step is to add Apache POI as a Maven dependency in the build file of our Java project :

<!-- dependency used for XLSX file format -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!-- dependency used for XLS older file format
(added for information) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
view raw build.xml hosted with ❤ by GitHub

We have chosen to generate Excel XLSX files in that tutorial but the logic is the same to generate Excel XLS older file format. So, we have added both dependencies in our Maven POM but just the poi-ooxml is needed for our example.

 

Creating a Contact POJO

In our example, we are going to generate an Excel file containing a list of contacts. So, we create a Contact POJO object to represent the contacts as a Java object :

package excel;
public class Contact {
public String firstName;
public String lastName;
public String email;
public String dateOfBirth;
public Contact(String firstName, String lastName, String email,
String dateOfBirth) {
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
this.dateOfBirth = dateOfBirth;
}
}
view raw Contact.java hosted with ❤ by GitHub

Like you can see, a Contact will have a first name, a last name, an email and a date of birth.

 

Creating the Excel file with the Contacts data

With the Apache POI API, an Excel file is representend by a Workbook object. Like we want to generate a XLSX file, we need to create a XSSFWorkbookinstance. For a XLS file, we would need to create a HSSFWorkbook instance. Once the Workbook instance is created, we need to create a new Sheet object by calling the createSheet method of the Workbook object :

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Contacts");
view raw CreateWorkbook.java hosted with ❤ by GitHub

 

In our generated file, we will have a row header with a specific style. For that, we create a Font object by calling the createFont method of the Workbookinstance. Then, we define the color and the size of our Font. Next step is to associate this font to a CellStyle object created by calling the createCellStylemethod of the Workbook instance like that :

Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 14);
headerFont.setColor(IndexedColors.RED.getIndex());
CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFont(headerFont);
view raw CreateCellStyle.java hosted with ❤ by GitHub

 

Now, we must create the header row. We set the title for each cell and also the specific cell style created. Note that the title for each cell of the row header come from a static array defined as a property of our ExcelWriter class :

// Create a Row
Row headerRow = sheet.createRow(0);
for (int i = 0; i < columns.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(columns[i]);
cell.setCellStyle(headerCellStyle);
}
view raw RowHeader.java hosted with ❤ by GitHub

 

Then, we create other rows and cells with contacts’ data by iterating on the list of contacts. Besides, we resize all columns of our file to fit the content size :

// Create Other rows and cells with contacts data
int rowNum = 1;
for (Contact contact : contacts) {
Row row = sheet.createRow(rowNum++);
row.createCell(0).setCellValue(contact.firstName);
row.createCell(1).setCellValue(contact.lastName);
row.createCell(2).setCellValue(contact.email);
row.createCell(3).setCellValue(contact.dateOfBirth);
}
// Resize all columns to fit the content size
for (int i = 0; i < columns.length; i++) {
sheet.autoSizeColumn(i);
}
view raw CreateRows.java hosted with ❤ by GitHub

 

Generating the Excel file

Finally, we have to generate the Excel file by writing the content of our Workbook instance to a file. For that, we create a FileOutputStream instance with the name for our file and then we pass this instance as a parameter of the write method of the Workbook object :

FileOutputStream fileOut = new FileOutputStream("contacts.xlsx");
workbook.write(fileOut);
fileOut.close();
view raw WritingExcel.java hosted with ❤ by GitHub

 

Complete source code of our ExcelWriter class

The complete source code of our ExcelWriter class is available just below :

package excel;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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;
public class ExcelWriter {
private static String[] columns = { "First Name", "Last Name", "Email",
"Date Of Birth" };
private static List<Contact> contacts = new ArrayList<Contact>();
public static void main(String[] args) throws IOException,
InvalidFormatException {
contacts.add(new Contact("Sylvain", "Saurel",
"sylvain.saurel@gmail.com", "17/01/1980"));
contacts.add(new Contact("Albert", "Dupond",
"sylvain.saurel@gmail.com", "17/08/1989"));
contacts.add(new Contact("Pierre", "Dupont",
"sylvain.saurel@gmail.com", "17/07/1956"));
contacts.add(new Contact("Mariano", "Diaz", "sylvain.saurel@gmail.com",
"17/05/1988"));
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Contacts");
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 14);
headerFont.setColor(IndexedColors.RED.getIndex());
CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFont(headerFont);
// Create a Row
Row headerRow = sheet.createRow(0);
for (int i = 0; i < columns.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(columns[i]);
cell.setCellStyle(headerCellStyle);
}
// Create Other rows and cells with contacts data
int rowNum = 1;
for (Contact contact : contacts) {
Row row = sheet.createRow(rowNum++);
row.createCell(0).setCellValue(contact.firstName);
row.createCell(1).setCellValue(contact.lastName);
row.createCell(2).setCellValue(contact.email);
row.createCell(3).setCellValue(contact.dateOfBirth);
}
// Resize all columns to fit the content size
for (int i = 0; i < columns.length; i++) {
sheet.autoSizeColumn(i);
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("contacts.xlsx");
workbook.write(fileOut);
fileOut.close();
}
}
view raw ExcelWriter.java hosted with ❤ by GitHub

 

Final Result

By executing the ExcelWriter class on a JVM, the contacts.xlsx file should be generated on your machine and you should have the following result :

 

That’s all for that tutorial. Don’t hesitate to visit the SSaurel’s Channel :