正文
POI插入图片至Excel使用固定的长宽
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
使用POI在Excel里插入图片,如何使插入的图片使用固定的大小?先介绍原有的两种方式:
一种是指定开始和结尾单元格,然后从头画到尾,相当于平铺
还有一种就是仅指定开始的单元格,图片的大小跟这个单元格的长宽有关,可以放大缩小固定的倍数,相当于左对齐
第一种效果如下:
第二种效果如下:
第一种方法的关键代码如下:
private void pictureToSheet(Sheet finalSheet, Row row, Cell cell, int pictureIdx) {
Drawing patriarch = finalSheet.createDrawingPatriarch();
ExcelPositionRange excelPositionRange = ExcelTransferUtils.getMergedRegionPositionRange(finalSheet, row.getRowNum(), cell.getColumnIndex());
ClientAnchor anchor = patriarch.createAnchor(0, 0, 1023, 255,
excelPositionRange.getFirstCol(),
excelPositionRange.getFirstRow(),
excelPositionRange.getLastCol(),
excelPositionRange.getLastRow()
);
patriarch.createPicture(anchor, pictureIdx);
}
注:代码中的excelPositionRange,是俺自定义的一个类型。里边只有四个变量和get/set方法,四个变量分别是单元格的开始、结尾单元格的横纵坐标。这个大家可以根据需要来改。
PS:其中1023和255指的是每个单元格被切分的份数,指定的是最后的单元格的最右下角的一个点,其方法的源代码在本文最后的附录里。
第二种方法的关键代码如下:
private void pictureToSheet(Sheet finalSheet, Row row, Cell cell, int pictureIdx) {
Drawing patriarch = finalSheet.createDrawingPatriarch();
ExcelPositionRange excelPositionRange = ExcelTransferUtils.getMergedRegionPositionRange(finalSheet, row.getRowNum(), cell.getColumnIndex()); CreationHelper helper = finalSheet.getWorkbook().getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor(); // 图片插入坐标
anchor.setCol1(excelPositionRange.getFirstCol());
anchor.setRow1(excelPositionRange.getFirstRow()); // 使用固定的长宽比例系数
double a = 5.9;
double b = 1; // 插入图片
Picture pict = patriarch.createPicture(anchor, pictureIdx);
pict.resize(a,b);
}
进阶方法:
在第二种方法的基础上,可以计算出不同的系数,达到生成图片都是同一个长宽的功能,从而输出固定大小的图片
private void pictureToSheet(Sheet finalSheet, Row row, Cell cell, int pictureIdx) {
Drawing patriarch = finalSheet.createDrawingPatriarch();
ExcelPositionRange excelPositionRange = ExcelTransferUtils.getMergedRegionPositionRange(finalSheet, row.getRowNum(), cell.getColumnIndex()); CreationHelper helper = finalSheet.getWorkbook().getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor(); // 图片插入坐标
anchor.setCol1(excelPositionRange.getFirstCol());
anchor.setRow1(excelPositionRange.getFirstRow()); // 指定我想要的长宽
double standardWidth = 112;
double standardHeight = 41; // 计算单元格的长宽
double cellWidth = finalSheet.getColumnWidthInPixels(cell.getColumnIndex());
double cellHeight = cell.getRow().getHeightInPoints()/72*96; // 计算需要的长宽比例的系数
double a = standardWidth / cellWidth;
double b = standardHeight / cellHeight; // 插入图片
Picture pict = patriarch.createPicture(anchor, pictureIdx);
pict.resize(a,b);
}
PS:这里参考了POI获取单元格长宽的的方法:http://www.cnblogs.com/acm-bingzi/p/poiWidth.html
附录一
一般插入图片的样例代码:
// 插入 PNG 图片至 Excel
String fileName = strAppRootPath + "images/" + "bxlogo.png"; InputStream is = new FileInputStream(fileName);
byte[] bytes = IOUtils.toByteArray(is); int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); CreationHelper helper = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor(); // 图片插入坐标
anchor.setCol1(0);
anchor.setRow1(1);
// 插入图片
Picture pict = drawing.createPicture(anchor, pictureIdx);
pict.resize();
附录二
patriarch.createAnchor的源代码跟踪
/**
* Creates a new client anchor and sets the top-left and bottom-right
* coordinates of the anchor.
*
* Note: Microsoft Excel seems to sometimes disallow
* higher y1 than y2 or higher x1 than x2, you might need to
* reverse them and draw shapes vertically or horizontally flipped!
*
* @param dx1 the x coordinate within the first cell.
* @param dy1 the y coordinate within the first cell.
* @param dx2 the x coordinate within the second cell.
* @param dy2 the y coordinate within the second cell.
* @param col1 the column (0 based) of the first cell.
* @param row1 the row (0 based) of the first cell.
* @param col2 the column (0 based) of the second cell.
* @param row2 the row (0 based) of the second cell.
*/
public HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2) {
super(dx1, dy1, dx2, dy2); checkRange(dx1, 0, 1023, "dx1");
checkRange(dx2, 0, 1023, "dx2");
checkRange(dy1, 0, 255, "dy1");
checkRange(dy2, 0, 255, "dy2");
checkRange(col1, 0, MAX_COL, "col1");
checkRange(col2, 0, MAX_COL, "col2");
checkRange(row1, 0, MAX_ROW, "row1");
checkRange(row2, 0, MAX_ROW, "row2"); setCol1((short) Math.min(col1, col2));
setCol2((short) Math.max(col1, col2));
setRow1(Math.min(row1, row2));
setRow2(Math.max(row1, row2)); if (col1 > col2){
_isHorizontallyFlipped = true;
}
if (row1 > row2){
_isVerticallyFlipped = true;
}
}
原创文章,欢迎转载,转载请注明出处!