Introduction

Implementing data export functionality is a common requirement in enterprise Java applications. By leveraging the Apache POI library, developers can generate Excel spreadsheets programmatically. This approach allows for dynamic column mapping using Java annotations and reflection, ensuring that the spreadsheet structure remains synchronized with the underlying data models.

Maven Dependencies

To begin, include the Apache POI dependency in your pom.xml file. This library provides the necessary APIs to manipulate Microsoft Office documents.

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

Custom Annotation for Column Headers

We define a custom annotation to mark fields within our POJOs (Plain Old Java Objects). This allows the utility class to retrieve the display name for each column automatically during runtime.

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ColumnHeader {
    String name();
}

Excel Generation Utility

The following utility class handles the workbook creation. It uses variable arguments to accept multiple lists of data, supporting the export of different data types into a single sheet if necessary. The implementation utilizes reflection to extract values from the object fields.

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class SpreadsheetBuilder {

    /**
     * Generates an HSSFWorkbook based on provided headers and data collections.
     *
     * @param tabName   The name of the Excel sheet.
     * @param headers   List of column headers.
     * @param dataLists Variable arguments containing lists of data objects.
     * @return A configured HSSFWorkbook.
     */
    public static HSSFWorkbook buildWorkbook(String tabName, List<String> headers, List<?>... dataLists) 
            throws IllegalAccessException {
        
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(tabName);
        HSSFRow titleRow = sheet.createRow(0);

        // Write Column Headers
        for (int i = 0; i < headers.size(); i++) {
            titleRow.createCell(i).setCellValue(headers.get(i));
        }

        int rowIndex = 1;
        
        // Iterate through all provided data lists
        for (List<?> currentList : dataLists) {
            for (Object entity : currentList) {
                HSSFRow dataRow = sheet.createRow(rowIndex++);
                Class<?> clazz = entity.getClass();
                Field[] fields = clazz.getDeclaredFields();

                for (int i = 0; i < fields.length; i++) {
                    fields[i].setAccessible(true);
                    Object value = fields[i].get(entity);
                    
                    if (value != null) {
                        if (value instanceof Date) {
                            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            dataRow.createCell(i).setCellValue(dateFormat.format(value));
                        } else {
                            dataRow.createCell(i).setCellValue(String.valueOf(value));
                        }
                    }
                }
            }
        }
        return workbook;
    }
}

Data Model Definition

The entity class below demonstrates the usage of the @ColumnHeader annotation. The fields represent the data columns to be exported.

public class UserRecord {
    @ColumnHeader(name = "User ID")
    private Long userId;

    @ColumnHeader(name = "Full Name")
    private String fullName;

    @ColumnHeader(name = "Comments")
    private String notes;

    // Constructors, Getters, and Setters
    public UserRecord() {}

    public Long getUserId() { return userId; }
    public void setUserId(Long userId) { this.userId = userId; }

    public String getFullName() { return fullName; }
    public void setFullName(String fullName) { this.fullName = fullName; }

    public String getNotes() { return notes; }
    public void setNotes(String notes) { this.notes = notes; }
}

Controller Endpoint Implementation

The Spring Boot controller method below handles the HTTP request. It sets the appropriate response headers to trigger a file download in the browser. It also dynamically extracts the header values from the UserRecord class annotations before generating the file.

import org.springframework.util.ReflectionUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

@RestController
public class DataExportController {

    @GetMapping("/export-users")
    public void exportUserList(HttpServletResponse response) {
        try {
            OutputStream outputStream = response.getOutputStream();
            String filename = "User_List_Report";
            
            // Configure response headers
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", 
                    "attachment; filename=" + new String(filename.getBytes("GB2312"), "ISO8859-1") + ".xls");

            // 1. Dynamically extract headers using Reflection
            List<String> headers = new ArrayList<>();
            Field[] fields = UserRecord.class.getDeclaredFields();
            for (Field field : fields) {
                ColumnHeader annotation = field.getAnnotation(ColumnHeader.class);
                if (annotation != null) {
                    headers.add(annotation.name());
                }
            }

            // 2. Prepare Mock Data
            List<UserRecord> primaryData = new ArrayList<>();
            UserRecord userOne = new UserRecord();
            userOne.setUserId(1001L);
            userOne.setFullName("Alice Smith");
            userOne.setNotes("Administrator");
            primaryData.add(userOne);

            UserRecord userTwo = new UserRecord();
            userTwo.setUserId(1002L);
            userTwo.setFullName("Bob Jones");
            userTwo.setNotes("Standard User");
            primaryData.add(userTwo);

            // 3. Generate and Write Workbook
            HSSFWorkbook workbook = SpreadsheetBuilder.buildWorkbook("UserData", headers, primaryData);
            workbook.write(outputStream);
            
            outputStream.close();
            workbook.close();

        } catch (IOException e) {
            // Handle logging appropriately
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
    }
}