java - Apache POI - 在适合合并区域的同时保持原始图片比例
问题描述
我需要将图片放入合并单元格区域的中心。合并的单元格区域始终只有一列宽，但多行高。
如果图片不适合单元格区域，则需要调整大小，同时保持原始比例。
int pictureIdx = wb.addPicture(bytes, pictype);
CreationHelper helper = wb.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
anchor.setCol1(column);
anchor.setRow1(row);
anchor.setRow2(row+rowspan);
anchor.setCol2(column+1);
Picture pict = drawing.createPicture(anchor, pictureIdx);
pict.resize();
short rowHeight = (short)0;
for (int i = row; i<row+rowspan; i++ ) {
rowHeight += sheet.getRow(i).getHeight();
}
int rowHeightPx = heightUnits2Pixel(rowHeight); // (from [https://stackoverflow.com/a/31837639/1320704][1] )
int columnWidthPx = (int) (sheet.getColumnWidthInPixels(column));
int pictWidthPx = pict.getImageDimension().width;
int pictHeightPx = pict.getImageDimension().height;
float scale = 1;
if (pictHeightPx > rowHeightPx) {
float tmpscale = (float)rowHeightPx / (float)pictHeightPx;
if (tmpscale < scale) scale = tmpscale;
}
if (pictWidthPx > columnWidthPx) {
float tmpscale = (float)columnWidthPx / (float)pictWidthPx;
if (tmpscale < scale) scale = tmpscale;
}
anchor.setDx1(
(int) ((sheet.getColumnWidthInPixels(column)- pict.getImageDimension().width) /2)
);
pict.resize(scale, scale);
结果如下：
如您所见，我没有得到预期的效果。这张图片是一个正方形 (256x256)，但不知何故它被扭曲了，即使 x 和 y 的比例传递相同。而且高度也不对。而且它不是居中的。
为了比较，这是原始尺寸的相同图片：
这是我真正想要实现的目标：
解决方案
第一个问题是，如果您想使用，Picture.resize则仅使用左上角单元格创建一个锚点。由于右下锚位置取决于调整大小，因此只有一个单元格锚。Col1Row1
第二个问题是你应该只在左上锚位置是众所周知的（包括）之后才进行调整Dx1大小Dy1。
但主要问题是您heightUnits2Pixel没有获得正确的行像素高度。最好使用Row.getHeightInPoints。这将获取行高，pt但可以将其转换为pxusing rowHeightPt * Units.PIXEL_DPI / Units.POINT_DPI。请参阅https://poi.apache.org/apidocs/dev/org/apache/poi/util/Units.html。
使用它至少可以正确计算比例因子，因为它现在具有正确的行高（以像素为单位）。
要将Dx1锚点设置为水平中心位置，还有一个问题。和Dx和之间的含义Dy非常不同。XSSFHSSF
在XSSF它是单位的水平中心位置EMU。所以你需要horCenterPosPx * Units.EMU_PER_PIXEL计算Dx. 请参阅https://poi.apache.org/apidocs/dev/org/apache/poi/util/Units.html。
HSSF的值Dx取决于 的因子，而column-width / default column-width的值Dy取决于 的因子row-height / default row-height。请参阅apache poi XSSFClientAnchor not 定位图片相对于 dx1, dy1, dx2, dy2。
为了计算垂直中心位置，首先Row1需要确定锚点的正确性。即使合并，也存在不同的行。因此Row1，锚点必须是图片开始的正确行。直到垂直中心位置的剩余像素是Dy1then。
以下完整示例对我有用。它将任何类型的不同大小的图像按比例缩放并居中到合并范围内A3:A12。
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;
import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
class CenterImageTest {
static void putPictureCentered(Sheet sheet, String picturePath, int pictureType, int column, int startRow, int rowspan) throws Exception {
Workbook wb = sheet.getWorkbook();
//load the picture
InputStream inputStream = new FileInputStream(picturePath);
byte[] bytes = IOUtils.toByteArray(inputStream);
int pictureIdx = wb.addPicture(bytes, pictureType);
inputStream.close();
//create an anchor with upper left cell column/startRow, only one cell anchor since bottom right depends on resizing
CreationHelper helper = wb.getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1(column);
anchor.setRow1(startRow);
//create a picture anchored to Col1 and Row1
Drawing drawing = sheet.createDrawingPatriarch();
Picture pict = drawing.createPicture(anchor, pictureIdx);
//get the picture width in px
int pictWidthPx = pict.getImageDimension().width;
//get the picture height in px
int pictHeightPx = pict.getImageDimension().height;
//get column width of column in px
float columnWidthPx = sheet.getColumnWidthInPixels(column);
//get the heights of all merged rows in px
float[] rowHeightsPx = new float[startRow+rowspan];
float rowsHeightPx = 0f;
for (int r = startRow; r < startRow+rowspan; r++) {
Row row = sheet.getRow(r);
float rowHeightPt = row.getHeightInPoints();
rowHeightsPx[r-startRow] = rowHeightPt * Units.PIXEL_DPI / Units.POINT_DPI;
rowsHeightPx += rowHeightsPx[r-startRow];
}
//calculate scale
float scale = 1;
if (pictHeightPx > rowsHeightPx) {
float tmpscale = rowsHeightPx / (float)pictHeightPx;
if (tmpscale < scale) scale = tmpscale;
}
if (pictWidthPx > columnWidthPx) {
float tmpscale = columnWidthPx / (float)pictWidthPx;
if (tmpscale < scale) scale = tmpscale;
}
//calculate the horizontal center position
int horCenterPosPx = Math.round(columnWidthPx/2f - pictWidthPx*scale/2f);
//set the horizontal center position as Dx1 of anchor
if (wb instanceof XSSFWorkbook) {
anchor.setDx1(horCenterPosPx * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
} else if (wb instanceof HSSFWorkbook) {
//see https://stackoverflow.com/questions/48567203/apache-poi-xssfclientanchor-not-positioning-picture-with-respect-to-dx1-dy1-dx/48607117#48607117 for HSSF
int DEFAULT_COL_WIDTH = 10 * 256;
anchor.setDx1(Math.round(horCenterPosPx * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75f * DEFAULT_COL_WIDTH / columnWidthPx));
}
//calculate the vertical center position
int vertCenterPosPx = Math.round(rowsHeightPx/2f - pictHeightPx*scale/2f);
//get Row1
Integer row1 = null;
rowsHeightPx = 0f;
for (int r = 0; r < rowHeightsPx.length; r++) {
float rowHeightPx = rowHeightsPx[r];
if (rowsHeightPx + rowHeightPx > vertCenterPosPx) {
row1 = r + startRow;
break;
}
rowsHeightPx += rowHeightPx;
}
//set the vertical center position as Row1 plus Dy1 of anchor
if (row1 != null) {
anchor.setRow1(row1);
if (wb instanceof XSSFWorkbook) {
anchor.setDy1(Math.round(vertCenterPosPx - rowsHeightPx) * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
} else if (wb instanceof HSSFWorkbook) {
//see https://stackoverflow.com/questions/48567203/apache-poi-xssfclientanchor-not-positioning-picture-with-respect-to-dx1-dy1-dx/48607117#48607117 for HSSF
float DEFAULT_ROW_HEIGHT = 12.75f;
anchor.setDy1(Math.round((vertCenterPosPx - rowsHeightPx) * Units.PIXEL_DPI / Units.POINT_DPI * 14.75f * DEFAULT_ROW_HEIGHT / rowHeightsPx[row1]));
}
}
//resize the picture to it's native size
pict.resize();
//if it must scaled down, then scale
if (scale < 1) {
pict.resize(scale);
}
}
public static void main(String[] args) throws Exception {
Workbook wb = new HSSFWorkbook(); String resultName = "CenterImageTest.xls";
//Workbook wb = new XSSFWorkbook(); String resultName = "CenterImageTest.xlsx";
Sheet sheet = wb.createSheet("Sheet1");
int column = 2;
int columnWidth = 100; //in default character widths
int startRow = 2;
int rowspan = 10;
//========================prepare sheet
//create cell A1 and set cell value
Row row = sheet.createRow(1);
Cell cell = row.createCell(column);
cell.setCellValue("Picture of product");
//create the cells with different heights
for (int r = startRow; r < startRow+rowspan; r++) {
row = sheet.createRow(r);
row.setHeightInPoints(12 + r*2);
}
//merge cells
sheet.addMergedRegion(new CellRangeAddress(startRow,startRow+rowspan-1,column,column));
//========================end prepare sheet
//set column width of column in default character widths
sheet.setColumnWidth(column, columnWidth * 256);
//put image centered
//String picturePath = "./logo.png"; // small image
//putPictureCentered(sheet, picturePath, Workbook.PICTURE_TYPE_PNG, column, startRow, rowspan);
String picturePath = "./Bilder/Sample-jpg-image-1.jpg"; // bigger image
putPictureCentered(sheet, picturePath, Workbook.PICTURE_TYPE_JPEG, column, startRow, rowspan);
FileOutputStream fileOut = new FileOutputStream(resultName);
wb.write(fileOut);
fileOut.close();
wb.close();
}
}
如果图片很小并且设置为锚点，则Picture.resize中似乎存在错误。Dx1比例因子scale从这个代码得到正确计算，但pict.resize(scale)不按比例缩放高度和宽度。它使用正确的因子缩放高度。但它不是使用相同的因子而是更大的因子来缩放宽度。
我已经提交了错误https://bz.apache.org/bugzilla/show_bug.cgi?id=64213。
为了避免错误Picture.resize，我们可以计算完整的两个单元格锚点。我们已经并且Col1已经在计算Dx1并且得到图片的左上角。现在我们可以额外计算并得到图片的右下角。Row1Dy1Dx2Row2Dy2
上面的例子是我putPictureCentered的：
...
/*
//resize the picture to it's native size
pict.resize();
//if it must scaled down, then scale
if (scale < 1) {
pict.resize(scale);
}
*/
//set Col2 of anchor the same as Col1 as all is in one column
anchor.setCol2(column);
//calculate the horizontal end position of picture
int horCenterEndPosPx = Math.round(horCenterPosPx + pictWidthPx*scale);
//set set the horizontal end position as Dx2 of anchor
if (wb instanceof XSSFWorkbook) {
anchor.setDx2(horCenterEndPosPx * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
} else if (wb instanceof HSSFWorkbook) {
//see https://stackoverflow.com/questions/48567203/apache-poi-xssfclientanchor-not-positioning-picture-with-respect-to-dx1-dy1-dx/48607117#48607117 for HSSF
int DEFAULT_COL_WIDTH = 10 * 256;
anchor.setDx2(Math.round(horCenterEndPosPx * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75f * DEFAULT_COL_WIDTH / columnWidthPx));
}
//calculate the vertical end position of picture
int vertCenterEndPosPx = Math.round(vertCenterPosPx + pictHeightPx*scale);
//get Row2
Integer row2 = null;
rowsHeightPx = 0f;
for (int r = 0; r < rowHeightsPx.length; r++) {
float rowHeightPx = rowHeightsPx[r];
if (rowsHeightPx + rowHeightPx > vertCenterEndPosPx) {
row2 = r + startRow;
break;
}
rowsHeightPx += rowHeightPx;
}
//set the vertical end position as Row2 plus Dy2 of anchor
if (row2 != null) {
anchor.setRow2(row2);
if (wb instanceof XSSFWorkbook) {
anchor.setDy2(Math.round(vertCenterEndPosPx - rowsHeightPx) * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
} else if (wb instanceof HSSFWorkbook) {
//see https://stackoverflow.com/questions/48567203/apache-poi-xssfclientanchor-not-positioning-picture-with-respect-to-dx1-dy1-dx/48607117#48607117 for HSSF
float DEFAULT_ROW_HEIGHT = 12.75f;
anchor.setDy2(Math.round((vertCenterEndPosPx - rowsHeightPx) * Units.PIXEL_DPI / Units.POINT_DPI * 14.75f * DEFAULT_ROW_HEIGHT / rowHeightsPx[row1]));
}
}
...