我有一个excel中含有多张照片，我想要按照将里面的图片全部适应单元格大小并居中处理 ,有什么好的方法吗? 最好性能上是比较好
请参阅以下示例代码来完成您的任务。请参考它，然后根据您的需要编写/更新您的代码。
例如，
示例代码：
//Open the Excel file
Workbook workbook = new Workbook("d:\\files\\Book1.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
//Get all the images in the worksheet
PictureCollection pictures = worksheet.getPictures();
for (int i = 0; i < pictures.getCount(); i++)
{
Picture picture = pictures.get(i);
//Get the cell containing the picture
int row = picture.getUpperLeftRow();
int column = picture.getUpperLeftColumn();
Cell cell = worksheet.getCells().get(row, column);
double cellWidth = worksheet.getCells().getColumnWidthPixel(column);
double cellHeight = worksheet.getCells().getRowHeightPixel(row);
//Get the original dimensions of the picture
double picWidth = picture.getWidth();
double picHeight = picture.getHeight();
//Calculate the scale factors to fit the image within the cell
double scaleX = cellWidth / picWidth;
double scaleY = cellHeight / picHeight;
//Choose the smaller scale to maintain the aspect ratio
double scale = Math.min(scaleX, scaleY);
//Resize the picture
picture.setWidth((int)(picWidth * scale));
picture.setHeight((int)(picHeight * scale));
//Center the image within the cell by adjusting the left and top offsets
int leftOffset = (int)((cellWidth - picture.getWidth()) / 2);
int topOffset = (int)((cellHeight - picture.getHeight()) / 2);
picture.setLeft(leftOffset);
picture.setTop(topOffset);
}
//Save the updated workbook
workbook.save("d:\\files\\out1.xlsx");
希望这会有所帮助。
@xiaoman
你可以使用单元格嵌入图片来实现需求，请查看附件。result.zip (4.1 MB)
样例代码如下：
//Open the Excel file
Workbook workbook = new Workbook(filePath + "sample.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
//Get all the images in the worksheet
PictureCollection pictures = worksheet.getPictures();
for (int i = 0; i < pictures.getCount(); i++)
{
Picture picture = pictures.get(i);
//Get the cell containing the picture
int row = picture.getUpperLeftRow();
int column = picture.getUpperLeftColumn();
Cell cell = worksheet.getCells().get(row, column);
Style style = cell.getStyle();
style.setHorizontalAlignment(TextAlignmentType.CENTER);
style.setVerticalAlignment(TextAlignmentType.CENTER);
cell.setEmbeddedImage(picture.getData());
cell.setStyle(style);
}
pictures.clear();
//Save the updated workbook
workbook.save(filePath + "out_java.xlsx");
@xiaoman ,
放在单元格中的图片(Placed in Cell Picture)是Microsoft Excel的新的特性，如果你使用的是WPS或者低版本的Microsoft Excel，此特性可能没有支持。
你可以尝试 @amjad.sahi 的解决方案：
你好 感谢你们的解答! 现在才有时间回复, 这个问题已经解决了,现在遇到新的问题,我研究了一下贵方的SDK,好像不支持跨单格/合并单元格的图片插入居中., 望解答 谢谢!
MS Excel 中没有特定选项可将图像插入到正常或合并的单元格中，从而使它们集中起来。您可以使用代码中的 Aspose.Cells for Java API 轻松完成此操作。请在压缩档案中找到附加的模板 Excel 文件，其中包含一个工作表，其中包含一些合并单元格和宽度和高度较大的正常单元格。我将选择两个单元格（D2 和 D8）来测试将图片插入单元格。D2 是合并单元格，D8 是正常单元格。另请查看我将插入单元格的测试图片。输出 Excel 文件也存在于附加档案中。请尝试参考带有模板 Excel 文件的代码片段，并根据需要编写/更新代码。
// Load the workbook and worksheet
Workbook workbook = new Workbook("d:\\files\\Bk_test1.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
Cell destCell = worksheet.getCells().get("D2");//merged cell
//Cell destCell = worksheet.getCells().get("D8");//normal cell
double columnWidth = worksheet.getCells().getColumnWidthInch(destCell.getColumn());
double rowHeight = worksheet.getCells().getRowHeightInch(destCell.getRow());
if(destCell.isMerged())
{
Range range = destCell.getMergedRange();
if (range != null) {
//Calculate total width of merged cells
for (int c = range.getColumnCount() - 1; c > 0; c--) {
columnWidth += worksheet.getCells().getColumnWidthInch(range.getFirstColumn() + c);
}
//Calculate total height of merged cells
for (int r = range.getRowCount() - 1; r > 0; r--) {
rowHeight += worksheet.getCells().getRowHeightInch(range.getFirstRow() + r);
}
}
}
// Load the image
String imagePath = "d:\\files\\pict1.png"; // Provide your image path
File imageFile = new File(imagePath);
if (!imageFile.exists()) {
System.out.println("Image file not found.");
return;
}
// Add the picture
int pictureIndex = worksheet.getPictures().add(destCell.getRow(), destCell.getColumn(), imagePath);
Picture picture = worksheet.getPictures().get(pictureIndex);
double imageWidthInch = picture.getWidthInch(); // Adjust according to the image's width
double imageHeightInch = picture.getHeightInch(); // Adjust according to the image's height
double xOffset = (columnWidth - imageWidthInch) / 2;
double yOffset = (rowHeight - imageHeightInch) / 2;
picture.setLeftInch(xOffset);
picture.setTopInch(yOffset);
picture.setWidthInch(imageWidthInch);
picture.setHeightInch(imageHeightInch);
// Save the workbook
workbook.save("d:\\files\\CenteredImageInMergedCell.xlsx");
files1.zip (680.3 KB)
希望这有帮助。
两个问题, 图片自适应居中,如果单元格/合并单元格比较大,图片容易拉伸变形 没有真正的等比例缩放! 第二个,设置了4张图片,生成的文件只能看到两张! 请帮忙看看 谢谢!
@Test
public void testApose() throws Exception {
Workbook workbook = new Workbook(“/Desktop/图片/Bk_test1.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(0);
// Load the image
String imagePath = "/Desktop/图片/原始图.png";
//String imagePath2 = "/Users/robert/Desktop/图片/橙4063.jpg";
writeImageApose(worksheet,imagePath,"A3");//merge
writeImageApose(worksheet,imagePath,"B2");//normal
writeImageApose(worksheet,imagePath,"A6");//normal
writeImageApose(worksheet,imagePath,"D2");// merge
workbook.save("/Desktop/CenteredImageInMergedCell-02.xlsx");
}
public static void writeImageApose(Worksheet worksheet,String imagePath, String cell) throws Exception {
Cell destCell = worksheet.getCells().get(cell);
double columnWidth = worksheet.getCells().getColumnWidthInch(destCell.getColumn());
double rowHeight = worksheet.getCells().getRowHeightInch(destCell.getRow());
if (destCell.isMerged()) {
Range range = destCell.getMergedRange();
if (range != null) {
for (int c = range.getColumnCount() - 1; c > 0; c--) {
columnWidth += worksheet.getCells().getColumnWidthInch(range.getFirstColumn() + c);
}
for (int r = range.getRowCount() - 1; r > 0; r--) {
rowHeight += worksheet.getCells().getRowHeightInch(range.getFirstRow() + r);
}
}
}
int pictureIndex = worksheet.getPictures().add(destCell.getRow(), destCell.getColumn(), imagePath);
Picture picture = worksheet.getPictures().get(pictureIndex);
double imageWidthInch = picture.getWidthInch();
double imageHeightInch = picture.getHeightInch();
// 图片只占cell的8成空间
double scale = Math.min(columnWidth * 0.8 / imageWidthInch, rowHeight * 0.8 / imageHeightInch) ;
picture.setWidthInch((int)(imageWidthInch * scale));
picture.setHeightInch((int)(imageWidthInch * scale));
double xOffset = (columnWidth - picture.getWidthInch()) / 2;
double yOffset = (rowHeight - picture.getHeightInch()) / 2;
picture.setLeftInch(xOffset);
picture.setTopInch(yOffset);
}
图片自适应居中.zip (507.1 KB)
图片不显示问题,我已经解决,现在的问题图片容易失真, 你可以在excel打开原始图片 然后设置跟导出的图片高度一样 会发现导出的图片的宽度和原始的宽度不一致
@Test
public void testApose(){
Workbook workbook = null;
try {
workbook = new Workbook(“/Users/robert/Desktop/图片/Bk_test1.xlsx”);
Worksheet worksheet = workbook.getWorksheets().get(0);
String path = “/Users/robert/Desktop/图片/原始图.png”;
//跨单元格居中
writeImageApose(worksheet, path,0,2,0,4);
writeImageApose(worksheet, path,0,5,0,5);
writeImageApose(worksheet, path,1,1,1,1);
writeImageApose(worksheet, path,3,1,5,4);
workbook.save("/Users/robert/Desktop/图片/PI模板-oot-merge-18.xlsx");
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void testAposeByName() throws Exception {
Workbook workbook = new Workbook("/Users/robert/Desktop/图片/Bk_test1.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
String imagePath = "/Users/robert/Desktop/图片/原始图.png";
writeImageAposeByName(worksheet,imagePath,"A3");//merge
writeImageAposeByName(worksheet,imagePath,"A6");//normal
writeImageAposeByName(worksheet,imagePath,"B2");//normal
writeImageAposeByName(worksheet,imagePath,"D2");// merge
workbook.save("/Users/robert/Desktop/图片/CenteredImageInMergedCell-11.xlsx");
}
//单元格并兼容合并单元格
public static void writeImageAposeByName(Worksheet worksheet,String imagePath, String cellName) throws Exception {
Cell destCell = worksheet.getCells().get(cellName);
double cellWidth = worksheet.getCells().getColumnWidthPixel(destCell.getColumn());
double rowHeight = worksheet.getCells().getRowHeightPixel(destCell.getRow());
if (destCell.isMerged()) {
Range range = destCell.getMergedRange();
if (range != null) {
for (int c = range.getColumnCount() - 1; c > 0; c--) {
cellWidth += worksheet.getCells().getColumnWidthPixel(range.getFirstColumn() + c);
}
for (int r = range.getRowCount() - 1; r > 0; r--) {
rowHeight += worksheet.getCells().getRowHeightPixel(range.getFirstRow() + r);
}
}
}
int pictureIndex = worksheet.getPictures().add(destCell.getRow(), destCell.getColumn(), imagePath);
Picture picture = worksheet.getPictures().get(pictureIndex);
double picWidth = picture.getWidth();
double picHeight = picture.getHeight();
double scaleX = cellWidth / picWidth;
double scaleY = rowHeight / picHeight;
double scale = Math.min(scaleX, scaleY) * 0.8;
picture.setWidth((int)(picWidth * scale));
picture.setHeight((int)(picHeight * scale));
int leftOffset = (int)((cellWidth - picture.getWidth()) / 2);
int topOffset = (int)((rowHeight - picture.getHeight()) / 2);
picture.setLeft(leftOffset);
picture.setTop(topOffset);
}
// 指定多个单元格
public static void writeImageApose(Worksheet worksheet,String imagePath, int col1 ,int row1,int col2 ,int row2) throws Exception {
int picIndex = worksheet.getPictures().add(row1,col1,imagePath);
Picture picture = worksheet.getPictures().get(picIndex);
double cellWidth = 0;
double cellHeight = 0;
// 计算跨单元格的总高 总宽
for(int i = col1; i <=col2; i++) {
cellWidth += worksheet.getCells().getColumnWidthPixel(i);
}
for(int i = row1; i <=row2;i++) {
cellHeight += worksheet.getCells().getRowHeightPixel(i);
}
double picWidth = picture.getWidth();
double picHeight = picture.getHeight();
double scaleX = cellWidth / picWidth;
double scaleY = cellHeight / picHeight;
double scale = Math.min(scaleX, scaleY) * 0.8;
picture.setWidth((int)(picWidth * scale));
picture.setHeight((int)(picHeight * scale));
int leftOffset = (int)((cellWidth - picture.getWidth()) / 2);
int topOffset = (int)((cellHeight - picture.getHeight()) / 2);
picture.setLeft(leftOffset);
picture.setTop(topOffset);
}
result.zip (756.3 KB)
另外 我们现在用的版本太低! 想看看你们近2年的优化记录,考虑要不要采购最新的SDK,可以提供迭代优化内容的链接吗 谢谢
@xiaoman
通过使用以下样例代码在最新版本v24.12上进行测试，我们可以得到正确的结果。请查看附件。out_java.zip (251.7 KB)
Workbook workbook = null;
workbook = new Workbook(filePath + "Bk_test1.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
String path = filePath + "原始图.png";
//跨单元格居中
writeImageApose(worksheet, path,0,2,0,4);
writeImageApose(worksheet, path,0,5,0,5);
writeImageApose(worksheet, path,1,1,1,1);
writeImageApose(worksheet, path,3,1,5,4);
workbook.save(filePath + "out_java.xlsx");
//单元格并兼容合并单元格
public static void writeImageAposeByName(Worksheet worksheet,String imagePath, String cellName) throws Exception {
Cell destCell = worksheet.getCells().get(cellName);
double cellWidth = worksheet.getCells().getColumnWidthPixel(destCell.getColumn());
double rowHeight = worksheet.getCells().getRowHeightPixel(destCell.getRow());
if (destCell.isMerged()) {
Range range = destCell.getMergedRange();
if (range != null) {
for (int c = range.getColumnCount() - 1; c > 0; c--) {
cellWidth += worksheet.getCells().getColumnWidthPixel(range.getFirstColumn() + c);
}
for (int r = range.getRowCount() - 1; r > 0; r--) {
rowHeight += worksheet.getCells().getRowHeightPixel(range.getFirstRow() + r);
}
}
}
int pictureIndex = worksheet.getPictures().add(destCell.getRow(), destCell.getColumn(), imagePath);
Picture picture = worksheet.getPictures().get(pictureIndex);
double picWidth = picture.getWidth();
double picHeight = picture.getHeight();
double scaleX = cellWidth / picWidth;
double scaleY = rowHeight / picHeight;
double scale = Math.min(scaleX, scaleY) * 0.8;
picture.setWidth((int)(picWidth * scale));
picture.setHeight((int)(picHeight * scale));
int leftOffset = (int)((cellWidth - picture.getWidth()) / 2);
int topOffset = (int)((rowHeight - picture.getHeight()) / 2);
picture.setLeft(leftOffset);
picture.setTop(topOffset);
}
//指定多个单元格
public static void writeImageApose(Worksheet worksheet,String imagePath, int col1 ,int row1,int col2 ,int row2) throws Exception {
int picIndex = worksheet.getPictures().add(row1,col1,imagePath);
Picture picture = worksheet.getPictures().get(picIndex);
double cellWidth = 0;
double cellHeight = 0;
// 计算跨单元格的总高 总宽
for(int i = col1; i <=col2; i++) {
cellWidth += worksheet.getCells().getColumnWidthPixel(i);
}
for(int i = row1; i <=row2;i++) {
cellHeight += worksheet.getCells().getRowHeightPixel(i);
}
double picWidth = picture.getWidth();
double picHeight = picture.getHeight();
double scaleX = cellWidth / picWidth;
double scaleY = cellHeight / picHeight;
double scale = Math.min(scaleX, scaleY) * 0.8;
picture.setWidth((int)(picWidth * scale));
picture.setHeight((int)(picHeight * scale));
int leftOffset = (int)((cellWidth - picture.getWidth()) / 2);
int topOffset = (int)((cellHeight - picture.getHeight()) / 2);
picture.setLeft(leftOffset);
picture.setTop(topOffset);
}
我们建议您尝试使用我们的最新版本:
Aspose.Cells for Java v24.12.
另外，请不要使用主题字体，如果设置了 Excel 主题字体，Excel 将根据当前语言环境自动在不同字体之间切换。关于主题字体，请查看相关文档。
请检查一下你的Excel显示的默认字体是什么？默认字体是否安装了？默认字体会影响列宽和行高的数据测量。如果你有任何疑问，请随时联系我们。
好的 我先试试 有问题再请教您 谢谢!
你好 想问一下 这三个方法返回的数据 要怎么转换 我现在有一段代码转换以后 好像图片居中就不对了
org.apache.poi.ss.usermodel.Sheet.getRow(i).getHeightInPoints()
com.aspose.cells.getCells().getRowHeight(i)
com.aspose.cells.getCells().getRowHeightPixel
//自动调整行高
if (autoFitRows != null && autoFitRows.size() == MIN_PRODUCT_CELLS && autoFitRows.get(0) != -1 && autoFitRows.get(1) != -1) {
aposeSheet.autoFitRows(autoFitRows.get(0), autoFitRows.get(1));
}
Sheet apacheSheet = excelWorker.get_sheet();
for (int i = autoFitRows.get(0); i <= autoFitRows.get(1); i++) {
if (apacheSheet.getRow(i) == null) {
continue;
}
double rowHeightBefore = apacheSheet.getRow(i).getHeightInPoints();
double rowHeightAfter = aposeSheet.getCells().getRowHeight(i);
//调整后的行高和原行高做比对 取最大的行高
aposeSheet.getCells().setRowHeight(i, Math.max(rowHeightBefore,rowHeightAfter));
}
好 我写个单元测试 这个是在业务代码里面的! 另外, 昨天你提到让我使用最新的24年的SDK,目前我们公司用不了,我们的是2022年的,能否给个临时的让我先测试,如果效果好,我们会重新采购贵方的最新SDK !
是的，我们建议您尝试升级到最新版本和 API 集。然后使用最新版本的 Aspose.Cells for Java 尝试您的场景/案例。要测试您的场景/案例，您可以根据您的要求申请 30 天的临时许可证。请参阅有关如何获取临时许可证的页面。
https://purchase.aspose.com/temporary-license/