【Java】Excel 读写图片工具类

一、需求背景：

做一个大屏管理系统，基础信息包括管理的应用名称，大屏的截图，通过一个excel批量导入

excel的单元格里要插入图片，对应一个大屏应用的信息

导入需要读取到大屏截图，至于存哪还没说....

二、技术实现

Hutool这块没有做图片读取的封装，看了网上的方式都是通过poi原生的api实现的

但是Hutool读取很方便，返回的Workbook接口对象可以识别新旧工作簿类型

然后原生的api的话，只能自己去封装想要的逻辑了

几个图片的关键信息，图片字节，图片类型，图片对应的记录坐标

我还纳闷怎么拿不到图片的文件名，仔细找了API才发现是压根没存这个信息

那就算了不强求了

根据上述需求，我需要封装一个存储图片的集合，拿到后提供给业务做具体实现

或者也可以在读取到这个图片的时候，执行业务逻辑

maven 依赖坐标：

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.4.1</version>
</dependency>

poi 的坐标没找着...

下面就是封装的工具类：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

import cn.hutool.poi.excel.ExcelReader;
import lombok.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
 
import java.io.FileOutputStream;
import java.util.*;
import java.util.function.Consumer;
 
public class ExcelPicUtil {
 
    /**
     * @description 读取Excel中的图片 返回sheet下标和对应图片的集合
     * @author OnCloud9
     * @date 2024/3/20 13:39
     * @params ExcelReader excelReader
     * @return Map<Integer, List<ExcelPic>>
     */
    public static Map<Integer, List<ExcelPic>> getExcelPic(ExcelReader excelReader) {
        Workbook workbook = excelReader.getWorkbook();
        if (Objects.isNull(workbook)) return null;
        boolean isXSSF = workbook instanceof XSSFWorkbook;
        boolean isHSSF = workbook instanceof HSSFWorkbook;
        if (isXSSF) return getExcelPicByXssfType((XSSFWorkbook) workbook, null);
        else if (isHSSF) return getExcelPicByHssfType((HSSFWorkbook) workbook, null);
        return null;
    }
 
    /**
     * @description
     * @author OnCloud9
     * @date 2024/3/20 14:12
     * @params
     * @return
     */
    public static Map<Integer, List<ExcelPic>> getExcelPic(ExcelReader excelReader, Consumer<ExcelPic> consumer) {
        Workbook workbook = excelReader.getWorkbook();
        if (Objects.isNull(workbook)) return null;
        boolean isXSSF = workbook instanceof XSSFWorkbook;
        boolean isHSSF = workbook instanceof HSSFWorkbook;
        if (isXSSF) return getExcelPicByXssfType((XSSFWorkbook) workbook, consumer);
        else if (isHSSF) return getExcelPicByHssfType((HSSFWorkbook) workbook, consumer);
        return null;
    }
 
    @Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    public static final class ExcelPic {
        private Integer sheetIdx;
        private String suffix;
        private String mimeType;
        private byte[] data;
        private Integer rowIdx;
        private Integer colIdx;
    }
 
    /**
     * @description XSSF工作簿读取图片
     * @author OnCloud9
     * @date 2024/3/20 13:57
     * @params
     * @return
     */
    private static Map<Integer, List<ExcelPic>> getExcelPicByXssfType(XSSFWorkbook xssfWorkbook, Consumer<ExcelPic> excelPicConsumer) {
        int numberOfSheets = xssfWorkbook.getNumberOfSheets();
        Map<Integer, List<ExcelPic>> picMap = new HashMap<>();
 
        for (int xssfSheetIdx = 0; xssfSheetIdx < numberOfSheets; xssfSheetIdx++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(xssfSheetIdx);
            XSSFDrawing drawingPatriarch = xssfSheet.getDrawingPatriarch();
            if (Objects.isNull(drawingPatriarch)) {
                picMap.put(xssfSheetIdx, Collections.emptyList());
                continue;
            }
            List<XSSFShape> shapes = drawingPatriarch.getShapes();
            List<ExcelPic> excelPicList = new ArrayList<>(shapes.size());
            for (XSSFShape xssfShape : shapes) {
                XSSFPicture xssfPicture = (XSSFPicture) xssfShape;
                XSSFClientAnchor clientAnchor = xssfPicture.getClientAnchor();
                XSSFPictureData xssfPictureData = xssfPicture.getPictureData();
 
                String fileExtension = xssfPictureData.suggestFileExtension();
                byte[] data = xssfPictureData.getData();
                String mimeType = xssfPictureData.getMimeType();
 
                short col2 = clientAnchor.getCol2();
                int row2 = clientAnchor.getRow2();
 
                ExcelPic build = ExcelPic.builder()
                        .sheetIdx(xssfSheetIdx)
                        .suffix(fileExtension)
                        .mimeType(mimeType)
                        .data(data)
                        .rowIdx(row2)
                        .colIdx((int) col2)
                        .build();
                if (Objects.nonNull(excelPicConsumer)) excelPicConsumer.accept(build);
                excelPicList.add(build);
            }
            picMap.put(xssfSheetIdx, excelPicList);
        }
        return picMap;
    }
 
    /**
     * @description HSSF工作簿读取图片
     * @author OnCloud9
     * @date 2024/3/20 13:58
     * @params
     * @return
     */
    private static Map<Integer, List<ExcelPic>> getExcelPicByHssfType(HSSFWorkbook hssfWorkbook, Consumer<ExcelPic> excelPicConsumer) {
        int numberOfSheets = hssfWorkbook.getNumberOfSheets();
        Map<Integer, List<ExcelPic>> picMap = new HashMap<>();
 
        for (int hssfSheetIdx = 0; hssfSheetIdx < numberOfSheets; hssfSheetIdx++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(hssfSheetIdx);
            HSSFPatriarch drawingPatriarch = hssfSheet.getDrawingPatriarch();
            if (Objects.isNull(drawingPatriarch)) {
                picMap.put(hssfSheetIdx, Collections.emptyList());
                continue;
            }
            List<HSSFShape> hssfShapeList = drawingPatriarch.getChildren();
            List<ExcelPic> excelPicList = new ArrayList<>(hssfShapeList.size());
            for (HSSFShape hssfShape : hssfShapeList) {
                HSSFPicture hssfPicture = (HSSFPicture) hssfShape;
                HSSFClientAnchor clientAnchor = hssfPicture.getClientAnchor();
                HSSFPictureData hssfPictureData = hssfPicture.getPictureData();
 
                String fileExtension = hssfPictureData.suggestFileExtension();
                byte[] data = hssfPictureData.getData();
                String mimeType = hssfPictureData.getMimeType();
 
                short col2 = clientAnchor.getCol2();
                int row2 = clientAnchor.getRow2();
 
                ExcelPic build = ExcelPic.builder()
                        .sheetIdx(hssfSheetIdx)
                        .suffix(fileExtension)
                        .mimeType(mimeType)
                        .data(data)
                        .rowIdx(row2)
                        .colIdx((int) col2)
                        .build();
 
                if (Objects.nonNull(excelPicConsumer)) excelPicConsumer.accept(build);
                excelPicList.add(build);
            }
            picMap.put(hssfSheetIdx, excelPicList);
        }
        return picMap;
    }
 
    /**
     * @description 根据文件路径和图片字节输出
     * @author OnCloud9
     * @date 2024/3/20 13:56
     * @params
     * @return
     */
    @SneakyThrows
    public static void writePicByteTo(byte[] picBytes, String outPutPath) {
        FileOutputStream fos = null;
       try {
           fos = new FileOutputStream(outPutPath);
           fos.write(picBytes);
       } catch (Exception e) {
           e.printStackTrace();
       } finally {
           if (Objects.nonNull(fos)) fos.close();
       }
    }
}

测试代码DEMO：

@Test
public void excelPicRead() {
    String file = "C:\\Users\\Administrator\\Desktop\\工作日志\\图片导入测试.xlsx";
    ExcelReader excelReader = ExcelUtil.getReader(file);
 
    List<List<Object>> read = excelReader.read();
    System.out.println(read);
 
    Map<Integer, List<ExcelPicUtil.ExcelPic>> excelPicMap = ExcelPicUtil.getExcelPic(excelReader, excelPic -> {
        // todo ...... 自己实现读取到这个图片时做啥
    });
 
    String rootPath = "D:\\ymcd-project\\config\\ouput\\";
    excelPicMap.values().forEach(excelPics -> excelPics.forEach(excelPic -> {
        ExcelPicUtil.writePicByteTo(excelPic.getData(), rootPath + excelPic.getRowIdx() + "-" + excelPic.getColIdx() + "."  + excelPic.getSuffix());
    }));
 
    System.out.println(excelPicMap.size());
}

演示文件：

读取后输出到目录：

三、图片写入

代码参考自51CTO

1	`https://blog.51cto.com/u_16213405/9673126`

关于图片写入Anchor参数的详细说明见：

1	`https://www.bilibili.com/video/BV1eA41157va?p=25`

我想到有图片读取导入，就有图片导出的功能

然后导出的时候和导入一样，一定是基于单元格定位的。

然后图片的呈现是根据单元格大小控制的（不影响图片质量）

这里继续对工具类追加了方法：

/**
 * @description 将图片写入到Excel中
 * @author OnCloud9
 * @date 2024/3/20 16:40
 * @params
 * @return
 */
@SneakyThrows
public static void writePicToExcel(ExcelWriter excelWriter, List<ExcelPicWrite> excelPicWriteList) {
    Workbook workbook = excelWriter.getWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    Sheet sheet;
    for (ExcelPicWrite picWrite : excelPicWriteList) {
        /* 判断是否提供sheet名称，不提供默认写入到第一个，遍历时不一定存在，所以要判断，不存在时创建出来 */
        String sheetName = picWrite.getSheetName();
        if (StringUtils.isBlank(sheetName)) sheet = workbook.getSheetAt(0);
        else {
            sheet = workbook.getSheet(sheetName);
            if (Objects.isNull(sheet)) sheet = workbook.createSheet(sheetName);
        }
 
        /* 获取图片内容，写入工作簿中 */
        byte[] picContent = picWrite.getData();
        Integer picType = picWrite.getPicType();
        int picIdx = workbook.addPicture(picContent, picType);
 
        /* 设置图片存放的位置 */
        Integer rowIdx = picWrite.getRowIdx();
        Integer colIdx = picWrite.getColIdx();
        ClientAnchor clientAnchor = creationHelper.createClientAnchor();
        clientAnchor.setRow1(rowIdx);
        clientAnchor.setRow2(rowIdx + 1);
        clientAnchor.setCol1(colIdx);
        clientAnchor.setCol2(colIdx + 1);
 
        /* 图片绘制渲染 */
        Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
        Picture picture = drawingPatriarch.createPicture(clientAnchor, picIdx);
    }
}
 
/**
 * @description 图片类型翻译
 * @author OnCloud9
 * @date 2024/3/20 17:46
 * @params
 * @return
 */
public static int picTypeTranslate(String picType) {
    if (StringUtils.isBlank(picType)) return Workbook.PICTURE_TYPE_JPEG;
    switch (picType) {
        case "png":
        case "PNG":
            return Workbook.PICTURE_TYPE_PNG;
        case "bmp":
        case "BMP":
            return Workbook.PICTURE_TYPE_DIB;
        default:
        case "jpeg":
        case "JPEG":
        case "jpg":
        case "JPG":
            return Workbook.PICTURE_TYPE_JPEG;
    }
}
 
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public static final class ExcelPicWrite {
    private String sheetName;  /* 导出创建时依照sheetName名称为标识，如果不写则默认放到第一个sheet页 */
    private byte[] data;  /* 图片字节数组 这里不关心图片输入的方式 */
    private Integer rowIdx; /* 定位的单元格行 */
    private Integer colIdx; /* 定位的单元格列 */
    private Integer picType; /* 图片类型 见上面翻译方法 */
}

测试代码：

这里图省事我就都用同一个图片源了，导出的Excel上的图片会跟随单元格宽高变化而变化

右键另存为的图片大小也是一样的

@Test
public void excelPicWrite() {
    ExcelWriter excelWriter = ExcelUtil.getWriter();
    String picPath = "D:\\ymcd-project\\config\\ouput\\3-2.jpeg";
    byte[] demoData = FileUtil.readBytes(picPath);
    ExcelPicUtil.writePicToExcel(excelWriter, Arrays.asList(
            ExcelPicUtil.ExcelPicWrite.builder()
                    .picType(Workbook.PICTURE_TYPE_JPEG)
                    .data(demoData)
                    .rowIdx(1)
                    .colIdx(3)
                    .build(),
            ExcelPicUtil.ExcelPicWrite.builder()
                    .picType(Workbook.PICTURE_TYPE_JPEG)
                    .data(demoData)
                    .rowIdx(2)
                    .colIdx(3).build(),
            ExcelPicUtil.ExcelPicWrite.builder()
                    .picType(Workbook.PICTURE_TYPE_JPEG)
                    .data(demoData)
                    .rowIdx(3)
                    .colIdx(3)
                    .build()
    ));
 
    excelWriter.flush(new File("D:\\ymcd-project\\config\\ouput\\write-test.xlsx"));
    excelWriter.close();
}