Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pictures in single cells - conflict with wrapping text
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pictures in single cells - conflict with wrapping text
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." wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pictures in single cells - conflict with wrapping text
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pictures in single cells - conflict with wrapping text
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." wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
pictures in single cells - conflict with wrapping text
On Dec 14, 5:59*pm, "Peter T" <peter_t@discussions wrote:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
pictures in single cells - conflict with wrapping text
"Rob Y." wrote in message
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
pictures in single cells - conflict with wrapping text
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wrapping Text in Merged Cells | Excel Worksheet Functions | |||
text wrapping in several cells | Excel Discussion (Misc queries) | |||
wrapping text in merged cells | Excel Discussion (Misc queries) | |||
Text wrapping in cells | Excel Discussion (Misc queries) | |||
Combine cells and text wrapping | Excel Programming |