POI将网络图片添加到excel

祁_z

已于 2022-02-08 11:01:40 修改

阅读量702

点赞数 2

分类专栏：烂笔头文章标签： java

于 2022-02-08 09:18:29 首次发布

本文链接：https://blog.csdn.net/qq_36881887/article/details/122817900

版权

烂笔头专栏收录该内容

30 篇文章

订阅专栏

该代码示例展示了如何读取Excel文件，处理数据，并将网络图片下载后插入到Excel表格中。主要涉及Hutool库、HttpUtil进行HTTP请求，以及Apache POI操作Excel。代码针对特定品牌（阿迪达斯和耐克）的商品货号爬取图片，并调整大小以适应Excel。最后，将处理后的Excel文件保存到本地。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

主要看: 读取网络图片,插入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<List<Object>> 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<String> titles = ReUtil.findAll("<input type=\"hidden\" id=\"shoppingcartpic\" value=\"(.*?)\">", 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<String> titles = ReUtil.findAll("<source srcSet=\"(.*?)\" media", resultUrl, 1);
            if (titles == null || titles.size() == 0) {
                System.out.println("  未查询到商品图片");
                return null;
            }else{
                for (String title : titles) {
                    //打印标题
                    if (title.contains("w_640")){
                        String replace = title.replace("w_640", "w_115");
                        return replace;
                    }
                }
            }
            return null;
        }else{
            System.out.println("  未查询到商品图片");
            return null;
        }
    }

    public static String setPicWH(String pic){
        if (pic != null){
            if (pic.contains("w_500,h_500")){
                return pic.replace("w_500,h_500", "w_150,h_150");
            }
            if (pic.contains("_500X500")){
                return pic.replace("_500X500", "") + "?x-oss-process=image/resize,m_pad,w_150,h_150,limit_0,color_ffffff";
            }
        }
        return null;
    }

}