关于excel导入带图片

public String importExcel(MultipartFile file) {

        if (file == null) {
            throw new CustomException("当前文件为空");
        }
        //判断文件是否为xls以及xlsx表格
        String fileName = file.getOriginalFilename();
        if (StrUtil.isNotEmpty(fileName) && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new CustomException("上传文件格式错误，请上传后缀为.xlsx的文件");
        }
        try {
            //hutool读取excel工具
            ExcelReader reader = cn.hutool.poi.excel.ExcelUtil.getReader(file.getInputStream());
            //将文件转成XSSFWorkbook工作簿
            XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
            //获取工作薄中第一个excel表格
            XSSFSheet sheet = wb.getSheetAt(0);
            //核心：：：获取excel表格中所有图片,处理图片上传到oss  key:行号-列号
            Map<String, XSSFPictureData> pictures = NewExcelUtils.getPictures(sheet);
            Map<String, String> map = new HashMap<>();
            Set<String> keySet = pictures.keySet();
            for (String key : keySet) {
                XSSFPictureData xssfPictureData = pictures.get(key);
                byte[] data = xssfPictureData.getData();
                InputStream inputStream = new ByteArrayInputStream(data);

                //图片压缩
                byte[] bytes1 = PicUtils.compressOfQuality(inputStream, 0.7f);
                InputStream in = new ByteArrayInputStream(bytes1);

                MultipartFile file1 = new MockMultipartFile(key + ".jpg", key + ".jpg", null, in);
                String imgUrl = aliOssUtil.uploadImg(file1);
                log.info("excel导入带图片,图片链接：{}", imgUrl);
                map.put(key, imgUrl);
            }
            //行数
            int rowCount = reader.getRowCount();
            //列数
            int columnCount = reader.getColumnCount();
            int startIndex = 1;
            int endIndex = 500;
            int bathSize = 500;
            int batch = 1;
            List<List<Object>> resdAll = null;
            while (endIndex < rowCount) {
                resdAll = reader.read(startIndex, endIndex);
                //保存数据库
                eventSuperiorService.saveEventSuperior(resdAll, map);

                startIndex = endIndex + 1;
                batch++;
                endIndex = batch * bathSize;
                if (endIndex > rowCount) {
                    endIndex = rowCount;
                }
            }
            resdAll = reader.read(startIndex, endIndex);
            //保存数据库
            eventSuperiorService.saveEventSuperior(resdAll, map);

        } catch (IOException e) {
            log.error(e.getMessage());
            throw new CustomException("导入出错");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "导入成功";
    }

/**
     * 导入保存
     *
     * @param resdAll
     * @param map
     */
    @Override
    public void saveEventSuperior(List<List<Object>> resdAll, Map<String, String> map) {
        List<EventSuperior> eventSuperiors = new ArrayList<>();
        for (int i = 0; i < resdAll.size(); i++) {
            EventSuperior eventSuperior = new EventSuperior();
            //excel中一行的数据
            List<Object> list = resdAll.get(i);
            //获取图片行号-列号集合
            Set<String> keySet = map.keySet();
            StringBuilder sb = new StringBuilder();
            for (String key : keySet) {
                Integer row = Integer.valueOf(key.substring(0, key.lastIndexOf("-")));
                //当图片中的行号=当前行时
                if (row == (i + 1)) {
                    sb.append(map.get(key)).append(",");
                }
            }
            if (StringUtils.isNotBlank(sb)) {
                String imageUrl = sb.substring(0, sb.lastIndexOf(","));
                //保存图片路径
                eventSuperior.setUploadImg(imageUrl);
            }
            
            eventSuperior.setOverseeCode(list.get(0).toString());
          
            eventSuperior.setLevel(list.get(1).toString());
            
            eventSuperior.setReplyDate(list.get(2).toString());
            
            eventSuperior.setFunctionDept(list.get(3).toString());
            
            String deptName = list.get(4).toString();
            eventSuperior.setCommunity(deptName);
            
            eventSuperior.setEventStatus(list.get(5) == null ? null : list.get(5).toString());
            eventSuperior.setQuestionDescribe(list.get(6).toString());
            
            eventSuperior.setAddressDescribe(list.get(7).toString());
            
            eventSuperior.setBigCategory(list.get(8).toString());
            
            if (list.get(9) != null && list.get(9) != "") {
                eventSuperior.setLon(Double.parseDouble(list.get(9).toString()));
            }
            
            if (list.get(10) != null && list.get(10) != "") {
                eventSuperior.setLat(Double.parseDouble(list.get(10).toString()));
            }
            
            eventSuperior.setAddTime(LocalDateTime.now());
            
            eventSuperior.setResult(0);

            //添加部门链条
            if (StringUtils.isNotBlank(deptName)) {
                Address address = addressService.getOneByParentIdAndName(null, deptName);
                if (address != null) {
                    String chainName = StringUtils.isNotBlank(address.getChainNames()) ? address.getChainNames().replaceAll(",", "") + address.getName() : address.getName();
                    eventSuperior.setDeptId(address.getId());
                    eventSuperior.setChainName(chainName);
                }
            }

            eventSuperiors.add(eventSuperior);
        }
        this.saveBatch(eventSuperiors);
    }

/**
 * 新excel工具类
 * 2022.10.10
 */
public class NewExcelUtils {

    /**
     * @param sheetTitle excel表格名称
     * @param head       excel表头
     * @param result     excel表格数据
     * @return
     */
    public static SXSSFWorkbook getSxssfwbExcel(SXSSFWorkbook wb, SXSSFSheet sheet, String sheetTitle, List<Object> head, List<List<String>> result) {
        if (wb == null) {
            //创建一个工作薄
            wb = new SXSSFWorkbook();
            //int sheetNum = 0;// 记录额外创建的sheet数量
            //创建excel表
            sheet = wb.createSheet(sheetTitle);
            // wb.setSheetName(sheetNum, sheetTitle+sheetNum);
        }
        sheet.setDefaultColumnWidth(15);
        sheet.setDefaultRowHeight((short) (8 * 128));

        int rownum = 0;
        Row row = sheet.createRow(rownum);
        Cell cell;
        // 创建标题,此时row=0,即第一行
        for (int j = 0; j < head.size(); j++) {
            cell = row.createCell(j);
            cell.setCellValue(head.get(j).toString());
        }

        // 遍历集合数据，创建excel内容,产生数据行
        if (result != null) {
            int index = 1;
            List<String> m = null;
            for (int i = 0; i < result.size(); i++) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                m = result.get(i);
                for (String str : m) {
                    row.createCell((short) cellIndex).setCellValue(str);
                    cellIndex++;
                }
                index++;
            }
        }
        return wb;
    }

    /**
     * excel根据指定行列放入图片
     *
     * @param workbook 工作薄
     * @param sheet    excel表
     * @param fileUrl  图片链接
     * @param row      表格行
     * @param col      表格列
     * @param scaleX   图片在表格中横坐标 默认1.0
     * @param scaleY   图片在表格中横坐标 默认1.0
     */
    public static void toLeadPicture(SXSSFWorkbook workbook, SXSSFSheet sheet, String fileUrl, int row, int col, double scaleX, double scaleY) {
        try {
            //防止URL地址有中文，解码
            String head = fileUrl.substring(0, fileUrl.lastIndexOf("/") + 1);
            String suffix = fileUrl.substring(fileUrl.lastIndexOf("/") + 1);
            String link = head + URLEncoder.encode(suffix, "UTF-8");
            // 构造URL
            URL url = new URL(link);
            // 打开连接
            URLConnection con = url.openConnection();
            //设置请求超时为5s
            con.setConnectTimeout(800000 * 1000);
            // 输入流
            InputStream is = con.getInputStream();

            //图片压缩
            byte[] bytes1 = PicUtils.compressOfQuality(is, 0.1f);
            InputStream in = new ByteArrayInputStream(bytes1);

            byte[] bytes = IOUtils.toByteArray(in);
            @SuppressWarnings("static-access")
            //图片输入流转成字节数组放到workbook 中
            int pictureIdx = workbook.addPicture(bytes, workbook.PICTURE_TYPE_PNG);
            CreationHelper helper = workbook.getCreationHelper();
            Drawing drawing = sheet.createDrawingPatriarch();
            ClientAnchor anchor = helper.createClientAnchor();
            // 图片插入坐标
            anchor.setCol1(col);
            anchor.setRow1(row);
            // 插入图片
            Picture pict = drawing.createPicture(anchor, pictureIdx);
            pict.resize(scaleX, scaleY);
            // 设置宽度  这里如果设置图片的宽度，则excel表格设置的参数将失效
            //      sheet.setColumnWidth(row, 10 * 256);
            // 设置高度 这里如果设置图片的高度，则excel表格设置的参数将失效
            Row rowl = sheet.createRow(col);
//            rowl.setHeight((short) (30 * 20));

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * excel导入
     * 获取excel表格中图片
     *
     * @param xssfSheet
     * @return
     */
    public static Map<String, XSSFPictureData> getPictures(XSSFSheet xssfSheet) {

        Map<String, XSSFPictureData> map = new HashMap<>();
        List<XSSFShape> list = xssfSheet.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);
            byte[] data = pdata.getData();
            InputStream inputStream = new ByteArrayInputStream(data);
            try {
                MockMultipartFile file = new MockMultipartFile(key, inputStream);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return map;
    }
}