easyexcel不创建对象导出图片

前些天项目中要使用easyexcel,研究了一下,需要导出图片

官方文档:https://www.yuque.com/easyexcel/doc/write#cb1b271f

官方的文档中,导出图片的样例很简单,是通过对象来导出的,并支持五种类型,File,InputStream,byte[],URL,String

但是在项目中,很多时候,我们导出的excel列可能是不固定的,我们无法通过对象来导出,所以需要通过不创建对象的方式来导出

一.导出单个图片

导出单个图片很简单,即返回的数据中设置一下对应的类型,File,InputStream,byte[],URL都可以

@Test
    public void noModelWrite() {
        // 写法1
        String fileName = TestFileUtil.getPath() + "noModelWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName).head(head()).sheet("sheet1").doWrite(dataList());
    }

//动态生成列头
private List<List<String>> head() {
        List<List<String>> list = new ArrayList<>();
        for (int i = 0; i <10 ; i++) {
            List<String> head0 = new ArrayList<>();
            head0.add("标题"+i);
            list.add(head0);
        }
        return list;
    }

private List<List<Object>> data(){
        List<List<Object>> datas = new ArrayList<>();
        for (int i = 0; i <10 ; i++) {
            List<Object> list = new ArrayList<>();
            for (int j = 0; j <10 ; j++) {
                if((i==1&&j==5)||(i==2&&j==0)||(i==3&&j==7)){
                        URL image = null;
                        try {
                            image = new URL("https://dss3.baidu.com/-rVXeDTa2gU2pMbgoY3K/it/u=1542006958,16092133&fm=202&src=903&pairwise&mola=new&crop=v1");
                        } catch (MalformedURLException e) {
                            e.printStackTrace();
                        }
　　　　　　　　　　　　//这里可以根据需求传入File,InputStream,byte[],URL各种类型,如果要传String类型的话,就需要考虑和普通字符串的区分了
                    list.add(images);
                }else {
                    list.add("内容"+i+"-"+j);
                }
            }
            datas.add(list);
        }
        return  datas;
    }

二 导出多个图片到一个单元格中

对比获取数据的方法,需要返回一个List<List<Object>>类型的数据,第一个list好理解,是行,第二个list对应的就是列数据,也就是说,导出的excel每个单元格的数据是一个Object对象,所以自然想到,我往这个Object对象里塞一个List<URL>这样的对象是不是就行了呢,传入之后一运行,发现报错了,原因是找不到对应的Converter ,既然找不到,那就创建一个converter就是咯,于是就有了如下的实验:

package cn.weaver.ebuilder.teams.excel.style;

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.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

/**
 * @author niexh
 * @description
 * @date 2021/1/18 14:01
 */
public class ArrayListURLConverter implements Converter<ArrayList> {


    @Override
    public Class supportJavaTypeKey() {
        return ArrayList.class;
    }

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

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

    @Override
    public CellData convertToExcelData(ArrayList value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        List<CellData> list  = new ArrayList<>();
        for (Object o : value) {
            if(o instanceof URL){
                URL url = (URL) o;
                InputStream inputStream = null;
                try {
                    inputStream = url.openStream();
                    byte[] bytes = IoUtils.toByteArray(inputStream);
                    list.add(new CellData(bytes));
                } finally {
                    if (inputStream != null) {
                        inputStream.close();
                    }
                }
            }
        }
        CellData cellData = new CellData(list);
        cellData.setType(CellDataTypeEnum.IMAGE);
        return cellData ;
    }
}

但是注意标红的地方,我们会发现这个convertToExcelData的实现方法,返回值是一个CellData,它并不能识别并返回一个List<CellData>对象,实际上我们看CellWriteHandler接口的实现方法afterCellDispose中,是有一个参数是List<CellData> cellDataList,

官方问题处理样例:https://www.yuque.com/easyexcel/faq/wpedtd上给的注释是 :

// cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入 一定只有一个

所以那个参数可能是填充情况才有的,找了半天,这种方式并不能返回一个List<CellData> cellDataList,所以只好通过CellData cellData = new CellData(list);将这个list对象塞到返回值CellData对象的data属性中,下面再用

为了调整图片大小位置等,一般会实现CellWriteHandler 的afterCellDataConverted 方法，将 CellData 的 type 设置成 EMPTY ,这样 easyexcel 不会帮忙填充该单元格的数据，然后实现 CellWriteHandler 的 afterCellDispose 方法，将将图片信息填充上去。

package cn.weaver.ebuilder.teams.excel.style;

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.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Units;

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

/**
 * @author niexh
 * @description
 * @date 2021/1/4 18:27
 */
public class CustomImageCellWriteHandler implements CellWriteHandler {
    private List<String> repeats = new ArrayList<>();
    @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<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        Sheet sheet = cell.getSheet();
        if (isHead||cellDataList==null) {
            return;
        }
        boolean islist= false;
        ArrayList datas = null;
　　　　　//这就是上面ArrayListURLConverter的返回值,被塞到了cellDataList.get(0).getData中
        if(cellDataList.get(0).getData() instanceof ArrayList){
            datas = (ArrayList) cellDataList.get(0).getData();
            if(datas.get(0) instanceof CellData){
                CellData c= (CellData) datas.get(0);
                if(c.getImageValue()==null){
                    return;
                }else{
                    islist=true;
                }
            }
        }
        if(!islist&&cellDataList.get(0).getImageValue()==null){
            return;
        }
        String key = cell.getRowIndex()+"_"+cell.getColumnIndex();
        if (repeats.contains(key)){//afterCellDispose好像会被重复调用
            return;
        }
        repeats.add(key);
　　　　　//这里默认要导出的图片大小为60*60px,60px的行高大约是900,60px列宽大概是248*8,这个可以根据需求来调
        sheet.getRow(cell.getRowIndex()).setHeight((short) 900);
        sheet.setColumnWidth(cell.getColumnIndex(),islist?240*8*datas.size():240*8);
        if(islist){
            for (int i = 0; i < datas.size(); i++) {
                CellData cellData= (CellData) datas.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(60);
        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);
    }
}

下面是测试方法: 　

　@Test    
public void simpleWrite() {       
 EasyExcelUtil easyExcelUtil =new EasyExcelUtil();        
// 写法1
        String fileName = "C:\Users\Administrator\Desktop"+File.separator + "demo2.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
　　　　//        easyExcelUtil.formExportBuild(EasyExcel.write(fileName),getHeadStyles(),getContentStyles()).head(head()).
　　　　//                sheet("sheet1").doWrite(data());
        ExcelWriter writer = null;
        try {
　　　　　　　//这块是自己封装的一个简单样式设置,可以不用管,直接按官方文档上的来 EasyExcel.write(fileName);
            writer =easyExcelUtil.formExportBuild(EasyExcel.write(fileName),getHeadStyles(), getContentStyles())
　　　　　　　　　　　　//将自己的写的转化器和拦截器注册进去
　　　　　　　　　　　　.registerWriteHandler(new CustomImageCellWriteHandler()).registerConverter(new ArrayListURLConverter()).build();
            for (int i = 0; i < 5; i++) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(head()).build();
                for (int j = 0; j <1 ; j++) {
                    // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                    writer.write(data(), writeSheet);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            // 千万别忘记finish 会帮忙关闭流
            if (writer != null) {
                writer.finish();
            }
        }
    }


　

private List<List<Object>> data(){
    List<List<Object>> datas = new ArrayList<>();
    for (int i = 0; i <10 ; i++) {
        List<Object> list = new ArrayList<>();
        for (int j = 0; j <10 ; j++) {
            if((i==1&&j==5)||(i==2&&j==0)||(i==3&&j==7)){
                List<URL> images = new ArrayList<>();
                for (int k = 0; k <i ; k++) {
                    URL image = null;
                    try {
                        image = new URL("https://dss3.baidu.com/-rVXeDTa2gU2pMbgoY3K/it/u=1542006958,16092133&fm=202&src=903&pairwise&mola=new&crop=v1");
                        images.add(image);
                    } catch (MalformedURLException e) {
                        e.printStackTrace();
                    }
                }
                list.add(images);
            }else {
                list.add("内容"+i+"-"+j);
            }
        }
        datas.add(list);
    }
    return  datas;
}

这样就可以导出如下的excel

实际上,即使不重写Converter也是可以导出多张图片到一个单元格中的,在获取data()方法中,我们设置图片数据时,依然设置成字符串,比如数据库中存的fileid,或者图片地址等等,多个的话用逗号或者特定的符号来分隔,下面存在的问题就是,如何区分普通的字符串和这种图片类型的字符串,这个可以根据业务逻辑来自己设置,提供一种:

通过传入参数来指定图片所在的列

package cn.weaver.ebuilder.teams.excel.style;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.IoUtils;
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.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Units;

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

/**
 * @author niexh
 * @description
 * @description 泛微协同商务系统, 版权所有.
 * @date 2021/1/4 18:27
 */
public class CustomImageCellWriteHandler implements CellWriteHandler {
    /**
     * 图片字段在excel中的列索引
     */
    private List<Integer> imageColumnIndexs = new ArrayList<>();

    public CustomImageCellWriteHandler(List<Integer> imageColumnIndexs) {
        this.imageColumnIndexs = imageColumnIndexs;
    }

    private List<String> repeats = new ArrayList<>();
    @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;
        }
        if(imageColumnIndexs.contains(cell.getColumnIndex())){
            cellData.setType(CellDataTypeEnum.EMPTY);
        }
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        Sheet sheet = cell.getSheet();
        if (isHead||cellDataList==null) {
            return;
        }
        if(!imageColumnIndexs.contains(cell.getColumnIndex())){
            return;
        }
        String key = cell.getRowIndex()+"_"+cell.getColumnIndex();
        if (repeats.contains(key)){//afterCellDispose好像会被重复调用
            return;
        }
        repeats.add(key);
        CellData cellData = cellDataList.get(0);
        String fieldids = cellData.getStringValue();
        if("".equals(fieldids)){
            return;
        }
        String[] fieldidArr = fieldids.split(",");
        sheet.getRow(cell.getRowIndex()).setHeight((short) 900);
        sheet.setColumnWidth(cell.getColumnIndex(),240*8*fieldidArr.length);
        for (int i = 0; i < fieldidArr.length; i++) {
            this.insertImage(sheet,cell,fieldidArr[i],i);
        }
    }

    private void insertImage(Sheet sheet,Cell cell,String fieldid,int i){
        //导出每张图片要求固定大小60*60px,所以这里偏移量也是60px
        int picWidth = Units.pixelToEMU(60);
        int index = sheet.getWorkbook().addPicture(getImage(fieldid), 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);
    }

    private byte[] getImage(String fieldid){
        InputStream inputStream = null;
        byte[] bytes=null;
        try {
            //测试数据,随便写死的的,实际业务中可以根据fieldid或者是图片地址去取等等...
            URL url =new URL("https://dss3.baidu.com/-rVXeDTa2gU2pMbgoY3K/it/u=1542006958,16092133&fm=202&src=903&pairwise&mola=new&crop=v1");
            inputStream = url.openStream();
            bytes= IoUtils.toByteArray(inputStream);
            return bytes;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return bytes;
    }
}

然后在调用的时候传入imageColumnIndexs即可

EasyExcelUtil easyExcelUtil =new EasyExcelUtil();
// 写法1
String fileName = "C:\Users\Administrator\Desktop"+File.separator + "demo.xlsx";

List<Integer> imageColumnIndexs = new ArrayList<>();
        imageColumnIndexs.add(0);
        imageColumnIndexs.add(4);
        imageColumnIndexs.add(8);
        easyExcelUtil.formTemplateBuild(EasyExcel.write(fileName),getHeadStyles())
                .registerWriteHandler(new CustomImageCellWriteHandler(imageColumnIndexs)).head(head()).
//                registerWriteHandler(new CommentWriteHandler()).
        sheet("sheet1").doWrite(getImageData());

导出如下:

 实际上,只要掌握了方法,具体的写法就可以自己发挥了,根据实际的业务逻辑来定

友情提示,注意CellWriteHandler,SheetWriteHandler等WriteHandler接口,里面是可以取到原生的poi对象的,所以可以在里面做很多自由发挥,比如说要实现导出excel,首行冻结,就可以这么做:

package cn.weaver.ebuilder.teams.excel.style;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;

/**
 * @author niexh
 * @description
 * @date 2021/1/5 13:15
 */
public class CustomSheetWriteHandler implements SheetWriteHandler {
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //首行冻结
        writeSheetHolder.getSheet().createFreezePane( 0, 1, 0, 1 );
    }
}