java 填充数据和图片到excel表格的单元格中

maven包

<dependency>
            <groupId>e-iceblue</groupId>
            <artifactId>spire.office.free</artifactId>
            <version>3.9.1</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/src/main/resources/lib/spire.office.free-3.9.0.jar</systemPath>
        </dependency>

代码

/**
     * @param val      传入的{条形码}或者{二维码}
     * @param imgpath  图片路径
     * @param filePath 模板路径
     * @param outPath  输出的路径
     * @throws Exception
     */
    public static void write(String val, String imgpath, String filePath, String outPath) throws Exception {
        if (!StringUtils.isEmpty(val) && !val.equals("{条形码}") && !val.equals("{二维码}")) {
            System.out.println("进来了 不匹配 跳出");
            return;
        }
        //载Excel示例文档
        Workbook workbook = new Workbook();
        workbook.loadFromFile(filePath);
        //获取第一张工作表
        Worksheet worksheet = workbook.getWorksheets().get(0);
        if (!StringUtils.isEmpty(val) && val.equals("{条形码}")) {
            //查找文档中的字符串“图片”
            CellRange[] ranges = worksheet.findAllString(val, false, false);
            for (int i = 0; i < ranges.length; i++) {
                //重置文本为空
                ranges[0].setText("");
                //获取单元格所在的行列
                int row = ranges[0].getRow();
                int column = ranges[0].getColumn();
                double width = (ranges[0].getColumnWidth() * 7) * 5;
                double height = ranges[0].getRowHeight() * 1.3;
                int newWidth = (int) width;
                int newheight = (int) height;
                //透明背景
                transferAlpha2File(imgpath, imgpath);

//            添加图片到获取的单元格
                ExcelPicture picture = worksheet.getPictures().add(row, column, imgpath);

//            picture.setCropTopOffset(50);
                picture.setTopRowOffset(5);//设置填充图片后的单元格 内边距
                picture.setLeftColumnOffset(10);


//            保存文档
                workbook.saveToFile(outPath, ExcelVersion.Version2013);
                return;
            }
            return;
        }
        if (!StringUtils.isEmpty(val) && val.equals("{二维码}")) {
            CellRange[] ranges = worksheet.findAllString(val, false, false);
            for (int i = 0; i < ranges.length; i++) {
                //重置文本为空
                ranges[0].setText("");
                //获取单元格所在的行列
                int row = ranges[0].getRow();
                int column = ranges[0].getColumn();
                transferAlpha2File(imgpath, imgpath);
                ExcelPicture picture = worksheet.getPictures().add(row, column, imgpath);

                //保存文档
                workbook.saveToFile(outPath, ExcelVersion.Version2013);

                return;
            }
            return;
        }
    }

/**
     * @param imgSrc    原图片地址
     * @param imgTarget 新图片地址
     * @return 转换结果ture or false
     */
    public static boolean transferAlpha2File(String imgSrc, String imgTarget) {
        File file = new File(imgSrc);
        InputStream is = null;
        boolean result = false;
        try {
            is = new FileInputStream(file);
            // 如果是MultipartFile类型，那么自身也有转换成流的方法：is = file.getInputStream();
            BufferedImage bi = ImageIO.read(is);
            Image image = (Image) bi;
            ImageIcon imageIcon = new ImageIcon(image);
            BufferedImage bufferedImage = new BufferedImage(imageIcon.getIconWidth(), imageIcon.getIconHeight(), BufferedImage.TYPE_4BYTE_ABGR);
            Graphics2D g2D = (Graphics2D) bufferedImage.getGraphics();
            g2D.drawImage(imageIcon.getImage(), 0, 0, imageIcon.getImageObserver());
            int alpha = 0;
            for (int j1 = bufferedImage.getMinY(); j1 < bufferedImage.getHeight(); j1++) {
                for (int j2 = bufferedImage.getMinX(); j2 < bufferedImage.getWidth(); j2++) {
                    int rgb = bufferedImage.getRGB(j2, j1);
                    int R = (rgb & 0xff0000) >> 16;
                    int G = (rgb & 0xff00) >> 8;
                    int B = (rgb & 0xff);
                    if (((255 - R) < 30) && ((255 - G) < 30) && ((255 - B) < 30)) {
                        rgb = ((alpha + 1) << 24) | (rgb & 0x00ffffff);
                    }
                    bufferedImage.setRGB(j2, j1, rgb);
                }
            }
            g2D.drawImage(bufferedImage, 0, 0, imageIcon.getImageObserver());
            result = ImageIO.write(bufferedImage, "png", new File(imgTarget));// 直接输出文件
//       result = ImageIO.write(bufferedImage, "jpg", new File(imgTarget));// 直接输出文件
        } catch (Exception e) {
            e.printStackTrace();
            result = false;
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                }
            }
        }
        return result;
    }

 

 效果图 如下 填充前

 

填充后 

 

 

把这个模板提出 转成透明背景的图片