easyexcel 3.0.5 多图片导出到表格

最新推荐文章于 2024-10-26 10:00:00 发布

hndelijun

最新推荐文章于 2024-10-26 10:00:00 发布

阅读量919

点赞数 2

文章标签： windows python linux

本文链接：https://blog.csdn.net/hndelijun/article/details/134126760

版权

1、创建一个类型转换器EasyExceUrlListConverter 。

2、字段加上，标记转换类。

3、创建一个拦截器CustomImageModifyHandler ，拦截自定义List<url>。

4、生成文档，加个拦截器和类型转换

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.IoUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EasyExceUrlListConverter implements Converter<List<URL>> {


    @Override
    public Class<?> supportJavaTypeKey() {
        return List.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        /**
         *这里记得枚举类型为IMAGE
         */
        return CellDataTypeEnum.RICH_TEXT_STRING;
    }

    @Override
    public List<URL> convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    @Override
    public WriteCellData<?> convertToExcelData(List<URL> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {

        List<WriteCellData> data = new ArrayList<>();
        for (URL url : value) {
            InputStream inputStream = null;
            try {
                URLConnection urlConnection = url.openConnection();
                inputStream = urlConnection.getInputStream();
                byte[] bytes = IoUtils.toByteArray(inputStream);
                data.add(new WriteCellData<>(bytes));
            } finally {
                if (inputStream != null) {
                    inputStream.close();
                }
            }
        }
        WriteCellData writeCellData = new WriteCellData<>();
        writeCellData.setData(data);
        writeCellData.setType(CellDataTypeEnum.RICH_TEXT_STRING);
        return writeCellData;
    }
}

    /**
     * 照片
     */
    @ExcelProperty(value = "整改照片", index =13,converter = EasyExceUrlListConverter.class)
    private List<URL> rectificationPictureList;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
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.Base64;
import java.util.List;

/**
 * @Description: 图片信息修改拦截器
 * @Author GreenArrow
 * @Date: 2021/7/7 16:05
 * @Version 1.0
 */
public class CustomImageModifyHandler implements CellWriteHandler {
    private List<String> 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, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //  在 数据转换成功后 不是头就把类型设置成空
        if (isHead) {
            return;
        }
        //将要插入图片的单元格的type设置为空,下面再填充图片
        if(cellData.getRichTextStringDataValue()!=null||cellData.getData() instanceof ArrayList){
            cellData.setType(CellDataTypeEnum.EMPTY);
        }
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        //  在 单元格写入完毕后 ，自己填充图片
        if (isHead || CollectionUtil.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 (CollectionUtil.isEmpty(data)) {
                return;
            }
            if (data.get(0) instanceof WriteCellData){
                WriteCellData cellData = (WriteCellData) data.get(0);
                if (cellData.getImageDataList() == null){
                    return;
                }else {
                    listFlag = true;
                }
            }
        }
        //
        if (!listFlag && cellDataList.get(0).getRichTextStringDataValue() == 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++) {
                WriteCellData cellData= (WriteCellData) data.get(i);
                if(cellData.getImageDataList()==null){
                    continue;
                }

                this.insertImage(sheet,cell, ((ImageData)cellData.getImageDataList().get(0)).getImage(), i);
            }
        }
//        else {
//            // cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入 一定只有一个
//            this.insertImage(sheet,cell,Base64.getDecoder().decode(((WriteCellData)data.get(0)).getStringValue()),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);
    }
}

 // 自适应列宽表头
        ExcelWidthStyleStrategy excelWidthStyleStrategy = new ExcelWidthStyleStrategy();
        CustomImageModifyHandler customImageModifyHandler = new CustomImageModifyHandler();
        EasyExceUrlListConverter easyExceUrlListConverter = new EasyExceUrlListConverter();
        try (OutputStream outputStream = response.getOutputStream()) {
//            EasyExcelFactory.write(outputStream, clazz).registerWriteHandler(horizontalCellStyleStrategy).sheet("sheet1").doWrite(list);
            EasyExcelFactory.write(outputStream, clazz).registerWriteHandler(excelWidthStyleStrategy).registerWriteHandler(customImageModifyHandler).registerConverter(easyExceUrlListConverter).sheet(StringUtils.isNotBlank(sheetName)?sheetName:"sheet1").doWrite(list);
        } catch (Exception e) {
            e.printStackTrace();
        }