Exporting Data to Excel

1. Adding Maven Dependency

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

2. Defining Data Model with Annotations

@Data
public class ScheduleRecord {
    @ExcelProperty(value = "Start Date")
    private String beginDate;
    @ExcelProperty(value = "Start Time")
    private String beginTime;
    @ExcelProperty(value = "End Date")
    private String finishDate;
    @ExcelProperty(value = "End Time")
    private String finishTime;
    @ExcelProperty(value = "Participant Name")
    private String participantName;
    @ExcelProperty(value = "Age")
    private String participantAge;
}

3. Implementing Auto Column Width Strategy

public class AutoColumnWidthStrategy extends AbstractColumnWidthStyleStrategy {
    private static final int MAXIMUM_WIDTH = 255;
    private static final int MINIMUM_WIDTH_MULTIPLIER = 2;
    private final Map<Integer, Map<Integer, Integer>> widthCache = new HashMap<>(8);

    @Override
    protected void setColumnWidth(WriteSheetHolder sheetHolder, List<CellData> cellDataList, 
                                 Cell cell, Head head, Integer rowIndex, Boolean isHeader) {
        if (!isHeader && CollectionUtils.isEmpty(cellDataList)) {
            return;
        }

        Map<Integer, Integer> columnWidthMap = widthCache.computeIfAbsent(
            sheetHolder.getSheetNo(), k -> new HashMap<>(16));

        int calculatedWidth = calculateCellWidth(cellDataList, cell, isHeader);
        if (calculatedWidth >= 0) {
            calculatedWidth = Math.min(calculatedWidth, MAXIMUM_WIDTH);
            if (calculatedWidth < MINIMUM_WIDTH_MULTIPLIER) {
                calculatedWidth *= MINIMUM_WIDTH_MULTIPLIER;
            }

            Integer currentMaxWidth = columnWidthMap.get(cell.getColumnIndex());
            if (currentMaxWidth == null || calculatedWidth > currentMaxWidth) {
                columnWidthMap.put(cell.getColumnIndex(), calculatedWidth);
                sheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), calculatedWidth * 256);
            }
        }
    }

    private int calculateCellWidth(List<CellData> cellDataList, Cell cell, Boolean isHeader) {
        if (isHeader) {
            return cell.getStringCellValue().getBytes().length;
        }
        
        CellData data = cellDataList.get(0);
        CellDataTypeEnum dataType = data.getType();
        if (dataType == null) {
            return -1;
        }

        switch (dataType) {
            case STRING:
                return data.getStringValue().getBytes().length;
            case BOOLEAN:
                return String.valueOf(data.getBooleanValue()).getBytes().length;
            case NUMBER:
                return String.valueOf(data.getNumberValue()).getBytes().length;
            default:
                return -1;
        }
    }
}

4. Exporting Excel File

public void exportScheduleData(HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    
    LocalDate today = LocalDate.now();
    String encodedFileName = URLEncoder.encode("Schedule_Report", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", 
        "attachment;filename*=UTF-8''" + encodedFileName + ".xlsx");
    response.setHeader("Access-Control-Expose-Headers", "filename");
    response.setHeader("filename", encodedFileName + ".xlsx");

    List<ScheduleRecord> records = scheduleService.getAllRecords();
    
    EasyExcel.write(response.getOutputStream(), ScheduleRecord.class)
        .sheet("Schedule Data")
        .registerWriteHandler(new AutoColumnWidthStrategy())
        .doWrite(records);
}

Importing Data from Excel

1. Creating Excel Analysis Listener

public class ExcelDataListener<T> extends AnalysisEventListener<T> {
    private final List<T> collectedData = new ArrayList<>();

    @Override
    public void invoke(T dataInstance, AnalysisContext context) {
        collectedData.add(dataInstance);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // Post-processing logic after complete analysis
    }

    public List<T> getCollectedData() {
        return collectedData;
    }
}

2. Processing Excel Upload

public ResponseEntity<Map<String, Object>> processExcelUpload(
        @RequestParam("document") MultipartFile file) throws Exception {
    
    ExcelDataListener<ScheduleRecord> firstSheetListener = new ExcelDataListener<>();
    EasyExcel.read(file.getInputStream(), ScheduleRecord.class, firstSheetListener)
        .sheet(0).doRead();
    List<ScheduleRecord> firstSheetData = firstSheetListener.getCollectedData();

    ExcelDataListener<ParticipantInfo> secondSheetListener = new ExcelDataListener<>();
    EasyExcel.read(file.getInputStream(), ParticipantInfo.class, secondSheetListener)
        .sheet(1).doRead();
    List<ParticipantInfo> secondSheetData = secondSheetListener.getCollectedData();

    Map<String, Object> processingResult = excelService.processUploadedData(
        firstSheetData, secondSheetData);
    
    return ResponseEntity.ok(processingResult);
}