Apache POI Excel 工作表:在保持比例的同时调整图片大小 [英] Apache POI Excel sheet: resize a picture while keeping its ratio
问题描述
您好，我使用 POI 创建了 excel 表.我以下一种方式添加了图片(jpg文件):
Workbook wb = new HSSFWorkbook();CreationHelper 助手 = wb.getCreationHelper();//...InputStream is = new FileInputStream("img.jpg");byte[] bytes = IOUtils.toByteArray(is);int picIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);绘图绘图 = sheet.createDrawingPatriarch();ClientAnchor 锚点 = helper.createClientAnchor();锚点.setCol1(5);锚点.setRow1(5);图片 pict = drawing.createPicture(anchor, picIdx);pict.resize();现在我想让图片适合那个单元格，但我不想改变它的纵横比.我可以调整大小的比例是关于单元格的，显然可以有不同的比例.我试图计算比例，但问题是，我无法以像素为单位获取或设置行高，只能以 pt 为单位，而且我无法计算单元格比率，因为我无法获得宽度和高度同一单位...有什么建议吗?
有一个 单元 POI 类，提供像素和点之间的转换.
这里有一些方法可能有助于设置单元格的宽度和高度.
1.厘米到像素
public static int cmToPx(double cm) {return (int) Math.round(cm * 96/2.54D);}96 是我的显示器的 DPI(从 dpilove 查看你的)
和 1 英寸 = 2.54 厘米
2.厘米到行高度
public static int cmToH(double cm) {返回 (int) (Units.pixelToPoints(cmToPx(cm)) * 20);//POI的单位}用法:sheet.setDefaultRowHeight(cmToH(1.0))
参考:HSSFRow#getHeightInPoints
以缇"为单位设置高度或 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 = Truncate([{可见字符数} * {最大数字宽度} + {5 像素填充}]/{最大数字宽度} * 256)/256<块引用>
以 Calibri 字体为例，11 磅字体的最大数字宽度为 7 像素(96 dpi).如果您将列宽设置为八个字符宽，例如setColumnWidth(columnIndex, 8*256)，那么可见字符的实际值(Excel中显示的值)由下式推导出来:
Truncate([numChars * 7 + 5]/7 * 256)/256 = 8;使用 XSSFClientAnchor 调整图片大小以填充单元格并保持其比例:
//在图片和网格线之间设置填充，这样网格线就不会被图片覆盖私有静态最终双 PADDING_SIZE = 10;private static final int PADDING = Units.toEMU(PADDING_SIZE);/*** 在特定单元格内绘制图像** @param wb 工作簿* @param 工作表* @param cellW 单元格宽度(以像素为单位)* @param cellH 单元格高度(以像素为单位)* @param imgPath 图片路径* @param col 第一个单元格的列(基于 0).* @param row 第一个单元格的行(基于 0).* @param colSize 单元格的列大小* @param rowSize 单元格的行大小*/public static void drawImageInCell(SXSSFWorkbook wb, SXSSFSheet sheet, int cellW, int cellH,String imgPath, int col, int row, int colSize, int rowSize) 抛出 IOException {路径路径 = 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);}/*** 计算POI cell anchor** @param cellX 单元格宽度在excel点* @param cellY 单元格高度在excel点* @param imgX 图像宽度* @param imgY 图片高度*/public static int[] calCellAnchor(double cellX, double cellY, int imgX, int imgY) {//假设 Y 首先有固定的填充返回 calCoordinate(true, cellX, cellY, imgX, imgY);}/*** 计算单元格坐标** @param fixTop 是 Y 有固定填充*/private static int[] calCoordinate(boolean fixTop, double cellX, double cellY, int imgX, int imgY) {双倍比率 = ((double) imgX)/imgY;int x = (int) Math.round(Units.toEMU(cellY - 2 * PADDING_SIZE) * ratio);x = (Units.toEMU(cellX) - x)/2;如果 (x <填充) {返回 calCoordinate(false, cellY, cellX, imgY, imgX);}返回 calDirection(fixTop, x);}/*** 计算X的方向** @param fixTop 是 Y 有固定填充* @param x X 的填充*/私有静态 int[] calDirection(boolean fixTop, int x) {如果(固定顶部){return new int[] { x, PADDING, -x, -PADDING };} 别的 {return new int[] { PADDING, x, -PADDING, -x };}}Hello I have created excel sheet by using POI. I've added picture(jpg-file) in the next way:
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();
Now I want the picture to fit into that cell, but i don't want to change its aspect ratio. the scales i can put into resize are in respect to the cell, which obviously can have a different ratio. I tried to calculate the scales but the problem is, that I can't get or set the row height in pixel, only in pt and i can't compute the cells ratio either bc the i can't get width and height in the same unit... Any suggestions?
There is a Units class in POI, which provide convertion between pixel and point.
And here are some methods might be helpful to set cell's width and height.
1.Centimeters to Pixels
public static int cmToPx(double cm) {
return (int) Math.round(cm * 96 / 2.54D);
}
96 is my Monitor's DPI (check yours from dpilove)
and 1 inch = 2.54 centimeters
2.Centimeters to RowHeight
public static int cmToH(double cm) {
return (int) (Units.pixelToPoints(cmToPx(cm)) * 20); //POI's Units
}
Usage: sheet.setDefaultRowHeight(cmToH(1.0))
Reference: HSSFRow#getHeightInPoints
Set the height in "twips" or 1/20th of a point.
3.Centimeters to ColumnWidth
public static int cmToW(double cm) {
return (int) Math.round(((cmToPx(cm) - 5.0D) / 8 * 7 + 5) / 7 * 256);
}
Usage: sheet.setColumnWidth(cmToW(1.0))
Use (px - 5.0D) / 8 to convert from pixels to points in excel width.(When drag column's width in excel, pixels and points will show around your cursor)
Reference: 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;
Use XSSFClientAnchor to resize a picture to fill the cell and keep its ratio:
// 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 };
}
}
这篇关于Apache POI Excel 工作表:在保持比例的同时调整图片大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
