java导入Excel(带图片)-暂支持浮动式图片

zhiyuan6296

已于 2024-02-04 17:25:57 修改

阅读量246

点赞数 3

文章标签： java excel

于 2024-02-04 17:16:01 首次发布

本文链接：https://blog.csdn.net/weixin_56233175/article/details/136029446

版权

导入工具

import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import com.sgcloud.clp.common.file.core.FileProperties;
import com.sgcloud.clp.common.file.core.FileTemplate;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.ss.usermodel.*;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.util.*;

/**
 * Excel导入 带图片
 * 暂仅支持浮动式图片
 *
 * @param <T>
 */
@Slf4j
public class ExcelImporter<T> {

    private final Class<T> entityType;
    private FileTemplate fileTemplate;
    private FileProperties properties;
    private String path;

    public ExcelImporter(Class<T> entityType, FileTemplate fileTemplate, FileProperties properties, String path) {
        this.entityType = entityType;
        this.fileTemplate = fileTemplate;
        this.properties = properties;
        this.path = path;
    }

    /**
     * 从Excel文件导入数据到实体列表。
     *
     * @param is Excel文件的InputStream。
     * @return 导入的实体列表。
     */
    public List<T> importExcel(InputStream is) {
        List<T> entities = new ArrayList<>();

        try {
            Workbook workbook = getWorkbook(is);

            // 处理图片
            Map<Integer, Map<Integer, byte[]>> pictureMap = processFloatingPictures(workbook);

            // 处理数据
            Map<Integer, T> rowEntities = processData(workbook);

            // 处理数据图片关联关系
            entities.addAll(processPictures2Entity(rowEntities, pictureMap));

        } catch (Exception e) {
            log.error("导入Excel时出错：{}", e);
        }

        return entities;
    }


    private Workbook getWorkbook(InputStream is) throws IOException {
        return WorkbookFactory.create(is);
    }

    /**
     * 处理浮动图片
     *
     * @param workbook
     * @return 图片数据的映射，以行和列为键，字节数组为值
     */
    private Map<Integer, Map<Integer, byte[]>> processFloatingPictures(Workbook workbook) {
        Map<Integer, Map<Integer, byte[]>> pictureMap = new HashMap<>();

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Drawing<?> drawing = sheet.getDrawingPatriarch();

            if (drawing != null) {
                Iterator<?> iterator = drawing.iterator();

                while (iterator.hasNext()) {
                    Shape shape = (Shape) iterator.next();
                    if (shape instanceof Picture) {
                        Picture picture = (Picture) shape;
                        ClientAnchor anchor = picture.getClientAnchor();
                        int row = anchor.getRow1();
                        int column = anchor.getCol1();
                        byte[] pictureByte = picture.getPictureData().getData();

                        pictureMap.computeIfAbsent(row, k -> new HashMap<>())
                                .put(column, pictureByte);
                    }
                }
            }
        }

        return pictureMap;
    }

    /**
     * 处理数据并关联图片
     *
     * @param workbook Excel Workbook
     * @return 实体列表
     */
    private Map<Integer, T> processData(Workbook workbook) {
        Map<Integer, T> entityMap = new HashMap<>();

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);

            for (int rowIndex = 2; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row excelRow = sheet.getRow(rowIndex);
                int rowNum = excelRow.getRowNum(); // 行号
                try {
                    // 实例化实体类
                    T entity = entityType.getDeclaredConstructor().newInstance();

                    for (Field field : entityType.getDeclaredFields()) {
                        ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                        if (excelProperty != null) {
                            int index = excelProperty.index();
                            Cell cell = excelRow.getCell(index);
                            // 设置实体类属性值
                            setEntityPropertyFromCell(entity, field, cell);
                        }
                    }

                    entityMap.put(rowNum, entity);
                } catch (Exception e) {
                    log.error("实例化实体类失败，Exception:{}", e);
                }
            }
        }
        return entityMap;
    }

    /**
     * 图片关联实体类
     *
     * @param entityMap
     * @param imgMap
     */
    private List<T> processPictures2Entity(Map<Integer, T> entityMap, Map<Integer, Map<Integer, byte[]>> imgMap) {
        List<T> entities = Lists.newArrayList();

        entityMap.keySet().forEach(k -> {
            T entity = entityMap.get(k);
            Map<Integer, byte[]> rowImg = imgMap.get(k);
            for (Field field : entityType.getDeclaredFields()) {
                ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                if (excelProperty == null) continue;

                int index = excelProperty.index();    // 列号
                int type = excelProperty.type();    // 类型

                try {
                    if (type == 2 && index != -1) {
                        field.setAccessible(true);
                        // 根据列号设置图片
                        String url = processImageSingle(rowImg.get(index));
                        field.set(entity, url);
                    }
                } catch (IllegalAccessException e) {
                    log.error("[{}]设置属性失败， Exception：{}", field.getName(), e);
                }
            }
            entities.add(entity);
        });

        return entities;
    }

    private boolean isValidPicture(Picture picture) {
        try {
            byte[] imageData = picture.getPictureData().getData();
            return imageData != null && imageData.length > 0;
        } catch (Exception e) {
            // 记录异常信息或进行其他处理
            log.warn("验证图像有效性时出现异常：{}", e.getMessage());
            return false;
        }
    }

    /**
     * 设置实体其他属性
     *
     * @param entity
     * @param field
     * @param cell
     */
    private void setEntityPropertyFromCell(T entity, Field field, Cell cell) {
        try {
            if (cell != null) {
                field.setAccessible(true);
                Class<?> fieldType = field.getType();

                if (fieldType == String.class) {
                    field.set(entity, cell.getStringCellValue());
                } else if (fieldType == LocalDateTime.class) {
                    if (cell.getCellType() == CellType.NUMERIC) {
                        field.set(entity, cell.getLocalDateTimeCellValue());
                    } else {
                        field.set(entity, null);
                    }
                }
            }
        } catch (IllegalAccessException e) {
            log.error("设置属性失败， Exception：{}", e);
        }
    }

    /**
     * 单个图片处理
     *
     * @param imageValue
     */
    private String processImageSingle(byte[] imageValue) {
        // 上传图片到服务器，返回图片的URL

        try (InputStream inputStream = new ByteArrayInputStream(imageValue)) {
            
            return "";
        } catch (Exception e) {
            log.error("上传失败", e);
            return "error";
        }
    }

    /**
     * 根据rowNum colNum处理图片
     *
     * @param entity
     * @param imageIndex
     * @param imageValue
     */
    private void processImageAnnotation(T entity, int imageIndex, byte[] imageValue) {
        // 上传图片到服务器，返回图片的URL ... ...
        
        try (InputStream inputStream = new ByteArrayInputStream(imageValue)) {
            
        } catch (Exception e) {
            log.error("上传失败", e);
        }
    }
}

自定义注解

主要 index 和 type 其他可有可无

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelProperty {
    String[] value() default {""};

    int index() default -1;

    int order() default Integer.MAX_VALUE;

    Class<? extends Converter<?>> converter() default AutoConverter.class;

    /** @deprecated */
    @Deprecated
    String format() default "";

    /**
     * 1-文本、2-图片
     */
    int type() default 1;
}