Wrapping not working on merged cells in Excel 2003
Michael Schurter
I'm wrapping 2 groups of cells, A & B lets say.
A wraps & resizes the row vertically correctly.
B wraps but does *not* resize the row vertically.
The main difference is that the B cells are also merged while the A
cell is fairly simple.
#Common code:
from xlwt import easyxf, Alignment, Font, Formatting, Workbook, XFStyle
w = Workbook('utf-8')
ws = w.add_sheet('whatever')
#Code for A cell:
right_style = easyxf('align: wrap on, vert top, horiz right;')
ws.write(4, 1, "WORKS", right_style)
#Code for B cells:
borders = Formatting.Borders()
borders.top = Formatting.Borders.THIN
borders.right = Formatting.Borders.THIN
borders.bottom = Formatting.Borders.THIN
borders.left = Formatting.Borders.THIN
bold_borders_style = easyxf(
'align: wrap on, vert top, horiz center; font: bold on;')
bold_borders_style.borders = borders
demostr = "BROKEN"
col = 0
columns = 5
ws.write_merge(2, 2, col, col+(columns - 1),
demostr, bold_borders_style)
w.save('TEST.xls')
# End of sample code
Hopefully I included all of the relevant code.
Thanks in advance for any help.
Michael Schurter (@schmichael on twitter & IRC)
Michael Schurter
Schurter<michael....@gmail.com> wrote:
> Hi John, thanks for the great library.
>
> I'm wrapping 2 groups of cells, A & B lets say.
>
> A wraps & resizes the row vertically correctly.
>
> B wraps but does *not* resize the row vertically.
I should have mentioned that this is the behavior in Excel 2003.
Wrapping & resizing works as expected in OpenOffice.org.
John Machin
> On Mon, Jun 8, 2009 at 5:57 PM, Michael
> Schurter<michael....@gmail.com> wrote:
>> Hi John, thanks for the great library.
>>
>> I'm wrapping 2 groups of cells, A & B lets say.
>>
>> A wraps & resizes the row vertically correctly.
>>
>> B wraps but does *not* resize the row vertically.
>
> I should have mentioned that this is the behavior in Excel 2003.
You did. Look at the subject of your message.
> Wrapping & resizing works as expected in OpenOffice.org.
What does "as expected" mean to you?
The strings "BROKEN" and "WORKS" fit in the default column width. No
wrapping or resizing is necessary or expected.
Neither wrapping nor resizing is apparent when I look at the output XLS
with any of Excel 2003, Excel 2007, OOo Calc 3.x, and Gnumeric.
Michael Schurter
> On 9/06/2009 11:11 AM, Michael Schurter wrote:
>> On Mon, Jun 8, 2009 at 5:57 PM, Michael
>> Schurter<michael....@gmail.com> wrote:
>> Wrapping & resizing works as expected in OpenOffice.org.
>
> What does "as expected" mean to you?
The row height is automatically adjusted to fit the text (wrapped or not).
> The strings "BROKEN" and "WORKS" fit in the default column width. No
> wrapping or resizing is necessary or expected.
Sorry those were poor examples. The real strings are quite long (at
least 2 lines usually in default sized columns). Feel free to use any
long string such as:
BROKEN BROKEN BROKEN BROKEN BROKEN BROKEN BROKEN BROKEN BROKEN BROKEN
BROKEN BROKEN
> Neither wrapping nor resizing is apparent when I look at the output XLS
> with any of Excel 2003, Excel 2007, OOo Calc 3.x, and Gnumeric.
Well obviously if the string isn't long enough no wrapping will occur.
:-) Given you use long enough strings (see above), the behavior I
expect & hope to replicate in Excel 2003 & 2007 (untested on my end so
far) is for:
1. the string to be wrapped (that is spanning multiple lines
instead of getting cut off at column boundries)
2. the entire row's height to be automatically adjusted to fit the
text whether its unwrapped (1 line) or wrapped (multiple lines)
If the only way to accomplish #2 is by setting the row height manually
by somehow calculating approximately how many lines the label will
take, then I'll pursue that. Obviously the ideal situation is what
OOo Calc 3 did: wrap (#1) & automatically resize (#2).
Thanks for the quick response!
John Machin
> On Mon, Jun 8, 2009 at 7:12 PM, John Machin<sjma...@lexicon.net> wrote:
>> On 9/06/2009 11:11 AM, Michael Schurter wrote:
>>> On Mon, Jun 8, 2009 at 5:57 PM, Michael
>>> Schurter<michael....@gmail.com> wrote:
>>> Wrapping & resizing works as expected in OpenOffice.org.
>> What does "as expected" mean to you?
>
> The row height is automatically adjusted to fit the text (wrapped or not).
>
>> The strings "BROKEN" and "WORKS" fit in the default column width. No
>> wrapping or resizing is necessary or expected.
>
> Sorry those were poor examples.
s/poor/not/
> Given you use long enough strings (see above), the behavior I
> expect & hope to replicate in Excel 2003 & 2007 (untested on my end so
> far) is for:
Excel 2007 behaves the same as Excel 2003.
It appears that you haven't even tested that you can achieve the desired
result manually. AFAICT, no matter whether one types the long text in
first then sets wrap and merge or one sets the formatting first then
types the long text in, Excel will not automatically resize the row.
Even selecting the row and doing Format/Row/Autofit doesn't work.
> 1. the string to be wrapped (that is spanning multiple lines
> instead of getting cut off at column boundries)
> 2. the entire row's height to be automatically adjusted to fit the
> text whether its unwrapped (1 line) or wrapped (multiple lines)
> If the only way to accomplish #2 is by setting the row height manually
> by somehow calculating approximately how many lines the label will
> take, then I'll pursue that.
Sorry that your googler is broken. First hit from my_googler("merged
cells row height") was
http://excel.tips.net/Pages/T003207_Automatic_Row_Height_For_Merged_Cells_with_Text_Wrap.html
It suggests an interesting hack which may be easier than calculating the
row height.
> Obviously the ideal situation is what
> OOo Calc 3 did: wrap (#1) & automatically resize (#2).
Consider using OOo Calc. Consider investing some of the savings in
supporting FOSS.