You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Hannes Kleindienst <kl...@grid-it.at> on 2011/01/27 17:32:14 UTC
Anchor type for images in Excel
Hi,
I am having trouble with the anchor type when I am adding images to an
excel file. The point is that I would like to use the autofilter
function within Excel, which requires the images to be positioned in a
way that they are allowed to be resized and moved with a cell.
I thought, the anchor type would take care of this. I tried the
following code
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1(col);
anchor.setRow1(row);
anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);
then adding the images as described in the examples. The images appear
as expected, the cell size is calculated and adjusted to the image size,
but when I click on "Size and Properties" of the image, the positioning
of the object (second tab) is still set to "depend only on cell
position" instead on "depend on cell position and size" (the translation
might be not entirely correct, since I don't have the english version of
Excel).
Of course, I also tried the other anchor types, no effect there.
Did I miss anything?
Cheers,
Hannes
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Anchor type for images in Excel
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Helo again Hannes,
This is a very quick response and may not actually identify the cause of the
problem. However, the javadoc for the resize() method includes the
following;
"Please note, that this method works correctly only for workbooks with the
default font size (Calibri 11pt for .xlsx). If the default font is changed
the resized image can be streched vertically or horizontally. "
I wonder if this is what you are falling foul of? The easiest way to check
of course would be to simply look at the default font setting for your
workbook.
I have been meaning to look back at earlier versions of HSSF becuase I am
pretty confident that the anchor 'problem' did not used to exist - by this I
mean that the anchor type specified in code was always successfully
translated into the appropriate workbook setting. My memory is getting poor
now but I do remember both XSSF and HSSF streams setting anchor types
correctly and wonder if the change is the result of some work that was
undertaken on the class. As of this morning, I do not know this to be the
case and need to raid the archive to get my hands on a few earlier versions
of POI to run the tests, but I will post again if and when I find anything.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Anchor-type-for-images-in-Excel-tp3360031p3383315.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 type for images in Excel
Posted by Hannes Kleindienst <kl...@grid-it.at>.
Hi Mark,
As discussed previously, I encountered some problems with the anchor
type using the HSSFWorkbook ... I now tried the XSSFWorkbook with the
same code and, what a miracle, the anchor type works just as it should.
(I had a look at the XSSFPicture source after I wrote this e-mail, see
results below...)
But now, I am facing another problem concerning the resize() method of
the Picture object.
As long as the image is larger (i.e. higher) than the cell, the resize()
works fine. But if the image fits into one cell, the effect of resize()
is that the image is scaled differentyl in X and Y. Width is ok, but
height seems to be just the default height of a row.
I am attaching the java test code to this e-mail.
What I want to achieve is that one image nicely fits into one cell.
Therefore I am setting the cell width and estimating the cell height.
Is it a bug or did I miss anything?
######
After checking the sources of XSSFPicture, I think I found the cause of
this behaviour. There is a bug if an image fits into one cell, the
calculation of the dx2 and dy2 is wrong:
In the
public XSSFClientAnchor getPreferredSize(double scale)
method, the remaining X (dx2) in the next column is caluclated as follows:
if (w > scaledWidth) {
double cw = getColumnWidthInPixels(col2 + 1);
double delta = w - scaledWidth;
dx2 = (int) (EMU_PER_PIXEL * (cw - delta));
}
which will give wrong results, if the image does not touch the
neighbouring column. I have added a test as follows:
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);
}
}
which solves the problem. Same for the rows in the same method:
if (h > scaledHeight) {
double ch = getRowHeightInPixels(row2 + 1);
double delta = h - scaledHeight;
dy2 = (int) (EMU_PER_PIXEL * (ch - delta));
// again test single row:
if (row2 == anchor.getRow1()) {
dy2 = (int) (EMU_PER_PIXEL * scaledHeight);
}
}
I am going to build the poi-ooxml library myself, although there is one
import missing in the XWPFStyle class:
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STStyleType;
I checked the poi-ooxml-schemas-3.7-20101029.jar and indeed this class
is not there. Anyway, I am commenting out the two methods
public void setType(STStyleType.Enum type)
and
public STStyleType.Enum getType()
hoping it won't do too much harm.
Cheers,
Hannes
PS: I am sending this as CC to the dev-list as well.
Am 07.02.2011 18:00, schrieb Mark Beardsley:
> Will have a look at the attached example when I can. Sadly, I am away from
> home at the moment and stuck using a laptop that does not have POI, Java or
> Excel on it unfortunately
--
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
Re: Anchor type for images in Excel
Posted by Hannes Kleindienst <kl...@grid-it.at>.
Hi Mark,
As discussed previously, I encountered some problems with the anchor
type using the HSSFWorkbook ... I now tried the XSSFWorkbook with the
same code and, what a miracle, the anchor type works just as it should.
(I had a look at the XSSFPicture source after I wrote this e-mail, see
results below...)
But now, I am facing another problem concerning the resize() method of
the Picture object.
As long as the image is larger (i.e. higher) than the cell, the resize()
works fine. But if the image fits into one cell, the effect of resize()
is that the image is scaled differentyl in X and Y. Width is ok, but
height seems to be just the default height of a row.
I am attaching the java test code to this e-mail.
What I want to achieve is that one image nicely fits into one cell.
Therefore I am setting the cell width and estimating the cell height.
Is it a bug or did I miss anything?
######
After checking the sources of XSSFPicture, I think I found the cause of
this behaviour. There is a bug if an image fits into one cell, the
calculation of the dx2 and dy2 is wrong:
In the
public XSSFClientAnchor getPreferredSize(double scale)
method, the remaining X (dx2) in the next column is caluclated as follows:
if (w > scaledWidth) {
double cw = getColumnWidthInPixels(col2 + 1);
double delta = w - scaledWidth;
dx2 = (int) (EMU_PER_PIXEL * (cw - delta));
}
which will give wrong results, if the image does not touch the
neighbouring column. I have added a test as follows:
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);
}
}
which solves the problem. Same for the rows in the same method:
if (h > scaledHeight) {
double ch = getRowHeightInPixels(row2 + 1);
double delta = h - scaledHeight;
dy2 = (int) (EMU_PER_PIXEL * (ch - delta));
// again test single row:
if (row2 == anchor.getRow1()) {
dy2 = (int) (EMU_PER_PIXEL * scaledHeight);
}
}
I am going to build the poi-ooxml library myself, although there is one
import missing in the XWPFStyle class:
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STStyleType;
I checked the poi-ooxml-schemas-3.7-20101029.jar and indeed this class
is not there. Anyway, I am commenting out the two methods
public void setType(STStyleType.Enum type)
and
public STStyleType.Enum getType()
hoping it won't do too much harm.
Cheers,
Hannes
PS: I am sending this as CC to the dev-list as well.
Am 07.02.2011 18:00, schrieb Mark Beardsley:
> Will have a look at the attached example when I can. Sadly, I am away from
> home at the moment and stuck using a laptop that does not have POI, Java or
> Excel on it unfortunately
--
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
Re: Anchor type for images in Excel
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Hello Hannes
Will have a look at the attached example when I can. Sadly, I am away from
home at the moment and stuck using a laptop that does not have POI, Java or
Excel on it unfortunately, so I will not be able to look into anything for a
few days. It might be worthwhile, in the interim, flagging this issue up on
the dev list as it is more likely to catch Yegor or Nick's attention there I
think.
Will post again if I make any discovery but that is unikely to be before the
weekend.
Yours
Mark B
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Anchor-type-for-images-in-Excel-tp3360031p3374568.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 type for images in Excel
Posted by Hannes Kleindienst <kl...@grid-it.at>.
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
Re: Anchor type for images in Excel
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Morning Hannes,
This is not a reply to your question regarding anchors as such, more some
observations I made a long time ago whilst working with images and I do not
know if they wil help you out of a tricky spot.
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.
Yours
Mark B
PS There is an example that demonstrates more what I have been talking about
-
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/AddDimensionedImage.java
- and the doc block includes a better explanation of the anchor's
parameters.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Anchor-type-for-images-in-Excel-tp3360031p3361069.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