xlutils.filter and formatting
Ben Gerdemann
certain cells. This works fine with xlutils.copy except that the
replaced cells loose their original formatting. An earlier post here
http://groups.google.com/group/python-excel/browse_thread/thread/6a413704a134f0dd/f5387ad7a95ad9b5?lnk=gst&q=xlutils+copy+formatting#f5387ad7a95ad9b5
suggested a workaround of recreating the styles manually when
overwriting the new values.
The poster also hints that using xlutils.filter might be another
solution. I've read the documentation for xlutils.filter, but I don't
see any examples of replacing cell values. Is this even possible and
if so, will it solve my formatting problem?
Thanks for any and all help!
Cheers,
Ben
Chris Withers
> I am trying to copy a sheet of my workbook replacing the values of
> certain cells. This works fine with xlutils.copy except that the
> replaced cells loose their original formatting.
No they don't.
It just means you didn't do:
wb = open_workbook('whatever.xls',formatting=True)
...either than or the formatting lost isn't supported by either xlrd or
xlwt.
cheers,
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk
Ben Gerdemann
> Ben Gerdemann wrote:
> > I am trying to copy a sheet of my workbook replacing the values of
> > certain cells. This works fine with xlutils.copy except that the
> > replaced cells loose their original formatting.
>
> No they don't.
> It just means you didn't do:
>
> wb = open_workbook('whatever.xls',formatting=True)
>
> ...either than or the formatting lost isn't supported by either xlrd or
> xlwt.
this code?
rb = xlrd.open_workbook(template, formatting_info=True)
wb = copy(rb)
ws = wb.get_sheet(0)
ws.write(row_index,col_index,new_value)
Examples of the formatting I am losing are: bolded text, borders and
centering.
I also forgot to mention that I am using xlutils 1.3.2 as newer
version don't work with Python 2.4.
Cheers,
Ben
Ben Gerdemann
I replace. The other cells keep their formatting in the copied sheet.
I'm only trying to find out if this is the expected behavior, or if
I'm doing something wrong.
Cheers,
Ben
John Machin
> Just to be clear, I'm only losing the formatting on cells whose values
> I replace. The other cells keep their formatting in the copied sheet.
> I'm only trying to find out if this is the expected behavior, or if
> I'm doing something wrong.
This is the expected behaviour. The 4th arg of Worksheet.write is a
Style object, and the default is a plain vanilla boring style. There is
currently no mechanism when overwriting cells to specify "data only".
This could be achieved using a dummy arg e.g. ws.write(rowx, colx, "new
data", style=Style.SAME_STYLE) which would cause the existing style to
be used if overwriting a cell, otherwise the vanilla style). Comments
please.
John Yeung
> There is currently no mechanism when overwriting
> cells to specify "data only".
>
> This could be achieved using a dummy arg e.g.
> ws.write(rowx, colx, "new data", style=Style.SAME_STYLE)
> which would cause the existing style to be used if
> overwriting a cell, otherwise the vanilla style).
> Comments please.
I think adding some mechanism for this would be highly desirable. I
am very confident that it would get a lot of use. (From users other
than Ben, even! ;)
I am only wondering if I would rather use the spelling you've proposed
above, or perhaps a new keyword argument, or even a separate data-only
method. Hmm. I think the dummy arg idea is best, and
Style.SAME_STYLE is a fine spelling, though maybe other people will
chime in. Ultimately, as long as it's documented, whatever you pick
will be easy enough to use, and will get used.
John Y.
Chris Withers
> This could be achieved using a dummy arg e.g. ws.write(rowx, colx, "new
> data", style=Style.SAME_STYLE) which would cause the existing style to
> be used if overwriting a cell, otherwise the vanilla style). Comments
> please.
My view is that the 3rd arg to write should be None by default, meaning
"leave my fscking style alone" ;-)
I don't even think we'd need to do anything beyond just changing the
default arg. A worst, we'd just need to make sure "the default" style is
used when we serialize if no style had ended up specified.
John Machin
> John Machin wrote:
>> This could be achieved using a dummy arg e.g. ws.write(rowx, colx,
>> "new data", style=Style.SAME_STYLE) which would cause the existing
>> style to be used if overwriting a cell, otherwise the vanilla style).
>> Comments please.
>
> My view is that the 3rd arg to write should be None by default, meaning
> "leave my fscking style alone" ;-)
The 3rd (not counting self) arg is a reference to the
text/number/whatever that you want to write. None as a 3rd arg is
already in use; it means write a blank (no data) cell with the style
given by the 4th arg.
The default 4th (style) arg is as I said a vanilla style and has always
been like that and we shouldn't change it.
> I don't even think we'd need to do anything beyond just changing the
> default arg. A worst, we'd just need to make sure "the default" style is
> used when we serialize if no style had ended up specified.
A better idea would be to leave the default style arg as-is, and give an
explicit None arg the special meaning of "leave the style alone".
However I think that SAME_STYLE or EXISTING_STYLE is even better ...
reduces the likelihood of a code reviewer needing to RTfsckingM to
understand what's going on.
Brent Marshall
> However I think that SAME_STYLE or EXISTING_STYLE is even better ... reduces
> the likelihood of a code reviewer needing to RTfsckingM to understand what's
> going on.
In that light, my instinctive reaction is that EXISTING_STYLE would,
of those two, be the better choice. I can imagine someone reading
SAME_STYLE and thinking, "Same as what?"
Brent
John Machin
> i vote for default to be the current style instead of no style i.e. to
> not change anything unless asked :)
1. There is no such thing as "no style".
2. The current default behaviour is NOT a bug and will NOT be changed.
Sybren A. Stüvel
> 2. The current default behaviour is NOT a bug and will NOT be changed.
IMO any loss of information that is not clearly described in documentation is
a bug. From the docs at
https://secure.simplistix.co.uk/svn/xlutils/trunk/xlutils/docs/copy.txt I
wouldn't have expected this behaviour.
All that seems to be needed to maintain the style is that the cell's xf_idx
property is not overwritten. At this moment this is possible to restore the
xf_idx using this very nasty hack:
sheet = wbook.get_sheet(0)
xf_idx = sheet.row(0)._Row__cells[0].xf_idx
sheet.write(0, 0, u'Updated')
sheet.row(0)._Row__cells[0].xf_idx = xf_idx
Of course an argument EXISTING_STYLE would be highly preferrable to this hack,
but with the current version of xlwt it'll have to.
Greetings,
--
Sybren A. Stüvel
http://stuvel.eu/
John Machin
> On Friday 09 July 2010 08:41:26 John Machin wrote:
>> 2. The current default behaviour is NOT a bug and will NOT be changed.
>
> IMO any loss of information that is not clearly described in documentation is
> a bug. From the docs at
> https://secure.simplistix.co.uk/svn/xlutils/trunk/xlutils/docs/copy.txt I
> wouldn't have expected this behaviour.
The question/problem/enhancement is about the style arg of
xlwt.Worksheet.write. I don't see how the xlutils documentation is
relevant.
> All that seems to be needed to maintain the style is that the cell's xf_idx
> property is not overwritten. At this moment this is possible to restore the
> xf_idx using this very nasty hack:
>
> sheet = wbook.get_sheet(0)
> xf_idx = sheet.row(0)._Row__cells[0].xf_idx
> sheet.write(0, 0, u'Updated')
> sheet.row(0)._Row__cells[0].xf_idx = xf_idx
>
> Of course an argument EXISTING_STYLE would be highly preferrable to this hack,
> but with the current version of xlwt it'll have to.
There'll be an SVN update soon after the discussion :-)
Cheers,
John
Sybren A. Stüvel
> The question/problem/enhancement is about the style arg of
> xlwt.Worksheet.write. I don't see how the xlutils documentation is
> relevant.
That document describes exactly what I want to do: to read an XLS file, modify
its contents, then save it.
Chris Withers
> On 9/07/2010 8:50 AM, Chris Withers wrote:
>> John Machin wrote:
>>> This could be achieved using a dummy arg e.g. ws.write(rowx, colx,
>>> "new data", style=Style.SAME_STYLE) which would cause the existing
>>> style to be used if overwriting a cell, otherwise the vanilla style).
>>> Comments please.
>>
>> My view is that the 3rd arg to write should be None by default,
>> meaning "leave my fscking style alone" ;-)
>
> The 3rd (not counting self) arg is a reference to the
Sorry, >11pm error ;-) Lets call it the "style" parameter ;-)
> The default 4th (style) arg is as I said a vanilla style and has always
> been like that and we shouldn't change it.
I agree with the effect; cells written to a cell with no style should
get a vanilla style. Might be nice if dates got a sane date style
(what's one of them? ;-) ).
However, for me, the intention of leaving the style parameter at its
default has always been "don't change whatever style information is
already present" and so I consider it a bug that unless I explicitly ask
for a style to be written, existing style information is destroyed.
The EXISTING_STYLE suggestion doesn't feel explicit to me, it just feels
overly verbose :-(
Sybren A. Stüvel
> All that seems to be needed to maintain the style is that the cell's
> xf_idx property is not overwritten. At this moment this is possible to
> restore the xf_idx using this very nasty hack:
Scratch that, it works in some cases but not in others. I'll wait for an
official implementation ;-)
John Machin
> John Machin wrote:
>> On 9/07/2010 8:50 AM, Chris Withers wrote:
>>> John Machin wrote:
>>>> This could be achieved using a dummy arg e.g. ws.write(rowx, colx,
>>>> "new data", style=Style.SAME_STYLE) which would cause the existing
>>>> style to be used if overwriting a cell, otherwise the vanilla
>>>> style). Comments please.
>>>
>>> My view is that the 3rd arg to write should be None by default,
>>> meaning "leave my fscking style alone" ;-)
>>
>> The 3rd (not counting self) arg is a reference to the
>
> Sorry, >11pm error ;-) Lets call it the "style" parameter ;-)
>
>> The default 4th (style) arg is as I said a vanilla style and has
>> always been like that and we shouldn't change it.
>
> I agree with the effect; cells written to a cell with no style should
> get a vanilla style. Might be nice if dates got a sane date style
> (what's one of them? ;-) ).
>
> However, for me, the intention of leaving the style parameter at its
> default has always been "don't change whatever style information is
> already present"
If that is so, your intention has always not been carried out, and you
have not realised that. The effect of omitting the style arg has always
been to write a vanilla style. This existing behaviour should not be
changed.
> and so I consider it a bug that unless I explicitly ask
> for a style to be written, existing style information is destroyed.
"so"? Your reason for so considering has no foundation.
John Yeung
> On 9/07/2010 8:30 PM, Chris Withers wrote:
>> However, for me, the intention of leaving the style parameter at its
>> default has always been "don't change whatever style information
>> is already present"
>
> If that is so, your intention has always not been carried out, and
> you have not realised that.
I expect Chris has realized it perfectly well, and simply not been
completely happy about it.
> The effect of omitting the style arg has always been to
> write a vanilla style. This existing behaviour should not be changed.
I agree that the existing behavior should not be changed, even if only
because it would break existing programs that expect or depend on it.
I actually do think Chris's preference is a common one, if not the
prevailing one, and I think it has just as much purely rational and
intuitive justification as the existing behavior. What the existing
behavior has that trumps everything else is that it's already in place
and well established.
John Y.
John Machin
> On Fri, Jul 9, 2010 at 7:12 AM, John Machin <sjma...@lexicon.net> wrote:
>
>> On 9/07/2010 8:30 PM, Chris Withers wrote:
>
>>> However, for me, the intention of leaving the style parameter at its
>>> default has always been "don't change whatever style information
>>> is already present"
>> If that is so, your intention has always not been carried out, and
>> you have not realised that.
>
> I expect Chris has realized it perfectly well, and simply not been
> completely happy about it.
and has suffered in silence ... uh-huh
>
>> The effect of omitting the style arg has always been to
>> write a vanilla style. This existing behaviour should not be changed.
>
> I agree that the existing behavior should not be changed, even if only
> because it would break existing programs that expect or depend on it.
> I actually do think Chris's preference is a common one, if not the
> prevailing one, and I think it has just as much purely rational and
> intuitive justification as the existing behavior. What the existing
> behavior has that trumps everything else is that it's already in place
> and well established.
I tend to agree with your comment about rational/intuitive behaviour --
missing style arg meaning "keep any existing style else use vanilla
style" would have been better if put in at the beginning.
Chris Withers
>>> The default 4th (style) arg is as I said a vanilla style and has
>>> always been like that and we shouldn't change it.
>>
>> I agree with the effect; cells written to a cell with no style should
>> get a vanilla style. Might be nice if dates got a sane date style
>> (what's one of them? ;-) ).
>>
>> However, for me, the intention of leaving the style parameter at its
>> default has always been "don't change whatever style information is
>> already present"
>
> If that is so, your intention has always not been carried out,
Indeed, and it's always been an annoying disappointment, especially
given how unnecessary the problems caused by that 4th parameter's
current default value are...
> and you
> have not realised that.
Oh I most certainly have...
> This existing behaviour should not be
> changed.
That existing behaviour can be happily accomodated by writing the
vanilla style on serialisation if one hasn't already been set for the
style...
>> and so I consider it a bug that unless I explicitly ask for a style to
>> be written, existing style information is destroyed.
>
> "so"? Your reason for so considering has no foundation.
Sure it does ;-)
Put differently, what would be the downside of the 2 part solution I
propose?