Excel插入图片

 场景：将Excel中插入图片，效果如下：

问题：1）图片尺寸，在实现过程中可设置图片尺寸大小，或保留原使图片，在设置图片尺寸时最大宽度为255（没有查到为何这样限制）。保留原图，图片尺寸又太大，所以决定先将图片抽点成合适尺寸，然后采用保留原始图片，同时如果想达到图片在特定单元格内除代码处理外需要先将Excel模板单元格的尺寸设置好，方法选中需要设置的行，右键设置行高。

 

方法一（实现：poi3.10）：

     

InputStream is = new FileInputStream("读入的Excel模板");
     HSSFWorkbook wb = new HSSFWorkbook(is);
     HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
  
     ByteArrayOutputStream bos = new ByteArrayOutputStream();
     BufferedImage BufferImg = ImageIO.read(new File("插入图片所在的地址"));
     ImageIO.write(BufferImg, "JPEG", bos);
    //public HSSFClientAnchor(int dx1,int dy1,int dx2,int dy2,short col1,int row1,short col2,int row2)
    HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,200,200,(short) 5,4,(short)5,4);
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    patriarch.createPicture(anchor,
    //wb.addPicture(bos.toByteArray(),wb.PICTURE_TYPE_JPEG));         //根据固定宽度
    wb.addPicture(bos.toByteArray(),wb.PICTURE_TYPE_JPEG)).resize(1); //保持原图：
    OutputStream os = new FileOutputStream("生成的Excel路径");
        wb.write(os);
        is.close();
        os.flush();
        os.close();

  

方法二：jxl读取模板，在图片所在列填充图片地址，在读入该Excel将地址转换成图片

  

//templateFileName excel模板, beans插入到Excel的数据Map, destFileName 输出到的模板，
  Configuration config = new Configuration();
  XLSTransformer transformer = new XLSTransformer(config);
  transformer.transformXLS(templateFileName, beans, destFileName);
  InputStream is = new FileInputStream(destFileName);
  Workbook resultWorkbook = WorkbookFactory.create(is);
  Iterator rowIter = resultWorkbook.getSheetAt(0).rowIterator();
  while (rowIter.hasNext()) {
   HSSFRow row = (HSSFRow) rowIter.next();
   Iterator cellIter = row.cellIterator();
   while (cellIter.hasNext()) {
    HSSFCell cell = (HSSFCell) cellIter.next();
    final String IMG_PREFIX = "#IMG#";
    if (cell.toString().startsWith(IMG_PREFIX)) {
     String cellValue = cell.toString();
     cell.setCellValue("");
     String imagePath = cellValue.replaceFirst(IMG_PREFIX, "");
     File imageFile = new File(imagePath);
     if (imageFile.exists()) {

      ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
      BufferedImage bufferImg = ImageIO.read(imageFile);
      ImageIO.write(bufferImg, "jpg", byteArrayOut);
      byte[] imgBytes = byteArrayOut.toByteArray();
      int pictureIdx = resultWorkbook.addPicture(imgBytes, Workbook.PICTURE_TYPE_JPEG);
      CreationHelper helper = resultWorkbook.getCreationHelper();

      Drawing drawing = resultWorkbook.getSheetAt(0).createDrawingPatriarch();
      ClientAnchor anchor = helper.createClientAnchor();
      anchor.setCol1(cell.getColumnIndex());
      anchor.setRow1(cell.getRowIndex());
      Picture pict = drawing.createPicture(anchor, pictureIdx);
      pict.resize();
     }
    }
   }
  }
  FileOutputStream fileOut = new FileOutputStream("D:\\333.xls");
  resultWorkbook.write(fileOut);
  fileOut.close();

  

注意：要在Excel模板中图片所在位置用表达式进行读取：#IMG#${图片路径值}