Jakarta POI: Computing anchor size for easy image insertion?

[grimborg]

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

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)/48);
if(imgWidth+colWidth<=d.getAmplada()) {
imgWidth +=colWidth;
} else {
col2--;
x2=(short)(d.getWidth()-imgWidth);
imgWidth=d.getWidth();
}
}
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 {
row2--;
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.)

 

[grimborg]

I found an answer: http://article.gmane.org/gmane.comp.jakarta.poi.user/8924

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

- get default workbook font. HSSFWorkbook.getFontAt(0) should always
return
the default one.
- get default cell width expressed in 1/256th units. See
HSSFSheet.getColumnWidth
- 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
anchor.

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