Jakarta POI: Computing anchor size for easy image insertion?

Discussion in 'Java' started by grimborg@gmail.com, Feb 12, 2007.

  1. Guest

    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)/48);
    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.)
    , Feb 12, 2007
    1. Advertisements

  2. Guest

    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

    - 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.
    , Feb 20, 2007
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Joe C

    Jakarta POI HSSF

    Joe C, Jul 26, 2004, in forum: Java
    Shane Mingins
    Jul 27, 2004
  2. atransiciel

    Jakarta POI API and OpenOffice

    atransiciel, Aug 3, 2004, in forum: Java
    Aug 3, 2004
  3. TheAmitha
    Jan 21, 2005
  4. Replies:
    Feb 16, 2005
  5. werrt

    Jakarta POI read word

    werrt, Oct 21, 2005, in forum: Java
    Roedy Green
    Oct 22, 2005

Share This Page