How do I find text being overlapped by an image?

Aspose.Cells Product Family
1

I am trying to build a CellRange based on a Shape like :

new CellRange(shape.getUpperLeftRow(), shape.getUpperLeftColumn(), shape.getLowerRightRow(), shape.getLowerRightColumn())

The problem though is that depending on the size of the image or how close are they to the end of the last cell it actually covers, the methods are not returning proper values. The jar is obfuscated but based on the javadocs we have a certain level of roundiness.

That said, what would be the best way to get such cell range? Is there a way to get the cells coordinates to make the math manually and compensate the roundings? What are the Deltas method about (i.e.: getLowerDeltaX)?

2

@samuelmartinucci,

CellRange is a class of Aspose.Cells.GridDesktop control and does not belong to Aspose.Cells for Java library. Are you using Aspose.Cells for Java library? it looks so.

I think you are doing right. Get the image (Shape) in the worksheet and use the relevant methods to get to know about the area of image it covers. Do you find any issue with getUpperLeftRow(), getUpperLeftColumn(), getLowerRightRow() and getLowerRightColumn() methods of Shape class in Aspose.Cells for Java API? Please elaborate with sample Excel file (please zip the file prior attaching) and sample code (that you are using). We will check your issue soon.

It gives horizontal offset to the lower right corner column for the shape.

3

@Amjad_Sahi, unfortunately I cannot share the content of the spreadsheet, what I can tell is that the size of the image varies on Mac and Windows due to Redirecting .

For Mac users, the image is far from touching the other column, while on Windows it touches 1 or 2 pixels. My question is much more generic, like, is there a way to know how many pixels is the image hiding from the right-most column (consider it for all sizes)? My idea is to apply a compensation algorithm and have some sort of tolerance in pixels. An image consuming 1 pixel from the right most cell is not really hiding its content.

4

@samuelmartinucci,

I think you may use the devised methods to know the area of the image shape. Then use the delta method(s) to get the offset from the corner column/row.

Let us know with sample files, sample code and details if you find any issue.

5

@Amjad_Sahi, I’ve tried some stuff and the only thing that seems to work is modifying the shape’s width / x values and let the framework do the job, which doesn’t seem to be ideal, given it may be doing much more than what I need.

Some very basic maths that I’ve tried were:

var sheet = new Workbook(Main.class.getClassLoader().getResourceAsStream("Book1.xlsx")).getWorksheets()
            .get(0);
    var shape = sheet.getShapes().get(0);

    System.out.println(String.format("shape.getLowerDeltaX() = %d", shape.getLowerDeltaX()));
    double columnWidth = sheet.getCells().getColumnWidth(shape.getUpperLeftColumn());
    System.out.println(String.format("shape.getUpperDeltaX() = %d", shape.getUpperDeltaX()));
    System.out.println(String.format("columnWidth = %f", columnWidth));
    System.out.println(String.format("shape.getLeft() = %d", shape.getLeft()));

For the spreadsheet attached, which has an image consuming a couple of pixels from the left and right cells, I should be able to get

Consuming left cells = getUpperDeltaX - columnWidth (which is giving ~1000)
Consuming right cells = getLowerDeltaX (which is giving 48)

Notice that both values doesn’t reflect a 1/2 pixels consumption to the left and to the right.

Another option that I’ve tried:

double totalWidth = 0;
    for (int i = 0 ; i < shape.getUpperLeftColumn(); i++) {
        totalWidth += sheet.getCells().getColumnWidth(i);
    }

    System.out.println(String.format("totalWidth = %f", totalWidth));
    System.out.println(String.format("shape.getLeft() = %d", shape.getLeft()));

Assuming the total width, until the cell the image starts, would be close to the value of getLeft or maybe getX, although I am getting:

totalWidth = 124.861429
shape.getLeft() = 63
shape.getX() = 1007

Can you help me understanding what’s wrong on my math?

Book1.zip (20.6 KB)

6

@samuelalmeida,

Thanks for the file and details.

We will evaluate your issue thoroughly and get back to you soon.

By the way, I did test using your file a bit. Could you please share your (exact) expected values against the attributes. This will help to evaluate and figure out your issue precisely.

7

@Amjad_Sahi, I am trying to identify how much an image is covering from the left most, right most, top most and bottom most cells, as such I’ve been exploring the API documentation and my conclusion was:

  1. To get the LEFT most coverage, I should be able via:

The sum of the width of all the preceding cells - getX
The sum of the width of all the preceding cells - getLeft
The width of the Upper Left cell - getUpperDeltaX

None of them are returning valid values since the diff is huge (more than 100 for most cases like I described above) whereas like you can see from the spreadsheet the coverage is minimal.

  1. As per the RIGHT most cell, it should be even simpler, getLowerDeltaX should’ve given me the value, but it’s returning 1000 like described above.
8

@samuelmartinucci,

Thanks for providing further details.

We will be looking into it and get back to you with details and samples.

9

@samuelmartinucci，
We will deprecate getUpperDeltaX() and getUpperDeltaY(), so these two functions are no longer recommended.
We recommend using the following three sets of functions depending on your purpose:

shape.getX(),shape.getY(),shape.getWidth(),shape.getHeight()
shape.getLeft(),shape.getTop(),shape.getRight(),shape.getBottom()
shape.getUpperLeftColumn(), shape.getUpperLeftRow(), shape.getLowerRightColumn(), shape.getLowerRightRow()

We describe the meaning of the above groups of functions in the image.png pictures for your reference. It should be noted that the values obtained by the first two sets of functions are pixel values.If you use the third set of functions in your calculation, it is recommended to convert to pixel values through ‘sheet.getCells().getColumnWidthPixel()’.
image.png (17.8 KB)