You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Tim Dudgeon <td...@informaticsmatters.com> on 2011/04/14 12:26:32 UTC
Anchor image to a cell
Hi all,
I'm anchoring images to a cell in Excel sheet using HSSFClientAnchor.
Its mostly going OK, but I see strange behaviour in Excel related to
sorting.
I have one column in sheet that contains images, and other columns that
contain corresponding text or numeric data corresponding the image.
The images are anchored using MOVE_AND_RESIZE.
The anchor is created like this:
int row = ...;
int col = ...;
int index = workbook.addPicture(image, format);
anchor = new HSSFCLientAnchor(0,0,0,0, (short)col, row, (short) (col +
1), row + 1);
anchor.setAnchorType(MOVE_AND_RESIZE);
patriarch.createPicture(anchor, index);
When the column or row heights are changed the images resize correctly.
When the sheet is filtered the correct images are associated with the
correct cells.
When the sheet is sorted the images do not move according the the new
sorted order.
It seems to me that the way Excel does sorting and filtering is
different. When data is filtered the displayed row numbers change. e.g.
the row numbers can now be 1,5,7,8,11,13... and the appropriate image is
still displayed against each row.
However when the data is sorted the row numbers are 1,2,3,4,5,6,7... and
the wrong (original) image is associated with the row.
So this looks to me like an inconsistency in Excel, but I thought I'd
ask if there were any suggestions for getting this working better with POI?
Thanks
Tim
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Ah, thanks Hannes. I could remember the topic but none of the details - very
old man with few brain cells left owing to drinking far too much string ale
in my long distant youth.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Anchor-image-to-a-cell-tp4302682p4307026.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Hannes Kleindienst <kl...@grid-it.at>.
> Actually it is a bug in the way, the row and column is calculated,
> leading to strange stretching of the image.
In order to clarify, the bug is in the class
XSSFClientAnchor
in the method
getPreferredSize(double scale)
The remaining X (dx2) is not calculated the right way.
Instead of
if (w > scaledWidth) {
double cw = getColumnWidthInPixels(col2 + 1);
double delta = w - scaledWidth;
dx2 = (int) (EMU_PER_PIXEL * (cw - delta));
}
it should be
if (w > scaledWidth) {
double cw = getColumnWidthInPixels(col2 + 1);
double delta = w - scaledWidth;
dx2 = (int) (EMU_PER_PIXEL * (cw - delta));
// test single col:
if (col2 == anchor.getCol1()) {
dx2 = (int) (EMU_PER_PIXEL * scaledWidth);
}
}
and same modification for the rows.
Cheers,
Hannes
--
GRID-IT Gesellschaft für angewandte Geoinformatik mbH
Dr. Hannes Kleindienst
Technikerstrasse 21a, A-6020 Innsbruck
web www.grid-it.at
tel +43-(0)512-507 4861
mobil +43-(0)676-4300399
fax +43-(0)512-5074869
mail kleindienst@grid-it.at
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Hannes Kleindienst <kl...@grid-it.at>.
Hi Mark,
since this was my issue a couple of weeks (or is it months?) ago, I
might get back on this.
Actually it is a bug in the way, the row and column is calculated,
leading to strange stretching of the image.
I corrected the bug in my code, recompiled it and now it works fine. I
sent an e-mail to the dev-list with the modifications I did. However, I
did not follow, if the changes found their way into the current beta
release.
Cheers,
Hannes
PS: I sent the e-mail with the bug correction on 12 Feb 2011 17:42 - I
can send it again, if required
PPS: So it is "months" after all ...
Am 15.04.2011 13:42, schrieb Mark Beardsley:
> Some time ago, another post exactly like this one surfaced and we found that
> the problem was with the anchor. It actually 'attaches' for want of a better
> word, the bottom left hand corner of the image to the top left hand corner
> of an adjacent cell. To explain this a little better, imagine that you
> inserted an image into cell A1, then the bottom left hand corner of that
> image would actually be 'attached' - again for waht of a better word - to
> the top left hand corner of cell B2. This allows Excel to stretch the image
> if the width of column A is increased or the heigth of row one is likewise
> increased. It also seemed to be the reason why the image did not move when
> the data was sorted - it was anchored to the top left hand corners of two
> cells and Excel did not know how to handle this problem. The solution seemed
> to be to change the way the images anchor was created so that you specified
> the width and height of the image using Excels co-ordinate system; and I
> cannot remember off hand exactly what these values were and so need to look
> up some old code. When I get the full information, I will post again later.
>
> The test I conducted to get to this answer was simple. I used POI to insert
> images into cells just as you did and confirmed the 'sort proble'. Next, I
> simply used Excel to modify the sizes of the images so that they no longer
> overlapped the edges of the cell and found that the sort problem was
> resolved. It might be worthwhile repeating this test yourself just to make
> sure my recollection is correct.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Hannes Kleindienst <kl...@grid-it.at>.
Hi Tim,
I am currently using the 3.8b3 and everything seems to be working as
expected.
Thanks
Hannes
Am 19.08.2011 10:26, schrieb Tim Dudgeon:
> On 20/04/2011 14:16, Hannes Kleindienst wrote:
>>
>> No, it requires a fix within POI (in the ooxml package). I think, I did
>> that on another computer at home, so I will check this evening.
>
> Hannes, did you manage to track down this fix?
>
> Tim
--
GRID-IT Gesellschaft für angewandte Geoinformatik mbH
Dr. Hannes Kleindienst
Technikerstrasse 21a, A-6020 Innsbruck
web www.grid-it.at
tel +43-(0)512-507 4861
mobil +43-(0)676-4300399
fax +43-(0)512-5074869
mail kleindienst@grid-it.at
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
On 20/04/2011 14:16, Hannes Kleindienst wrote:
>
> No, it requires a fix within POI (in the ooxml package). I think, I did
> that on another computer at home, so I will check this evening.
Hannes, did you manage to track down this fix?
Tim
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Hannes Kleindienst <kl...@grid-it.at>.
Hi Tim,
Am 19.04.2011 13:48, schrieb Tim Dudgeon:
> Thanks for that info and the code. My need is very similar to yours,
> without the scaling.
Ok, I added the scaling just to get a better resolution for the print
... otherwise the images will print with about 72dpi, which might result
in bad quality.
> But I'm not clear on the fix.
> Is the bug in POI and need to be patched prior to your code working, or
> does the code example you provided work around the bug?
No, it requires a fix within POI (in the ooxml package). I think, I did
that on another computer at home, so I will check this evening.
Cheers,
Hannes
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
Hi Hannes,
Thanks for that info and the code. My need is very similar to yours,
without the scaling.
But I'm not clear on the fix.
Is the bug in POI and need to be patched prior to your code working, or
does the code example you provided work around the bug?
Thanks
Tim
On 18/04/2011 06:39, Hannes Kleindienst wrote:
> Good morning,
>
> Am 16.04.2011 11:01, schrieb Mark Beardsley:
>> I do not think it is a bug, rather that is how Excel works;
>
> I can assure you, it is a bug. I fixed the code, and since then it is
> working just as expected. I didn't want to bother the list with
> technical details, but I can provide further info, if you want.
>
> Tim, maybe this can help: In my application, I had the requirement that
> the image should fill once cell, not extend over a range of cells. I
> have several images in a row, the cell width is set to 200px. The images
> are resized to a widht of 400px (scaling factor 2).
>
> My code looks like this (row and col are integer row index and col index):
>
> // image scale (relevant for higher DPI)
> float imgscale = 2;
> // resize image
> BufferedImage img = Util.resizeImage(ImageIO.read(imagefile),
> (int)(width*imgscale));
> // get dimensions of image (height)
> int height = img.getHeight();
> Row r = sheet.getRow(row);
> if (r == null) {
> r = sheet.createRow(row);
> }
> // set row height depending on image height
> r.setHeightInPoints(1+ height * 0.75F / imgscale);
> // write image to cell
> ByteArrayOutputStream baos = new ByteArrayOutputStream();
> ImageIO.write(img, "JPG", baos);
> int pictureIdx = wb.addPicture(baos.toByteArray(),
> Workbook.PICTURE_TYPE_JPEG);
> CreationHelper helper = wb.getCreationHelper();
> // Create the drawing patriarch. This is the top level container for all
> shapes.
> Drawing drawing = sheet.createDrawingPatriarch();
> //add a picture shape
> ClientAnchor anchor = helper.createClientAnchor();
> //set top-left corner of the picture (relative to cells)
> anchor.setCol1(col);
> anchor.setRow1(row);
> // important for being able to sort
> anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);
> Picture pict = drawing.createPicture(anchor, pictureIdx);
> //auto-size picture relative to its top-left corner
> pict.resize(1/imgscale);
>
> Maybe this helps.
>
> Cheers
> Hannes
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Yes please Hannes, if you could post the information that you have it will
help the developers. The best thing to do is to open a bug in Bugzilla, post
your code along with an explanation of the problem so that it can be fixed
in the 'official' POI release.
Thanks again and it is always good to learn something new.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Anchor-image-to-a-cell-tp4302682p4310053.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Hannes Kleindienst <kl...@grid-it.at>.
Good morning,
Am 16.04.2011 11:01, schrieb Mark Beardsley:
> I do not think it is a bug, rather that is how Excel works;
I can assure you, it is a bug. I fixed the code, and since then it is
working just as expected. I didn't want to bother the list with
technical details, but I can provide further info, if you want.
Tim, maybe this can help: In my application, I had the requirement that
the image should fill once cell, not extend over a range of cells. I
have several images in a row, the cell width is set to 200px. The images
are resized to a widht of 400px (scaling factor 2).
My code looks like this (row and col are integer row index and col index):
// image scale (relevant for higher DPI)
float imgscale = 2;
// resize image
BufferedImage img = Util.resizeImage(ImageIO.read(imagefile),
(int)(width*imgscale));
// get dimensions of image (height)
int height = img.getHeight();
Row r = sheet.getRow(row);
if (r == null) {
r = sheet.createRow(row);
}
// set row height depending on image height
r.setHeightInPoints(1+ height * 0.75F / imgscale);
// write image to cell
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ImageIO.write(img, "JPG", baos);
int pictureIdx = wb.addPicture(baos.toByteArray(),
Workbook.PICTURE_TYPE_JPEG);
CreationHelper helper = wb.getCreationHelper();
// Create the drawing patriarch. This is the top level container for all
shapes.
Drawing drawing = sheet.createDrawingPatriarch();
//add a picture shape
ClientAnchor anchor = helper.createClientAnchor();
//set top-left corner of the picture (relative to cells)
anchor.setCol1(col);
anchor.setRow1(row);
// important for being able to sort
anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);
Picture pict = drawing.createPicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
pict.resize(1/imgscale);
Maybe this helps.
Cheers
Hannes
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
I do not think it is a bug, rather that is how Excel works; I guess I am
saying that there is a trade off - to get the images to re-size, they need
to be 'attached' to the adjoining cells but doing so prevents the sort from
working correctly. Honestly, I cannot think of any way that POI could be
changed to circumvent this problem as it is a 'feature' of the Excel
application itself I believe. It might be worth playing around with the
image sizes in Excel just to ensure that I am correct, but I do think that
as soon as the image is wholly contained within the cell - so to speak - the
sort will work but re-sizing will fail and once the image overlaps so that
it is attached to the adjoining cell, the reverse will hold true. Further, I
would ask this question on an Excel forum just to make sure that my
POIcentric view of events is actually accurate.
As to the offsets, I have not had the chance to look at my old code yet so
cannot provide a definitive answer. If I am correct, you need to use the
first four paramaters of the anchor to set the x and y coordinates for the
top left and bottom right hand corners of the image. There are maxima for
the coordinates of the bottom right hand corner of the image which I cannot
remember off hand but I do know that HSSF enforces them so the best advise
is to try a few values and see what happens. From memory, the exception
message includes the maximum value.
If you want the top left hand corner of the image to be inside another cell,
then provide the co-ordinates for the position within the cell as the first
two - I think - arguments to the anchor and the index for the cell as
arguments number 5 and 6. As you say, you cannot use negative numbers and so
will have to combine setting two 'sets' of arguments so that they work
together.
Yours
Mark B
PS Even if the image is not 'attached' to the adjoining cell, it still ought
to shrink in size - I think - and it will grow in size also until it reaches
it's full size.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Anchor-image-to-a-cell-tp4302682p4307172.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
On 15/04/2011 12:42, Mark Beardsley wrote:
> Some time ago, another post exactly like this one surfaced and we found that
> the problem was with the anchor. It actually 'attaches' for want of a better
> word, the bottom left hand corner of the image to the top left hand corner
> of an adjacent cell. To explain this a little better, imagine that you
> inserted an image into cell A1, then the bottom left hand corner of that
> image would actually be 'attached' - again for waht of a better word - to
> the top left hand corner of cell B2. This allows Excel to stretch the image
> if the width of column A is increased or the heigth of row one is likewise
> increased. It also seemed to be the reason why the image did not move when
> the data was sorted - it was anchored to the top left hand corners of two
> cells and Excel did not know how to handle this problem. The solution seemed
> to be to change the way the images anchor was created so that you specified
> the width and height of the image using Excels co-ordinate system; and I
> cannot remember off hand exactly what these values were and so need to look
> up some old code. When I get the full information, I will post again later.
>
> The test I conducted to get to this answer was simple. I used POI to insert
> images into cells just as you did and confirmed the 'sort proble'. Next, I
> simply used Excel to modify the sizes of the images so that they no longer
> overlapped the edges of the cell and found that the sort problem was
> resolved. It might be worthwhile repeating this test yourself just to make
> sure my recollection is correct.
Yes, I tried this and it does then work correctly. Though doing so
prevents the images being re-scaled when the row or column size is
changed, so its not a realy solution.
So if I understand this and the other posts correctly, then this is a
bug that will be fixed in some future release (it is in the pipeline to
be resolved?).
As a work around I would be able to make the images smaller so that they
were entitely within the cells. But I can't figure out how to do this. I
don't seem able to say "anchor to bottom right corner of cell", only to
coordinates relative to the top left corner. I also can't specify
negative coordinates for the neighboring cell (I tried but it throws
exceptions).
Tim
>
> Yours
>
> Mark B
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Anchor-image-to-a-cell-tp4302682p4305346.html
> Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor image to a cell
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Some time ago, another post exactly like this one surfaced and we found that
the problem was with the anchor. It actually 'attaches' for want of a better
word, the bottom left hand corner of the image to the top left hand corner
of an adjacent cell. To explain this a little better, imagine that you
inserted an image into cell A1, then the bottom left hand corner of that
image would actually be 'attached' - again for waht of a better word - to
the top left hand corner of cell B2. This allows Excel to stretch the image
if the width of column A is increased or the heigth of row one is likewise
increased. It also seemed to be the reason why the image did not move when
the data was sorted - it was anchored to the top left hand corners of two
cells and Excel did not know how to handle this problem. The solution seemed
to be to change the way the images anchor was created so that you specified
the width and height of the image using Excels co-ordinate system; and I
cannot remember off hand exactly what these values were and so need to look
up some old code. When I get the full information, I will post again later.
The test I conducted to get to this answer was simple. I used POI to insert
images into cells just as you did and confirmed the 'sort proble'. Next, I
simply used Excel to modify the sizes of the images so that they no longer
overlapped the edges of the cell and found that the sort problem was
resolved. It might be worthwhile repeating this test yourself just to make
sure my recollection is correct.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Anchor-image-to-a-cell-tp4302682p4305346.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org