Optimizing Excel Data Handling in Spring Boot Applications

When dealing with Excel import/export functionality in Java web applications, memory efficiency becomes crucial. Traditional libraries like EasyPoi or Hutool can consume significant memory, making EasyExcel a better choice for large datasets. This implementation leverages Java 8 functional programming to minimize code while maintaining performance.

Key Optimizaton Strategies

1. Functional programming approach for streamlined data import
2. Reflection-based single interface for generic Excel imports
3. Thread pool implementation for large-scale data processing
4. Generic type support for flexible data exports

Dependency Setup

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

Generic Object Import Implementation

Data Model Definition

@Data
@TableName("student_data")
public class StudentRecord {
    @ColumnWidth(12)
    @ExcelProperty(value = "Full Name", index = 0)
    private String fullName;
    
    @ExcelProperty(value = "Age", index = 1)
    private Integer age;
    
    @ExcelProperty(value = "Height", index = 2)
    private Double height;
    
    @ExcelProperty(value = "Enrollment Date", index = 3)
    @DateTimeFormat("yyyy-MM-dd")
    private String enrollmentDate;
}

Custom Read Listener

public class DataImportListener<T> implements ReadListener<T> {
    private static final int BATCH_SIZE = 200;
    private List<T> buffer = new ArrayList<>(BATCH_SIZE);
    private Consumer<List<T>> processor;

    public DataImportListener(Consumer<List<T>> processor) {
        this.processor = processor;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        buffer.add(data);
        if (buffer.size() >= BATCH_SIZE) {
            processBatch();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (!buffer.isEmpty()) {
            processBatch();
        }
    }

    private void processBatch() {
        processor.accept(buffer);
        buffer.clear();
    }
}

Controller Implementation

@PostMapping("/import/students")
public ResponseEntity<String> importStudentData(@RequestParam("file") MultipartFile file) {
    try {
        EasyExcel.read(file.getInputStream(), StudentRecord.class,
                new DataImportListener<>(records -> {
                    studentService.saveAll(records);
                    log.info("Processed {} records", records.size());
                }))
            .sheet()
            .doRead();
        return ResponseEntity.ok("Import successful");
    } catch (IOException e) {
        log.error("Import failed", e);
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body("Import failed");
    }
}

Dynamic Column Mapping

For flexible imports where column order may vary:

public class DynamicImportListener implements ReadListener<Map<Integer, String>> {
    private List<Map<String, String>> mappedData = new ArrayList<>();
    private Map<Integer, String> headerMapping;

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headers, AnalysisContext context) {
        headerMapping = headers.entrySet().stream()
            .collect(Collectors.toMap(
                Map.Entry::getKey,
                e -> e.getValue().getStringValue()
            ));
    }

    @Override
    public void invoke(Map<Integer, String> row, AnalysisContext context) {
        Map<String, String> mappedRow = new HashMap<>();
        row.forEach((index, value) -> {
            if (headerMapping.containsKey(index)) {
                mappedRow.put(headerMapping.get(index), value);
            }
        });
        mappedData.add(mappedRow);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // Process complete dataset
    }
}

Export Functionality

Generic export implementation:

public <T> void exportData(String fileName, List<T> data, Class<T> type, 
                          HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
    
    EasyExcel.write(response.getOutputStream())
        .head(type)
        .sheet("Data")
        .doWrite(data);
}

Dynamic header export:

public void exportWithCustomHeaders(String fileName, List<List<Object>> data, 
                                   List<List<String>> headers, 
                                   HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.ancode(fileName, "UTF-8") + ".xlsx");
    
    EasyExcel.write(response.getOutputStream())
        .head(headers)
        .sheet("Data")
        .doWrite(data);
}