Preferred way for copying cell styles?

[Adam Morris]

Hi, with the 2.4 alpha (or possibly earlier, I'm still stuck on a 2.1 variant), is using Style() depreciated?  Is there a preferred way to copy cell styles?

Where before I would do something like;

desc_cell.style = src_cell.style.copy(number_format='XXX')

Should I be doing something like:

def copy_style(src_cell, dest_cell):
    dest_cell.font = src_cell.font
    dest_cell.fill = src_cell.fill
    dest_cell.border = src_cell.border
    dest_cell.alignment = src_cell.alignment
    dest_cell.number_format = src_cell.number_format

copy_style(src_cell, dest_cell)
dest_cell.number_format = 'XXX'

Hope all is well!

Kind Regards,

Adam

[Charlie Clark]

Am .04.2016, 18:02 Uhr, schrieb Adam Morris <w...@myemptybucket.com>:

> Hi, with the 2.4 alpha (or possibly earlier, I'm still stuck on a 2.1
> variant), is using Style() depreciated?

I think you mean "deprecated"? Though who knows, there may well special
accounting rules for software components.! ;-)

Anyway, you should definitely upgrade to 2.3: it's much nicer and faster.

A single, immutable Style() object for cells is indeed deprecated.

> Is there a preferred way to copy cell styles?

Well, it's best not thinking about them as styles.

> Where before I would do something like;
>
> desc_cell.style = src_cell.style.copy(number_format='XXX')
>
>
> Should I be doing something like:
>
> def copy_style(src_cell, dest_cell):
> dest_cell.font = src_cell.font
> dest_cell.fill = src_cell.fill
> dest_cell.border = src_cell.border
> dest_cell.alignment = src_cell.alignment
> dest_cell.number_format = src_cell.number_format
>
> copy_style(src_cell, dest_cell)
> dest_cell.number_format = 'XXX'

That would work though it's faster to copy the underlying StyleArray:

c1 = ws['A1']
c2 = ws['B2']

c2._style = copy(c1._style)

However, what I am working on are NamedStyles. These represent styles that
are supposed to be shared just like the "Style Templates" in Excel. The
API will be something like this:

normal = NamedStyle(name="Normal", font=Font(…), etc.)
wb.named_styles.add(normal)

ws['A1'].style = "Normal"

This would hopefully be easy to work with and also very fast.

However, there are a few implementation hurdles to jump:

* named styles and cell formats are, according to the specification,
supposed to be commutative so that you could define a named style, apply
it to range of cells, change it, add local cell formats and everything
would look great. Except Excel explicitly doesn't follow the standard
here. Also it's not clear how to resolve possible conflicts. What do you
do when a cell font is bold but the named style has font.bold = False?.
This is particularly important if the style is supposed to mutable after
assignment.

The simplest implementation would do sort of what Excel does and copy the
attributes of the named style as well as maintaining a link to it but this
makes a lot of assumptions about use in code: a named style would need to
be immutable after being assigned once; existing cell formats would have
to be replaced en masse but could then be overwritten. Or resolution has
to be done when serialising the cell, which could slow things down a lot.

Also the code for the API is a bit tricky.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Adam Morris]

Thank for the detailed response - the NamedStyles seems to make the most sense.  Also, thank you so much for all the progress and updates to openpyxl - it just keeps shining more and more!  I'm still here in the background (although work has been non-stop the last year...)

(And, yes, deprecated... I've been working in a number of P&L's recently where it's all about depreciation :-)

In terms of the cumulative effect, I wonder if the default Font() could be created with b=None instead of b=False, such that it could distinguish between "wasn't set" and "not bold".  Although, honestly, I'm used to style objects like Font() being immutable.  (If I set a named style to cell, and then update cell.font = Font(), I would set all the possible attributes that I'd want, especially if I had set any in the NamedStyle).

The simplest implementation makes sense to me. I don't see the need to re-define a named style once it's been added.  I'll dig into the code a bit.

Cheers,

Adam

[Charlie Clark]

Am .04.2016, 20:53 Uhr, schrieb Adam Morris <w...@myemptybucket.com>:

> Thank for the detailed response - the NamedStyles seems to make the most
> sense. Also, thank you so much for all the progress and updates to
> openpyxl - it just keeps shining more and more! I'm still here in the
> background (although work has been non-stop the last year...)
>
> (And, yes, deprecated... I've been working in a number of P&L's recently
> where it's all about depreciation :-)

Well, American English does tend to add syllables to latinate words:

obliged -> obligated
transport -> transportation

> In terms of the cumulative effect, I wonder if the default Font() could
> be created with b=None instead of b=False, such that it could distinguish
> between "wasn't set" and "not bold".

That wouldn't work because of the three-value logic in some of this stuff:
if a font element has a child b element then it is bold unless the value
attribute is set to false. Pretty stupid using child elements here anyway
but that's the spec. :-/

> Although, honestly, I'm used to style objects like Font() being
> immutable. (If I set a named style to cell, and then update cell.font =
> Font(), I would set all the possible attributes
> that I'd want, especially if I had set any in the NamedStyle).

Yes, but what about the NamedStyles that come with a file and that someone
wants to edit?

> The simplest implementation makes sense to me. I don't see the need to
> re-define a named style once it's been added. I'll dig into the code a
> bit.

Difficult to do this simply: immutability is very hard to in Python for
anything that does not derive from an immutable base.

To do:

* assign a named style to a Styleable object by name only (requires
look-up of the positional index by the name of wb.named_styles)
* when writing files create the relevant StyleArray for each named style
* when writing cells that have name styles check whether they have any
other formatting attributes and resolve using __iadd__

[Adam Morris]

Thanks - I'll look at the todo list and let you know if I make any progress.

Are you going to any python conventions, or having any openpyxl coding sessions this year?

Cheers,
Adam

[Charlie Clark]

Am .04.2016, 16:08 Uhr, schrieb Adam Morris <w...@myemptybucket.com>:

> Thanks - I'll look at the todo list and let you know if I make any
> progress.

Ta.

> Are you going to any python conventions, or having any openpyxl coding
> sessions this year?

I won't be going to PyCon in Portland but I do hope to make it to PyCon UK
in lovely Wales in September and possibly also to the Plone Conference in
Boston in October, though that will be more of a Pyramid gig.

Otherwise we'll probably be holding another sprint in Düsseldorf in the
autumn. I've got quite a list of things I'd like to do so a sprint would
be very useful.

In 2.5 I want finish work on packaging so full-blown round-tripping is
possible.

After that I'd like to switch the implementation of cells to reduce memory
use. This might also make aggregate operations a whole lot easier.

[Charlie Clark]

Am .04.2016, 16:08 Uhr, schrieb Adam Morris <w...@myemptybucket.com>:

> Thanks - I'll look at the todo list and let you know if I make any
> progress.

Hi Adam,

have you managed to have a look at this. I've spent time recently cleaning
up the packaging and if I don't have time soon to work on named styles
then I may leave it for 2.5 so that 2.4 can be released.

[Charlie Clark]

Am .04.2016, 16:08 Uhr, schrieb Adam Morris <w...@myemptybucket.com>:

> Are you going to any python conventions, or having any openpyxl coding
> sessions this year?

I can confirm that I will be attending PyCon UK in Cardiff from 16th to
18th September where I will be speaking about how I've used profiling in
openpyxl. If there is any interest in a sprint then I could stay for that
on the Monday. Otherwise it will be back to Germany by way of Manchester.

The PyCon UK programme looks pretty decent and the organisers have got
some good deals at the local hotels.

For more information see:
http://2016.pyconuk.org/programme/