Fwd: Re: Anchor type for images in Excel

[Hannes Kleindienst <kl...@grid-it.at>]

Hi,

as Mark suggested, I am forwarding my last e-mail concerning the Image 
anchor type problem to the Dev-Group.

I am facing the problem, that the anchor type has no influence when 
adding an image to an Excel sheet. But I would like to create an 
autofilter afterwards, which doesn't work with images as long as the 
images do not move and resize with the cells.

If there is a workaround, I'd appreciate some hints.

I have added a ZIP file with some code as described below.

Kind regards,
Hannes



-------- Original-Nachricht --------
Betreff: Re: Anchor type for images in Excel
Datum: Mon, 07 Feb 2011 17:21:45 +0100
Von: Hannes Kleindienst <kl...@grid-it.at>
Antwort an: POI Users List <us...@poi.apache.org>
An: POI Users List <us...@poi.apache.org>

Hello Mark,

sorry that I didn't answer to your reply, I just found your e-mail in my
spam folder.

Thank you for your hints. You were correct, I just specified the upper
left corner of the image cell. But I set the width and height of the
cell (row/column) according to the image size, assuring the image will
fill no more than one cell.

I also tried the other way of creating the anchor providing the cell
range. And I tried extending the image over several cells.

However, for all anchor types the image behaviour is the same.

I have created a small class for tests, which I am attaching to this
e-mail. The contents of the ZIP are
- img1.jpg (an example image)
- POItest.java (the java test class)
- poitest.xls (the resulting XLS file)
- excel_dialog.jpg (a screenshot)

... in the dialog (which is in German, sorry), the option "Nur von
Zellposition abhängig" is selected for all three cases. What should be
set is "Von Zellposition und -größe abhängig" ... meaning that the
object/image depends on cell position and size and therefore will be
included in the autofilter.

Cheers,
Hannes


Am 28.01.2011 09:15, schrieb Mark Beardsley:
> I noticed from the code you posted, that you only specify the co-ordinates
> for the top left hand corner of the image. In that case, the image will be
> inserted into the cell and occupy the full height and width of the cell.
> What it actually does is stretch over the cell boundary at the bottom and
> right hand edges of the cel it is in and 'touch' for want of a better word
> the left hand and top most boundary of the adjoining cell. This is how Excel
> knows to expand the size of the image if the cell it is in is made
> wider/thinner or higher/narrower. Sadly, I noticed that it also makes it
> impossible to include the image in any sort; Excel cannot split the contact
> between image and the adjoining cell boundary and so fails to move the cell
> during the sort.
>
> The solution to this was to specify co-ordinates for both corner - top left
> and bottom right - and to use the offsets to ensure the image did not pass
> the boundary of it's containing cell. In short the coordinates for both
> corners referenced the same cell - A1 for example - and the offsets were
> used to specify that it filled the whole of that cell. Interestingly, this
> also had an impact on the resizing behaviour of the image; once it was no
> longer 'attached' to the boundary of an adjacent cell, it would decrease but
> not increase in size beyond that which it had been when inserted into the
> cell. Sorry, but I cannot remember the maxima for the offsets of hand but
> you can find it out from the javadoc for the anchor I feel certain.
>
> Of course, all of this may well not have any impact on your requirement re
> the autofilter, I do not know, but it might and it save you from a wait
> whilst the developers attend to a possible bug.



-- 
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