Excel读取图片工具类

package org.master.utils;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Excel图片读取工具类
 * 支持读取Excel中的图片，包括嵌入式图片和浮动图片
 */
@Slf4j
public class ExcelImageReaderUtil {

    public static List<ExcelRowData> readByRow(MultipartFile file) {
        return readByRow(file, null);
    }

    /**
     * 按行读取Excel文件内容（包括文本和图片）
     * 目前图片的格式仅支持jpg/jpeg/png
     *
     * @param file Excel文件
     * @param sheetName Sheet名称，如果为null则读取第一个Sheet
     * @return 行数据列表
     */
    public static List<ExcelRowData> readByRow(MultipartFile file, String sheetName) {
        List<ExcelRowData> rowDataList = new ArrayList<>();

        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet;
            if (StringUtils.hasText(sheetName)) {
                sheet = workbook.getSheet(sheetName);
                if (sheet == null) {
                    log.warn("Sheet不存在: {}, 使用第一个Sheet", sheetName);
                    sheet = workbook.getSheetAt(0);
                }
            } else {
                sheet = workbook.getSheetAt(0);
            }

            String currentSheetName = sheet.getSheetName();

            // 先读取所有图片信息
            List<ExcelImageInfo> allImages = new ArrayList<>();
            if (workbook instanceof XSSFWorkbook) {
                allImages.addAll(readImagesFromXSSFSheet((XSSFSheet) sheet));
            } else if (workbook instanceof HSSFWorkbook) {
                allImages.addAll(readImagesFromHSSFSheet((HSSFSheet) sheet));
            }

            // 按行号分组图片
            Map<Integer, List<ExcelImageInfo>> imagesByRow = allImages.stream()
                    .collect(Collectors.groupingBy(ExcelImageInfo::getRow));

            // 遍历所有行
            for (Row row : sheet) {
                int rowIndex = row.getRowNum();
                // 表头跳过
                if (rowIndex == 0) {
                    continue;
                }
                ExcelRowData rowData = new ExcelRowData();
                rowData.setSheetName(currentSheetName);
                rowData.setRowIndex(rowIndex);

                // 处理文本数据
                for (Cell cell : row) {
                    int colIndex = cell.getColumnIndex();
                    String cellValue = getCellValueAsString(cell);
                    rowData.setTextValue(colIndex, cellValue);
                }
                
                // 处理图片数据
                List<ExcelImageInfo> rowImages = imagesByRow.getOrDefault(rowIndex, Collections.emptyList());
                for (ExcelImageInfo imageInfo : rowImages) {
                    rowData.setImage(imageInfo.getCol(), imageInfo);
                }

                rowDataList.add(rowData);
            }
        } catch (Exception e) {
            log.error("按行读取Excel内容失败", e);
        }

        return rowDataList;
    }

    /**
     * 获取浮动图片映射
     */
    private static Map<String, XSSFPictureData> getFloatPictureMap(XSSFSheet sheet) {
        Map<String, XSSFPictureData> map = new HashMap<>();
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    if (shape instanceof XSSFPicture) {
                        XSSFPicture picture = (XSSFPicture) shape;
                        XSSFClientAnchor anchor = (XSSFClientAnchor) picture.getAnchor();
                        if (anchor == null) {
                            log.warn("图片位置信息为空，跳过");
                            continue;
                        }
                        // 使用图片左上角的位置作为key
                        String key = anchor.getRow1() + "-" + anchor.getCol1();
                        map.put(key, picture.getPictureData());
                    }
                }
            }
        }
        return map;
    }

    /**
     * 从文件路径获取扩展名
     */
    private String getExtensionFromPath(String path) {
        if (path == null || path.isEmpty()) {
            return ".jpg";
        }
        int lastDotIndex = path.lastIndexOf(".");
        if (lastDotIndex > 0) {
            return path.substring(lastDotIndex);
        }
        return ".jpg";
    }

    /**
     * 获取单元格的文本值
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 避免数值显示为科学计数法
                    double value = cell.getNumericCellValue();
                    if (value == (long) value) {
                        return String.format("%d", (long) value);
                    } else {
                        return String.format("%s", value);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return String.valueOf(cell.getStringCellValue());
                } catch (Exception e) {
                    try {
                        return String.valueOf(cell.getNumericCellValue());
                    } catch (Exception ex) {
                        return cell.getCellFormula();
                    }
                }
            case BLANK:
                return "";
            default:
                return "";
        }
    }

    /**
     * 读取XSSF工作表中的图片
     */
    private static List<ExcelImageInfo> readImagesFromXSSFSheet(XSSFSheet sheet) {
        List<ExcelImageInfo> imageInfoList = new ArrayList<>();
        String sheetName = sheet.getSheetName();

        // 读取浮动图片
        Map<String, XSSFPictureData> floatPictureMap = getFloatPictureMap(sheet);
        for (Map.Entry<String, XSSFPictureData> entry : floatPictureMap.entrySet()) {
            String[] position = entry.getKey().split("-");
            int row = Integer.parseInt(position[0]);
            int col = Integer.parseInt(position[1]);

            XSSFPictureData pictureData = entry.getValue();

            // 创建图片信息对象
            ExcelImageInfo imageInfo = new ExcelImageInfo();
            imageInfo.setSheetName(sheetName);
            imageInfo.setRow(row);
            imageInfo.setCol(col);
            imageInfo.setImageData(pictureData);

            imageInfoList.add(imageInfo);
        }

        return imageInfoList;
    }

    /**
     * 读取HSSF工作表中的图片
     */
    private static List<ExcelImageInfo> readImagesFromHSSFSheet(HSSFSheet sheet) {
        List<ExcelImageInfo> imageInfoList = new ArrayList<>();
        String sheetName = sheet.getSheetName();

        // 获取所有图片
        if (sheet.getDrawingPatriarch() != null) {
            List<HSSFShape> shapes = sheet.getDrawingPatriarch().getChildren();
            for (HSSFShape shape : shapes) {
                if (shape instanceof HSSFPicture) {
                    HSSFPicture picture = (HSSFPicture) shape;
                    HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor();
                    HSSFPictureData pictureData = picture.getPictureData();

                    int row = anchor.getRow1();
                    int col = anchor.getCol1();

                    // 创建图片信息对象
                    ExcelImageInfo imageInfo = new ExcelImageInfo();
                    imageInfo.setSheetName(sheetName);
                    imageInfo.setRow(row);
                    imageInfo.setCol(col);
                    imageInfo.setImageData(pictureData);

                    imageInfoList.add(imageInfo);
                }
            }
        }

        return imageInfoList;
    }

    /**
     * Excel图片信息类
     */
    @Data
    public static class ExcelImageInfo {
        /**
         * Sheet名称
         */
        private String sheetName;

        /**
         * 行号
         */
        private Integer row;

        /**
         * 列号
         */
        private Integer col;

        /**
         * 图片数据对象
         */
        private PictureData imageData;
    }

    /**
     * Excel行数据类，包含一行的文本和图片
     */
    @Data
    public static class ExcelRowData {
        private String sheetName;
        private int rowIndex;
        
        /**
         * 行数据Map，key为列索引，value为列数据
         * 每列数据可能是文本或图片
         */
        private Map<Integer, ColumnData> columnDataMap = new HashMap<>();

        // 全部的文本
        List<String> texts = new ArrayList<>();

        // 全部的图片
        List<ExcelImageReaderUtil.ExcelImageInfo> images = new ArrayList<>();

        /**
         * 获取指定列的文本值
         */
        public String getTextValue(int colIndex) {
            ColumnData columnData = columnDataMap.get(colIndex);
            return columnData != null ? columnData.getTextValue() : "";
        }

        /**
         * 获取指定列的图片
         */
        public ExcelImageInfo getImage(int colIndex) {
            ColumnData columnData = columnDataMap.get(colIndex);
            return columnData != null ? columnData.getImageInfo() : null;
        }

        /**
         * 设置列的文本值
         */
        public void setTextValue(int colIndex, String value) {
            columnDataMap.computeIfAbsent(colIndex, k -> new ColumnData()).setTextValue(value);
            texts.add(value);
        }

        /**
         * 设置列的图片
         */
        public void setImage(int colIndex, ExcelImageInfo imageInfo) {
            columnDataMap.computeIfAbsent(colIndex, k -> new ColumnData()).setImageInfo(imageInfo);
            images.add(imageInfo);
        }

        /**
         * 将行数据转换为Map
         */
        public Map<String, Object> toMap() {
            Map<String, Object> map = new HashMap<>();
            map.put("sheetName", sheetName);
            map.put("rowIndex", rowIndex);

            // 按列号顺序添加数据
            columnDataMap.forEach((colIndex, columnData) -> {
                String key = "col_" + colIndex;
                if (columnData.hasText()) {
                    map.put(key, columnData.getTextValue());
                } else if (columnData.hasImage()) {
                    map.put(key, "【图片】");
                }
            });

            return map;
        }
    }

    /**
     * 列数据类，包含文本或图片
     */
    @Data
    public static class ColumnData {
        private String textValue;
        private ExcelImageInfo imageInfo;

        public boolean hasText() {
            return textValue != null;
        }

        public boolean hasImage() {
            return imageInfo != null;
        }
    }
}