POI将网络图片添加到excel

主要看: 读取网络图片,插入excel表格 部分

package com.example.demo.exect;

import cn.hutool.core.lang.Console;
import cn.hutool.core.util.ReUtil;
import cn.hutool.http.HttpUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URL;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class Exect {

	
    public static void main(String[] args) throws IOException {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("明细");
        // 设置指定列的宽度
        sheet.setColumnWidth(24, 22 * 256);

        // 读取excel
        String path = "C:\Users\Administrator\Desktop\待处理.xlsx";
        ExcelReader reader = ExcelUtil.getReader(new File(path), "明细");
        List> readAll = reader.read();
        long timeMillis = System.currentTimeMillis();
        // 货号
        String hh = "0";
        // 图片url
        String picPath = null;
        // 商品品牌
        String name = null;
        int index = 0;
        for (int i = 0; i < readAll.size(); i++) {
            Row row = sheet.createRow(i);
            System.out.println(i + "行");
            for (Object lineObj : readAll.get(i)) {
                // 添加每个单元格数据
                row.createCell(index).setCellValue(lineObj + "");
                // 商品品牌
                if (index == 2){
                    name = lineObj + "";
                    System.out.println("  "+name);
                }

                // 货号
                if (index == 11) {
                    // 爬取图片
                    if (!hh.equals(lineObj)){
                        hh = lineObj + "";
                        // 根据货号爬取图片地址
                        if (name.contains("阿迪")){
                            System.out.println("  查询货号:" + hh);
                            picPath = setPicWH(getPicPath(hh + ""));
                        }else if (name.contains("耐克")){
                            // 耐克货号名称不带-符号的话,自动加上
                            if (!hh.contains("-")){
                                hh = hh.substring(0,hh.length()-3) +"-"+hh.substring(hh.length()-3);
                                System.out.println("  查询货号:" + hh);
                            }
                            picPath = getPicPathNK(hh);
                        }else{
                            picPath = null;
                        }
                    }


                    // 读取网络图片,插入excel表格!!!
                    if (picPath != null){
                    	// 设置行高
                        row.setHeight((short) 2280);
                        // URL url = new URL("https://img.adidas.com.cn/resources/2021/9/2/16305671552242554.JPG?x-oss-process=image/resize,m_pad,w_150,h_150,limit_0,color_ffffff");
                        // 图片url地址
                        URL url = new URL(picPath);
                        BufferedImage bufferImg = ImageIO.read(url);
                        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        ImageIO.write(bufferImg, "jpg", byteArrayOut);
                        byte[] bytes = byteArrayOut.toByteArray();
                        int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                        byteArrayOut.close();
                        CreationHelper helper = wb.getCreationHelper();
                        Drawing drawing = sheet.createDrawingPatriarch();
                        ClientAnchor anchor = helper.createClientAnchor();
                        // 图片存放- 列
                        anchor.setCol1(24);
                        // 图片存放- 行
                        anchor.setRow1(i);
                        Picture pict = drawing.createPicture(anchor, pictureIdx);
                        pict.resize();
                    }
                }
                ++index;
            }
            index = 0;
        }

        //save workbook
        String file = "处理完成-图片.xls";
        if (wb instanceof XSSFWorkbook) {
            file += "x";
        }
        FileOutputStream fileOut = new FileOutputStream(file);
        wb.write(fileOut);
        fileOut.close();
        System.out.println("处理完成, 耗时(秒):" + (System.currentTimeMillis() - timeMillis) / 1000);

    }

    // 阿迪达斯
    public static String getPicPath(String path) {
        //String result = HttpUtil.get("https://www.adidas.com.cn/item/GY1028?isFromSearchPage=true");
        String result = HttpUtil.get("https://www.adidas.com.cn/item/" + path + "?isFromSearchPage=true");
        List titles = ReUtil.findAll("", result, 1);
        if (titles == null || titles.size() == 0) {
            System.out.println("  未查询到商品图片");
            return null;
        }

        return titles.get(0);
    }

    public static String getPicPathNK(String path) {
        // {countryLang}       https://www.nike.com/cn/t/air-force-1-07-60-女子运动鞋-ngqK8t/DR0148-171
        String result = HttpUtil.get("https://www.nike.com/cn/w?q="+path+"&vst="+path);
        // 匹配规则
        String reg = "countryLang}(.*?)\"}],";
        Pattern pattern = Pattern.compile(reg);

        // 内容 与 匹配规则 的测试
        Matcher matcher = pattern.matcher(result);
        if( matcher.find() ){
            // 不包含前后的两个字符
            String ma = matcher.group(1);
            String url = ma.substring(0, ma.indexOf(",") - 1);
            url = "https://www.nike.com/cn" + url.replace("\u002F", "/");

            // 二次请求
            String resultUrl = HttpUtil.get(url);
            List titles = ReUtil.findAll("