easyexcel（十七）：导出图片，自定义宽度和高度 - zhangwuji的博客

原 easyexcel（十七）：导出图片，自定义宽度和高度

版权声明：本文为博主原创文章，请尊重他人的劳动成果，转载请附上原文出处链接和本声明。
本文链接：https://www.91mszl.com/zhangwuji/article/details/1460

由于某些原因，现在不支持支付宝支付，如需要购买源码请加博主微信进行购买，微信号：13248254750

一：引入pom

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.1</version>
</dependency>

概述：easyexcel导出图片有多种方式

方法一：使用java对象实现导出图片

方法二：使用拦截器实现CellWriteHandler实现导出图片

方法三：使用模板导出，参考：https://www.91mszl.com/zhangwuji/article/details/1458

二：方法一（使用java对象实现导出图片）

2.1）java实体

package com.gaia.business.entity.sjzc;

import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import lombok.Data;
import java.net.URL;

@ContentRowHeight(60) // 行高
@Data
public class UserInfo {

    private String name;

    private Integer age;

    private String address;

    @ColumnWidth(60) // 列宽
    private URL logo;


}

备注：excel里面的长和宽和我们看到的图片长和宽不是等价的，需要进行转换，假设我们需要导出的图片长和宽分别为：1096 * 620，则我们需要设置的长为：@ColumnWidth(137)，高为：@ContentRowHeight(465)，即：1096 / 137 = 8，620 / 465 = 1.33

官方链接：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write#%E5%9B%BE%E7%89%87%E5%AF%BC%E5%87%BA

官方导出图片一共给出了5种类型的方法，根据自己的需求选取其中一种即可：

ImageDemoData imageDemoData = new ImageDemoData();
// 放入五种类型的图片 实际使用只要选一种即可
imageDemoData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
imageDemoData.setFile(new File(imagePath));
imageDemoData.setString(imagePath);
imageDemoData.setInputStream(inputStream);
imageDemoData.setUrl(new URL("https://raw.githubusercontent.com/alibaba/easyexcel/master/src/test/resources/converter/img.jpg"));

2.2）导出图片

/**
 * 功能：easyexcel导出图片，自定义图片高度和宽度
 * 来源：https://91mszl.com
 * @Author: zxb
 * @Date: 2023-12-09 11:38:26
 */
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
	String fileName = System.currentTimeMillis() + ".xlsx";
	response.reset();
	response.addHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
	OutputStream os=response.getOutputStream();

	List<UserInfo> dataList=new ArrayList<>();
	UserInfo u1=new UserInfo();
	u1.setName("张无忌");
	u1.setAge(18);
	u1.setAddress("上海市青浦区虹桥国际机场1号");
	u1.setLogo(new URL("https://cdn.91mszl.com/cdn/images/logo.png"));

	UserInfo u2=new UserInfo();
	u2.setName("赵敏");
	u2.setAge(20);
	u2.setAddress("上海市黄浦区东方明珠大厦100号");
	u2.setLogo(new URL("https://cdn.91mszl.com/cdn/images/logo.png"));

	dataList.add(u1);
	dataList.add(u2);

	EasyExcel.write(os).head(UserInfo.class).sheet("用户信息").doWrite(dataList);
}

2.3）最终效果图

三：方法二（使用拦截器实现CellWriteHandler实现导出图片）

3.1）导出图片

/**
 * 功能：easyexcel导出图片，自定义图片高度和宽度
 * 来源：https://91mszl.com
 * @Author: zxb
 * @Date: 2023-12-09 11:38:26
 */
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
	String fileName = System.currentTimeMillis() + ".xlsx";
	response.reset();
	response.addHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
	OutputStream os=response.getOutputStream();

	List<UserInfo> dataList=new ArrayList<>();
	UserInfo u1=new UserInfo();
	u1.setName("张无忌");
	u1.setAge(18);
	u1.setAddress("上海市青浦区虹桥国际机场1号");
	u1.setLogo(new URL("https://cdn.91mszl.com/cdn/images/logo.png"));

	UserInfo u2=new UserInfo();
	u2.setName("赵敏");
	u2.setAge(20);
	u2.setAddress("上海市黄浦区东方明珠大厦100号");
	u2.setLogo(new URL("https://cdn.91mszl.com/cdn/images/logo.png"));

	dataList.add(u1);
	dataList.add(u2);

	List<List<String>> headList=new ArrayList<>();
	headList.add(Lists.newArrayList("姓名"));
	headList.add(Lists.newArrayList("年龄"));
	headList.add(Lists.newArrayList("地址"));
	headList.add(Lists.newArrayList("logo"));

	EasyExcel.write(os).head(headList).sheet("用户信息")
			.registerWriteHandler(new CustomImageCellWriteHandler()).doWrite(dataList);
}

3.2）自定义拦截器（easyexcel 3.3.1），注意：easyexcel版本不一样，写法略微有差别，这里给出了2个版本的写法，根据自己的版本任选其一即可。

package com.gaia.business.common.utils;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.ImageData;
import com.alibaba.excel.metadata.data.WriteCellData;
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;

public class CustomImageCellWriteHandler implements CellWriteHandler {

    private List<String> repeats = new ArrayList<>();

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

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

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> 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){
                ImageData c=(ImageData) datas.get(0);
                if(c.getImage()==null){
                    return;
                } else{
                    islist=true;
                }
            }
        }

        if(!islist && cellDataList.get(0).getImageDataList()==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++) {
                ImageData cellData= (ImageData) datas.get(i);
                if(cellData.getImage()==null){
                    continue;
                }
                this.insertImage(sheet,cell,cellData.getImage(),i);
            }
        } else{
            // cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入 一定只有一个
            this.insertImage(sheet, cell, cellDataList.get(0).getImageDataList().get(0).getImage(),0);
        }
    }

    private void insertImage(Sheet sheet, Cell cell, byte[] pictureData, int i){
        int picWidth = Units.pixelToEMU(120);
        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);
        anchor.setDy1(picWidth);
        anchor.setDx2(picWidth);
        anchor.setDy2(picWidth);
        // 设置图片位置
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex());
        anchor.setRow1(cell.getRowIndex());
        anchor.setRow2(cell.getRowIndex());
        // 设置图片可以随着单元格移动
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        drawing.createPicture(anchor, index);
    }


}

3.2）自定义拦截器（easyexcel 2.2.1）

package com.mszl.utils;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.metadata.Head;
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;

public class CustomImageCellWriteHandler implements CellWriteHandler {

    private List<String> repeats = new ArrayList<>();

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

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

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder var1, WriteTableHolder var2, CellData var3, Cell var4, Head var5, Integer var6, Boolean var7) {

    }

    @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) 4785);
        sheet.setColumnWidth(cell.getColumnIndex(), islist ? 16125 * datas.size() : 16125);
        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());
            }
        } else{
            // cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入 一定只有一个
            this.insertImage(sheet, cell, cellDataList.get(0).getImageValue());
        }
    }

    private void insertImage(Sheet sheet, Cell cell, byte[] pictureData){
        int picWidth = Units.pixelToEMU(120);
        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);
        anchor.setDy1(picWidth);
        anchor.setDx2(picWidth);
        anchor.setDy2(picWidth);
        // 设置图片位置
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex());
        anchor.setRow1(cell.getRowIndex());
        anchor.setRow2(cell.getRowIndex());
        // 设置图片可以随着单元格移动
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
        drawing.createPicture(anchor, index);
    }


}

3.3）最终效果图

2023-12-09 12:57:07 阅读(1306)

名师出品，必属精品 https://www.91mszl.com

联系博主

博主信息

昵称： zhangwuji
最新文章

1. idea自定义菜单栏

2. idea设置菜单栏快捷方式

3. word中打出上标和下标

4. idea 2023.3.7和idea 2024.1破解教程

5. idea过滤idea，iml，target等文件

6. uniapp中使用svg文件

7. spring-boot 3.2.3整合mybatis plus报错：java.lang.IllegalArgumentException: Invalid value type for attribu

8. spring-boot 3.2.3整合druid报错：Failed to configure a DataSource: 'url' attribute is not specified and no

9. 使用hutool实现姓名、身份证、手机号、地址、邮箱、车牌、银行卡脱敏

10. java集成ip2region实现离线IP地址定位