Inserting borders on a group of cells
noel
I have been trying to insert a border after the cells were already
been filled in this manner:
ws.write(row,col.style=Style.easyxf('border: left thick, top thick'))
The above code is just a sample but in reality I have several ws.write
call in this manner as I am trying to box in a range of cells and have
been doing repeated calls.It wrote the borders fine but the cell
contents of those at the edges (i.e, the cells where the borders
belong to) are replaced with blank cells. I suppose you need to write
the cell contents and the borders at the same time but this is
something that is hard to do in advance because my table grows and
shrinks dynamically with the data I put in there. Moreover, I am
intending to replace my COM calls to excel application with xlrd/xlwt/
xlutil and I want the transition seamless (i.e, I created a helper
class using COM and mimicked that with helper class for xlrd/xlwt/
xlutil. Now, COM allows you to place borders after cells were already
been written) Is there a way to write the cell values first and then,
write the borders later and still retain the cell contents at the
border boundaries? I would think that these information goes to
different attributes of the cell and hence, it is easy enough to avoid
overwriting the attributes which you want retained.
I searched the documentation (it simply says to be extra careful when
you do this but did not give any details) and this forum and found no
answer. Thanks!
John Machin
> Hi!
>
> I have been trying to insert a border after the cells were already
> been filled in this manner:
>
> ws.write(row,col.style=Style.easyxf('border: left thick, top thick'))
Perhaps you mean
ws.write(row, col, style=Style.easyxf('border: left thick, top thick'))
Suggestions: (1) copy/paste, don't type from memory (2) following PEP 8
style by using a space after a comma makes such typos more apparent
Calling easyxf for each cell is rather inefficient. For drawing a border
around a rectangular area, get the 8 styles that you need ONCE each.
> The above code is just a sample but in reality I have several ws.write
> call in this manner as I am trying to box in a range of cells and have
> been doing repeated calls.It wrote the borders fine but the cell
> contents of those at the edges (i.e, the cells where the borders
> belong to) are replaced with blank cells. I suppose you need to write
> the cell contents and the borders at the same time but this is
> something that is hard to do in advance because my table grows and
> shrinks dynamically with the data I put in there.
Do you mean as in you think you have got the last row and format it
accordingly but then something happens to make you change your mind?
Can't you just organise your data in Python structures and write all
your cells at once after the dust settles? What is the maximum number of
rows and columns in a table? Alternatively, you could just save the
border data ...
> Moreover, I am
> intending to replace my COM calls to excel application with xlrd/xlwt/
> xlutil and I want the transition seamless (i.e, I created a helper
> class using COM and mimicked that with helper class for xlrd/xlwt/
> xlutil.
Fortunately/unfortunately depending on one's point of view,
xlrd/xlwt/xlutils were not designed to mimic Excel/COM.
> Now, COM allows you to place borders after cells were already
> been written) Is there a way to write the cell values first and then,
> write the borders later and still retain the cell contents at the
> border boundaries? I would think that these information goes to
> different attributes of the cell and hence, it is easy enough to avoid
> overwriting the attributes which you want retained.
>
> I searched the documentation (it simply says to be extra careful when
> you do this but did not give any details) and this forum and found no
> answer. Thanks!
xlwt is open-source, and pre-discussed patches are welcome, especially
for "easy enough" enhancements.
noel
I actually store the styles ahead of time (for the 8 borders) like
this:
lt = Style.easyxf('border: left thick, top thick')
and then, makes this call later:
ws.write(row, col, style=lt)
You mentioned below that:
'Alternatively, you could just save the
> border data ...'. What do you mean here and how does that work?
I have further comments below ------------------> but they are not as
important. Thanks again!
but only those which I frequently use. Except for the borders, I have
them working identically as follows:
from dxls import dxls # for the Excel/COM, my own helper class
from sxls import sxls # for the xlrd/xlwt, my own helper class
wb = dxls(filename) # using COM helper class
or
wb = sxls(filename) # if I want to use xlrd/xlwt
# now, from here on, everything are now identical
worksheet = wb.ws('sheetname') # you could also use integer index
worksheet.write(row,col,value) # write to a cell; in xlrd/xlwt, it
actually writes to a temporary file
# created using
xlutil.copy2. When you close the wb, the
# temporary file
overwrites the xls file that you opened giving
# you the
illusion that you could read and write to the same xls
# file using
xlrd/xlwt. Not perfect but serves my purpose.
worksheet.read(row,col) # for xlrd/xlwt, wb is handled
using xlrd. The approach is, use
# xlwt if xls
file do not exist, and use xlrd + xlutil.copy if it exists
# So, if file
exists, you are using xlrd for reads, and a second
# wb is created
using xlutil.copy for your writes
worksheet.write(row,col,'green') # in case you want background color
in that
wb.close()
I have added some more functionality not discussed here and if anybody
is interested, I send them my code. I am still a python newbie, so,
don't expect much.
>
> > Now, COM allows you to place borders after cells were already
> > been written) Is there a way to write the cell values first and then,
> > write the borders later and still retain the cell contents at the
> > border boundaries? I would think that these information goes to
> > different attributes of the cell and hence, it is easy enough to avoid
> > overwriting the attributes which you want retained.
>
> > I searched the documentation (it simply says to be extra careful when
> > you do this but did not give any details) and this forum and found no
> > answer. Thanks!
>
> xlwt is open-source, and pre-discussed patches are welcome, especially
> for "easy enough" enhancements.
of how excel stores the data internally is flawed.
John Machin
>
> You mentioned below that:
>
> 'Alternatively, you could just save the
>> border data ...'. What do you mean here and how does that work?
If you have a table with C Columns and R rows, you have 2 * (R + C - 1)
cells that need borders re-styled. Save their values, and write them at
the end.
>> Can't you just organise your data in Python structures and write all
>> your cells at once after the dust settles? What is the maximum number of
>> rows and columns in a table?
... you didn't answer the above question ...
>>> Now, COM allows you to place borders after cells were already
>>> been written) Is there a way to write the cell values first and then,
>>> write the borders later and still retain the cell contents at the
>>> border boundaries? I would think that these information goes to
>>> different attributes of the cell and hence, it is easy enough to avoid
>>> overwriting the attributes which you want retained.
>>> I searched the documentation (it simply says to be extra careful when
>>> you do this but did not give any details) and this forum and found no
>>> answer. Thanks!
>> xlwt is open-source, and pre-discussed patches are welcome, especially
>> for "easy enough" enhancements.
>
> ----------------> sorry about this comment! I guess my understanding
> of how excel stores the data internally is flawed.
You guess wrongly. I meant that the structures weren't very complicated,
and writing a method or 2 to do what you want would be easy enough.
I've uploaded a file change_style.py to the google-group's file area.
It contains two prototype methods for the Worksheet class, called like this:
sheet.change_style(row_index, col_index, new_style)
sheet.set_style(row_index, col_index, new_style)
and corresponding Row methods plus a demonstration of them in action.
Each method will change the style of an existing (non-empty) cell to
new_style.
If the target cell is empty, set_style() will create a blank cell with
style=new_style, and change_style() will raise an exception. I would
envisage change_style() being used where you expected to have filled in
all the border cells and would like to be told if this was not true.
set_style() might be useful when you had a sparse crosstab i.e. lots of
empty cells in the table.
I'd be interested in feedback ... there must be others out there besides
"noel" who have needed to make borders on variable-size tables.
Cheers,
John
John Yeung
> If you have a table with C Columns and R rows, you
> have 2 * (R + C - 1) cells that need borders re-styled.
Would that not be 2 * (R + C - 2) cells? Not that it matters terribly
how many cells, but rather which cells; and whether you got the right
cells will be quite evident in the resulting Excel file.
> I've uploaded a file change_style.py to the google-group's file area.
>
> I'd be interested in feedback ... there must be others out there besides
> "noel" who have needed to make borders on variable-size tables.
I've tried the new methods and they look like they would be handy. I
think they would be welcomed by many xlwt users, and not just for
putting borders around stuff. I also think the way you've proposed
exposing them (in the demonstration section) is good the way it is.
In other words, I'm happy with the naming, order of parameters,
semantics, etc.
John Y.
John Machin
> On Wed, Jun 16, 2010 at 9:02 AM, John Machin <sjma...@lexicon.net> wrote:
>> If you have a table with C Columns and R rows, you
>> have 2 * (R + C - 1) cells that need borders re-styled.
>
> Would that not be 2 * (R + C - 2) cells?
Correct; I shouldn't try to do algebra in my head :-)
> Not that it matters terribly
> how many cells, but rather which cells; and whether you got the right
> cells will be quite evident in the resulting Excel file.
True.
>
>> I've uploaded a file change_style.py to the google-group's file area.
>>
>> I'd be interested in feedback ... there must be others out there besides
>> "noel" who have needed to make borders on variable-size tables.
>
> I've tried the new methods and they look like they would be handy. I
> think they would be welcomed by many xlwt users, and not just for
> putting borders around stuff. I also think the way you've proposed
> exposing them (in the demonstration section) is good the way it is.
> In other words, I'm happy with the naming, order of parameters,
> semantics, etc.
Thanks for the feedback.
Doing Worksheet.set_style() on a large rectangular area could be sped up
considerably by a method which pulled getting the Row object out of the
inner loop and pulled getting the xf_index out of both loops -- anybody
with any interest in this? Note that order of the 4 row/column indexes
and semantics (last or last+1) of the 2 "high" row/column indexes could
lead to interesting debate ;-)
Cheers,
John
Hans Maulwurf
I've uploaded a file change_style.py to the google-group's file area.
It contains two prototype methods for the Worksheet class, called like this:sheet.change_style(row_index, col_index, new_style)
sheet.set_style(row_index, col_index, new_style)
Chris Withers
I'll try and work it into xlutils somewhere...
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk