<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.3.3</version>
    <relativePath/>
</parent>

<dependencies>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>cn.idev.excel</groupId>
        <artifactId>fastexcel</artifactId>
        <version>1.0.0</version>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

</dependencies>

server:
  port: 8082

package com.congge.entity;

import cn.idev.excel.annotation.ExcelProperty;
import lombok.*;

@Setter
@Getter
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class User {

    @ExcelProperty("編號")
    private Integer id;

    @ExcelProperty("名字")
    private String name;

    @ExcelProperty("年齡")
    private Integer age;
}

package com.congge.component;

import cn.idev.excel.context.AnalysisContext;
import cn.idev.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

public class BaseExcelListener<T> extends AnalysisEventListener<T> {

    private final List<T> dataList = new ArrayList<>();

    @Override
    public void invoke(T t, AnalysisContext analysisContext) {
        // 每讀取一行數(shù)據(jù)，就將其添加到dataList中
        dataList.add(t);
        System.out.println("解析到數(shù)據(jù): " + t);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("讀取完成，共讀取了 " + dataList.size() + " 條數(shù)據(jù)");
    }

    public List<T> getDataList() {
        return dataList;
    }
}

package com.congge.controller;

import cn.idev.excel.FastExcel;
import com.congge.component.BaseExcelListener;
import com.congge.entity.User;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

@RestController
@RequestMapping("/api/excel")
public class ExcelController {

    /**
     * Excel導(dǎo)出功能
     * @param response
     * @throws IOException
     * localhost:8082/api/excel/download
     */
    @GetMapping("/download")
    public void download(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("用戶數(shù)據(jù)", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        // 寫入數(shù)據(jù)
        FastExcel.write(response.getOutputStream(), User.class)
                .sheet("用戶信息")
                .doWrite(buildData());
    }

    /**
     * Excel導(dǎo)出功能
     * @param file
     * @return
     * localhost:8082/api/excel/upload
     */
    @PostMapping("/upload")
    public ResponseEntity<String> upload(@RequestParam("file") MultipartFile file) {
        if (file.isEmpty()) {
            return ResponseEntity.badRequest().body("請選擇一個文件上傳！");
        }
        try {
            BaseExcelListener<User> baseExcelListener = new BaseExcelListener<>();
            FastExcel.read(file.getInputStream(), User.class, baseExcelListener)
                    .sheet()
                    .doRead();
            // 獲取讀取的數(shù)據(jù)
            List<User> dataList = baseExcelListener.getDataList();
            System.out.println("讀取到的數(shù)據(jù): " + dataList);
            return ResponseEntity.ok("文件上傳并處理成功！共處理 " + dataList.size() + " 條數(shù)據(jù)");
        } catch (IOException e) {
            return ResponseEntity.status(500).body("文件處理失敗: " + e.getMessage());
        }
    }

    // 創(chuàng)建測試數(shù)據(jù)
    private List<User> buildData() {
        User user1 = new User();
        user1.setId(1);
        user1.setName("張三");
        user1.setAge(18);

        User user2 = new User();
        user2.setId(2);
        user2.setName("李四");
        user2.setAge(19);

        return List.of(user1, user2);
    }
}

import cn.idev.excel.context.AnalysisContext;
import cn.idev.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;

public class BatchExcelListener<T> extends AnalysisEventListener<T> {
    private static final int BATCH_COUNT = 1000;
    private List<T> dataList = new ArrayList<>(BATCH_COUNT);
    
    @Override
    public void invoke(T data, AnalysisContext context) {
        dataList.add(data);
        // 達到BATCH_COUNT了，需要去存儲一次數(shù)據(jù)庫，防止數(shù)據(jù)幾萬條數(shù)據(jù)在內(nèi)存，容易OOM
        if (dataList.size() >= BATCH_COUNT) {
            saveData();
            // 存儲完成清理 list
            dataList = new ArrayList<>(BATCH_COUNT);
        }
    }
    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 確保最后遺留的數(shù)據(jù)也存儲到數(shù)據(jù)庫
        if (!dataList.isEmpty()) {
            saveData();
        }
        System.out.println("所有數(shù)據(jù)解析完成！");
    }
    
    private void saveData() {
        // 這里實現(xiàn)批量保存到數(shù)據(jù)庫的邏輯
        System.out.println("批量保存" + dataList.size() + "條數(shù)據(jù)到數(shù)據(jù)庫");
        // userService.saveBatch(dataList);
    }
}

//localhost:8082/api/excel/convertToPdf
@GetMapping("/convertToPdf")
public void convertToPdf(){
    String excelPath = "D:\\data\\用戶數(shù)據(jù).xlsx";
    String pdfFilePath = "D:\\data\\用戶數(shù)據(jù).pdf";
    FastExcel.convertToPdf(new File(excelPath), new File(pdfFilePath), null, null);
    System.out.println("轉(zhuǎn)換完成");
}

<!-- easyexcel 依賴 -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>xxxx</version>
</dependency>

<dependency>
  <groupId>cn.idev.excel</groupId>
  <artifactId>fastexcel</artifactId>
  <version>1.0.0</version>
</dependency>

// 將 easyexcel 的包名替換為 FastExcel 的包名
import com.alibaba.excel.**;

import cn.idev.excel.**;