Exemple #1
0
 protected int loadPicture(HSSFWorkbook wb, File reportPNG) throws IOException {
   int pictureIndex = 0;
   FileInputStream fis = null;
   ByteArrayOutputStream bos = null;
   try {
     fis = new FileInputStream(reportPNG);
     bos = new ByteArrayOutputStream();
     int c;
     while ((c = fis.read()) != -1) bos.write(c);
     pictureIndex = wb.addPicture(bos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG);
   } finally {
     if (fis != null) fis.close();
     if (bos != null) bos.close();
   }
   return pictureIndex;
 }
  // Metodo que permite insertar imagenes a el excel
  private static int Cargarimagen(File ruta, HSSFWorkbook libro) throws IOException {
    int indice;
    FileInputStream origen = null;
    ByteArrayOutputStream destino = null;
    try {
      // Leer la imagen del archivo
      origen = new FileInputStream(ruta);
      destino = new ByteArrayOutputStream();
      int c;
      // Copiar la imagen bit a bit para ser pasada al execel
      while ((c = origen.read()) != -1) destino.write(c);

      // Agregamos la iamgen al libro execel
      indice = libro.addPicture(destino.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG);

    } finally {
      if (origen != null) origen.close();
      if (destino != null) destino.close();
    }
    return indice;
  }
  /**
   * 复制来源表格中第1个SHEET中的图片到目标表格中的第1个SHEET中。
   *
   * @param destBook -- 目标表格
   * @param srcBook -- 来源表格
   */
  private static void copySheetImage(HSSFWorkbook destBook, HSSFWorkbook srcBook) {
    // 来源表单
    HSSFSheet srcSheet = srcBook.getSheetAt(0);
    // 目标表单
    HSSFSheet destSheet = destBook.getSheetAt(0);

    // 需要偏移的行数
    int endRowNum = destSheet.getPhysicalNumberOfRows();

    // 取来源表单中的图片对象
    List<HSSFPicture> lsSrcPicture = getAllPicture(srcSheet);
    _log.showDebug("----------source picture size:" + lsSrcPicture.size());

    // 取所有子图形数据,如果是主从报表且明细数据占多页时,则会报空指针错误
    List<HSSFPictureData> lsPicData = null;
    try {
      lsPicData = srcBook.getAllPictures();
    } catch (Exception e) {
      // e.printStackTrace();
      _log.showWarn("由于表单明细有多页,造成临时表的图片数据取不到,只能采用原表第1个图替代!");

      // 原表中也没有图片,则不处理图片复制了
      lsPicData = destBook.getAllPictures();
      if (lsPicData == null || lsPicData.isEmpty()) return;

      // 只取原表中第1个图片信息
      List<HSSFPictureData> destData = FactoryUtil.newList();
      for (int i = 0, n = lsSrcPicture.size(); i < n; i++) {
        destData.add(lsPicData.get(0));
      }
      lsPicData = destData;
    }
    if (lsPicData == null || lsPicData.isEmpty()) return;
    _log.showDebug("----------source data size:" + lsPicData.size());

    // data数量可能大于图片数量
    if (lsSrcPicture.size() > lsPicData.size()) {
      _log.showWarn("图片数量与数据数量不符!");
      return;
    }

    // 取图片管理器
    HSSFPatriarch destDraw = destSheet.getDrawingPatriarch();
    if (destDraw == null) {
      destDraw = destSheet.createDrawingPatriarch();
    }

    // 取原目标表单中的图片对象
    List<HSSFPicture> lsDestPicture = getAllPicture(destSheet);
    int index = lsDestPicture.size();

    for (int i = 0, n = lsSrcPicture.size(); i < n; i++) {
      // 取图片对象
      HSSFPicture picture = lsSrcPicture.get(i);
      // 根据图片序号取图片数据
      HSSFPictureData picdata = lsPicData.get(i);
      // 取图片字节信息
      byte[] datas = picdata.getData();

      // 取图片位置信息
      HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor();

      // 添加行偏移值
      anchor.setRow1(anchor.getRow1() + endRowNum);
      anchor.setRow2(anchor.getRow2() + endRowNum);

      // 插入新图片,返回的新图片序号无效
      destBook.addPicture(datas, picdata.getFormat());
      // 上面代码中新建图片的序号没有考虑原有图片数量,所以取原图片数量+1作为新图片的序号
      index++;
      _log.showDebug("---------copy new image index=" + index);

      destDraw.createPicture(anchor, index);
    }
  }
Exemple #4
0
  /**
   * 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
   *
   * @param headers 表格属性列名数组
   * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
   *     javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
   */
  @SuppressWarnings("unchecked")
  public Workbook exportExcel(String[] headers, Collection<T> dataset) {
    // 生成一个表格
    HSSFSheet sheet = workbook.createSheet(sheetName);
    // 设置表格默认列宽度为15个字节
    if (useStyle) sheet.setDefaultColumnWidth(defaultColumnWidth);
    // 生成一个样式

    // 声明一个画图的顶级管理器
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    // 定义注释的大小和位置,详见文档
    HSSFComment comment =
        patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
    // 设置注释内容
    comment.setString(new HSSFRichTextString(docPrse));
    // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.
    comment.setAuthor(author);

    // 产生表格标题行
    HSSFRow row = sheet.createRow(startRow);
    int colNum1 = startCol;
    for (short i = 0; i < headers.length; i++) {
      HSSFCell cell = row.createCell(colNum1);
      if (useStyle) cell.setCellStyle(style);
      HSSFRichTextString text = new HSSFRichTextString(headers[i]);
      cell.setCellValue(text);
      colNum1++;
    }

    // 遍历集合数据,产生数据行
    Iterator<T> it = dataset.iterator();
    int index = startRow;
    while (it.hasNext()) {
      index++;
      row = sheet.createRow(index);
      T t = (T) it.next();
      // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
      Field[] fields = t.getClass().getDeclaredFields();
      int colRow2 = startCol;
      for (short i = 0; i < fields.length; i++) {
        HSSFCell cell = row.createCell(colRow2);
        if (useStyle) cell.setCellStyle(style2);
        Field field = fields[i];
        String fieldName = field.getName();
        String getMethodName =
            "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
        try {
          Class tCls = t.getClass();
          Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
          Object value = getMethod.invoke(t, new Object[] {});
          // 判断值的类型后进行强制类型转换
          String textValue = null;
          if (value instanceof Boolean) {
            boolean bValue = (Boolean) value;
            textValue = "男";
            if (!bValue) {
              textValue = "女";
            }
          } else if (value instanceof Date) {
            Date date = (Date) value;
            SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
            textValue = sdf.format(date);
          } else if (value instanceof byte[]) {
            // 有图片时,设置行高为60px;
            row.setHeightInPoints(picCellHeight);
            // 设置图片所在列宽度为80px,注意这里单位的一个换算
            sheet.setColumnWidth(i, picCellWidth);
            // sheet.autoSizeColumn(i);
            byte[] bsValue = (byte[]) value;
            HSSFClientAnchor anchor =
                new HSSFClientAnchor(
                    0, 0, 1023, 255, (short) colRow2, index, (short) colRow2, index);
            anchor.setAnchorType(2);
            patriarch.createPicture(
                anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
          } else {
            // 其它数据类型都当作字符串简单处理
            textValue = value.toString();
          }
          // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
          if (textValue != null) {
            Pattern p = Pattern.compile("^//d+(//.//d+)?$");
            Matcher matcher = p.matcher(textValue);
            if (matcher.matches()) {
              // 是数字当作double处理
              cell.setCellValue(Double.parseDouble(textValue));
            } else {
              HSSFRichTextString richString = new HSSFRichTextString(textValue);
              HSSFFont font3 = workbook.createFont();
              font3.setColor(HSSFColor.BLUE.index);
              richString.applyFont(font3);
              cell.setCellValue(richString);
            }
          }
        } catch (SecurityException e) {
          e.printStackTrace();
        } catch (NoSuchMethodException e) {
          e.printStackTrace();
        } catch (IllegalArgumentException e) {
          e.printStackTrace();
        } catch (IllegalAccessException e) {
          e.printStackTrace();
        } catch (InvocationTargetException e) {
          e.printStackTrace();
        } finally {
          // 清理资源
        }
        colRow2++;
      }
    }
    return workbook;
  }
  public void testRemoveShapes() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

    HSSFSimpleShape rectangle = patriarch.createSimpleShape(new HSSFClientAnchor());
    rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);

    int idx = wb.addPicture(new byte[] {1, 2, 3}, Workbook.PICTURE_TYPE_JPEG);
    patriarch.createPicture(new HSSFClientAnchor(), idx);

    patriarch.createCellComment(new HSSFClientAnchor());

    HSSFPolygon polygon = patriarch.createPolygon(new HSSFClientAnchor());
    polygon.setPoints(new int[] {1, 2}, new int[] {2, 3});

    patriarch.createTextbox(new HSSFClientAnchor());

    HSSFShapeGroup group = patriarch.createGroup(new HSSFClientAnchor());
    group.createTextbox(new HSSFChildAnchor());
    group.createPicture(new HSSFChildAnchor(), idx);

    assertEquals(patriarch.getChildren().size(), 6);
    assertEquals(group.getChildren().size(), 2);

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 12);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 12);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);

    assertEquals(patriarch.getChildren().size(), 6);

    group = (HSSFShapeGroup) patriarch.getChildren().get(5);
    group.removeShape(group.getChildren().get(0));

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 10);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 10);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);

    group = (HSSFShapeGroup) patriarch.getChildren().get(5);
    patriarch.removeShape(group);

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 8);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 8);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 5);

    HSSFShape shape = patriarch.getChildren().get(0);
    patriarch.removeShape(shape);

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 6);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 4);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 6);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 4);

    HSSFPicture picture = (HSSFPicture) patriarch.getChildren().get(0);
    patriarch.removeShape(picture);

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 5);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 3);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 5);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 3);

    HSSFComment comment = (HSSFComment) patriarch.getChildren().get(0);
    patriarch.removeShape(comment);

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 3);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 2);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 3);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 2);

    polygon = (HSSFPolygon) patriarch.getChildren().get(0);
    patriarch.removeShape(polygon);

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 2);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 1);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 2);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 1);

    HSSFTextbox textbox = (HSSFTextbox) patriarch.getChildren().get(0);
    patriarch.removeShape(textbox);

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 0);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 0);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);
  }
  @Test
  public void test_poi() {

    final int rowNum = 27;
    final int colNum = 15;
    HSSFWorkbook wb = null;
    Sheet sheet = null;

    String today = "2013/8/31";
    String sign = "Month to date";

    String[] titles = {
      "",
      "",
      "",
      "Chinapay eMail\r\n 商城总计",
      "Japan Page\r\n 日本馆首页",
      "Taiwan Page\r\n 台湾馆首页",
      "USA Page\r\n 美国馆首页",
      "Anhui Page\r\n 安徽馆首页",
      "China Page\r\n 中国馆首页"
    };

    String[] colNames = {
      "",
      "Page View (PV)\r\n 浏览量",
      "Unique Visitor (UV)\r\n 独立访客",
      "Completed Orders\r\n 确认订单",
      "Transaction Amount\r\n 交易金额",
      "1st Top Seller\r\n 最佳销量",
      "Unit Price 单价",
      "Qty Sold 销量",
      "2nd Top Seller\r\n 第二销量",
      "Unit Price 单价",
      "Qty Sold 销量",
      "3rd Top Seller\r\n 第三销量",
      "Unit Price 单价",
      "Qty Sold 销量",
      "1st Top Seller\r\n 最佳销量",
      "Unit Price 单价",
      "Qty Sold 销量",
      "2nd Top Seller\r\n 第二销量",
      "Unit Price 单价",
      "Qty Sold 销量",
      "3rd Top Seller\r\n 第三销量",
      "Unit Price 单价",
      "Qty Sold 销量"
    };

    int n = 0;
    int len = 1;
    String fileName = "D:/日报.xls";
    File f = new File(fileName);

    ByteArrayOutputStream byteArrayOut = null;
    BufferedImage bufferImg = null;

    String[] jpgUrls = {
      "http://img.chinapay.com/data/files/store_37452/goods_93/small_201303271804531386.jpg",
      "http://img.chinapay.com/data/files/store_44066/goods_37/201308280953576580.jpg",
      "http://img.chinapay.com/data/files/store_289253/goods_95/small_201309031434558044.jpg",
      "http://img.chinapay.com/data/files/store_289253/goods_180/small_201309031403003861.jpg",
      "http://img.chinapay.com/data/files/store_37452/goods_98/small_201309121508186810.jpg",
      "http://img.chinapay.com/data/files/store_37452/goods_24/small_201301241133447193.jpg"
    };
    String[] https = {
      "http://emall.chinapay.com/goods/37452/1010000109792.html",
      "http://emall.chinapay.com/goods/44066/1010000119323.html",
      "http://emall.chinapay.com/goods/289253/1010000119621.html?jpsv=laoxcashback6",
      "http://emall.chinapay.com/goods/289253/1010000119627.html?jpsv=laoxcashback6",
      "http://emall.chinapay.com/goods/37452/1010000120588.html",
      "http://emall.chinapay.com/goods/37452/1010000107096.html"
    };

    URL url = null;

    HSSFHyperlink link = null;
    HSSFPatriarch patri = null;
    HSSFClientAnchor anchor = null;

    try {

      if (!f.exists()) {
        wb = new HSSFWorkbook();
      } else {
        FileInputStream in = new FileInputStream(fileName);
        wb = new HSSFWorkbook(in);
      }

      CellStyle style = wb.createCellStyle();
      style.setFillForegroundColor(HSSFCellStyle.THIN_BACKWARD_DIAG);
      style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      // style.setLeftBorderColor(HSSFColor.RED.index);

      style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
      style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
      style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
      style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

      style.setWrapText(true);

      sheet = wb.createSheet("sheet " + ((int) (100000 * Math.random())));

      // 设置列的宽度
      sheet.setDefaultColumnWidth(20);
      sheet.setDefaultRowHeight((short) 400);

      Row row = null;
      Cell cell = null;

      for (int r = 0; r < rowNum; r++) {
        row = sheet.createRow(r);

        // 设置第1行当高度
        if (r == 0) {
          row.setHeightInPoints(30);
        }

        // 设置第2列以后的宽度(即列号>=2的列,列号从0开始)
        if (r >= 2) {
          sheet.setColumnWidth(r, 3020);
        }

        for (int c = 0; c < colNum; c++) {
          cell = row.createCell(c);
          cell.setCellStyle(style);

          // 处理第一行
          if (r == 0) {
            sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
            sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 6));
            sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 8));
            sheet.addMergedRegion(new CellRangeAddress(r, r, 9, 10));
            sheet.addMergedRegion(new CellRangeAddress(r, r, 11, 12));
            sheet.addMergedRegion(new CellRangeAddress(r, r, 13, 14));

            if (c < 3) {
              cell.setCellValue(titles[n++]);
            } else {
              if ((c & 1) == 1) {
                System.out.println("c===" + c);
                cell.setCellValue(titles[n++]);
              }
            }
          }

          // 处理第2~8行
          if (r > 0 && r <= 8) {
            if (c == 0) {
              if (r < 8 && (r & 1) == 1) {

                sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));

                System.err.println("row----->" + r + "   len----->" + (len));
                cell.setCellValue(colNames[len++]);
              } else if (r > 8) {

                System.out.println("len+++++++++>" + (len));
                cell.setCellValue(colNames[len++]);
              }
            } else if (c == 1) {
              cell.setCellValue((r & 1) == 1 ? today : sign);
              System.err.println("r---->" + r);
            } else if (c == 2) {
              cell.setCellValue((r & 1) == 1 ? "当天" : "当月");
            } else {
              if ((c & 1) == 1) {
                sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
                cell.setCellValue("26.55");
              }
            }
          }

          // 处理第8行以后的数据(不包括第8行)
          if (r > 8) {
            // 设置列高(图片的高度)
            if (r % 3 == 0) {
              sheet.getRow(r).setHeightInPoints(110);
            }

            if (c == 0) {
              System.err.println("r---->" + r);
              cell.setCellValue(colNames[r - 4]);
            } else if (c == 1) {
              cell.setCellValue((r % 3) == 0 ? today : (r % 3 == 1 ? "PV 浏览量" : "Total Sales 总额"));

            } else if (c == 2) {
              if (r % 9 == 0) {
                sheet.addMergedRegion(new CellRangeAddress(r, r + 8, c, c));

                if (r / 9 == 1) cell.setCellValue("当天");
                else cell.setCellValue("当月");

                cell.setCellStyle(style);
              }

            } else {
              if (r % 3 == 0) {
                if ((c & 1) == 1) {
                  sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));

                  // 添加远程图片信息
                  url = new URL(jpgUrls[(c - 3) / 2]);
                  bufferImg = ImageIO.read(url.openStream());

                  byteArrayOut = new ByteArrayOutputStream();
                  ImageIO.write(bufferImg, "jpg", byteArrayOut);

                  patri = (HSSFPatriarch) sheet.createDrawingPatriarch();
                  anchor = new HSSFClientAnchor(10, 2, 0, 0, (short) c, r, (short) (c + 2), r + 1);
                  patri.createPicture(
                      anchor,
                      wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

                  bufferImg.flush();
                  // link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);

                  // System.out.println(https[(c-3)/2]);
                  // link.setAddress("fetion/"+https[(c-3)/2]);
                  // cell.setHyperlink(link);

                  // link = (HSSFHyperlink) cell.getHyperlink();
                  // link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
                  // link.setAddress(https[(c-3)/2]);
                  // cell.setHyperlink(link);
                }

              } else {
                if ((c & 1) == 0) {
                  link = wb.getCreationHelper().createHyperlink(Hyperlink.LINK_URL);
                  link.setAddress(https[(c - 3) / 2]);
                  cell.setHyperlink(link); // 设定单元格的链接
                  cell.setCellValue("图片超链接");
                } else {
                  cell.setCellValue("Number");
                }
              }
            }
          }
        }
      }

      // 备注
      row = sheet.createRow(27);
      cell = row.createCell(0);
      sheet.addMergedRegion(new CellRangeAddress(27, 27, 0, colNum - 1));
      cell.setCellValue("* 销量排名不以销售金额计算,如相同销量者,则以PV量少者为优胜");

      FileOutputStream out = new FileOutputStream(fileName);
      wb.write(out);
      out.close();
    } catch (Exception e) {
      e.printStackTrace();
    }

    System.out.println("++++++++++++  EXCEl文件  success  +++++++++++++");
  }