<!-- Apache POI -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.1</version>
        </dependency>

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.IdUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.bi.my.vo.ExcelVo;
import lombok.extern.slf4j.Slf4j;
import net.dreamlu.mica.core.result.R;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * 數(shù)據(jù)+圖片導(dǎo)入
 *
 * @author wss
 */
@Slf4j
@RestController
@RequestMapping("/easy")
public class EasyExcelController {

    /**
     * 數(shù)據(jù)導(dǎo)入并識別圖片
     *
     * @param file file
     * @return R<Object>
     * @author wss
     */
    @PostMapping("/import")
    public R<Object> dataImport(@RequestParam("file") MultipartFile file) {
        List<ExcelVo> excelVos = new ArrayList<>();
        //數(shù)據(jù)處理
        this.dataProcess(file, excelVos);
        //圖像處理
        this.imageProcess(file, excelVos);
        //返回結(jié)果，這里也可以處理excelVos數(shù)據(jù)，如往mysql存儲(chǔ)
        return R.success(excelVos);
    }

    /**
     * 圖片處理
     */
    public void imageProcess(MultipartFile file, List<ExcelVo> excelVos) {
        try {
            XSSFWorkbook book = new XSSFWorkbook(file.getInputStream());
            //方式1 獲取sheet數(shù)量，采用下標(biāo)方式遍歷讀取每個(gè)工作表數(shù)據(jù)
            int sheetsNos = book.getNumberOfSheets();
            for (int sheetNo = 0; sheetNo < sheetsNos; sheetNo++) {
                Sheet sheet = book.getSheetAt(sheetNo);
                //...省略，內(nèi)容為方式2
            }
            //方式2 獲取sheet數(shù)量，直接遍歷讀取每個(gè)工作表數(shù)據(jù)
            for (Sheet sheet : book) {
                XSSFSheet xssSheet = (XSSFSheet) sheet;
                //獲取工作表中繪圖包
                XSSFDrawing drawing = xssSheet.getDrawingPatriarch();
                if (drawing == null) {
                    break;
                }
                //獲取所有圖像形狀
                List<XSSFShape> shapes = drawing.getShapes();
                //遍歷所有形狀
                for (XSSFShape shape : shapes) {
                    //獲取形狀在工作表中的頂點(diǎn)位置信息（anchor錨點(diǎn)）
                    XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                    //圖片形狀在工作表中的位置, 所在行列起點(diǎn)和終點(diǎn)位置
                    short c1 = anchor.getCol1();
                    int r1 = anchor.getRow1();
                    String key = r1 + "行," + c1 + "列";
                    if (shape instanceof XSSFPicture) {
                        try {
                            XSSFPicture pic = (XSSFPicture) shape;
                            //形狀獲取對應(yīng)的圖片數(shù)據(jù)
                            XSSFPictureData picData = pic.getPictureData();
                            //保存圖片到本地
                            byte[] data = picData.getData();
                            //TODO 這里上傳文件至oss并生成鏈接，這里不做過多描述，有疑問請參照oss服務(wù)調(diào)用
                            String fileName = "https://oss.cn/" + DateUtil.today() + "/" + IdUtil.simpleUUID() + "/" + picData.suggestFileExtension();
                            //fileTemplate.putObject(properties.getBucketName(), fileName, new ByteArrayInputStream(data));
                            //TODO 放入excel集合，這里行數(shù)要減去1，獲取圖片是從表頭開始（表頭位置為0），獲取excelVos是從數(shù)據(jù)開始（第一條數(shù)據(jù)位置為0）他們相差一個(gè)表頭，所以要減去1才能對應(yīng)
                            excelVos.get(r1 - 1).setPicture(fileName);
                        } catch (Exception e) {
                            log.error("asyncImportList XSSFClientAnchor key|{} error|{}", key, e.getMessage());
                        }
                    }
                }
            }
        } catch (Exception e) {
            log.error("asyncImportList XSSFWorkbook error|{}", e.getMessage());
        }
    }

    /**
     * 數(shù)據(jù)處理
     */
    public void dataProcess(MultipartFile file, List<ExcelVo> excelVos) {
        // 這里默認(rèn)讀取第一個(gè)sheet
        try {
            EasyExcel.read(file.getInputStream(), ExcelVo.class, new ReadListener() {
                /**
                 * 單次緩存的數(shù)據(jù)量
                 */
                public static final int BATCH_COUNT = 100;
                /**
                 *臨時(shí)存儲(chǔ)
                 */
                private List<ExcelVo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

                @Override
                public void invoke(Object object, AnalysisContext context) {
                    ExcelVo data = (ExcelVo) object;
                    cachedDataList.add(data);
                    if (cachedDataList.size() >= BATCH_COUNT) {
                        saveData();
                        // 存儲(chǔ)完成清理 list
                        cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                    }
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    saveData();
                }

                /**
                 * 加上存儲(chǔ)數(shù)據(jù)庫
                 */
                private void saveData() {
                    log.info("已獲取數(shù)據(jù)|{}條", cachedDataList.size());
                    excelVos.addAll(cachedDataList);
                }
            }).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * 導(dǎo)入VO
 *
 * @author wss
 */
@Data
public class ExcelVo {
    @ExcelProperty("序號")
    private Integer ordinal;
    @ExcelProperty("標(biāo)題")
    private String title;
    @ExcelProperty("圖片")
    private String picture;
}

    /**
     * 數(shù)據(jù)導(dǎo)入并識別圖片
     *
     * @param file file
     * @return R<Object>
     * @author wss
     */
    @PostMapping("/import")
    public R<Object> dataImport(@RequestParam("file") MultipartFile file, @RequestExcel List<ExcelVo> excelVos) {
        //圖像處理
        this.imageProcess(file, excelVos);
        //返回結(jié)果，這里也可以處理excelVos數(shù)據(jù)，如往庫里存儲(chǔ)
        return R.success(excelVos);
    }