导出图片到excel(批量)

一. 创建公共工具类

入参解释:
1. sheetName sheet名称
2. fileName 文件名称
3. urlList 图片路径集合
4. rowNumber 起始行
5. columnNumber 起始列
6. pictureWidth 图片宽度
7. pictureHeight 图片高度
8. sheetWidth 单元格默认宽度
9. sheetHeight 单元格默认高度
public static void exportPictureToExcel(HttpServletResponse response, String sheetName,
                        String fileName,List<String> urlList, Integer rowNumber,
                        Integer columnNumber, Integer pictureWidth,Integer pictureHeight,
                        Integer sheetWidth, Integer sheetHeight) throws IOException {
    // 1. 校验参数
    // 1.1 sheet名称校验
    if (StringUtils.isEmpty(sheetName)){
        sheetName = "sheet1";
    }

    // 1.2 文件名称校验
    if (StringUtils.isEmpty(fileName)){
        fileName = "图片文档";
    }

    // 1.3 校验图片宽高,默认宽高:100*100
    if (pictureWidth == null || pictureHeight == null) {
        pictureWidth = 100;
        pictureHeight = 100;
    }

    // 1.4 校验图片显示的起始行、列,如果其中任何一个为空,则默认起始坐标为A1(0,0)
    if (rowNumber == null || columnNumber == null) {
        rowNumber = 1;
        columnNumber = 1;
    }

    // 2. 创建一个工作表
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(sheetName);
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    // 2.1 设置工作表单元格默认宽高
    sheet.setDefaultRowHeight((short)(sheetHeight==null?1000:sheetHeight));
    sheet.setDefaultColumnWidth((short)(sheetWidth==null?1000:sheetWidth));

    // 3. 拆分重组图片路径,注意线上处理“.png”时,它的图片颜色空间为CMYK模式,所以如果直接用ImageIO.read()后图片颜色会发生异常、
    // 3.1 创建图片集合
    List<BufferedImage> images = new ArrayList<>();
    for (String url : urlList) {
        // 3.2 构建图片流
        BufferedImage tag = new BufferedImage(pictureWidth, pictureHeight,
                                            BufferedImage.TYPE_INT_RGB);

        // 3.3 如果图片为线上且为“.png”格式时,进行单独处理,防止导出图片时颜色异常(红色蒙版)的问题
        BufferedImage bufferedImage;
        if (url.contains("http:") || url.contains("https:")) {
            if (url.contains(".png")) {
                bufferedImage = urlToBufferedImage(url);
            } else {
                bufferedImage = ImageIO.read(new URL(url));
            }
        } else {
            // 3.4 如果是本地图片,获取images后先转为ToolkitImage类型,再转换为BufferedImage后保存
            bufferedImage = ((ToolkitImage) new ImageIcon(url).getImage()).getBufferedImage();
        }
        // 3.5 绘制改变尺寸后的图
        tag.getGraphics().drawImage(bufferedImage, 0, 0, pictureWidth, pictureHeight, null);
        images.add(tag);
    }

    // 4. 设置图片属性
    short i = 0;
    for (BufferedImage image : images) {
        // 4.1 创建字节数组输出流,并进行格式转换和写入
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        ImageIO.write(image, "jpg", byteArrayOut);

        // 4.2 设置图片输出位置:col1,row1 指定起始的单元格,col2,row2 指定结束的单元格,下标均从0开始
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
                (short) (Math.max(rowNumber - 1, 0)), (Math.max(columnNumber - 1, 0)) + i,
                (short) (int)rowNumber, columnNumber + i);

            // 4.3 插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(),
                                                          HSSFWorkbook.PICTURE_TYPE_JPEG));
            i++;
        }

    // 5. 设置客户端响应属性
    response.reset();
    response.setHeader("Content-disposition", "attachment; filename=" +
                             new String(fileName.getBytes("gbk"), "iso8859-1") + ".xls");
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");

    // 6. 写入excel文件
    OutputStream output = response.getOutputStream();
    wb.write(output);
}

二. 创建线上Url处理工具类

// 入参url:线上图片链接,防止图片颜色空间为CMYK模式时,导出的颜色异常问题
public static BufferedImage urlToBufferedImage(String url) throws IOException {
    // 1.读取图片转换为 BufferedImage
    BufferedImage bufferedImage = ImageIO.read(new URL(url));

    // 2.BufferedImage 转化为 ByteArrayOutputStream
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    ImageIO.write(bufferedImage, "jpg", out);

    return ((ToolkitImage)new ImageIcon(out.toByteArray()).getImage()).getBufferedImage();
}

三. 测试接口

@RestController
public class TestController {
    @RequestMapping("/exportToExcel")
    public void exportToExcel(HttpServletResponse response) throws IOException {
        List<String> urlList = new ArrayList<>();
        urlList.add("https://***/warehouseFile/122.jpg");
        urlList.add("https://***/warehouseFile/123.jpg");
        urlList.add("https://***/warehouseFile/152369.png");
        urlList.add("D:\\excel\\111.jpeg");
        urlList.add("D:\\excel\\222.png");
        ExcelUtils.exportPictureToExcel(response,"图片sheet","图片文档",urlList,2,
                2,500,500,1000,1000);
    }
}

番外:

//参考网址
https://blog.csdn.net/iteye_7465/article/details/82208936

//JavaExcel导出行样式HSSFCellStyle样式详解
https://www.cnblogs.com/langtianya/p/6345010.html
https://blog.csdn.net/niugang0920/article/details/85245356