Hi,
We have a requirement wherein we add images to worksheet. For every image we add, we attempt to resize the underlying cell. Images can have borders as well. Unfortunately, manually setting the size of the cell based on the image dimensions and its border width doesn’t work. The column width is slightly more than expected in the excel output. Here’s the code snippet that we use:
public static void main(String[] args) throws Exception
{
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
double borderWidth = 5; // input is always expected in pixels.
int index = worksheet.getPictures().add(1, 1, "D:\\issues\\imageresize\\school.jpg");
Picture picture = worksheet.getPictures().get(index);
LineFormat lf = picture.getLine();
lf.setCapType(LineCapType.FLAT);
lf.setJoinType(LineJoinType.MITER);
lf.setCompoundType(MsoLineStyle.SINGLE);
lf.setDashStyle(MsoLineDashStyle.SOLID);
borderWidth = borderWidth / CellsHelper.getDPI(); // convert it to inches
picture.getLine().setWeight(borderWidth * 72); // converting to points by multiplying with 72
picture.setBorderWeight(borderWidth * 72);
Color color = Color.fromArgb(Integer.parseInt("FF8040", 16));
picture.setBorderLineColor(color);
double imageHeight = picture.getHeightInch() + (2 * borderWidth);
double imageWidth = picture.getWidthInch() + (2 * borderWidth);
picture.setTopInch(borderWidth);
picture.setLeftInch(borderWidth);
worksheet.getCells().setColumnWidthInch(1, imageWidth);
worksheet.getCells().setRowHeightInch(1, imageHeight);
workbook.save("D:\\issues\\imageresize\\work.xlsx");
}
-What could be wrong with the approach?
-Attaching snapshot of excel output
work.zip (14.1 KB)
Regards,
Akash Srivastava
@Akash007,
Thanks for your query.
I have executed your sample code using one of my sample image school.jpg and got the output similar to the one provided by you. You mentioned that you are attaching snapshot but it is not there in the attachments. Could you please elaborate the issue more by providing your sample image school.jpg, description of the issue using snapshots and expected output created using Excel? We will analyze the issue by comparing the output generated by Aspose.Cells with the expected output created by Excel and provide our feedback.
Sorry, I missed to edit that I had actually attached the excel output instead of a snapshot(Earlier, I had plans to attach just the snapshot of excel output). However, now I have attached a snapshot of the excel output which highlights the concerned issues. Along with it, I have also attached the image which is used in the code(school.jpg).
The intention of the code was to resize cell 1,1 to exactly match the image size along with its borders. Please correct if there’s anything wrong with the math. The same math however seems to work fine for setting row height.
Regards,
Akash Srivastava
school.jpg (7.0 KB)
snapshot.png (140.2 KB)
@Akash007,
I have tried your code without any change using latest library Aspose.Cells for Java 18.11.x but could not observe any issue. The output Excel file cell is exactly resized according to the image size and no difference is observed in the size as shown in the attached image. You may please try this code using latest version and provide your feedback.
work_18.11.0.zip (14.2 KB)
Aspose18.11.x.Output.JPG (46.8 KB)
I get it now. I opened the excel output using Excel 2016. And could find the issue again. However, if I open it in Excel 365, the issue is no more seen. Any reason behind this behavior?
@Akash007
Could you please share the screenshots because I can not notice the issue with Excel 2016.
Attaching the screenshot.
capture3.png (124.8 KB)
@Akash007,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSJAVA-42771 - Issues while resizing underlying cell to image
@Akash007,
We evaluated your issue in details. This issue must be caused by different DPI set for the machine. The picture size is same in unit of inches in MAC and Windows. But the column’s width is in unit of Character in the Excel file, so the width of the column is different in unit of pixel and inch in different DPIs. We are looking into the solution for different DPI settings, but it seems that you have to create several files if your customers have different DPI settings.
@Akash007,
There is a temporary solution that setting the placement as MOVE_AND_SIZE, then image will size with cells.
But the border will be out of the cell, it’s better that you can process image with border as an input image by image tools.
See try the following codes:
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
double borderWidth = 5; // input is always expected in pixels.
int index = worksheet.getPictures().add(1, 1,2,2, "D:\\Filetemp\\school.jpg");
Picture picture = worksheet.getPictures().get(index);
picture.setPlacement(PlacementType.MOVE_AND_SIZE);
LineFormat lf = picture.getLine();
lf.setCapType(LineCapType.FLAT);
lf.setJoinType(LineJoinType.MITER);
lf.setCompoundType(MsoLineStyle.SINGLE);
lf.setDashStyle(MsoLineDashStyle.SOLID);
// borderWidth = borderWidth / CellsHelper.getDPI(); // convert it to inches
// picture.getLine().setWeight(borderWidth * 72); // converting to points by multiplying with 72
// picture.setBorderWeight(borderWidth * 72);
// Color color = Color.fromArgb(Integer.parseInt("FF8040", 16));
// picture.setBorderLineColor(color);
double imageHeight = picture.getOriginalHeightInch();//+ (2 * borderWidth);
double imageWidth = picture.getOriginalWidthInch();// + (2 * borderWidth);
//picture.setTopInch(borderWidth);
//picture.setLeftInch(borderWidth);
worksheet.getCells().setColumnWidthInch(1, imageWidth);
worksheet.getCells().setRowHeightInch(1, imageHeight);
workbook.save("D:\\Filetemp\\work0.xlsx");
Please let us know your feedback.
Hi,
We were actually doing the same in our earlier code implementation, which was to set the cell size to be of the image only and not the borders. It was working all fine. The trouble came when we decided to accomodate the borders as well.
The image tools you mention here, is it from Aspose?. Can you provide links to its documentation?
@Akash007,
We are referring to 3rd party image tools in suggested solution. The issue is still open and we will further look into the details and will update you soon.
@Akash007,
Below is a temporary solution to support your request without 3rd party image tools:
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
double borderWidth = 5; // input is always expected in pixels.
int index = worksheet.getPictures().add(1, 1, "D:\\Filetemp\\school.jpg");
Picture picture = worksheet.getPictures().get(index);
LineFormat lf = picture.getLine();
lf.setCapType(LineCapType.FLAT);
lf.setJoinType(LineJoinType.MITER);
lf.setCompoundType(MsoLineStyle.SINGLE);
lf.setDashStyle(MsoLineDashStyle.SOLID);
borderWidth = borderWidth / CellsHelper.getDPI(); // convert it to inches
picture.getLine().setWeight(borderWidth * 72); // converting to points by multiplying with 72
picture.setBorderWeight(borderWidth * 72);
Color color = Color.fromArgb(Integer.parseInt("FF8040", 16));
picture.setBorderLineColor(color);
picture.toImage( "D:\\Filetemp\\school1.jpg", null);
worksheet.getPictures().removeAt(index);
index = worksheet.getPictures().add(1, 1,2,2, "D:\\Filetemp\\school1.jpg");
picture = worksheet.getPictures().get(index);
picture.setPlacement(PlacementType.MOVE_AND_SIZE);
double imageHeight = picture.getOriginalHeightInch();//+ (2 * borderWidth);
double imageWidth = picture.getOriginalWidthInch();// + (2 * borderWidth);
worksheet.getCells().setColumnWidthInch(1, imageWidth);
worksheet.getCells().setRowHeightInch(1, imageHeight);
workbook.save("D:\\Filetemp\\work0.xlsx");
Let us know your feedback.