You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Guha, Rajarshi (NIH/NHGRI) [C]" <gu...@mail.nih.gov> on 2010/07/01 23:26:51 UTC
Inserted images don't move with cells
Hi, I'm using POI 3.6 and the HSSF model to generate a spreadsheet with
images in a column, along with numbers in an adjacent column. The code I'm
using specifically calls
anchor.setAnchorType(0);
To indicate that the images should move with the cells. But when I sort the
two columns, based on column A, the images don't seem to move with the
cells. That is, if column A is sorted in descending order, the value 18 gets
an image in its row, whereas it should not.
The code is available at http://gist.github.com/460578 (though any PNG can
be used in place of the one referenced in the code).
Is my code wrong? Or have I misunderstood the idea of images moving with
cells?
Thanks,
--
Rajarshi Guha
NIH Chemical Genomics Center
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Inserted images don't move with cells
Posted by MSB <ma...@tiscali.co.uk>.
On the one hand, it's good to know that you have 'solved' the problem but it
is a bit worrying to discover that Excel exhibits 'abberant' behaviour on
different platforms. This is definately something to note just in case
another user experiences a similar problem. Thanks for letting us know.
Yours
Mark B
PS You are quite correct to say that images are not inserted into cells.
They actually 'float' over the surface of the worksheet and the anchor
determines where they appear.
Guha, Rajarshi (NIH/NHGRI) [C] wrote:
>
>
>
>
> On 7/2/10 2:17 AM, "MSB" <ma...@tiscali.co.uk> wrote:
>
>> This problam seems to come about when the image is inserted into the cell
>> and occupies all of it, in fact the image overlaps very slightly and
>> touches
>> the edges of the adjoining cells to the right and below - this is just
>> the
>> way the anchor works by default I am afraid. What the anchor object does
>> is
>> not just say 'insert this image into cell A1' but it also specifies the
>> insets from the top left and bottom right hand corners of the cell. To
>> fill
>> the cell completely, it actaully 'tells' Excel that the image ought to
>> touch
>> the top left hand corner of the cell that appears below and to the right
>> of
>> the image's cell - cell B2 in this example. As the image is touching
>> other
>> cells, it cannot be moved when you sort as it is - effectively -
>> occupying
>> more than one cell and the application does not know exactly what to do.
>> To
>> confirm this, all you need to do is use Excel to shrink each image down
>> so
>> that it does fit within the bounds of a single cell and then try the sort
>> again. This time, it ought to work successfully.
>
> It appears that I don't need to fiddle with insets. My original code works
> fine when the resultant file is opened using Excel on Windows XP but does
> not work (even when using the AddDimensionedImage class) on Excel for OSX
> :(
>
> So at least the problem is solved - just use Windows :)
>
> --
> Rajarshi Guha
> NIH Chemical Genomics Center
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/Inserted-images-don%27t-move-with-cells-tp29050555p29057517.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: Inserted images don't move with cells
Posted by "Guha, Rajarshi (NIH/NHGRI) [C]" <gu...@mail.nih.gov>.
Thanks for the info. Comments below
On 7/2/10 2:17 AM, "MSB" <ma...@tiscali.co.uk> wrote:
> This is going to get quite technical and I apologise for that but there is no
> other option really. Also, I am making an assumption here - that you are
> inserting images into cells and those images occupy all of the cell.
Hmm, my code doesn't actually put an image "into" a cell via the
setCellValue method of HSSFCell - as far as I can tell there is no method to
put an image in there - is there such a method?
The only place that a reference to a cell location is made is when I
instantiate a HSSFClientAnchor.
> This problam seems to come about when the image is inserted into the cell
> and occupies all of it, in fact the image overlaps very slightly and touches
> the edges of the adjoining cells to the right and below - this is just the
> way the anchor works by default I am afraid. What the anchor object does is
> not just say 'insert this image into cell A1' but it also specifies the
> insets from the top left and bottom right hand corners of the cell. To fill
> the cell completely, it actaully 'tells' Excel that the image ought to touch
> the top left hand corner of the cell that appears below and to the right of
> the image's cell - cell B2 in this example. As the image is touching other
> cells, it cannot be moved when you sort as it is - effectively - occupying
> more than one cell and the application does not know exactly what to do. To
> confirm this, all you need to do is use Excel to shrink each image down so
> that it does fit within the bounds of a single cell and then try the sort
> again. This time, it ought to work successfully.
I tried manually scaling the images to fit within the cell, but that doesn't
seem to solve the problem (this is Excel on OS X)
--
Rajarshi Guha
NIH Chemical Genomics Center
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Inserted images don't move with cells
Posted by "Guha, Rajarshi (NIH/NHGRI) [C]" <gu...@mail.nih.gov>.
On 7/2/10 2:17 AM, "MSB" <ma...@tiscali.co.uk> wrote:
> This problam seems to come about when the image is inserted into the cell
> and occupies all of it, in fact the image overlaps very slightly and touches
> the edges of the adjoining cells to the right and below - this is just the
> way the anchor works by default I am afraid. What the anchor object does is
> not just say 'insert this image into cell A1' but it also specifies the
> insets from the top left and bottom right hand corners of the cell. To fill
> the cell completely, it actaully 'tells' Excel that the image ought to touch
> the top left hand corner of the cell that appears below and to the right of
> the image's cell - cell B2 in this example. As the image is touching other
> cells, it cannot be moved when you sort as it is - effectively - occupying
> more than one cell and the application does not know exactly what to do. To
> confirm this, all you need to do is use Excel to shrink each image down so
> that it does fit within the bounds of a single cell and then try the sort
> again. This time, it ought to work successfully.
It appears that I don't need to fiddle with insets. My original code works
fine when the resultant file is opened using Excel on Windows XP but does
not work (even when using the AddDimensionedImage class) on Excel for OSX :(
So at least the problem is solved - just use Windows :)
--
Rajarshi Guha
NIH Chemical Genomics Center
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Inserted images don't move with cells
Posted by MSB <ma...@tiscali.co.uk>.
This is going to get quite technical and I apologise for that but there is no
other option really. Also, I am making an assumption here - that you are
inserting images into cells and those images occupy all of the cell. If not,
ignore the following as it is based on some experimentation we did whith
images obeying those basic rules.
This problam seems to come about when the image is inserted into the cell
and occupies all of it, in fact the image overlaps very slightly and touches
the edges of the adjoining cells to the right and below - this is just the
way the anchor works by default I am afraid. What the anchor object does is
not just say 'insert this image into cell A1' but it also specifies the
insets from the top left and bottom right hand corners of the cell. To fill
the cell completely, it actaully 'tells' Excel that the image ought to touch
the top left hand corner of the cell that appears below and to the right of
the image's cell - cell B2 in this example. As the image is touching other
cells, it cannot be moved when you sort as it is - effectively - occupying
more than one cell and the application does not know exactly what to do. To
confirm this, all you need to do is use Excel to shrink each image down so
that it does fit within the bounds of a single cell and then try the sort
again. This time, it ought to work successfully.
To correct the issue, you will need to set the size of the image - well,
it's insets - using an anchor object.
Yours
Mark B
PS If you want to see how to use anchors, there is an example -
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/AddDimensionedImage.java
- just look at the addImageToSheet() method.
Guha, Rajarshi (NIH/NHGRI) [C] wrote:
>
> Hi, I'm using POI 3.6 and the HSSF model to generate a spreadsheet with
> images in a column, along with numbers in an adjacent column. The code I'm
> using specifically calls
>
> anchor.setAnchorType(0);
>
> To indicate that the images should move with the cells. But when I sort
> the
> two columns, based on column A, the images don't seem to move with the
> cells. That is, if column A is sorted in descending order, the value 18
> gets
> an image in its row, whereas it should not.
>
> The code is available at http://gist.github.com/460578 (though any PNG can
> be used in place of the one referenced in the code).
>
> Is my code wrong? Or have I misunderstood the idea of images moving with
> cells?
>
> Thanks,
>
> --
> Rajarshi Guha
> NIH Chemical Genomics Center
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/Inserted-images-don%27t-move-with-cells-tp29050555p29052537.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