How to center-align an icon in cell and avoid stretching vertically with row resizing?

We have 2 problems with Aspose Cells exporting icons:

  1. Whatever we have tried yet - the icon can not be placed center-aligned in its cell.
  2. When resizing rows the icon height auto-grows with row height. Our icons should keep their size, no matter what happens with row height or width. Icon dimensions should be completely independent from row dimensions. Please see attached this issue.

How to solve these problems?

Hi Christian,


Thank you for contacting Aspose support.

It would have been appropriate if you could have shared the sample spreadsheet containing the said icons for thorough investigation. Anyway, please check the following information so you may sort out the problem on our own.

  • Please note, you cannot center align image in Excel automatically, however, you can re-position the image manually in a such a way that it looks in the center of the cell. In perspective of Aspose.Cells APIs, you can adjust cell height and width so that the image looks in the center of cell or adjust the image dimensions if you do not wish to re-size the cell.
  • This is possibly due to the reason that you have the Move and size with cell property enabled for the images so when you re-size the cell, the image’s dimensions also change.

Please find attached the spreadsheet and here, see source code for exporting the images:

private void exportImage(final Object field, final Worksheet sheet, final int rowIdx,
final int colIdx) {
try {
final Image img = (Image) field;
final BufferedImage bImage = SwingFXUtils.fromFXImage(img, null);
final ByteArrayOutputStream s = new ByteArrayOutputStream();
ImageIO.write(bImage, “png”, s);
final byte[] res = s.toByteArray();
final InputStream in = new ByteArrayInputStream(res);

final double widthInPoints = img.getWidth() * 72 / 96;
final double heightInPoints = img.getHeight() * 72 / 96;
sheet.getCells().setRowHeight(rowIdx + 1, heightInPoints);
sheet.getCells().setColumnWidth(colIdx, widthInPoints);

final int picId = sheet.getPictures().add(rowIdx + 1, colIdx, in);
final Picture pic = sheet.getPictures().get(picId);
pic.setWidthPt(widthInPoints);
pic.setHeightPt(heightInPoints);
} catch (IOException ex) {
Logger.getLogger(DeviceListExporter.class.getName())
.log(Level.WARNING, “Error exporting image”, ex);
}
}

As you can see, I did not set for the picture such a property mentioned by you (at least not conciously).

Hi Christian,


Thank you for sharing the sample.

Instead of using your code for inserting images in worksheet, I have used your existing spreadsheet to evaluate the presented scenario, and I was able to center all images to some extent. I have used DeltaX which is in the unit of 1/1024 of the width of the column where Shape.LowerDeltaX property can be used to set the horizontal offset from shape’s right column. Please check the following piece of code and its resultant spreadsheet as attached. Please note, I have tested the code with Aspose.Cells for Java (Latest Version) (latest at the moment) so if you do not get correct results with your existing version of the API, please give a try to the aforementioned release Moreover, you can use this code in your existing logic after inserting the image in the worksheet. In case you still face any difficulty, please provide us your executable code (preferably a sample application) along with the desired results, that you may create manually in Excel application to show what you require to accomplish with Aspose.Cells APIs.

Java

Workbook book = new Workbook(dir + “spreadsheet.xlsx”);
Worksheet sheet = book.getWorksheets().get(0);
for (int i = 0; i < sheet.getPictures().getCount(); i++)
{
Picture picture = sheet.getPictures().get(i);
picture.setPlacement(PlacementType.FREE_FLOATING);
picture.setLowerDeltaX(512);
}
book.save(dir + “output.xlsx”);