POI插入图片至Excel使用固定的长宽

系统管理员 2021-12-07 12:29 383阅读 0赞

使用POI在Excel里插入图片，如何使插入的图片使用固定的大小？先介绍原有的两种方式：

一种是指定开始和结尾单元格，然后从头画到尾，相当于**平铺**  
　　还有一种就是仅指定开始的单元格，图片的大小跟这个单元格的长宽有关，可以放大缩小固定的倍数，相当于**左对齐**

第一种效果如下：

![380239-20170810090349917-460533454.png][]

第二种效果如下：

![380239-20170810090400511-748346106.png][]

第一种方法的关键代码如下：

注：代码中的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 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][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的源代码跟踪

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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 }

原创文章，欢迎转载，转载请注明出处！

转载于:https://www.cnblogs.com/acm-bingzi/p/poiPicture.html

[380239-20170810090349917-460533454.png]: /images/20211207/a44a296822784d5fb7fbaf41c6f24202.png
[380239-20170810090400511-748346106.png]: /images/20211207/53c2fe5c9e7c4ee589d7d2947b77ab4e.png
[http_www.cnblogs.com_acm-bingzi_p_poiWidth.html]: http://www.cnblogs.com/acm-bingzi/p/poiWidth.html
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211207/cabd7710c6344057bf80e5fbcf619ea8.png