easyexcel不创建对象导出图片

标签:简单   LLC   eth   contain   有一个   system   reg   ali   而且   

前些天项目中要使用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 );
    }
}

 

easyexcel不创建对象导出图片

标签:简单   LLC   eth   contain   有一个   system   reg   ali   而且   

原文地址:https://www.cnblogs.com/NieXiaoHui/p/14297858.html

版权声明:完美者 发表于 2021-01-20 11:46:20。
转载请注明:easyexcel不创建对象导出图片 | 完美导航

暂无评论

暂无评论...