[pyxl] border problem by merged cells

[Twilo]

It seems I've a problem with the examples merged.py, merged0.py and
merged1.py. Office 2007 and OpenOffice 3.1 will not show the border
correctly.

merged.py screenshots:
http://groups.google.com/group/python-excel/web/merged_office2007.png
http://groups.google.com/group/python-excel/web/merged_openoffice311.png

Thanks in advance.

--
You received this message because you are subscribed to the Google Groups "python-excel" group.
To post to this group, send an email to python...@googlegroups.com.
To unsubscribe from this group, send email to python-excel...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/python-excel?hl=en-GB.

[John Machin]

On 27/04/2010 10:00 PM, Twilo wrote:
> It seems I've a problem with the examples merged.py, merged0.py and
> merged1.py. Office 2007 and OpenOffice 3.1 will not show the border
> correctly.

You're not wrong. You have a problem.

I created a merged.xls with both the latest xlwt and the 2005-08-11
version of pyExcelerator (you didn't say what package you were using,
nor what version) and viewed them with not only Excel 2007 and OO 3.1
but also with Excel 2003, Excel Viewer, and Gnumeric ... no border problems.

What version of what XLS-writer?
What OS?

Please send your merged.xls for inspection.

[barb...@voila.fr]

> Message du 27/04/10 à 17h05
> De : "John Machin" <sjma...@lexicon.net>
> A : python...@googlegroups.com
> Copie à :
> Objet : Re: [pyxl] border problem by merged cells

>
>
> On 27/04/2010 10:00 PM, Twilo wrote:
> > It seems I've a problem with the examples merged.py, merged0.py and
> > merged1.py. Office 2007 and OpenOffice 3.1 will not show the border
> > correctly.
>
> You're not wrong. You have a problem.
>
> I created a merged.xls with both the latest xlwt and the 2005-08-11
> version of pyExcelerator (you didn't say what package you were using,
> nor what version) and viewed them with not only Excel 2007 and OO 3.1
> but also with Excel 2003, Excel Viewer, and Gnumeric ... no border problems.
>
> What version of what XLS-writer?
> What OS?
>
> Please send your merged.xls for inspection.

Hi, I had the same issue with the 'writeMerge' method under SunOs (xlwt 0.7.2, python 2.4.4)

I used this workaround (can't remember where I found it):

def writeMerge(xlwtSheet, r1, r2, c1, c2, label, style):
overwriteOk = sheet._cell_overwrite_ok
sheet._cell_overwrite_ok = True
# first merging
sheet.merge(r1, r2, c1, c2)
# then writing in first cell
sheet.write(r1, c1, label, style)
for row in range(r1, r2 + 1):
for col in range(c1, c2 + 1):
if not (row == r1 and col == c1):
#writing empty values in other cells
sheet.write(row, col, None, style)
sheet._cell_overwrite_ok = overwriteOk

Hope this helps!

>
> --
> You received this message because you are subscribed to the Google Groups "python-excel" group.
> To post to this group, send an email to python...@googlegroups.com.
> To unsubscribe from this group, send email to python-excel...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/python-excel?hl=en-GB.
>
>
>

____________________________________________________

 Nouveau ! Partagez vos envies et découvrez les bons plans voyages des internautes sur http://forums.voila.fr/

[John Machin]

On 28/04/2010 1:19 AM, barb...@voila.fr wrote:
>> Message du 27/04/10 à 17h05
>> De : "John Machin" <sjma...@lexicon.net>
>> A : python...@googlegroups.com
>> Copie à :
>> Objet : Re: [pyxl] border problem by merged cells
>>
>>
>> On 27/04/2010 10:00 PM, Twilo wrote:
>>> It seems I've a problem with the examples merged.py, merged0.py and
>>> merged1.py. Office 2007 and OpenOffice 3.1 will not show the border
>>> correctly.

>

> Hi, I had the same issue with the 'writeMerge' method under SunOs (xlwt 0.7.2, python 2.4.4)

write_merge, perhaps?

Is the box running SunOs bigendian?
I.e. what does
...../python -c "import sys; print sys.byteorder"
print?

>
> I used this workaround (can't remember where I found it):
>
> def writeMerge(xlwtSheet, r1, r2, c1, c2, label, style):
> overwriteOk = sheet._cell_overwrite_ok
> sheet._cell_overwrite_ok = True
> # first merging
> sheet.merge(r1, r2, c1, c2)

This writes a "blank" (formatting but no data) in every cell in the
rectangle except (r1, c1).

The following code does (laboriously!) what the standard write_merge
method does.

> # then writing in first cell
> sheet.write(r1, c1, label, style)
> for row in range(r1, r2 + 1):
> for col in range(c1, c2 + 1):
> if not (row == r1 and col == c1):
> #writing empty values in other cells
> sheet.write(row, col, None, style)
> sheet._cell_overwrite_ok = overwriteOk
>
> Hope this helps!

It would help if you could reproduce the problem using the merged.py in
the examples folder, and put up a copy (renamed to indicate the source
e.g. 'merged_barbuse_sunos_244_072.xls') of the output merged.xls in
this group's file area, and say what the observed problem was with each
of the three sheets in the file.

Cheers,
John

[Twilo]

Hello

On 27 Apr., 17:05, John Machin <sjmac...@lexicon.net> wrote:
> I created a merged.xls with both the latest xlwt and the 2005-08-11
> version of pyExcelerator (you didn't say what package you were using,
> nor what version) and viewed them with not only Excel 2007 and OO 3.1
> but also with Excel 2003, Excel Viewer, and Gnumeric ... no border problems.

xlwt 0.7.2
Windows 2003 and Debian sid
jython 2.5.1

I've the problem with jython (standalone)
java -classpath jython.jar org.python.util.jython merged.py
http://groups.google.com/group/python-excel/web/merged.xls

With "python2.5 merged.py" or "python2.6 merged.py" will show the
border correctly.

[Twilo]

Hello

On 27 Apr., 17:19, "barbo...@voila.fr" <barbo...@voila.fr> wrote:
> Hi, I had the same issue with the 'writeMerge' method under SunOs (xlwt 0.7.2, python 2.4.4)
>
> I used this workaround (can't remember where I found it):
>
> def writeMerge(xlwtSheet, r1, r2, c1, c2, label, style):
>     overwriteOk = sheet._cell_overwrite_ok
>     sheet._cell_overwrite_ok = True
>     # first merging
>     sheet.merge(r1, r2, c1, c2)
>     # then writing in first cell
>     sheet.write(r1, c1, label, style)
>     for row in range(r1, r2 + 1):
>         for col in range(c1, c2 + 1):
>             if not (row == r1 and col == c1):
>                 #writing empty values in other cells
>                 sheet.write(row, col, None, style)
>     sheet._cell_overwrite_ok = overwriteOk
>
> Hope this helps!

super!

Office 2007 and OpenOffice 3.1 will show the border correctly.

http://groups.google.com/group/python-excel/web/merged_with_writeMerge.py
http://groups.google.com/group/python-excel/web/merged_with_writeMerge.xls

[John Yeung]

On Tue, Apr 27, 2010 at 12:52 PM, John Machin <sjma...@lexicon.net> wrote:
> On 28/04/2010 1:19 AM, barb...@voila.fr wrote:
>> I used this workaround (can't remember where I found it):
>

> The following code does (laboriously!) what the standard write_merge method
> does.
>
>>    # then writing in first cell
>>    sheet.write(r1, c1, label, style)
>>    for row in range(r1, r2 + 1):
>>        for col in range(c1, c2 + 1):
>>            if not (row == r1 and col == c1):
>>                #writing empty values in other cells
>>                sheet.write(row, col, None, style)
>>    sheet._cell_overwrite_ok = overwriteOk

It may laboriously do what the standard write_merge is *supposed* to
do, but I have also had occasion to use a similar workaround (only
writing cells along the outer edge), which worked better for me than
the standard write_merge. Granted, my needs include being able to run
on an iSeries, and I use a tweaked version of xlwt 0.7.1.

But, for gits and shiggles, I just threw a clean[1] xlwt 0.7.2 onto my
Windows XP machine running Python 2.5.1, and promptly got a nice
merged.xls and merged0.xls, but a problematic merged1.xls (all opened
with Excel 2000). The upper left cell in each merged range fails to
have a border.

I notice that merged.py and merged0.py use the write_merge method and
seemed to work fine; while merged1.py uses the merge method and had a
problem. I also notice that comments in the source code for the merge
method say to avoid its use, and to use write_merge instead.
Evidently, that is sound advice on my PC.

But the standard write_merge didn't work with my iSeries tweaks (it's
been so long since I worked on it that I don't remember what the
symptoms were; possibly that all *but* the upper left cell were
missing the border?). Maybe my tweaks are not compatible with
MulBlankCell (which seems to be the less laborious way to write
blanks?).

In any case, using the standard, broken merge method and then
splatting individual cells over the top of the resulting area has
reliably worked for me both on the PC and iSeries, so I no longer use
the standard write_merge at all.

> It would help if you could reproduce the problem using the merged.py in the
> examples folder, and put up a copy (renamed to indicate the source e.g.
> 'merged_barbuse_sunos_244_072.xls') of the output merged.xls in this group's
> file area, and say what the observed problem was with each of the three
> sheets in the file.

If you would like me to do this for my examples as well, I can when I get home.

John Y.


[1] It's clean to the extent that I just plopped the naked xlwt Python
source into site-packages. Maybe this is not the intended way to
"install" it, but the fact that the extract-and-copy method works for
xlwt is one reason I am quite happy with the package.

[John Machin]

The scoop is that there is a bug in xlwt (inherited from pyExcelerator,
and not noticed by me at two opportunities) that affects all bigendian
platforms (which presumably includes Jython, SunOS, and IBM iSeries).

BIFFRecords.py:1456 (in MulBlankRecord.__init__) reads

self._rec_data = pack('%dH' % blanks_count, *([xf_index]*blanks_count))
but should be
self._rec_data = pack('<%dH' % blanks_count, *([xf_index]*blanks_count))

The result on a bigendian platform is that an XF index which should be
e.g. 17 ('\x11\x00') is written as '\x00\x11' and thus read by Excel etc
as 0x1100 (decimal 4352). Excel appears to silently ignore the fact that
XF 4352 is grossly out of range, and use the default omnipresent XF 0
instead. XF 0 doesn't have borders, hence the observed results.

The one-keystroke fix will be in SVN RSN.

The second missed opportunity was some time ago when you wrote about
various problems that you were having -- unfortunately after reading
about your non-standard iSeries Python, your hacked-up xlwt, your
confession of being muddled, and that you believed that all of your
problems were due to the Python and xlwt being non-standard, I must have
stopped reading before the important bit: "it seems (from my naive point
of view) that something is not quite right with MulBlankCell". Thank you
for trying to alert me then and now.

> In any case, using the standard, broken merge method and then
> splatting individual cells over the top of the resulting area has
> reliably worked for me both on the PC and iSeries, so I no longer use
> the standard write_merge at all.
>
>> It would help if you could reproduce the problem using the merged.py in the
>> examples folder, and put up a copy (renamed to indicate the source e.g.
>> 'merged_barbuse_sunos_244_072.xls') of the output merged.xls in this group's
>> file area, and say what the observed problem was with each of the three
>> sheets in the file.
>
> If you would like me to do this for my examples as well, I can when I get home.

It would be great if you could
(a) report what you get on your iSeries Python from:
(1) sys.byteorder # if implemented
(2) repr(struct.pack('H', 258)) # bigendian -> '\x01\x02'
(b) make the "<" fix and try it out

Cheers and thanks again,
John

[John Machin]

On 28/04/2010 5:14 AM, Twilo wrote:
>
> xlwt 0.7.2
> Windows 2003 and Debian sid
> jython 2.5.1
>
> I've the problem with jython (standalone)
> java -classpath jython.jar org.python.util.jython merged.py
> http://groups.google.com/group/python-excel/web/merged.xls

Thanks for the file; it was what I needed to inspect to find the bug.

> With "python2.5 merged.py" or "python2.6 merged.py" will show the
> border correctly.

Cheers,
John

[John Yeung]

On Tue, Apr 27, 2010 at 9:15 PM, John Machin <sjma...@lexicon.net> wrote:

> It would be great if you could
> (a) report what you get on your iSeries Python from:
> (1) sys.byteorder # if implemented

'big'

> (2) repr(struct.pack('H', 258)) # bigendian -> '\x01\x02'

"'\\x01\\x02'"

> (b) make the "<" fix and try it out

Kind of swamped right now, will give it a go at next opportunity. But
you're right that the iSeries is big-endian.

John Y.

[Jeannot]

On 27 avr, 18:52, John Machin <sjmac...@lexicon.net> wrote:

> On 28/04/2010 1:19 AM, barbo...@voila.fr wrote:
>
> >> Message du 27/04/10 à 17h05

> >> De : "John Machin" <sjmac...@lexicon.net>

> >> A : python...@googlegroups.com
> >> Copie à :
> >> Objet : Re: [pyxl] border problem by merged cells
>
> >> On 27/04/2010 10:00 PM, Twilo wrote:
> >>> It seems I've a problem with the examples merged.py, merged0.py and
> >>> merged1.py. Office 2007 and OpenOffice 3.1 will not show the border
> >>> correctly.
>
> > Hi, I had the same issue with the 'writeMerge' method under SunOs (xlwt 0.7.2, python 2.4.4)
>
> write_merge, perhaps?

yes

>
> Is the box running SunOs bigendian?
> I.e. what does
>      ...../python -c "import sys; print sys.byteorder"
> print?

'big'

>
>
>
> > I used this workaround (can't remember where I found it):
>
> > def writeMerge(xlwtSheet, r1, r2, c1, c2, label, style):
> >     overwriteOk = sheet._cell_overwrite_ok
> >     sheet._cell_overwrite_ok = True
> >     # first merging
> >     sheet.merge(r1, r2, c1, c2)
>
> This writes a "blank" (formatting but no data) in every cell in the
> rectangle except (r1, c1).
>
> The following code does (laboriously!) what the standard write_merge
> method does.

no comment

>
> >     # then writing in first cell
> >     sheet.write(r1, c1, label, style)
> >     for row in range(r1, r2 + 1):
> >         for col in range(c1, c2 + 1):
> >             if not (row == r1 and col == c1):
> >                 #writing empty values in other cells
> >                 sheet.write(row, col, None, style)
> >     sheet._cell_overwrite_ok = overwriteOk
>
> > Hope this helps!
>
> It would help if you could reproduce the problem using the merged.py in
> the examples folder, and put up a copy (renamed to indicate the source
> e.g. 'merged_barbuse_sunos_244_072.xls') of the output merged.xls in
> this group's file area, and say what the observed problem was with each
> of the three sheets in the file.

merged_SunOs_py244_xlwt072.xls
merged1_SunOs_py244_xlwt072.xls
merged0_SunOs_py244_xlwt072.xls

[John Machin]

On 28/04/2010 6:20 PM, Jeannot wrote:
>
> On 27 avr, 18:52, John Machin <sjmac...@lexicon.net> wrote:
>> On 28/04/2010 1:19 AM, barbo...@voila.fr wrote:
>>

>>> Hi, I had the same issue with the 'writeMerge' method under SunOs (xlwt 0.7.2, python 2.4.4)
>> write_merge, perhaps?
>
> yes
>
>> Is the box running SunOs bigendian?
>> I.e. what does
>> ...../python -c "import sys; print sys.byteorder"
>> print?
>
> 'big'
>

>> It would help if you could reproduce the problem using the merged.py in
>> the examples folder, and put up a copy (renamed to indicate the source
>> e.g. 'merged_barbuse_sunos_244_072.xls') of the output merged.xls in
>> this group's file area, and say what the observed problem was with each
>> of the three sheets in the file.
>
> merged_SunOs_py244_xlwt072.xls

The merged_* file has the same symptoms as twilo's file i.e. XF indexes
in MULBLANK records are bigendian instead of little-endian.

[Jeannot]

On 28 avr, 07:34, John Yeung <gallium.arsen...@gmail.com> wrote:

> On Tue, Apr 27, 2010 at 9:15 PM, John Machin <sjmac...@lexicon.net> wrote:
> > It would be great if you could
> > (a) report what you get on your iSeries Python from:
> > (1) sys.byteorder # if implemented
>
> 'big'
>
> > (2) repr(struct.pack('H', 258)) # bigendian -> '\x01\x02'
>
> "'\\x01\\x02'"
>
> > (b) make the "<" fix and try it out
>
> Kind of swamped right now, will give it a go at next opportunity.  But
> you're right that the iSeries is big-endian.
>

The "<" fix seems to work fine.
see:

merged_SunOs_py244_xlwt072_fixed.xls
merged1_SunOs_py244_xlwt072_fixed.xls
merged0_SunOs_py244_xlwt072_fixed.xls

[John Yeung]

On Wed, Apr 28, 2010 at 8:31 AM, Jeannot <barb...@voila.fr> wrote:
> On 28 avr, 07:34, John Yeung <gallium.arsen...@gmail.com> wrote:
>> On Tue, Apr 27, 2010 at 9:15 PM, John Machin <sjmac...@lexicon.net> wrote:
>> > (b) make the "<" fix and try it out
>

> The "<" fix seems to work fine.

It also works here on our iSeries.

John Y.