EasyExcel将图片填充进表格模板

实现目标

使用EasyExcel将图片填充进表格模板中的一个合并单元格内，铺满并控制图片与合并单元格间距

实现步骤

1.在maven中添加EasyExcel的库

        <!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

        <dependency>
            <groupId>com.google.zxing</groupId>
            <artifactId>core</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>com.google.zxing</groupId>
            <artifactId>javase</artifactId>
            <version>3.5.2</version>
        </dependency>

2.创建模板与实体类

注意模板中的字段名需要与实体类的成员变量一致

模板填充实体类：

@Data
public class PropertyLabelDTO {
    private String propertyUseName;
    private String propertyId;
    private String propertyDeviceId;
    private String propertyDepartment;
    private String propertyCurrentLiable;

    private WriteCellData<Void> propertyQR;

}

WriteCellData类型用于控制单元格的填充形式

3.新建实体类对象并设置图片格式

 //设备使用明细2资产标签
    public static PropertyLabelDTO detail2Label(ProPropertyDetail detail,String currentDept,byte[]QRPict){
        PropertyLabelDTO labelDTO=new PropertyLabelDTO();
        labelDTO.setPropertyId(detail.getPropertyId());
        labelDTO.setPropertyDeviceId(detail.getPropertyDeviceId());
        labelDTO.setPropertyCurrentLiable(detail.getPropertyCurrentLiable());
        labelDTO.setPropertyDepartment(currentDept);
        labelDTO.setPropertyUseName(detail.getPropertyUseName());
        WriteCellData<Void> writeCellData = new WriteCellData<>();
        // 可以放入多个图片
        List<ImageData> imageDataList = new ArrayList<>();
        ImageData imageData = new ImageData();
        // 放入2进制图片
        imageData.setImage(QRPict);
        // 图片类型
        imageData.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
        //合并单元格跨格处理
        imageData.setRelativeFirstRowIndex(0);
        imageData.setRelativeLastRowIndex(4);
        imageData.setRelativeFirstColumnIndex(0);
        imageData.setRelativeLastColumnIndex(2);
        // 上 右 下 左 需要留空
        imageData.setTop(5);
        imageData.setRight(5);
        imageData.setBottom(5);
        imageData.setLeft(5);

        imageDataList.add(imageData);
        writeCellData.setImageDataList(imageDataList);
        labelDTO.setPropertyQR(writeCellData);
        return labelDTO;
    }

下面这段代码用于控制插入图片的跨行列，以合并单元格左上角为(0,0)，在我给出的例子中合并单元格右下角的坐标可以理解为(4,2)

      	imageData.setRelativeFirstRowIndex(0);
        imageData.setRelativeLastRowIndex(4);
        imageData.setRelativeFirstColumnIndex(0);
        imageData.setRelativeLastColumnIndex(2);

4.生成二维码与执行模板填充

     // 生成二维码字节数组（长宽控制分辨率）
    public static byte[] getQRCodeImageBytes(String text, int width, int height)
            throws WriterException, IOException {
        QRCodeWriter qrCodeWriter = new QRCodeWriter();
        BitMatrix bitMatrix = qrCodeWriter.encode(text, BarcodeFormat.QR_CODE, width, height);

        ByteArrayOutputStream pngOutputStream = new ByteArrayOutputStream();
        MatrixToImageWriter.writeToStream(bitMatrix, "PNG", pngOutputStream);
        return pngOutputStream.toByteArray();
    }

    @Override
    public void exportPropertyLabel(HttpServletResponse response, ProPropertyDetail proPropertydetail)  {
        String QRText="testQRCode";
        String currentDepartment=proPropertydetailMapper.selectDeptNameByPropertyDepartment(proPropertydetail.getPropertyDepartment());
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=label.xlsx");

        ClassPathResource templateResource = new ClassPathResource("templates/propertyLabel.xlsx");
        try(InputStream templateInputStream = templateResource.getInputStream();)
        {
            byte[] qrCodeBytes = getQRCodeImageBytes(QRText, 5000, 5000);

            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    .withTemplate(templateInputStream)
                    .build();

            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            excelWriter.fill(detail2Label(proPropertydetail, currentDepartment,qrCodeBytes), writeSheet);
            excelWriter.finish();

        }
        catch (IOException | WriterException ioException){
            System.out.println("Error!"+ioException);
        }

    }

总结

这个例子插入的二维码图片采用的是二进制数组，如有其他图片形式可以参考EasyExcel官网文档

https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write