【无标题】

使用EasyExcel实现列表图片导出

使用EasyExcel实现列表图片对应值导出对应图片

1、设置转换器

public class 	ImageConverter implements Converter<String> {

    @Override
    public WriteCellData<?> convertToExcelData(@NotNull String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        InputStream inputStream = null;
        try {
            if(value.isEmpty()){
                return new WriteCellData<>("图片名称为空");
            }
            // 获取到web app路径
            String baseurl = this.getClass().getResource("/").getPath().replaceFirst("/","").replaceAll("WEB_INFO/classes/","");
            // 根据状态取图片
            File file = new File(baseurl+"/images/"+value+".png");
            inputStream = new FileInputStream(file);
            byte[] bytes = IoUtils.toByteArray(inputStream);
            ImageData imag = new ImageData();
            imag.setImage(bytes);
            imag.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
            //距离单元格顶部距离
            imag.setTop(1);
            //距离单元格底部距离
            imag.setBottom(1);
            //距离单元格左边距离
            imag.setLeft(1);
            //距离单元格右边距离
            imag.setRight(1);
            imag.setAnchorType(ClientAnchorData.AnchorType.DONT_MOVE_AND_RESIZE);
            WriteCellData cellData = new WriteCellData<>();
            ArrayList<ImageData> imageList = new ArrayList<>();
            imageList.add(imag);
            cellData.setImageDataList(imageList);
            cellData.setType(CellDataTypeEnum.EMPTY);
            return cellData;
        }catch (FileNotFoundException e){
            return new WriteCellData<>("状态错误");
        }finally {
            if(inputStream != null){
                inputStream.close();
            }
        }
    }

}

2.设置拦截器

public class CustomImageModifyHandler implements CellWriteHandler {

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if(isHead || ObjectUtil.isEmpty(cellData)){
            return;
        }
        Boolean listFlag = false;
        ArrayList data = null;
        Sheet sheet = cell.getSheet();
        //此处添加转换器返回的值
        if(cellData.getImageDataList() instanceof Arrays){
            data = (ArrayList) cellData.getImageDataList();
            if(CollectionUtils.isEmpty(data)){
                return;
            }
            if(data.get(0) instanceof CellData){
                ImageData imageData = (ImageData) data.get(0);
                if(imageData == null){
                    return;
                }else {
                    listFlag = true;
                }
            }
        }
        if(ObjectUtil.isEmpty(cellData.getImageDataList()) || !listFlag  && cellData.getImageDataList().get(0) == null ){
            return;
        }
        sheet.getRow(cell.getRowIndex()).setHeight((short) 900);
        sheet.setColumnWidth(cell.getColumnIndex(),(int)(21.8*256));
        this.insertImage(sheet,cell,cellData.getImageDataList().get(0));
        cellData.setImageDataList(null);
    }
    
    private void insertImage(Sheet sheet, Cell cell, ImageData dateValue) {
        int picwidth = Units.pixelToEMU(40);
        int pichigt = Units.pixelToEMU(1);
        int index = sheet.getWorkbook().addPicture(dateValue.getImage(), HSSFWorkbook.PICTURE_TYPE_PNG);
        Drawing drawing = sheet.getDrawingPatriarch();
        if(drawing == null){
            drawing = sheet.createDrawingPatriarch();
        }
        CreationHelper helper = sheet.getWorkbook().getCreationHelper();
        ClientAnchor anchor = helper.createClientAnchor();
        // 设置图片坐标
        anchor.setDx1(0);
        anchor.setDx2(picwidth);
        anchor.setDy1(pichigt);
        anchor.setDy2(1);
        //设置图片位置
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex());
        anchor.setRow1(cell.getRowIndex());
        anchor.setRow2(cell.getRowIndex()+1);
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        drawing.createPicture(anchor,index);
    }

}

3.业务代码

 ClassPathResource classPathResource = new ClassPathResource("/template/prepareInfo.xlsx");
        String filePath = "/prepareInfo/";
        FileUtil.mkdir(filePath + "prepareInfo/");
        String fileName = filePath + "prepareInfo/prepareInfo" + System.currentTimeMillis() + ".xlsx";
        @Cleanup ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(classPathResource.getInputStream()).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(new CustomImageModifyHandler()).build();
        WriteSheet writeSheet = EasyExcelFactory.writerSheet(0).build();
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        excelWriter.fill(prepareDownLoadVOList, fillConfig, writeSheet);
        excelWriter.finish();
        File file = new File(fileName);
        byte[] bytes = FileUtils.readFileToByteArray(file);
        return bytes;

4.效果图