Introduction

Excel's xls and xlsx files are commonly used file types for storing formatted tabular data. In this tutorial, I'll show you how to write data to Excel file in Java using the Apache POI library. In this example, we'll create a list of User objects and export it to Excel file. So let's start with dependencies. 

Dependencies

Apache POI has a lot of implicit dependencies you hear about when you try to export your data. I also had issues with orchestrating versions of those dependencies. Following is the maven dependency list needed for your code to work. Be aware that changing versions may cause artifact version incompatibility and you may end up with ClassNotFoundException or MethodNotFoundError. Currently, version 4.1 of Apache POI is the newest.

<dependencies>
<dependency>
    <groupId>org.apache.servicemix.bundles</groupId>
    <artifactId>org.apache.servicemix.bundles.poi</artifactId>
    <version>4.1.2_1</version>
</dependency>
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>3.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.4</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-compress</artifactId>
    <version>1.20</version>
</dependency>
</dependencies>

Coding time - export data to Excel file

User class contains three parameters: id of Integer type and firstName and lastName of type String.

class User {

	Integer id;
	String firstName;
	String lastName;

	public User(Integer id, String firstName, String lastName) {
		this.id = id;
		this.firstName = firstName;
		this.lastName = lastName;
	}
}

And this is the working code where you'll see how to export data to Excel file. You'll notice that we defined file location and file name as constants. I left comments in code so it's easier to understand what is what. 

package exportdemo;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
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 ExportData {
	// CONSTANTS
	/*----------------------------------------------------------*/
	static final String FILE_SAVE_LOCATION = "C:\\reports\\";
	static final String FILE_NAME = "UserReport.xlsx";
	/*----------------------------------------------------------*/

	public static void main(String[] args) throws IOException {
		// creating data to be exported
		List<User> userList = new ArrayList<User>();
		userList.add(new User(1, "John", "Doe"));
		userList.add(new User(2, "Peter", "Peterson"));
		
		// creating workbook
		XSSFWorkbook workbook = new XSSFWorkbook();
		// creating sheet with name "Report" in workbook
		XSSFSheet sheet = workbook.createSheet("Report");
		// this method creates header for our table
		createHeader(sheet, workbook);

		int rowCount = 0;
		for (User user : userList) {
			// creating row 
			Row row = sheet.createRow(++rowCount);
			
			// adding first cell to the row
			Cell idCell = row.createCell(0);
			idCell.setCellValue(user.id);
			
			// adding second cell to the row
			Cell nameCell = row.createCell(1);
			nameCell.setCellValue(user.firstName);
			
			//adding third cell to the row
			Cell statusCell = row.createCell(2);
			statusCell.setCellValue(user.lastName);

		}
		try (FileOutputStream outputStream = new FileOutputStream(FILE_SAVE_LOCATION + FILE_NAME)) {
			workbook.write(outputStream);
		} finally {
                        // don't forget to close workbook to prevent memory leaks
			workbook.close();
                }

	}

	private static void createHeader(XSSFSheet sheet, XSSFWorkbook workbook) {
		Row headerRow = sheet.createRow(0);
		headerRow.createCell(0).setCellValue("User ID");
		headerRow.createCell(1).setCellValue("First name");
		headerRow.createCell(2).setCellValue("Last name");
	}

}

And the result is:

User Id First name Last name
1 John Doe
2 Peter Peterson

The code is also available on GitHub.

Check also...