POI——SXSSFWorkbook导出数据包含图片（网络地址）到Excel

先看效果图：

每一行数据包含一个logo图片，从网上看了好多资料，但是不尽人意，都模模糊糊，所以以成功案例简单讲解SXSSFWorkbook导出数据包含图片到Excel的过程。

我的数据全部在json里，首先我用的图片是服务器网络地址，所以需要用IO转换成byte[]数组；

图片的位置： HSSFClientAnchor

插图片的位置： HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2)；
dx1,dy1,dx2,dy2：前四个参数只是图片偏移量
重要的是这四个参数：col1,row1,col2,row2
(row1,col1) 是指图片位置的左上角 ； (row2,col2) 是指图片位置的右下角

代码

详细的讲解看代码注释。

package cn.com.newcapec.utils;

import cn.com.newcapec.modules.operation.bean.jyb.JobinfoBean;
import cn.com.newcapec.modules.operation.bean.partjob.PartjobInfoBean;
import cn.com.newcapec.modules.operation.entity.EvaluateEntity;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import lombok.val;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.imageio.ImageIO;
import javax.imageio.stream.ImageInputStream;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelUtil {

    public static void exportJob(String json, HttpServletResponse response) throws IOException {
        OutputStream outputStream = null;
        try {
            //获取文档对象
            Workbook workbook = new SXSSFWorkbook();
            //获取sheet
            Sheet sheet = workbook.createSheet();

            String[] titles = {"id", "职位名称", "公司ID", "公司名称", "logo", "有无logo", "规模", "行业", "企业性质", "最低薪资", "最高薪资", "标签",
                    "学历要求", "所在省", "所在市", "职位来源", "职位类型", "创建时间",};
            String[] columns = {"id", "workName", "comId", "comName", "threeComLog", "islogo", "guimo", "businessName",
                    "xingzhi", "salaryFloor", "salayCeil", "tags", "xueliIdName", "provinceIdName", "cityIdName", "type1", "jobtype1", "createTime",};
            Row titleRow = sheet.createRow(0);//在工作薄创建第一行
            // 图片字节数组
            byte[] imgByte = null;
            for (int i = 0; i < titles.length; i++) {
                Cell cell = titleRow.createCell(i);//给第一列  创建多个单元格  第1列
                cell.setCellValue(titles[i]);//表头
            }
            JSONArray array = null;
            if (json.startsWith("[")) {
                array = JSON.parseArray(json);
            }
             //array 存着我的数据
            if (array!=null) {
                //行
                int rowNum = 1;
                for (int i = 0; i < array.size(); i++) {
                    JSONObject obj = array.getJSONObject(i);
                    Row row = sheet.createRow(rowNum);//创建行
                    // 设置行高
                    row.setHeightInPoints((short) 70);
                    Cell cell;
                    for (int j = 0; j < titles.length; j++) {
                    //进入列
                        cell = row.createCell(j);
                        // 设置列宽
                        sheet.setColumnWidth(j, 256 * 25);
                        //找到图片列
                        if (obj.getString(columns[j]) != null && j == 4) {
                             //logo将链接展示成图片
                            //过滤不能显示的图片链接
                            if (obj.getString(columns[j]).contains("hrserver.17wanxiao.com")
                            ||
                               obj.getString(columns[j]).equals("//s11.jiuyeb.cn/static/images/dcom.png")
                            || StringUtils.isEmpty(obj.getString(columns[j]))
                            ){
                                 //不合格的链接直接直接将这一列为空
                                cell.setCellValue("");
                            } else {
                                String str = "";
                                //判断是否是以‘//’开头的链接，需要拼接http
                                if (obj.getString(columns[j]).startsWith("//")) {
                                    str = "http:" + obj.getString(columns[j]);
                                }else {
                                    str=obj.getString(columns[j]);
                                }
                                //图片地址
                                // System.out.println("图片地址:"+str);
                                //获得图片流
                                imgByte=getFileStream(str);
                                if (imgByte != null) {
                                    int addPicture;
                                    // 图片存在即输出图片
                                    //判断图片后缀
                                    String hz=str.substring(str.length() - 3);
                                    if("jpg".equals(hz)){
                                        addPicture = workbook.addPicture(imgByte, workbook.PICTURE_TYPE_JPEG);
                                    }else {
                                        addPicture = workbook.addPicture(imgByte, workbook.PICTURE_TYPE_PNG);
                                    }

                                    Drawing drawing = sheet.createDrawingPatriarch();
                                    //图片位置 j是列，rowNum是行
                                    ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j,rowNum, j+1,rowNum +1);
                                    Picture picture = drawing.createPicture(anchor, addPicture);

                                }


                            }

                        }
                        else {
                            cell.setCellValue(obj.getString(columns[j]));
                        }
                    }
                    rowNum++;
                }
            }

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" +
                    System.currentTimeMillis() + ".csv");
            outputStream = response.getOutputStream();
           //输出excel
            workbook.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != outputStream) {
                outputStream.close();
            }
        }
    }
    /**
     * 得到文件流
     * @param url
     * @return
     */
    public static byte[] getFileStream(String url){
        try {
            URL httpUrl = new URL(url);
            HttpURLConnection conn = (HttpURLConnection)httpUrl.openConnection();
            conn.setRequestMethod("GET");
            conn.setConnectTimeout(5 * 1000);
            InputStream inStream = conn.getInputStream();//通过输入流获取图片数据
            byte[] btImg = readInputStream(inStream);//得到图片的二进制数据
            return btImg;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 从输入流中获取数据
     * @param inStream 输入流
     * @return
     * @throws Exception
     */
    public static byte[] readInputStream(InputStream inStream) throws Exception{
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int len = 0;
        while( (len=inStream.read(buffer)) != -1 ){
            outStream.write(buffer, 0, len);
        }
        inStream.close();
        return outStream.toByteArray();
    }

}