EasyExcel一单元格导出多图片

这里导出excel需要使用阿里的EasyExcel组件，项目需要引入EasyExcel依赖,版本可自己选择，博主这里使用2.2.7版本


	com.alibaba
	easyexcel
	2.2.7

说到多图片导出，先要说下EasyExel的单图片导出，单图片导出比较简单，
直接使用下EasyExel自带的类UrlImageConverter（Converter的子类）实现，ExcelModel设置如下：

@Data
@ContentRowHeight(20)
@HeadRowHeight(20)
@ColumnWidth(25)
@HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER)
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)
public class ExcelDemo {

    @ExcelProperty(value = "图片", converter = UrlImageConverter.class)
    private URL imageUrl;
    
}

我们先来看一下UrlImageConverter的实现：

package com.alibaba.excel.converters.url;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.IoUtils;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;

public class UrlImageConverter implements Converter {
    public UrlImageConverter() {
    }

    public Class supportJavaTypeKey() {
        return URL.class;
    }

    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.IMAGE;
    }

    public URL convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        throw new UnsupportedOperationException("Cannot convert images to url.");
    }

    // 核心方法 读取图片url的io流
    public CellData convertToExcelData(URL value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws IOException {
        InputStream inputStream = null;

        CellData var6;
        try {
            inputStream = value.openStream();
            byte[] bytes = IoUtils.toByteArray(inputStream);
            var6 = new CellData(bytes);
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }

        }

        return var6;
    }
}

其实转化的方法很简单，只是读取图片url的io，写进Excel的cellData对象里，那多图片的转化只需要加个for循环一次读取转化就行里吧？我们来自己实现下多图片的转换器（实现easyexcel的扩展接口Converter）

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.IoUtils;

import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;


public class ListUrlConverterUtil implements Converter> {
    @Override
    public Class supportJavaTypeKey() {
        return List.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        
        return CellDataTypeEnum.IMAGE;
    }

    @Override
    public List convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    @Override
    public CellData convertToExcelData(List value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 这里进行对数据实体类URL集合处理
        List data = new ArrayList<>();
        // for 循环一次读取
        for (URL url : value) {
            InputStream inputStream = null;
            try {
                inputStream = url.openStream();
                byte[] bytes = IoUtils.toByteArray(inputStream);
                data.add(new CellData(bytes));
            } catch (Exception e) {
                throw new IOException("导出excel图片读取异常");
            } finally {
                if (inputStream != null){
                    inputStream.close();
                }
            }
        } 
        
        // 这种方式并不能返回一个List,所以只好通过CellData cellData = new CellData(data);将这个list对象塞到返回值CellData对象的data属性中；
        CellData cellData = new CellData(data);
        cellData.setType(CellDataTypeEnum.IMAGE);
        return cellData;
    }
}

ExcelModel设置如下：

@Data
@ContentRowHeight(20)
@HeadRowHeight(20)
@ColumnWidth(25)
@HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER)
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)
public class ExcelDemo {

    @ExcelProperty(value = "图片", converter = ListUrlConverterUtil.class)
    private List imageUrls;

}

图片统一大小返回, 这里需要实现CellWriteHandler 的afterCellDataConverted 方法，将 CellData 的 type 设置成 EMPTY ,这样 EasyExcel 不会帮忙填充该单元格的数据，然后实现 CellWriteHandler 的 afterCellDispose 方法，将图片信息填充上去

注意：每列的单元格图片张数不确定，单元格宽度需按照张数最多的长度来设置

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Units;

import java.util.ArrayList;
import java.util.List;


public class CustomImageModifyHandler implements CellWriteHandler {
    private List repeats = new ArrayList<>();
    // 单元格的图片最大张数（每列的单元格图片张数不确定，单元格宽度需按照张数最多的长度来设置）
    private Integer maxDataSize = 0;
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //  在 数据转换成功后 不是头就把类型设置成空
        if (isHead) {
            return;
        }
        //将要插入图片的单元格的type设置为空,下面再填充图片
        if(cellData.getImageValue()!=null||cellData.getData() instanceof ArrayList){
            cellData.setType(CellDataTypeEnum.EMPTY);
        }
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //  在 单元格写入完毕后 ，自己填充图片
        if (isHead || CollectionUtils.isEmpty(cellDataList)) {
            return;
        }
        Boolean listFlag = false;
        ArrayList data = null;
        Sheet sheet = cell.getSheet();
        // 此处为ListUrlConverterUtil的返回值
        if (cellDataList.get(0).getData() instanceof ArrayList){
            data = (ArrayList) cellDataList.get(0).getData();
            if (CollectionUtils.isEmpty(data)) {
                return;
            }
            if (data.get(0) instanceof CellData){
                CellData cellData = (CellData) data.get(0);
                if (cellData.getImageValue() == null){
                    return;
                }else {
                    listFlag = true;
                }
            }
        }
        if (!listFlag && cellDataList.get(0).getImageValue() == null){
            return;
        }
        String key = cell.getRowIndex() + "_" + cell.getColumnIndex();
        if (repeats.contains(key)){
            return;
        }
        repeats.add(key);
        if (data.size() > maxDataSize) {
            maxDataSize = data.size();
        }
        // 默认要导出的图片大小为175*175px,175px的行高大约是900,175px列宽大概是25*256
        sheet.getRow(cell.getRowIndex()).setHeight((short)1800);
        sheet.setColumnWidth(cell.getColumnIndex(),listFlag?25*256*maxDataSize:25*256);

        if (listFlag){
            for (int i = 0; i < data.size(); i++) {
                CellData cellData= (CellData) data.get(i);
                if(cellData.getImageValue()==null){
                    continue;
                }
                this.insertImage(sheet,cell,cellData.getImageValue(),i);
            }
        }else {
            // cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入 一定只有一个
            this.insertImage(sheet,cell,cellDataList.get(0).getImageValue(),0);
        }

    }

    private void insertImage(Sheet sheet,Cell cell,byte[] pictureData,int i){
        int picWidth = Units.pixelToEMU(175);
        int index = sheet.getWorkbook().addPicture(pictureData, 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(picWidth*i);
        anchor.setDx2(picWidth+picWidth*i);
        anchor.setDy1(0);
        anchor.setDy2(0);
        //设置图片位置
        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);
    }
}

使用EasyExcel的write方法将数据写到excel中，这里比较重要的是需要.registerWriteHandler，这里需要将自己写的图片拦截器注入进去

EasyExcel.write(outputStream, ExcelDemo.class)
    .registerWriteHandler(new CustomImageModifyHandler()).sheet().doWrite(查询出来并且完成数据转换的Excel数据集合)

导出的excel图片结果如下（博主这里每列的图片张数不固定，单元格宽度按张数最多的那一列长度设置）：

EasyExcel一单元格导出多图片

Java相关栏目本月热门文章