ruoyi使用easypoi工具类实现Excel带图导入

• HTML

<a class="btn btn-warning" onclick="easyImportExcel()">
   <i class="fa fa-download"></i> 导入
</a>
<input type="file" id="file" onchange="easyImportExcelDo()"
     style="filter:alpha(opacity=0);opacity:0;width: 0;height: 0;"/>

• JavaScript

//使用EasyPoi方式导入
    function easyImportExcel(fileId) {
        var currentId = $.common.isEmpty(fileId) ? 'file' : fileId;
        $("#" + currentId).trigger("click");
    }
//执行EasyPoi方式导入
    function easyImportExcelDo(fileId) {
        $.modal.loading("正在导入，请等待");
        var currentId = $.common.isEmpty(fileId) ? 'file' : fileId;
        var file = $("#" + currentId)[0].files[0];
        if (file == 'undefined' || file == undefined) {
            return;
        }
        var myform = new FormData();
        myform.append('file', file);
        /*$.modal.loading("正在导入数据，请稍后...");*/
        $.ajax({
            url: prefix + "/import",
            type: "POST",
            data: myform,
            contentType: false,
            processData: false,
            success: function (result) {
                if (result.code == 0) {
                    //清空附件
                    $("#" + currentId).val("");
                    $.modal.closeLoading();
                    $.modal.msgSuccess(result.msg);
                    $.table.refresh();
                } else {
                    $.modal.msgError(result.msg);
                }
                //清空附件
                $("#" + currentId).val("");
                $.modal.closeLoading();
            },
            error: function (data) {
                console.log(data);
                //清空附件
                $("#" + currentId).val("");
                $.modal.closeLoading();
            }
        });
    }

• controller层

import static com.ruoyi.common.utils.UploadFileToOSSUtils.uploadImgByByte;
import static com.ruoyi.common.utils.poi.EasyExcelUtil.getPictureFromExcel;
    /**
     * 导入商品信息
     *
     * @param file
     * @return
     */
    @PostMapping("/import")
    @ResponseBody
    public AjaxResult importExcel(@RequestParam("file") MultipartFile file) throws Exception {
        List<Goods> itemList = EasyExcelUtil.importExcel(file, 1, 1, Goods.class);
        if (itemList == null) {
            return error(AjaxResult.Type.WARN, "请选择正确的导入模板");
        }
        int insertCount = 0;
        //获取图片集合 ** 工具类：EasyExcelUtil
        List<Map<String, PictureData>> pictureList = getPictureFromExcel(file);
        int row = 2;
        for (Goods item : itemList) {
            Shop shop = shopService.selectShopByName(item.getShopName());
            GoodsType goodstype = goodsTypeService.selectGoodsTypeByTypenameAndShopname(item.getTypeName(), item.getShopName());
            if (null == shop) {
                return error(AjaxResult.Type.WARN, item.getName() + "这件商品信息错误! 原因：未找到对应店铺");
            }
            if (null == goodstype) {
                return error(AjaxResult.Type.WARN, item.getName() + "这件商品信息错误! 原因：未找到对应的分类");
            }
            PictureData img = pictureList.get(0).get("0_" + row + "_4");
            String imgUrl = uploadImgByByte(img.getData());
            item.setImgUrl(imgUrl);
            row++;
        }
        for (Goods item : itemList) {
            Shop shop = shopService.selectShopByName(item.getShopName());
            GoodsType goodstype = goodsTypeService.selectGoodsTypeByTypenameAndShopname(item.getTypeName(), item.getShopName());
            int i = goodsService.importGoods(item, shop, goodstype);
            insertCount+=i;
        }
        return success("导入成功,新增【" + insertCount + "】条记录！");
    }

• 工具类

/**
     * 获取excel表中的图片
     *
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     * @throws EncryptedDocumentException
     * @Param fis 文件输入流
     * @Param sheetNum Excel表中的sheet编号
     */
    public static List<Map<String, PictureData>> getPictureFromExcel(MultipartFile file) throws EncryptedDocumentException, InvalidFormatException, IOException {
        InputStream input = file.getInputStream();

        // 获取文件后缀名
        String fileExt =  file.getName().substring(file.getName().lastIndexOf(".") + 1);

        // 创建Workbook
        Workbook wb = null;

        // 创建sheet
        Sheet sheet = null;

        //根据后缀判断excel 2003 or 2007+
        if (fileExt.equals("xls")) {
            wb = (HSSFWorkbook) WorkbookFactory.create(input);
        } else {
            wb = new XSSFWorkbook(input);
        }

        //获取excel sheet总数
        int sheetNumbers = wb.getNumberOfSheets();

        // sheet list
        List<Map<String, PictureData>> sheetList = new ArrayList<Map<String, PictureData>>();

        // 循环sheet
        for (int i = 0; i < sheetNumbers; i++) {

            sheet = wb.getSheetAt(i);
            // map等待存储excel图片
            Map<String, PictureData> sheetIndexPicMap;

            // 判断用07还是03的方法获取图片
            if (fileExt.equals("xls")) {
                sheetIndexPicMap = getSheetPictrues03(i, (HSSFSheet) sheet, (HSSFWorkbook) wb);
            } else {
                sheetIndexPicMap = getSheetPictrues07(i, (XSSFSheet) sheet, (XSSFWorkbook) wb);
            }
            // 将当前sheet图片map存入list
            sheetList.add(sheetIndexPicMap);
        }
        return sheetList;
    }

    /**
     * 获取Excel2003图片
     * @param sheetNum 当前sheet编号
     * @param sheet 当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     * @throws IOException
     */
    public static Map<String, PictureData> getSheetPictrues03(int sheetNum, HSSFSheet sheet, HSSFWorkbook workbook) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        if (pictures.size() != 0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    String picIndex = String.valueOf(sheetNum) + "_"
                            + String.valueOf(anchor.getRow1()) + "_"
                            + String.valueOf(anchor.getCol1());
                    sheetIndexPicMap.put(picIndex, picData);
                }
            }
            return sheetIndexPicMap;
        } else {
            return null;
        }
    }

    /**
     * 获取Excel2007图片
     * @param sheetNum 当前sheet编号
     * @param sheet 当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     */
    public static Map<String, PictureData> getSheetPictrues07(int sheetNum,XSSFSheet sheet, XSSFWorkbook workbook) {
        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;
                    XSSFClientAnchor anchor = pic.getPreferredSize();
                    CTMarker ctMarker = anchor.getFrom();
                    String picIndex = String.valueOf(sheetNum) + "_"
                            + ctMarker.getRow() + "_" + ctMarker.getCol();
                    sheetIndexPicMap.put(picIndex, pic.getPictureData());
                }
            }
        }
        return sheetIndexPicMap;
    }

• 上传图片工具类

/**
     * 上传图片
     *
     * @return
     * @author xxx 时间：2020年03月03日
     */
    public static String uploadImgByByte(byte[] img) {
        InputStream sbs = new ByteArrayInputStream(img);
        OSSClient ossClient = new OSSClient(endpoint, accessKeyId, accessKeySecret);
        String key = UUID.randomUUID().toString() + System.currentTimeMillis() + ".png";
        ossClient.putObject(bucketName, key, sbs);
        OSSObject object = ossClient.getObject("zhuxing-public", key);
        ossClient.shutdown();
        String uri = object.getResponse().getUri();
        if (uri.contains("http")) {
            uri = uri.replace("http", "https");
        }
        return uri;
    }