excel 上传使用easyexcel解析

 使用poi解析大文件excel导致内存溢出的可以使用easyExcel来解析可以解决问题

添加依赖

首先，在你的 pom.xml 文件中添加 EasyExcel 的依赖：

        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version><!-- 使用时请检查最新版本 -->
        </dependency>

数据模型

假设我们有一个DataExcel类：

@Data
public class DataExcel {
    @ExcelProperty( "客户名称")//对应表头
    private String clientName;

    @ExcelProperty( "票号")
    private String tktNo;

    @ExcelProperty("金额")
    private BigDecimal price;

    @ExcelProperty("备注")
    private String remark;

}

 自定义 Listener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.List;

public class YourCustomListener extends AnalysisEventListener<DataExcel> {

    private final List<DataExcel> dataList;

    public YourCustomListener(List<DataExcel> dataList) {
        this.dataList= dataList;
    }

    @Override
    public void invoke(DataExcel dataExcel, AnalysisContext analysisContext) {
        dataList.add(dataExcel);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {}

    @Override
    public void onException(Exception exception, AnalysisContext analysisContext) {}
}

 这里，YourCustomListener将解析到的DataExcel对象添加到一个列表中。

 上传 Excel 文件并解析Controller

创建一个Spring Boot Controller以处理上传、解析和下载：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.ReadListener;
import org.springframework.http.HttpHeaders;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

@RestController
public class ExcelController {

    private List<DataExcel> dataList = new ArrayList<>();

    @PostMapping("/upload")
    public String upload(@RequestParam("file") MultipartFile file) {
        try {
            //解析
            InputStream inputStream = file.getInputStream();
            ReadListener<DataExcel> listener = new YourCustomListener(dataList);
            EasyExcel.read(inputStream, DataExcel.class, listener).sheet().doRead();
            //保存
            ByteArrayInputStream byteArrayInputStream= new ByteArrayInputStream(file.getBytes());
            String filePath = "D:/" + file.getOriginalFilename();
            FileOutputStream outputStream = new FileOutputStream(filePath);
            byte[] buffer = new byte[1024];
            int bytesRead;
            while ((bytesRead = byteArrayInputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }
            outputStream.close();
            inputStream.close();
            byteArrayInputStream.close();
            return "File uploaded and parsed successfully.";
        } catch (Exception e) {
            return "Failed to upload and parse file: " + e.getMessage();
        }
    }

    @PostMapping("/download")
    public void download(HttpServletResponse response) throws Exception {
        //下载
        String fileUrl = "D:/file/...";//文件路径
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=existing-file.xlsx");
        InputStream inputStream = new FileInputStream(fileUrl);
        OutputStream outputStream = response.getOutputStream();
        byte[] buffer = new byte[1024];
        int bytesRead;
        // 读取文件到响应流
        while ((bytesRead = inputStream.read(buffer)) != -1) {
            outputStream.write(buffer, 0, bytesRead);
        }
        inputStream.close();
        outputStream.close();
        outputStream.flush();

    }
}

easyExcel更多用法请阅读easyexcel官方文档：关于Easyexcel | Easy Excel