The most important thing in any application is the data. Regardless of application type, exporting data from the application to an external document format is vital. To be specific, we need data exporting for various reporting purposes, tally data, audit purposes, etc. In our previous tutorial, we learned how to export data into PDF documents in the Spring Boot application. Now it’s time to learn to export data into an excel document.

We are going to use Apache POI for creating, writing, and exporting data into an excel document.

What do we need?

We need the following dependencies/technologies in our project:

1. Java 8
2. Spring Boot
3. Apache POI
4. Spring Web, Spring Data JPA
5. Maven

Create the Spring Boot project

Let’s start with creating the Spring boot project with the required dependencies. We’ll create our project using Spring Boot Initializer API. First, go to start.spring.io and add Spring web, Spring Data JPA, H2 database dependencies under the dependency section and export the project.

After downloading the project, import it into an IDE. We are using Eclipse IDE here. To import the project in eclipse IDE, go to File >> import >> existing maven project and select the project to import.

Configure Apache POI

It’s time to add maven dependency for Apache POI. We are using Apache POI for writing into excel documents. We’ll need below two dependencies from the Apache POI library.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.1.0</version>
</dependency>

Configuring H2 database

We need to add the following properties in our application.properties file:

#H2 Database
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=admin
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

We are using the H2 database and the purpose of this article is to explain how to export data into an excel document. If you want to use another database like MySQL, you can configure it here.

What are we creating?

We will take one simple example here to elaborate usage of Apache POI. Let’s create an Exam record application that’ll contain the students’ exam records. Also, we will explain how to create and export the exam records in an excel document file.

Create Model Class

We will create our model/entity class. It will contain the necessary fields like id, studentName, examYear, score etc.

package com.example.apachepoi.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class ExamRecord {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private long id;
	
	@Column(name = "name")
	private String studentName;
	
	@Column(name = "year")
	private String examYear;
	
	@Column(name = "score")
	private String score;

    // Getters and Setters
}

Create a Repository to handle database operations

Repositories in a Spring boot application works as a medium between the database and the application. We use the JPARepository interface to create our Repository interface for our ExamRecord application.

package com.example.apachepoi.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import com.example.apachepoi.model.ExamRecord;

public interface ExamRecordRepository extends JpaRepository<ExamRecord, Long> {}

Create Service class

Let’s create our service class now. The service class will have methods that will call the respective method from the ExamRecordRepository class to extract results from the database and to insert an exam record in the database.

We are using the findAll and save method from the repository that’ll find all the records and save a specific exam record respectively in the database.

package com.example.apachepoi.service;

import java.util.List;
import org.springframework.stereotype.Service;
import com.example.apachepoi.model.ExamRecord;
import com.example.apachepoi.repository.ExamRecordRepository;

@Service
public class ExamRecordService {

    @Autowired
	private ExamRecordRepository repo;

	public List<ExamRecord> getAllRecords() {
		return repo.findAll();
	}

	public void addExamRecord(ExamRecord record) {
		repo.save(record);
	}
}

How to export data into an Excel file?

We are creating our utility class ExcelGenerator now to create an excel file and upload exam records in an excel file. We will be using Apache POI.

package com.example.apachepoi.util;

import java.io.IOException;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.example.apachepoi.model.ExamRecord;

public class ExcelGenerator {

	private List<ExamRecord> listRecords;
	private XSSFWorkbook workbook;
	private XSSFSheet sheet;

	public ExcelGenerator(List<ExamRecord> listRecords) {
		this.listRecords = listRecords;
		workbook = new XSSFWorkbook();
	}

	private void writeHeader() {
		sheet = workbook.createSheet("Exam Records");

		Row row = sheet.createRow(0);

		CellStyle style = workbook.createCellStyle();
		XSSFFont font = workbook.createFont();
		font.setBold(true);
		font.setFontHeight(16);
		style.setFont(font);

		createCell(row, 0, "ID", style);
		createCell(row, 1, "Student Name", style);
		createCell(row, 2, "Exam Year", style);
		createCell(row, 3, "Score", style);

	}

	private void createCell(Row row, int columnCount, Object value, CellStyle style) {
		sheet.autoSizeColumn(columnCount);
		Cell cell = row.createCell(columnCount);
		if (value instanceof Integer) {
			cell.setCellValue((Integer) value);
		} 
        else if (value instanceof Long) {
			cell.setCellValue((Long) value);
		} else if (value instanceof Boolean) {
			cell.setCellValue((Boolean) value);
		} else {
			cell.setCellValue((String) value);
		}
		cell.setCellStyle(style);
	}

	private void write() {
		int rowCount = 1;

		CellStyle style = workbook.createCellStyle();
		XSSFFont font = workbook.createFont();
		font.setFontHeight(14);
		style.setFont(font);

		for (ExamRecord record : listRecords) {
			Row row = sheet.createRow(rowCount++);
			int columnCount = 0;

			createCell(row, columnCount++, record.getId(), style);
			createCell(row, columnCount++, record.getStudentName(), style);
			createCell(row, columnCount++, record.getExamYear(), style);
			createCell(row, columnCount++, record.getScore(), style);

		}
	}

	public void generate(HttpServletResponse response) throws IOException {
		writeHeader();
		write();
		ServletOutputStream outputStream = response.getOutputStream();
		workbook.write(outputStream);
		workbook.close();

		outputStream.close();

	}
}

Let’s understand the code:

• We are using XSSFWorkbook to create the workbook that is our excel file. Apache POI’s XSSFSheet is used to create the sheet inside our excel workbook.
• First, we are creating an instance of XSSFWorkbook and then calling createSheet method that’ll create our first sheet inside the workbook.
• After, we are creating rows and cells. In our first row, we are creating headers.
• Then, we are creating more rows to hold our students’ exam records data.
• Finally, after all the data is written into a sheet, we are writing the outputStream. Do not forget to close the resources.

Create Controller class

Now, we will create our controller class. This will call the utility class to generate and export the data into an excel document.

package com.example.apachepoi.controller;

import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

import com.example.apachepoi.model.ExamRecord;
import com.example.apachepoi.service.ExamRecordService;
import com.example.apachepoi.util.ExcelGenerator;

@Controller
public class ExamRecordController {

	@Autowired
	private ExamRecordService service;

	@GetMapping("/records/export/excel")
	public void exportIntoExcel(HttpServletResponse response) throws IOException {
		response.setContentType("application/octet-stream");
		DateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
		String currentDateTime = dateFormatter.format(new Date());

		String headerKey = "Content-Disposition";
		String headerValue = "attachment; filename=records_" + currentDateTime + ".xlsx";
		response.setHeader(headerKey, headerValue);

		List<ExamRecord> listOfRecords = service.getAllRecords();

		ExcelGenerator generator = new ExcelGenerator(listOfRecords);

		generator.generate(response);
	}

}

In the above code, we are calling the utility class to generate the excel file. We are setting the content type in response, retrieving the list of all records, then finally calling the utility method.

Test the application

So, there are two ways to test this application. You can create a view to adding student exam records and then generate and export the excel using the URL via the view.

As the purpose of this article is to understand how we can export the data into an excel document, we are not creating a view for it. However, we can use CommandLineRunner to achieve the same results without a view.

Let’s implement CommandLineRunner in our Spring boot main application:

package com.example.apachepoi;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.example.apachepoi.model.ExamRecord;
import com.example.apachepoi.service.ExamRecordService;

@SpringBootApplication
public class ExamRecordApplication implements CommandLineRunner {

	@Autowired
	private ExamRecordService service;

	public static void main(String[] args) {
		SpringApplication.run(ExamRecordApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {

		for (int i = 0; i < 11; i++) {
			ExamRecord record = new ExamRecord();
			record.setId(i);
			record.setStudentName("Student " + i);
			record.setExamYear("2021");
			record.setScore("90%");
			
			service.addExamRecord(record);
		}
	}

}

Results

So let’s look at the result of our how-to export data into excel in the spring boot application. The following are the results of our application. However, test the application to see the result yourself.

Conclusion

In this article, we have learned to export the excel from spring boot application. We have learned the use of Apache POI in java. All the provided example codes are copy-paste runnable.