java - Apache POI Excel 工作表 : resize a picture while keeping its ratio

Question

您好，我使用 POI 创建了 Excel 表格。我已经通过以下方式添加了图片(jpg 文件):

Workbook wb = new HSSFWorkbook();
CreationHelper helper = wb.getCreationHelper();
//...
InputStream is = new FileInputStream("img.jpg");
byte[] bytes = IOUtils.toByteArray(is);
int picIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1(5);
anchor.setRow1(5);
Picture pict = drawing.createPicture(anchor, picIdx);
pict.resize();

现在我想让图片适合那个单元格，但我不想改变它的纵横比。我可以调整大小的比例是相对于单元格的，它显然可以有不同的比例。我试图计算比例，但问题是，我无法以像素为单位获取或设置行高，只能以 pt 为单位，而且我也无法计算单元格比率，因为我无法在同一单位...有什么建议吗？

Answer 1

有一个Units POI中的类，提供像素和点之间的转换。
这里有一些方法可能有助于设置单元格的宽度和高度。

1.厘米换算为像素

public static int cmToPx(double cm) {
    return (int) Math.round(cm * 96 / 2.54D);
}

96 是我显示器的 DPI(从 dpilove 检查你的 DPI)
1 英寸 = 2.54 厘米

2.厘米到行高

public static int cmToH(double cm) {
    return (int) (Units.pixelToPoints(cmToPx(cm)) * 20); //POI's Units
}

用法:sheet.setDefaultRowHeight(cmToH(1.0))
引用:HSSFRow#getHeightInPoints

Set the height in "twips" or 1/20th of a point.

3.厘米换算列宽

public static int cmToW(double cm) {
    return (int) Math.round(((cmToPx(cm) - 5.0D) / 8 * 7 + 5) / 7 * 256);
}

用法:sheet.setColumnWidth(cmToW(1.0))
使用 (px - 5.0D)/8 将 excel 宽度中的像素转换为点。(在 excel 中拖动列宽时，像素和点将显示在光标周围)
引用:HSSFSheet#setColumnWidth

Set the width (in units of 1/256th of a character width)

Excel uses the following formula (Section 3.3.1.12 of the OOXML spec):

// Excel width, not character width
width = Truncate([{Number of Visible Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width} * 256) / 256

Using the Calibri font as an example, the maximum digit width of 11 point font size is 7 pixels (at 96 dpi). If you set a column width to be eight characters wide, e.g. setColumnWidth(columnIndex, 8*256), then the actual value of visible characters (the value shown in Excel) is derived from the following equation:

Truncate([numChars * 7 + 5] / 7 * 256) / 256 = 8;

---

使用 XSSFClientAnchor 调整图片大小以填充单元格并保持其比例:

// set padding between picture and gridlines so gridlines would not covered by the picture
private static final double PADDING_SIZE = 10;
private static final int PADDING = Units.toEMU(PADDING_SIZE);

/**
 * Draw Image inside specific cell
 *
 * @param wb workbook
 * @param sheet sheet
 * @param cellW cell width in pixels
 * @param cellH cell height in pixels
 * @param imgPath image path
 * @param col the column (0 based) of the first cell.
 * @param row the row (0 based) of the first cell.
 * @param colSize the column size of cell
 * @param rowSize the row size of cell
 */
public static void drawImageInCell(SXSSFWorkbook wb, SXSSFSheet sheet, int cellW, int cellH,
      String imgPath, int col, int row, int colSize, int rowSize) throws IOException {
    Path path = Paths.get(imgPath);
    BufferedImage img = ImageIO.read(path.toFile());
    int[] anchorArray = calCellAnchor(Units.pixelToPoints(cellW), Units.pixelToPoints(cellH),
       img.getWidth(), img.getHeight());
    XSSFClientAnchor anchor = new XSSFClientAnchor(anchorArray[0], anchorArray[1], anchorArray[2],
        anchorArray[3], (short) col, row, (short) (col + colSize), row + rowSize);
    int index = wb.addPicture(Files.readAllBytes(path), XSSFWorkbook.PICTURE_TYPE_JPEG);
    sheet.createDrawingPatriarch().createPicture(anchor, index);
}

/**
 * calculate POI cell anchor
 *
 * @param cellX cell width in excel points
 * @param cellY cell height in excel points
 * @param imgX image width
 * @param imgY image height
 */
public static int[] calCellAnchor(double cellX, double cellY, int imgX, int imgY) {
    // assume Y has fixed padding first
    return calCoordinate(true, cellX, cellY, imgX, imgY);
}

/**
 * calculate cell coordinate
 *
 * @param fixTop is Y has fixed padding
 */
private static int[] calCoordinate(boolean fixTop, double cellX, double cellY, int imgX, int imgY) {
    double ratio = ((double) imgX) / imgY;
    int x = (int) Math.round(Units.toEMU(cellY - 2 * PADDING_SIZE) * ratio);
    x = (Units.toEMU(cellX) - x) / 2;
    if (x < PADDING) {
        return calCoordinate(false, cellY, cellX, imgY, imgX);
    }
    return calDirection(fixTop, x);
}

/**
 * calculate X's direction
 *
 * @param fixTop is Y has fixed padding
 * @param x X's padding
 */
private static int[] calDirection(boolean fixTop, int x) {
    if (fixTop) {
        return new int[] { x, PADDING, -x, -PADDING };
    } else {
        return new int[] { PADDING, x, -PADDING, -x };
    }
}