将图片导出到excel表格中

需求描述

前几天需要将公众号中的用户发送的图片导出到excel表格中，特此记录。
用户给公众号发送图片后，微信会推给开发者相关数据，比如openid，类型，图片地址等。
我需要做的是：获取图片地址，访问微信，获取图片， 写入文件。

工具类

/**
String[] rowsName :excel中的行标题
String fileName = "XXX" + ".xls";
pageList 保存数据的list集合
*/
public static HSSFWorkbook QdActivePictureExport(String[] rowsname, List<ActiveUserBean> pageList, String fileName) {
        logger.info("START:---导出开始");
        // 创建excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建当前工作表
        HSSFSheet sheet = workbook.createSheet("XXXX报表");
        //画图的顶级管理器，一个sheet只能获取一个
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        /**
         *
         * 1.读取原文件中的内容 2.创建标题行 3.循环创建每一行，然后将数据输入
         */
        String columContent[] = null;
        int rows = 0;// 用于记录创建的行数
        // 设置标题
        // 创建第一行
        HSSFRow row = sheet.createRow(rows);
        HSSFCell cell = null;
        for (int i = 0; i < rowsname.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(rowsname[i]);
        }
        int pageSize = pageList.size();
        if (pageList != null && pageSize > 0) {
            // 循环数据列表
            for (ActiveUserBean dff : pageList) {
                // 第一行已经创建
                rows++;
                // 开始创建行
                row = sheet.createRow(rows);
                // 单元格赋值
                //todo 需要加参数
                cell = row.createCell(0);
                cell.setCellValue(dff.getFrom_user_name());
                cell = row.createCell(1);
                cell.setCellValue(dff.getCity_code());
                cell = row.createCell(2);
                cell.setCellValue(dff.getCreate_time());
                cell = row.createCell(3);
                cell.setCellValue(dff.getMsg_type());
                cell = row.createCell(4);
                //cell.setCellValue(dff.getContent1());
                intoPic(dff.getContent1(), fileName, workbook, sheet, patriarch, row, rows, pageSize);
               
            }
        }
        return workbook;
    }

• intoPic()方法 ,用io流导出

public static void intoPic(String pictureUrl, String filename, HSSFWorkbook workbook, HSSFSheet sheet,
                               HSSFPatriarch patriarch, HSSFRow row, int rows, int pageSize) {
        FileOutputStream fileOut = null;
        logger.info("图片导入开始：" + pictureUrl + "__filename:" + filename + "__rows:" + rows);
        try {
       
            //todo 列 动态变化
            sheet.setColumnWidth(4, 256 * 50 + 184);
            row.setHeight((short) 1000);

            //使用了代理
            String replaceUrl = pictureUrl.replace("http://", "http://www.XXXX.com/XXXX/");
            URL url = new URL(replaceUrl);

            //打开链接
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            //设置请求方式为"GET"
            conn.setRequestMethod("GET");
            //超时响应时间为5秒
            conn.setConnectTimeout(5 * 1000);
            //通过输入流获取图片数据
            InputStream inStream = conn.getInputStream();
           
            //得到图片的二进制数据，以二进制封装得到数据，具有通用性
            byte[] data = readInputStream(inStream);

            //anchor主要用于设置图片的属性  todo row1 row2 动态变化 行 列
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 4, rows, (short) 4, rows);
            //（图片在单元格的位置）
            //0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells.
            anchor.setAnchorType(3);
            patriarch.createPicture(anchor, workbook.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
          
            fileOut = new FileOutputStream(filename);
          
            // 写入excel文件
            workbook.write(fileOut);
            logger.info("图片文件已经生成");

        } catch (Exception e) {
            e.printStackTrace();
            logger.info("错误："+e);
        } finally {
                if (fileOut != null) {
                    try {
                        fileOut.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                        logger.info("finally出错！");
                    }
            }
        }

    }

	//todo 必须有
    private static byte[] readInputStream(InputStream inStream) throws IOException {
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        //创建一个Buffer字符串
        byte[] buffer = new byte[1024 * 8];
        //每次读取的字符串长度，如果为-1，代表全部读取完毕
        int len = 0;
        //使用一个输入流从buffer里把数据读取出来
        while ((len = inStream.read(buffer)) != -1) {
            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        //关闭输入流
        inStream.close();
        //把outStream里的数据写入内存
        return outStream.toByteArray();
    }

• 导出到本地

//上面 工具类返回了 HSSFWorkbook workbook
 QdActiveOutExcelUtil.write(response,fileName,workbook);
 
 /**
     * 输出到客户端
     *
     * @param response
     * @param fileName
     * @param workbook
     * @throws IOException
     */
    public static void write(HttpServletResponse response, String fileName, HSSFWorkbook workbook) throws IOException {
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode(fileName));
        workbook.write(response.getOutputStream());
    }

本地测试类

	@Test
    public void test3() throws IOException {
        FileOutputStream fileOut = null;
        try {
            //String befor = "E:\\zsl\\picture\\28314.jpg";

            //创建一个表格
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("导出图片报表");
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            //todo 需要改变
            HSSFRow row = sheet.createRow(3);

            //sheet.setColumnWidth( 100,  100);
            //todo  可变
            sheet.setColumnWidth(5, 256 * 50 + 184);
            row.setHeight((short) 1000);

            //todo 图片地址
            String pictureUrl = weixin图片地址";

            URL url = new URL(pictureUrl);
            //打开链接
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            //设置请求方式为"GET"
            conn.setRequestMethod("GET");
            //超时响应时间为5秒
            conn.setConnectTimeout(5 * 1000);
            //通过输入流获取图片数据
            InputStream inStream = conn.getInputStream();

            //本地测试
            //BufferedInputStream inStream = new BufferedInputStream(new FileInputStream(befor));
            //得到图片的二进制数据，以二进制封装得到数据，具有通用性
            byte[] data = readInputStream(inStream);

            //anchor主要用于设置图片的属性 todo 参数需要改变  row1，row2 决定图片在哪一行哪一列
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 5, 3, (short) 5, 3);
            //（图片在单元格的位置）
            //0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells.
            anchor.setAnchorType(0);
            patriarch.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
            //String name = "试Excel.xls";
            fileOut = new FileOutputStream("E:/试4Excel.xls");
            //fileOut = new FileOutputStream(name);
            // 写入excel文件
            wb.write(fileOut);
            System.out.println("----Excle3文件已生成------");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fileOut != null) {
                try {
                    fileOut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

导出图片的时候设置了单元格的大小，需要注意图片在哪个单元格内。