Apache POI Excel工作表:调整图片大小，同时保持其比例

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();

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

将高度设置为"；twipps"；或一个点的1/20。

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

设置宽度（以字符宽度的1/256为单位）

Excel使用以下公式（OOXML规范第3.3.1.12节）：

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

以Calibri字体为例，11点字体大小的最大数字宽度为7像素（96 dpi）。如果将列宽设置为八个字符宽，例如setColumnWidth（columnIndex，8*256），则可见字符的实际值（Excel中显示的值）由以下等式导出：

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 };
    }
}

    byte[] imageBase64Data = base64DataString.getBytes();
    byte[] imageRawData = Base64.getDecoder().decode(imageBase64Data);
    int imgWidth = 1920; // only initial if not known
    int imgHeight = 1080; // only initial if not known
    try {
        BufferedImage img = ImageIO.read(new ByteArrayInputStream(imageRawData));
        imgWidth = img.getWidth();
        imgHeight = img.getHeight();
    } catch (IOException e) {
        e.printStackTrace();
    }
    int pictureIdx = workbook.addPicture(imageRawData, Workbook.PICTURE_TYPE_PNG);
    CreationHelper helper = workbook.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setCol1(desiredCol);
    anchor.setRow1(desiredRow);
    Picture picture = drawing.createPicture(anchor, pictureIdx);
    picture.resize(0.7 * imgWidth / XSSFShape.PIXEL_DPI, 5 * imgHeight / XSSFShape.PIXEL_DPI);