POI上传带图片的Excel

 实现逻辑，把excel中带图片那一列的图片通过fastDFS上传到文件系统，然后把url替换存入数据库

经查阅，easyExcel和hutool均无法实现excel图片上传功能，所以手动封装一个工具类

import com.github.tobato.fastdfs.domain.StorePath;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ThreadPoolExecutor;

/**
 * 通用的poi导入excel工具类
 *
 * @author zab
 * @date 2021/5/19 13:53
 */
@Slf4j
@Component
public class PoiExcelUtil {
    @Value("${fdfs.web-server-url}")
    private String webPathPre;
    @Autowired
    private FastDfsClient fastDfsClient;

    /**
     * 可用于读取包含多个表的excel
     * 外层map用于表示sheet，list用于表示每个sheet的行记录，内层map用于表示sheet-->row-->cell即：行中的每一列数据
     *
     * @param filePath 文件路径
     * @return {@link Map<String, List<Map<String, Object>>>}
     */
    public Map<String, List<Map<String, Object>>> readExcel(String filePath) throws Exception {
        filePath = filePath.replace("\\", "/");
        File file = new File(filePath);
        FileInputStream fis = new FileInputStream(file);
        //第一步得到工作簿，整个excel
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(fis);
        } else if (filePath.endsWith("xlsx")) {
            wb = new XSSFWorkbook(fis);
        } else {
            throw new Exception("文件格式不支持");
        }
        //外层map用于表示sheet，list用于表示每个sheet的行记录，内层map用于表示sheet-->row-->cell即：行中的每一列数据
        Map<String, List<Map<String, Object>>> workbookData = new HashMap<>();

        int numberOfSheets = wb.getNumberOfSheets();
        //第二步，遍历工作簿，拿到每个工作表
        for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) {
            Sheet sheet = wb.getSheetAt(sheetNum);
            String sheetName = sheet.getSheetName();
            int firstRowIndex = sheet.getFirstRowNum();
            int lastRowIndex = sheet.getLastRowNum();
            //每个sheet数据的保存的地方
            List<Map<String, Object>> sheetData = new ArrayList();
            //保存每个表第一行数据，即表头的各个列名
            List<String> columnNameList = new ArrayList();
            //第三步，遍历工作表的行，拿到每一行数据
            for (int rowIndex = firstRowIndex; rowIndex <= lastRowIndex; rowIndex++) {
                Map<String, Object> map = new HashMap<>();
                Row row = sheet.getRow(rowIndex);
                if (row != null) {
                    int firstCellIndex = row.getFirstCellNum();
                    int lastCellIndex = row.getLastCellNum();

                    List<String> imageUrlList = new ArrayList<>();
                    // 判断用07还是03的方法获取图片
                    if (filePath.endsWith("xls")) {
                        // TODO: 03版本的
                    } else {
                        imageUrlList = this.getWorkbookPicBySingle((XSSFSheet) sheet);
                    }

                    //第四步，获取当前行的每个表格（cell）
                    for (int columnIndex = firstCellIndex; columnIndex < lastCellIndex; columnIndex++) {
                        Cell cell = row.getCell(columnIndex);
                        Object value = "";

                        if (cell == null) {
                            continue;
                        }
                        //表示第一行，表头
                        if (rowIndex == firstRowIndex) {
                            value = getCellValue(cell);
                            if (value != null) {
                                columnNameList.add(value.toString());
                            }
                        } else {
                            //检查表格内容数量和表头数量是否一致，不一致抛异常
                            if (lastCellIndex != columnNameList.size()) {
                                log.info("表头列数量和第" + rowIndex + "行的内容列数量不一致");
                            }
                            value = getCellValue(cell);
                            if (columnIndex >= columnNameList.size()) {
                                //比表头多余的数据列，舍弃
                                continue;
                            }
                            String columnName = columnNameList.get(columnIndex);
                            if (value == null || value.toString().trim().equals("")) {
                                value = "";
                            }
                            //如果是图片这一列，那么就用图片列表里的url替代value，“示意图”需要改成其他的图片列名称
                            if ("示意图".equals(columnName)) {
                                //rowIndex比图片列表的数量多一个表头
                                try {
                                    value = imageUrlList.get(rowIndex - 1);
                                } catch (Exception e) {
                                    value = "";
                                }
                            }
                            map.put(columnName, value);
                            map.put("rowNum", rowIndex + 1);
                        }
                    }
                }
                if (!map.isEmpty() && map.values() != null) {
                    sheetData.add(map);
                }
            }
            workbookData.put(sheetName, sheetData);
        }
        return workbookData;
    }

    public Object getCellValue(Cell cell) {
        Object cellValue = null;
        if (cell == null) {
            return cellValue;
        } else {
            switch (cell.getCellType().getCode()) {
                case 0:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                        cellValue = formater.format(date);
                    } else {
                        DecimalFormat df = new DecimalFormat("0");
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    break;
                case 1:
                    cellValue = cell.getStringCellValue().trim();
                    break;
                case 2:
                    cellValue = cell.getCellFormula();
                    break;
                case 3:
                    cellValue = null;
                    break;
                case 4:
                    cellValue = cell.getBooleanCellValue();
                    break;
                default:
                    cellValue = "";
            }
            return cellValue;
        }
    }

    /**
     * xlsx 获取sheet的图片并且上传到fastdfs 保存url到列表，单线程方式
     *
     * @param sheet 工作表
     * @return {@link List<String>} 工作表的图片url列表
     */
    public List<String> getWorkbookPicBySingle(XSSFSheet sheet) {
        List<String> resultList = new ArrayList<>();
        XSSFDrawing drawing = sheet.getDrawingPatriarch();
        List<XSSFShape> shapes = drawing.getShapes();
        Map<Integer, String> map = new LinkedHashMap();
        for (XSSFShape xssfShape : shapes) {

            XSSFPicture pic = (XSSFPicture) xssfShape;
            String ext = pic.getShapeName();
            String mimeType = pic.getPictureData().getMimeType();
            //mimeType长这样：image/png
            String fullImageName = ext + "." + mimeType.substring(mimeType.lastIndexOf("/") + 1);
            File file = new File(PoiExcelUtil.class.getResource("/").getPath() + fullImageName);

            byte[] data = pic.getPictureData().getData();

            try (OutputStream out = new FileOutputStream(file)) {
                out.write(data);
            } catch (Exception e) {
                log.error("获取excel图片异常：{}", e);
            }
            StorePath storePath = fastDfsClient.uploadFile(file);
            String fullPath = storePath.getFullPath();
            String imageUrl = webPathPre + "/" + fullPath;
            //resultList.add(fullImageName + "," + imageUrl);

            XSSFAnchor anchor = pic.getAnchor();
            if (anchor instanceof XSSFClientAnchor) {
                int row1 = ((XSSFClientAnchor) anchor).getRow1();
                map.put(row1, fullImageName + "," + imageUrl);
            }

        }

        int size = map.size();
        for (int i = 1; i <= size; i++) {
            resultList.add(map.get(i));
        }

        return resultList;
    }

}