Execl 文件导入（包含多张图片）存入数据，图片存入oss,

import com.aliyun.oss.OSS;
import com.aliyun.oss.OSSClientBuilder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.util.*;

/**
 * Execl 文件包含图片导入，存入oss
 */
@Slf4j
@Component
public class ImportExecl {

    @Value("${custom.aliyun.oss.accessKeyId}")
    private String accessKeyId;
    @Value("${custom.aliyun.oss.accessKeySecret}")
    private String accessKeySecret;
    @Value("${custom.aliyun.oss.oss_end_point}")
    private String endpoint;
    @Value("${custom.aliyun.oss.oss_bucket_name}")
    private String bucket;

    public Map<String, Map<Integer, Map<Integer, String>>> readExcelImageToMap(MultipartFile file) {
        // 声明所有页的集合
        Workbook wb = readExcel(file); // 读取上传excel
        if (wb == null) {
            return Collections.emptyMap();
        }
        // 获取总页数
        int pageSize = wb.getNumberOfSheets();

        // excel 里边的sheet 有多个时 限制只取第一个。
        if (pageSize > 1) {
            pageSize = 1;
        }
        Map<String, Map<Integer, Map<Integer, String>>> mapSheet = new HashMap<>();

        for (int i = 0; i < pageSize; i++) {
            // 声明当前页的行和列
            Map<Integer, Map<Integer, String>> map = new HashMap<>();
            // 获取当前页
            Sheet sheet = wb.getSheetAt(i);
            //获取sheet 名
            String sheetName = sheet.getSheetName();
            // log.info("当前sheet名称:" + sheetName);
            int rowSize = sheet.getPhysicalNumberOfRows(); // 获取不为空的总行数
            // 遍历每一行 从第一行开始计算 title 标签去掉
            for (int rowNum = 0; rowNum < rowSize; rowNum++) {
                Row row = sheet.getRow(rowNum);
                //int columnSize = row.getPhysicalNumberOfCells(); // 获取不为空的列个数
                int columnSize = row.getLastCellNum(); // 获取最后一个不为空的列是第几个
                // 声明当前列
                Map<Integer, String> columnMap = new HashMap<>();
                // 遍历一行中每列值  2023/4/14 17:27
                for (int cellNum = 0; cellNum < columnSize; cellNum++) {

                    //  System.out.println(row.getCell(cellNum));
                    Cell cell = row.getCell(cellNum);
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                        String value = cell.toString();
                        System.out.println(cellNum);
                        columnMap.put(cellNum, value);
                    }
                }
                // 添加当前行的内容 rowNum 代表第几行 value是列的内容 意思是第几行第几列的内容
                map.put(rowNum, columnMap);
            }

            // 声明当前页图片的集合
            Map<String, PictureData> sheetImageMap = null;
            // 获取图片
            try {
                //2003版本的excel，用.xls结尾

                sheetImageMap = getPicturesHSS((HSSFSheet) sheet);
            } catch (Exception ex) {
                log.error(ex.getMessage());
                try {
                    //2007版本的excel，用.xlsx结尾
                    sheetImageMap = getPicturesXSS((XSSFSheet) sheet);
                } catch (Exception e) {
                    log.error(ex.getMessage());
                }
            }
            //解析图片并上传到服务器 并设置该字段的值为字符串类型添加到map中 进行数据库上传
            Object key[] = sheetImageMap.keySet().toArray();
            //--------------
            List<String>ls=new ArrayList<>();
            for (int p = 0; p < sheetImageMap.size(); p++) {
                PictureData pic = sheetImageMap.get(key[p]);
                String picName = key[p].toString();
                byte[] picData = pic.getData();
                try {
                    // 上传图片到服务器并返回访问路径
                    String imageUrl = uploadPicture(picData);
                    if (StringUtils.isNotEmpty(imageUrl)) {
                        //按（行-列）规则解析key 并根据key 设置 某一行的某一列的 图片链接
                        String[] split = picName.split("-");
                        Integer rowIndex = Integer.parseInt(split[0].toString()), columnIndex = Integer.parseInt(split[1].toString());
                        //根据行下标 获取所有的列
                        Map<Integer, String> columns = map.get(rowIndex);
                        // 获取该位置已有的图片链接
                        String existingLinks = columns.get(columnIndex);
                        // 如果该位置之前已经存储了图片链接，则将现有的图片链接和新上传的图片链接用逗号分隔拼接成一个字符串
                        if (StringUtils.isNotEmpty(existingLinks)) {
                            imageUrl = existingLinks + "," + imageUrl;
                        }
                      // 存储图片链接
                        columns.put(columnIndex, imageUrl);
                    }
                } catch (Exception e) {
                    log.error(e.getMessage());
                }
            }
            // 添加当前页的所有内容
            mapSheet.put(sheetName, map);
        }
        return mapSheet;
    }

    /**
     * 读取excel
     *
     * @param file
     * @return
     */
    private static Workbook readExcel(MultipartFile file) {
        Workbook wb = null;
        ZipSecureFile.setMinInflateRatio(0);
        if (file == null) {
            return null;
        }
        String filename = file.getOriginalFilename();
        InputStream is = null;

        try {
            is = file.getInputStream();
            //2003版本的excel，用.xls结尾
            wb = new HSSFWorkbook(is);//得到工作簿
        } catch (Exception ex) {
            log.error(ex.getMessage());
            try {
                //2007版本的excel，用.xlsx结尾
                is = file.getInputStream();
                wb = new XSSFWorkbook(is);//得到工作簿
            } catch (IOException e) {
                log.error(ex.getMessage());
            }
        }

        return wb;
    }

    /**
     * 将图片上传，返回图片存储路径
     *
     * @param pictureData

     * @return
     * @throws IOException
     */
    public String uploadPicture(byte[] pictureData) throws IOException {

        String fileDir = "uploadxlspic/"; // 线下图片上传所在文件夹路径
        InputStream inputStream = new ByteArrayInputStream(pictureData);
        // 获取图片哈希值
        String imageHash = null;
        try {
            imageHash = getMD5Checksum(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //String fileName = picIndexName + "_" + imageHash; // 新图片文件名是 excel图片索引+图片哈希
        String newFileName = imageHash;//+ "." + ext; // 文件名（加后缀）
        // 将图片按路径和文件名上传到服务器
        inputStream = new ByteArrayInputStream(pictureData);
        File file = File.createTempFile(fileDir, newFileName);

        OutputStream outputStream = new FileOutputStream(file);
        IOUtils.copy(inputStream, outputStream);
        inputStream.close();
        outputStream.close();
        String savePath = upload(file, newFileName, fileDir);
        file.delete();
        return savePath;
    }

    public static String getMD5Checksum(InputStream is) throws NoSuchAlgorithmException, IOException {
        byte[] buffer = new byte[1024];
        MessageDigest complete = MessageDigest.getInstance("MD5");

        int numRead;
        do {
            numRead = is.read(buffer);
            if (numRead > 0) {
                complete.update(buffer, 0, numRead);
            }
        } while (numRead != -1);

        if (is != null) {
            is.close();
        }

        byte[] digest = complete.digest();
        String result = "";

        for (int i = 0; i < digest.length; ++i) {
            result = result + Integer.toString((digest[i] & 255) + 256, 16).substring(1);
        }

        return result;
    }

    public String upload(File file, String fileName, String rootPath) {
        // 存储路径
        String path = rootPath + "-" + "/" + fileName.replace("+", "_");
        // oss
        OSS client = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
        // 存储文件 阿里云oss
        client.putObject(bucket, path, file);
        return  "https://" + bucket + "." + endpoint.substring(endpoint.indexOf("//")+2) + "/" + path;
    }

    /**
     * 获取图片和位置 (xls)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPicturesHSS(HSSFSheet sheet) {
        Map<String, PictureData> map = new HashMap<String, PictureData>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
                PictureData pdata = picture.getPictureData();
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
                map.put(key, pdata);
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    private static Map<String, PictureData> getPicturesXSS(XSSFSheet sheet) {
       // Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
//        for (POIXMLDocumentPart dr : sheet.getRelations()) {
//            if (dr instanceof XSSFDrawing) {
//                XSSFDrawing drawing = (XSSFDrawing) dr;
//                List<XSSFShape> shapes = drawing.getShapes();
//                for (XSSFShape shape : shapes) {
//                    XSSFPicture pic = (XSSFPicture) shape;
//                    //解决图片空指针报错问题   2021-12-27
//                    XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
//                    //XSSFClientAnchor anchor = pic.getPreferredSize();
//                    CTMarker marker = anchor.getFrom();
//                    String key = marker.getRow() + "-" + marker.getCol(); // 行号-列号
//                    sheetIndexPicMap.put(key, pic.getPictureData());
//                }
//            }
//        }

        IdentityHashMap<String, PictureData> map = new IdentityHashMap<>();
        List<XSSFShape> list = sheet.getDrawingPatriarch().getShapes();
        for (XSSFShape shape : list) {
            XSSFPicture picture = (XSSFPicture) shape;
            XSSFClientAnchor xssfClientAnchor = (XSSFClientAnchor) picture.getAnchor();

            XSSFPictureData pdata = picture.getPictureData();
            // 行号-列号
            String key = xssfClientAnchor.getRow1() + "-" + xssfClientAnchor.getCol1();
            map.put(key,  pdata);
        }
        return map;
    }





























}