Generate Excel in Spring Boot using Apache POI

Generating Excel files programmatically can be a critical requirement in many business applications. In this guide, we will explore how to create Excel files using Apache POI in a Spring Boot application. We will walk through the layers of the application, from the controller to the service layer, providing a comprehensive solution.

Prerequisites

Before we begin, ensure you have the following:

• A basic understanding of Spring Boot.
• Java Development Kit (JDK) installed.
• Apache POI library added to your project.

Project Setup

1. Add Dependencies

First, add the necessary dependencies to your pom.xml if you are using Maven:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version> <!-- Use the latest version -->
</dependency>

2. Create the Service Layer

The service layer will handle the logic for generating the Excel file.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.io.IOException;

@Service
public class ExcelService {

    public byte[] generateExcel() throws IOException {
        try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            Sheet sheet = workbook.createSheet("Sample Sheet");

            // Create a Row
            Row headerRow = sheet.createRow(0);

            // Create cells
            Cell cell = headerRow.createCell(0);
            cell.setCellValue("Header 1");

            cell = headerRow.createCell(1);
            cell.setCellValue("Header 2");

            // Create Other rows and cells with data
            Row dataRow = sheet.createRow(1);
            dataRow.createCell(0).setCellValue("Data 1");
            dataRow.createCell(1).setCellValue("Data 2");

            workbook.write(out);
            return out.toByteArray();
        }
    }
}

3. Create the Controller Layer

The controller layer will handle incoming HTTP requests and use the service to generate Excel files.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.IOException;

@RestController
public class ExcelController {

    @Autowired
    private ExcelService excelService;

    @GetMapping("/download/excel")
    public ResponseEntity<byte[]> downloadExcel() {
        try {
            byte[] bytes = excelService.generateExcel();

            return ResponseEntity.ok()
                .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=sample.xlsx")
                .contentType(MediaType.APPLICATION_OCTET_STREAM)
                .body(bytes);
        } catch (IOException e) {
            return ResponseEntity.status(500).build();
        }
    }
}

Running the Application

To test the application:

• Start your Spring Boot application.
• Navigate to http://localhost:8080/download/excel in your web browser.
• The browser will prompt you to download the generated Excel file.

Conclusion

By following the steps outlined above, you can create a simple Spring Boot application that generates Excel files using Apache POI. This solution includes a structured approach, utilizing both the service and controller layers to manage the generation and download of Excel files.

This setup can be extended to include more complex data and formatting options, making it a powerful tool for any application requiring Excel file generation.