Hi all,
I have recently downloaded and experimented with Apache POI V3.0.1, hoping
to use it in an application I am working to upgrade to generate actual Excel
files to export data to the user with embedded chart images and table data,
instead of the current MIME formatted message, which can be only be rendered
by certain versions of Excel on a Windows machine. However, I have quickly
discovered the limitations with trying to embed an image object into an
Excel spreadsheet using this project.
I discovered that unless you set an X2 and Y2 end point greater than the X1
and Y2 start point on the client anchor for the picture when anchoring to
the same cell (start and end cell are the same), you will never get an image
to appear...This should answer some of the other user's questions out there
about this. However, after doing some experimenting with different sized
columns, I noticed that the X2 and Y2 end point is not an actual fixed
number (a specific point in the last anchoring cell), instead, it appears to
be a percentage of the width and height, respectively, for that anchoring
cell. And what seems strange to me, is that the max units for the X2 and Y2
are different (X2 has a valid range of 0 - 1023 and Y2 has a valid range of
0 - 254), I am not quite sure why, if these are truly percentages, why they
would have different available value ranges, other than the fact that these
fields are being overloaded for multiple uses for the anchor object.
Anyway, the real issue comes into play when you attempt to display an image
with its actual width and height (in pixels) in the cell(s) without HSSF (or
maybe its Excel) automatically adjusting its size to the anchor object,
instead, I would like to be able to set the anchor (or the cells that will
be contained by the anchor) to some width and height, that is at least big
enough, but probably a bit bigger (the width would be the big unknown factor
since I will need to merge across several cells in the row, unless of course
the anchor width could just be ignored and image could automatically just
span over as many cells as its needs, akin to absolute positioning, however,
from reading past posts, I doubt this is available). Then when the anchor
cells are defined for a space bigger than what the image needs, I would like
to be able to have either an option to use the original height and width of
the image object within the anchor, instead of it automatically resizing it
to fit the anchor cells, or I would like options on the Picture object that
allow you to specify the actual width and height, or even the resize ratio,
to use when rendering the picture, so that you have direct control of the
size of the rendered image and it can be independent of the size of the
cells contained by its anchor. Suffice it to say, I am not sure these
options will be implemented anytime soon, so I decided to use a little width
hack to set the width "percentage" (when using a single anchor cell or a
ShapeGroup to contain the embedded image.
Basically, I use a merged region to contain a ShapeGroup that contains my
image, then I find the total width of these cells that make up the
ShapeGroup anchor, then attempt to convert the Image width (specified in
pixels) to the same unit as the total width of these anchor cells. This is
the tricky part, since I am not sure exactly what unit type is being
reported by the getColumnWidth function, I am assuming twips, but the
conversion doesn't quite seem right, unless I apply a fudge factor (if
someone knows please let me know), and then divide by the total width of the
anchor cells to obtain a ratio, then multiply by 1024 since this is how many
values are available for the width percentage, and this formula will give
the proper X2 value to use for the Child Anchor, which will size the width
of the image to be pretty close to the original width of the image.
The height is easily assigned to the row that contains the ShapeGroup, since
I am only using 1 row to display the image, by just converting the Image
height into points and then setting the height of the row current conversion
factor for 1 pixel is 0.75 points
Current conversion factor from 1 pixel is 15 twips
fudgefactor = 2 (this seems to be needed and will get the image to be
consistently about 95% of its original width)
Formula: scaleWidth = ((ImageWidth * 15* fudgefactor)/total anchor cell
width)*1024
...
int imageHeight = 169;
float imgPHeight = Float.parseFloat(String.valueOf((float)imageHeight*.75));
r.setHeightInPoints(imgPHeight);
...
int imageWidth = 117; //in pixels
Region rg = new Region(1, (short)1,1,(short)3);
s.addMergedRegion(rg);
HSSFPatriarch patriarch = s.createDrawingPatriarch();
HSSFClientAnchor anchor;
anchor = new
HSSFClientAnchor(0,0,0,0,rg.getColumnFrom(),1,(short)(rg.getColumnTo()+1),2;
HSSFShapeGroup group = patriarch.createGroup(anchor);
anchor.setAnchorType( 3 );
int rngWidth = 0;
for (short cellnum = rg.getColumnFrom(); cellnum <= rg.getColumnTo();
cellnum++){
rngWidth += s.getColumnWidth(cellnum);
}
int scaleWidth = (int)Math.round(((imageWidth*15*2)/rngWidth)*1024);
HSSFChildAnchor an1 = new HSSFChildAnchor(0,0,scaleWidth,254);
HSSFPicture pic = group.createPicture(an1, loadPicture( basePath +
"/images/logoKarmokar4.png", wb ));
Thanks,
Geoff Groskreutz
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
