Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Jakarta POI: Computing anchor size for easy image insertion?

Thread Tools

Jakarta POI: Computing anchor size for easy image insertion?
Posts: n/a
Hi all,

I'm using Jakarta POI 3.0-alpha3 to write an Excel spreadsheet with
images and I'm having some trouble computing the HSSFClientAnchor

At the moment I try to manage it by adding column widths and row
heights until I reach approximately the size of the image in cells,
and then set up dx and dy, but I can almost never get it to work
perfectly and images are never exactly at 100%.

Is there an easier way to do this? I have searched around in google,
groups and google code search but couldn't find anything useful. How
do you people do this?

Thanks a lot!

Below is a snippet of my code. Basically, you give the starting row/
col position of the image you want to insert and it goes around adding
row/column sizes into imgHeight and imgWidth until their values are
equal to or greater than the size of the image. If they're greater, it
substracts one row or one column, depending on what was greater, and
puts the remaining pixels on dx or dy.

protected int addImage(HSSFWorkbook wb, HSSFSheet sheet, InputStream
img, int numRow, short numCol) {
int x1 = 0;
int y1 = 0;
short x2 = 0;
int y2 = 0;
int row1 = numRow;
short col1 = numCol;
int row2 = numRow;
short col2 = numCol;
ImageData d = new ImageData();
int idx = loadAndConvertPicture(img,wb,d);
int imgHeight = 0;
int imgWidth = 0;

while(imgWidth <d.getWidth() || imgHeight <d.getHeight()) {
if(imgWidth <d.getWidth()) col2++;
if(imgHeight <d.getHeight()) row2++;
if(imgWidth != d.getWidth()) {
int colWidth = (int) (sheet.getColumnWidth((short)col2)/4;
if(imgWidth+colWidth<=d.getAmplada()) {
imgWidth +=colWidth;
} else {
if(imgHeight != d.getHeight()) {
int rowHeight;
HSSFRow row = sheet.getRow(row2);
if(row != null) rowHeight = (int) row.getHeight()/20;
else rowHeight = (int) sheet.getDefaultRowHeight()/20;
if(row == null) row = sheet.createRow(row2);
if(imgHeight +rowHeight<=d.getHeight()) {
imgHeight +=rowHeight;
} else {
y2=d.getHeight()-imgHeight ;
imgHeight =d.getHeight();

(note: variable and custom method names are translated since I'm not
coding this in English; there might be a bug in there somewhere due to
this translation. My apologies if this is the case.)

Reply With Quote
Posts: n/a
I found an answer:

Excel anchores objects against top-left and buttom-right cells. In
addition it uses dx and dy for "fine positioning" relative to these
cells. So to get the correct aspect ratio you need to know the number
of cells and rows your image will occupy. Here it gets complicated .

The problem is that column width in Excel is expressed in units of
1/256th of a character width of the default font.
Conversion from these weird 1/256th units to pixels is not trivial.

The minimal steps to calculate width of a cell in pixels are as

- get default workbook font. HSSFWorkbook.getFontAt(0) should always
the default one.
- get default cell width expressed in 1/256th units. See
- Assuming that the default character is "a" compute the width of a
surrogate string using Java2D classes.

image.width/cell.width is the number of columns to be used in the

I think this logic needs to be buried into POI API somehow. Probably
it will be included in future.

Reply With Quote

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lowongan : Web Programmer / Developer (Jakarta Raya Jakarta Pusat) untung Java 0 10-07-2009 01:22 PM
Optical Computing: special issue - Natural Computing, Springer optical supercomputing C Programming 0 01-16-2009 12:18 PM
Optical Computing: special issue - Natural Computing, Springer optical supercomputing C Programming 0 12-19-2008 12:22 PM
mega pixels, file size, image size, and print size - Adobe Evangelists Frank ess Digital Photography 0 11-14-2006 05:08 PM
Razer Tarantula Gaming Keyboard Review at XYZ Computing at XYZ Computing Silverstrand Front Page News 0 11-01-2006 05:13 PM