pictures in single cells - conflict with wrapping text

[Rob Y.]

I'm using Apache POI to generate XLS's, and have mastered the 'art' of
sizing pictures with anchors. But I've run up against a brick wall
(or, at least, a catch-22).

I have some columns that want to auto-wrap text. Excel is nice enough
to do this for me, but in the process it changes the row height. The
problem is that if I also have a picture anchored to a cell on that
row, Excel interprets the anchor based on the new row height *after*
it's wrapped the text. This results in the picture being stretched to
the height of the multi-line row. Since I'm counting on Excel to size
the rows, it's kind of silly for Excel to treat the anchor as though I
knew the row size upfront.

This only happens the first time Excel opens the spreadsheet. If I
modify the row after opening the spreadsheet, Excel respects the 'move
with cell but don't resize' option. And of course, if I create a
spreadsheet manually in Excel rather than programmatically, there's no
problem. Excel seems to set the initial anchor to match the picture
size and adjust it as needed when wrapping text causes the row to grow
(is that right, or does Excel convert the anchor to some other
positioning/sizing object internally?).

I can sort of get around this by setting the row height explicitly.
That prevents my pictures from being stretched because it also
prevents Excel from auto-wrapping my text. Bottom line is that I can
either have fixed-size pictures or auto-wrapping text, but not both.
Is this true?

Thanks,
Rob

 

[Peter T]

ActiveSheet.Pictures.Placement = xlMove

This is like selecting all pictures on the sheet, rt-click, format,
properties, Move but don't size with cells.

I'm not sure what you mean about anchors in Excel

Regards,
Peter T

 

[Rob Y.]

ActiveSheet.Pictures.Placement = xlMove

This is like selecting all pictures on the sheet, rt-click, format,
properties, Move but don't size with cells.

I'm not sure what you mean about anchors inExcel

I already have my pictures set to 'move but don't size with cells'.
That much works.

The problem is that the *original* size of the pictures is based on
the original size of the cells, and I don't know how tall the rows are
going to be once Excel performs its auto-wrapping magic on the rest of
the data on the row.

The 'anchor' terminology comes from the Apache POI toolkit I'm using
to create the XLS's - though I think the terminology just reflects the
internal data structures of an XLS. Excel sizes pictures through a
combination of a range of cells and starting and ending offsets within
the cells on the edges of the range. In my case there's just a single
cell.

The problem is that the 'offsets' are defined as a percentage of the
size of the cell (it's even more complicated than that, but
effectively that's how it works). Because I don't know the height of
my cell at the time I'm creating the XLS (because I don't know how
much wrapping will be required by text in other cells on the same
row), I can't compute the ending vertical offset correctly. I just
assume that the row is one line high and had hoped that Excel would
interpret my ending vertical offset based on the initial cell height
before wrapping text on that line. After all, what's the point of
asking Excel to auto-wrap my text if Excel assumes I know how much
wrapping will be required? But the picture sizing mechanism seems to
assume just that.

Note that I'm creating a complete XLS file before Excel ever gets its
hands on it. I'm not using OLE automation to fill a spreadsheet one
operation at a time (in which case, perhaps, Excel would go through
all the internal stuff it has to do to hide the anchor mechanism from
you). If that were the case, then this might not be a problem. But
surely, there are other ways to create XLS's than to automate Excel
itself (like, for example, Apache POI). I'm just hoping that there's
some way to represent what I'm trying to do in the XLS file so that
Excel will size my pictures to a 1-line row height and *then* proceed
to auto-wrap any text on the row. If not, then I just have to choose
between pictures and auto-wrapping and accept that it's not possible
to have both.

 

[Peter T]

I hadn't digested in your first post that in effect the xls is being created
with BIFF8. The makers of Apache POI have done well because until recently,
when MS published the BIFF8 in full under the open source agreement, the
documentation about drawing objects was very thin.

I probably can't answer your question other than to describe how objects on
sheets are positioned and resized, subject their move/resize properties.

Initially Left/Top (distance from top left of sheet) and Width/Height are
the main properties. When the object is created it references two other
cells, TopLeft and BottomRight.

If the object is set to "move" with cells, if the TopLeft cell moves (eg
row/col repositioned or deleted) the object's Top/Left properties will
change accordingly.

Moving the BottomRight cell similarly affects size but it depends -
If the object's bottom or right edge touches the cell's bottom or right
edge, if the bottom-right corner of the BottomRight cell moves the object
will resize.
Otherwise, the object will resize according to any change in the position of
the top-left corner of the BottomRight cell. Hope that all makes sense.


I don't know at what point the object first creates a reference to its
TopLeft and BottomRight cells, particularly when created with BIFF8, maybe
only after the file is first used in Excel and perhaps that's what's giving
you a problem. Not sure if for your purposes it might be worth finding out
if those cells can be returned in BIFF8, as they can via Excel's object
model.

I suppose the obvious question is why not automate Excel, even if only to
tidy things up in the file that was originally created elsewhere.

Regards,
Peter T

 

[Rob Y.]

I hadn't digested in your first post that in effect the xls is being created
with BIFF8. The makers of Apache POI have done well because until recently,
when MS published the BIFF8 in full under the open source agreement, the
documentation about drawing objects was very thin.

I've seen reference to BIFF8 on POI discussion threads, but don't know
what it is. From the context of those posts, I was under the
impression that BIFF was a tool to display your XLS in an XML format.
Are you saying that BIFF8 is one of several file formats that Excel
refers to as .XLS? In any case, yep, that seems to be what POI
implements.

I don't know at what point the object first creates a reference to its
TopLeft and BottomRight cells, particularly when created with BIFF8, maybe
only after the file is first used in Excel and perhaps that's what's giving
you a problem. Not sure if for your purposes it might be worth finding out
if those cells can be returned in BIFF8, as they can via Excel's object
model.

Yep. It seems to be that 'first used in Excel' condition that's the
problem. I did an experiment, though, and rigged up a report that had
pictures in the first column only and wrapping text in the next
column. The pictures were still stretched to the height of the rows
after the text was wrapped, so I don't seem to be able to control that
'first use in Excel' condition. Or, in any case, it's not as simple
as 'the first cell Excel encounters on a row'.

I suppose the obvious question is why not automate Excel, even if only to
tidy things up in the file that was originally created elsewhere.

Well, the file's created on an AIX system, so can't automate Excel
there. And it's produced for download to a browser, so can't really
automate Excel there either. I guess the end-user could do it, but
again, I'm not even sure how I'd automate this. You'd need logic to
figure out which cells are affected (my xls-creating facility is
general purpose - produces any number of different xls layouts), and
then automate resizing the pictures. Best would be a way to insert
the pictures at a fixed size in points or pixels or whatever. But as
you point out, the BIFF8 thingy doesn't seem to allow that.

As it stands, the best compromise I can think of is to just set the
row height to inhibit automatic wrapping whenever I insert a picture
into a cell. The user can then expand the rows to see the wrapping
text. That's easy enough to do by highlighting the entire first
column of the spreadsheet and then hitting Format-Row-Auto Fit.
Excel's nice enough to leave the pictures alone after it's rendered
them the first time, so that'd work out okay.

Thanks for addressing my question. The info about BIFF8 is useful in
understanding things in POI that don't seem to make sense.

Rob

 

[Peter T]

I've seen reference to BIFF8 on POI discussion threads,
but don't know what it is.

BIFF8 defines the binary format or "layout" of an xls file (Excel8-11).
With the documentation to hand you can create your own app to read and write
(from scratch or update) Excel files from/to disk.

To give you an idea following was the best reference available

http://sc.openoffice.org/excelfileformat.pdf

More recently, Spring 2008 I think, MS published an official version but
can't seem to find it (a 300+ page pdf). It's more complete but harder to
follow.

In theory it should be possible to create your pictures in file in the same
state as Excel does after initially opening. If it's really important to you
maybe compare in a Hex editer a copy of the original file after
open/save/close in Excel. There are bound to be some differences but see if
you can relate any to your picture object. BTW ensure the VBE is closed
during the open/save session.

Warning - BIFF8 is not for the faint hearted !

Good luck!

Peter T

 

[Rob Y.]

In theory it should be possible to create your pictures in file in the same

state as Excel does after initially opening.

That won't help, since to know that, you'd need to know how much word
wrapping Excel will do. If I knew that, I wouldn't have a problem in
the first place. It's not hard to correctly size a picture once you
know the true row height. I suppose I could write code to parse out
all my text and figure out where Excel will wrap it - assuming POI
knows the widths of characters, etc, which I'm not sure it does. At
that point, I question whether it's worth it.

I've 'solved' the problem by forcing the row height to a single line
whenever I import an image into a cell. That produces the correct
images. As suspected, the user can then just click on any column
header to highlight all the rows and then go Format - Row - Auto Fit.
Works like a charm. I just wish the 'format - row - auto fit' could
happen automatically without distorting my images. But I'm going in
circles now...

Thanks for hearing me out.
Rob