EasyExcel导出Excel和多个图片到Zip，并实现超链接

前言：之前做了将图片直接插入到excel的需求，由于数据太多会导致导出慢或者直接报错，于是采用了将图片和excel分开放在一个zip压缩包中，并且，excel中对应图片的列点击后可以直接超链接到对应的图片。

 实现效果：

 

依赖版本：

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

动态添加超链接处理类：

package com.aicut.monitor.utils;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.metadata.Head;
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.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;

import java.util.List;

/**
 * excel动态添加超链接处理类
 */
public class ExcelHyperlinkHandler implements CellWriteHandler {
    /**
     * 添加超链接的字段的下标
     */
    private int[] mergeColumnIndex;
    /**
     * 从第几行开始添加，0代表标题行
     */
    private int mergeRowIndex;

    public ExcelHyperlinkHandler() {
    }

    public ExcelHyperlinkHandler(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @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) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex >= mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {

        //获得超链接，当前单元格的内容就是一个超链接
        String stringCellValue = cell.getCellType()==CellType.STRING ? cell.getStringCellValue():null;
        if (StrUtil.isNotBlank(stringCellValue)) {
            CreationHelper creationHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
            Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
            hyperlink.setAddress(stringCellValue);
            cell.setHyperlink(hyperlink);//添加超链接
        }
    }
}

导出zip方法：

@PostMapping("/exportZip")
    public void exportZip(HttpServletResponse response,
                       @RequestParam(value = "startTime",required = false)String startTime,
                       @RequestParam(value = "endTime",required = false)String endTime,
                       @RequestParam(value = "deviceName",required = false)String deviceName) throws IOException {
        List<CutterImageVO> cutterImageVOList = CutterImageService.getCutterImageList(startTime, endTime, deviceName);

        String fileName = "豁口图片数据";
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/zip");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".zip", "UTF-8"));

        //easyExcelUtil.exportExcelInZip(response,fileName,"豁口图片数据",CutterImageVO.class, cutterImageVOList);

        ServletOutputStream outputStream = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream);
        byte[] buf = new byte[1024];
        try {
            GetPresignedObjectUrlArgs args = null;
            for (CutterImageVO cutterImageVO : cutterImageVOList) {
                args = GetPresignedObjectUrlArgs.builder()
                        .bucket(minioConfig.getBucketName())
                        .object(cutterImageVO.getImageUrl())
                        .expiry(1, TimeUnit.DAYS)
                        .method(Method.GET)
                        .build();
               String imgUrl = client.getPresignedObjectUrl(args);
               URL url = new URL(imgUrl);
               URLConnection conn = url.openConnection();
               InputStream in = conn.getInputStream();
               zipOutputStream.putNextEntry(new ZipEntry(cutterImageVO.getImageUrl()));
               int len = -1;
               while ((len=in.read(buf))!=-1){
                    zipOutputStream.write(buf,0,len);
               }
               in.close();
            }
            //构建一个excel对象,这里注意type要是xls不能是xlsx,否则下面的写入后流会关闭,导致报错
            ExcelWriter excelWriter = EasyExcel.write()
                    .excelType(ExcelTypeEnum.XLS)
                    .registerWriteHandler(new ExcelHyperlinkHandler(1, new int[]{7}))
                    .build();
            //构建一个sheet页
            WriteSheet writeSheet = EasyExcel.writerSheet(fileName).build();
            //构建excel表头信息
            WriteTable writeTable0 = EasyExcel.writerTable(0).head(CutterImageVO.class).needHead(Boolean.TRUE).build();
            //将表头和数据写入表格
            excelWriter.write(cutterImageVOList, writeSheet, writeTable0);

            //创建压缩文件
            ZipEntry zipEntry = new ZipEntry(fileName + ".xls");
            zipOutputStream.putNextEntry(zipEntry);
            Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
            //将excel对象以流的形式写入压缩流
            workbook.write(zipOutputStream);

        }catch (Exception e){
            log.error(e.getMessage(), e);
            //抛出异常结束程序
            throw new RuntimeException("数据导出接口异常");
        }finally {
            IOUtils.closeQuietly(zipOutputStream);
            IOUtils.closeQuietly(outputStream);
        }
    }

参考博客：

easyExcel导出功能+图片压缩打包成ZIP，实现浏览器自动下载_eazyexcel 导出excel打包成zip-CSDN博客

使用EasyExcel动态合并单元格和对指定列添加超链接_easyexcel 超链接-CSDN博客

Java代码实现Excel导出多份文件并压缩成zip包保存到OSS服务器返回URL访问下载_java 将文件压缩成压缩包存储到服务器-CSDN博客