首页 > 代码库 > POI插入图片至Excel使用固定的长宽
POI插入图片至Excel使用固定的长宽
使用POI在Excel里插入图片，如何使插入的图片使用固定的大小？先介绍原有的两种方式：
一种是指定开始和结尾单元格，然后从头画到尾，相当于平铺
还有一种就是仅指定开始的单元格，图片的大小跟这个单元格的长宽有关，可以放大缩小固定的倍数，相当于左对齐
第一种效果如下：
第二种效果如下：
第一种方法的关键代码如下：
1 private void pictureToSheet(Sheet finalSheet, Row row, Cell cell, int pictureIdx) { 2 Drawing patriarch = finalSheet.createDrawingPatriarch(); 3 ExcelPositionRange excelPositionRange = ExcelTransferUtils.getMergedRegionPositionRange(finalSheet, row.getRowNum(), cell.getColumnIndex()); 4 ClientAnchor anchor = patriarch.createAnchor(0, 0, 1023, 255, 5 excelPositionRange.getFirstCol(), 6 excelPositionRange.getFirstRow(), 7 excelPositionRange.getLastCol(), 8 excelPositionRange.getLastRow() 9 );10 patriarch.createPicture(anchor, pictureIdx);11 }
注：代码中的excelPositionRange，是俺自定义的一个类型。里边只有四个变量和get/set方法，四个变量分别是单元格的开始、结尾单元格的横纵坐标。这个大家可以根据需要来改。
PS：其中1023和255指的是每个单元格被切分的份数，指定的是最后的单元格的最右下角的一个点，其方法的源代码在本文最后的附录里。
第二种方法的关键代码如下：
1 private void pictureToSheet(Sheet finalSheet, Row row, Cell cell, int pictureIdx) { 2 Drawing patriarch = finalSheet.createDrawingPatriarch(); 3 ExcelPositionRange excelPositionRange = ExcelTransferUtils.getMergedRegionPositionRange(finalSheet, row.getRowNum(), cell.getColumnIndex()); 4 5 CreationHelper helper = finalSheet.getWorkbook().getCreationHelper(); 6 ClientAnchor anchor = helper.createClientAnchor(); 7 8 // 图片插入坐标 9 anchor.setCol1(excelPositionRange.getFirstCol());10 anchor.setRow1(excelPositionRange.getFirstRow());11 12 // 使用固定的长宽比例系数13 double a = 5.9;14 double b = 1;15 16 // 插入图片17 Picture pict = patriarch.createPicture(anchor, pictureIdx);18 pict.resize(a,b);19 }
进阶方法：
在第二种方法的基础上，可以计算出不同的系数，达到生成图片都是同一个长宽的功能，从而输出固定大小的图片
1 private void pictureToSheet(Sheet finalSheet, Row row, Cell cell, int pictureIdx) { 2 Drawing patriarch = finalSheet.createDrawingPatriarch(); 3 ExcelPositionRange excelPositionRange = ExcelTransferUtils.getMergedRegionPositionRange(finalSheet, row.getRowNum(), cell.getColumnIndex()); 4 5 CreationHelper helper = finalSheet.getWorkbook().getCreationHelper(); 6 ClientAnchor anchor = helper.createClientAnchor(); 7 8 // 图片插入坐标 9 anchor.setCol1(excelPositionRange.getFirstCol());10 anchor.setRow1(excelPositionRange.getFirstRow());11 12 // 指定我想要的长宽13 double standardWidth = 112;14 double standardHeight = 41;15 16 // 计算单元格的长宽17 double cellWidth = finalSheet.getColumnWidthInPixels(cell.getColumnIndex());18 double cellHeight = cell.getRow().getHeightInPoints()/72*96;19 20 // 计算需要的长宽比例的系数21 double a = standardWidth / cellWidth;22 double b = standardHeight / cellHeight;23 24 // 插入图片25 Picture pict = patriarch.createPicture(anchor, pictureIdx);26 pict.resize(a,b);27 }
PS：这里参考了POI获取单元格长宽的的方法：http://www.cnblogs.com/acm-bingzi/p/poiWidth.html
附录一
一般插入图片的杨丽代码：
1 // 插入 PNG 图片至 Excel 2 String fileName = strAppRootPath + "images/" + "bxlogo.png"; 3 4 InputStream is = new FileInputStream(fileName); 5 byte[] bytes = IOUtils.toByteArray(is); 6 7 int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); 8 9 CreationHelper helper = workbook.getCreationHelper();10 Drawing drawing = sheet.createDrawingPatriarch();11 ClientAnchor anchor = helper.createClientAnchor();12 13 // 图片插入坐标14 anchor.setCol1(0);15 anchor.setRow1(1);16 // 插入图片17 Picture pict = drawing.createPicture(anchor, pictureIdx);18 pict.resize();
附录二
patriarch.createAnchor的源代码跟踪
1 /** 2 * Creates a new client anchor and sets the top-left and bottom-right 3 * coordinates of the anchor. 4 * 5 * Note: Microsoft Excel seems to sometimes disallow 6 * higher y1 than y2 or higher x1 than x2, you might need to 7 * reverse them and draw shapes vertically or horizontally flipped! 8 * 9 * @param dx1 the x coordinate within the first cell.10 * @param dy1 the y coordinate within the first cell.11 * @param dx2 the x coordinate within the second cell.12 * @param dy2 the y coordinate within the second cell.13 * @param col1 the column (0 based) of the first cell.14 * @param row1 the row (0 based) of the first cell.15 * @param col2 the column (0 based) of the second cell.16 * @param row2 the row (0 based) of the second cell.17 */18 public HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2) {19 super(dx1, dy1, dx2, dy2);20 21 checkRange(dx1, 0, 1023, "dx1");22 checkRange(dx2, 0, 1023, "dx2");23 checkRange(dy1, 0, 255, "dy1");24 checkRange(dy2, 0, 255, "dy2");25 checkRange(col1, 0, MAX_COL, "col1");26 checkRange(col2, 0, MAX_COL, "col2");27 checkRange(row1, 0, MAX_ROW, "row1");28 checkRange(row2, 0, MAX_ROW, "row2");29 30 setCol1((short) Math.min(col1, col2));31 setCol2((short) Math.max(col1, col2));32 setRow1(Math.min(row1, row2));33 setRow2(Math.max(row1, row2));34 35 if (col1 > col2){36 _isHorizontallyFlipped = true;37 }38 if (row1 > row2){39 _isVerticallyFlipped = true;40 }41 }
原创文章，欢迎转载，转载请注明出处！
POI插入图片至Excel使用固定的长宽
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。