[pyxl] Borders for merged cells
John Yeung
xlwt/examples directory, merged.py and merged0.py seem to work, in
that the borders are applied all the way around the merged cells.
merged1.py, however, generates merged cells that are missing the top
and left borders in what would be the top left cell if the range were
not merged. In my own programs, I've gotten only the top and left
border of the top left cell, with the rest of the merged cell
unbordered.
These were observed using xlwt 0.7.0 and 0.7.1 with Python 2.5.1 on
Windows XP (and xlwt 0.7.1 with iSeries Python 2.3.3), both with merge
and write_merge. I'm opening these files with Excel 2000.
I also tried writing individual cells with the bordered style and then
merging them, with mixed success. (I had a better time of it with
this tactic in 0.7.0 than in 0.7.1.)
Any help would be greatly appreciated.
John Y.
John Machin
Publishing a small script that shows what you actually tried in your own
programs, with comments flagging what worked (if anything) and what
didn't work would be a good idea.
John Yeung
>
> Publishing a small script that shows what you actually tried in your own
> programs, with comments flagging what worked (if anything) and what
> didn't work would be a good idea.
Well, this is merged0.py from xlwt/examples:
### begin merged0.py
#!/usr/bin/env python
# -*- coding: windows-1251 -*-
# Copyright (C) 2005 Kiseliov Roman
from xlwt import *
wb = Workbook()
ws0 = wb.add_sheet('sheet0')
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
borders = Borders()
borders.left = 6
borders.right = 6
borders.top = 6
borders.bottom = 6
style = XFStyle()
style.font = fnt
style.borders = borders
ws0.write_merge(3, 3, 1, 5, 'test1', style)
ws0.write_merge(4, 10, 1, 5, 'test2', style)
ws0.col(1).width = 0x0d00
wb.save('merged0.xls')
### end merged0.py
It seems to work fine in Windows. Replacing the calls to write_merge
above with simply merge:
ws0.merge(3, 3, 1, 5, style)
ws0.merge(4, 10, 1, 5, style)
results in cells B4 and B5 having no styling, but the rest of their
merged cells have the correct border. In merged1.py (same directory,
but on the long side for quoting), merge is called and has the same
border issues, and the pattern is not applied.
If I write the upper left cell first with the text and desired style,
then do the merge, it seems to work; and it seems to behave like
write_merge. For example, in the altered merge0.py:
ws0.write(3, 1, 'test1', style)
ws0.merge(3, 3, 1, 5, style)
ws0.write(4, 1, 'test2', style)
ws0.merge(4, 10, 1, 5, style)
seems to work the same as the original, included merge0.py, with its
write_merge calls.
Unfortunately, the behavior is different on the AS/400, and there are
a lot more variables because its Python is not quite standard and its
xlwt is not quite standard. The upshot is I can apparently rely
simply on write_merge on Windows, but I am really not using xlwt for
anything on Windows. On the AS/400, I haven't found any combination
of writing and merging that will get the borders to work.
My earlier statement about possible differences between 0.7.0 and
0.7.1 probably comes down to the need for cell_overwrite_ok=True for
some of my tests to run in the latter but not the former. I was
switching between platforms and versions so much that I must have
simply gotten confused. I now think any differences in border or
other styling behavior dealing with merging comes from the
nonstandardness of my Python and xlwt on the AS/400 versus what's on
Windows (as one would expect).
Sorry for being so muddled.
John Y.
John Yeung
On Fri, Aug 7, 2009 at 5:04 AM, John Yeung<gallium....@gmail.com> wrote:
> The upshot is I can apparently rely simply on
> write_merge on Windows [...]. On the AS/400,
> I haven't found any combination of writing and
> merging that will get the borders to work.
I discovered that I can get the borders to work on the AS/400 if I
merge the cells first (using the merge or write_merge methods) and
then write the individual constituent cells as needed (using the write
method). For example:
# Put in a file and invoke as a script
import sys
import xlwt
wb = xlwt.Workbook()
ws = wb.add_sheet('Sheet1', cell_overwrite_ok=True)
header_style = xlwt.easyxf(
'font: bold on;'
'align: wrap on, horiz center;'
'borders: left thin, right thin, top thin, bottom thin')
ws.merge(0, 0, 2, 6)
ws.write(0, 2, 'Test 1', header_style)
for col in range(3, 7):
ws.write(0, col, None, header_style)
wb.save(sys.argv[0].replace('.py', '.xls'))
The above works, generating a merged range (C1:G1) with a thin border
around the whole thing.
If I add the following lines before the save,
ws.merge(3, 3, 2, 6)
ws.write(3, 2, 'Test 2', header_style)
ws.row(3).write_blanks(2, 6, header_style)
then a file gets generated, but it crashes Excel when I try to open
it. That is, I get the "Microsoft Excel for Windows has encountered a
problem and needs to close. We are sorry for the inconvenience."
dialog, with available dump.
When I run the above script on Windows (Python 2.5, xlwt 0.7.1),
including the lines for Test 2, the file opens, I get two merged
ranges, each with a complete border, but the Test 2 label doesn't
appear.
I haven't had any crashes using write_merge, but it seems (from my
naive point of view) that something is not quite right with
MulBlankCell.
John Y.