Feature request: Support for copying the Format instance

[croshchupkin]

Is it possible to add the subject? So that when we have a "general" format for the data processed in a loop, we could e.g. assign a separate Format instance to the cells that we want to write the hyperlinks into.

[jmcnamara]

Hi Constantin,

Yes, that should be possible. I actually had a format.copy() method in a previous version of this module but didn't port it to this version because no-one seemed to use it.

Could you give me a small code example of what you are trying/would like to do.

John

[croshchupkin]

@classmethod
def write(cls, data, workbook, worksheet, cell_address, cell_format=None):
    # snip
    if cell_format:
        cell_format = cell_format.copy()
        cell_format.set_underline()
        cell_format.set_color('blue')
    # snip
    return worksheet.write(
         cell_address,
         value, # this is a URL
         cell_format,
         u'ссылка')

[jmcnamara]

I'll leave that as a feature request and add it in if it gets a few +1s.

In the meantime, URLs get a default blue underline format from XlsxWriter if none is supplied. So you may be able to use that feature instead:

import xlsxwriter

workbook = xlsxwriter.workbook.Workbook('url.xlsx')
worksheet = workbook.add_worksheet()

worksheet.set_column('A:A', 20)

worksheet.write(
         'A1',
         'http://www.python.org',
         None, # Note. No format.
         u'Some text')

workbook.close()

[croshchupkin]

OK, thanks for the info. But I don't think this will be the only use case when I may need to apply additional formatting to a separate cell. Also, in LibreOffice, the link is not underlined (although I don't think it's crucial for presenting the spreadsheet to our client). So I agree that this issue shouldn't be closed.
Again, thank you for your swift replies.

[ifmihai]

👍 for sure

I think it would be useful to have a format1.update(source) method also, to update format1 with a dict or another format

or format1.combine(format2), to return a new combined format (to not change format1)

Working with data and pandas, I see myself almost each time thinking about how I could combine formats for different columns and rows

UPDATE1
After investigating the structure of a format,
seeing the underlying dict there,
i'm not sure how or if .update() or .combine() would work
in this case, .copy() would be the only way, so 👍

[jmcnamara]

Thanks for the 👍

Format copying and updating will be handled as part of #111 "Feature request: Separate data and formatting".

I just need to carve out the time to tackle that. :-)

[ifmihai]

Just for reference I guess,

I implemented in a rush these 2 functions, format_copy() and format_update()
maybe other people need it for fast results until #111 is available

from copy import copy

def format_copy(format):
    '''returns a copy of the format
    '''
    return copy(format)

def format_update(format, d, inplace=False):
    '''updates format with a dict d
    format: the format
    d: dict with new attributes
    inplace: bool
             if format should be modified or not
    '''
    if inplace:
        fmt = format
    else:
        fmt = format_copy(format)

    fmt.__dict__.update(d)

    return fmt

UPDATE
unfortunately, these functions don't work, I don't understand why for now,
format_copy() creates unreadable content for excel
format_update() generates Exception IndexError: list assignment index out of range in self._prepare_formats()

if I get them to work, I will update this comment

[ifmihai]

@jmcnamara
I'm no expert, and I understand #111 just intuitively now,
but,

for my needs, working with pandas mostly,
having these 2 functions is what i need in all my cases

but maybe #111 is way better, I don't know

just wanted to say: "take your time with #111"
:)

[jmcnamara]

unfortunately, these functions don't work, I don't understand why for now

Formats have to be created via the workbook.add_format() method so that the workbook object can track them and give the formats an index number for the format, fonts, fills and other properties prior to saving and so that it ca remove duplicates and do some other operations required by Excel.

So a straight copy won't work. Copying properties from 2 format objects created with add_format() should work though.

[ifmihai]

What do you mean: "copying properties from 2 format objects" ?
I don't understand

maybe two objects of format type?

so then I should first create a new workbook.add_format(), then copy, then return it?

[sontek]

+1 to copying formats, my code is out of hand from duplicating the same things over and over again

[creeson]

Copying formats would be a huge help to me.

[cimarronm]

👍 would like to see this

@jmcnamara I just recently discovered xlsxwriter...excellent work

[andrew-zenefits]

+1 for copying formats

[bond777]

+1

[samuel-gh]

+1 for copying formats, or overloading workbook.add_format() to take as input an existing format, and it will make the copy plus add the new format.

[georgelovas]

+1 for copy and update format.....

[gsingh93]

+1 from me too.

[gsingh93]

I wrote this helper function to copy formats for me. It's ugly, error prone (I don't know what each field means), and incomplete (I only copy fields I needed copied in my code because I was too lazy to fill in the rest). Feel free to modify it. Hopefully this feature gets implemented in the future so we don't have to use this.

def copy_fmt(wb, f, properties={}):
    new_fmt = wb.add_format()

    new_fmt.num_format = 0
    new_fmt.num_format_index = 0
    new_fmt.font_index = 0
    new_fmt.has_font = 0
    new_fmt.has_dxf_font = 0

    new_fmt.bold = f.bold
    new_fmt.underline = f.underline
    new_fmt.italic = f.italic
    new_fmt.font_name = f.font_name
    new_fmt.font_size = f.font_size
    new_fmt.font_color = 0x0
    new_fmt.font_strikeout = 0
    new_fmt.font_outline = 0
    new_fmt.font_shadow = 0
    new_fmt.font_script = 0
    new_fmt.font_family = 2
    new_fmt.font_charset = 0
    new_fmt.font_scheme = 'minor'
    new_fmt.font_condense = 0
    new_fmt.font_extend = 0
    new_fmt.theme = 0
    new_fmt.hyperlink = 0

    new_fmt.hidden = 0
    new_fmt.locked = 1

    new_fmt.text_h_align = f.text_h_align
    new_fmt.text_wrap = 0
    new_fmt.text_v_align = f.text_v_align
    new_fmt.text_justlast = 0
    new_fmt.rotation = 0
    new_fmt.center_across = 0

    new_fmt.fg_color = f.fg_color
    new_fmt.bg_color = f.bg_color
    new_fmt.pattern = 0
    new_fmt.has_fill = 0
    new_fmt.has_dxf_fill = 0
    new_fmt.fill_index = 0
    new_fmt.fill_count = 0

    new_fmt.border_index = f.border_index
    new_fmt.has_border = f.has_border
    new_fmt.has_dxf_border = f.has_dxf_border
    new_fmt.border_count = f.border_count

    new_fmt.bottom = f.bottom
    new_fmt.bottom_color = f.bottom_color
    new_fmt.diag_border = f.diag_border
    new_fmt.diag_color = f.diag_color
    new_fmt.diag_type = f.diag_type
    new_fmt.left = f.left
    new_fmt.left_color = f.left_color
    new_fmt.right = f.right
    new_fmt.right_color = f.right_color
    new_fmt.top = f.top
    new_fmt.top_color = f.top_color

    new_fmt.indent = 0
    new_fmt.shrink = 0
    new_fmt.merge_range = 0
    new_fmt.reading_order = 0
    new_fmt.just_distrib = 0
    new_fmt.color_indexed = 0
    new_fmt.font_only = 0

    for key, value in properties.items():
        getattr(new_fmt, 'set_' + key)(value)

    return new_fmt

Usage:

    wb = xlsxwriter.Workbook('workbook.xlsx')
    base_fmt = wb.add_format({'border': 1})
    center_fmt = copy_fmt(wb, base_fmt, {'align': 'center'})

[creeson]

I wrote the following which might be a bit cleaner:

def copy_format(book, fmt):
    properties = [f[4:] for f in dir(fmt) if f[0:4] == 'set_']
    dft_fmt = book.add_format()
    return book.add_format({k : v for k, v in fmt.__dict__.iteritems() if k in properties and dft_fmt.__dict__[k] <> v})

I used it for setting borders, so my use case was (for example):

out_fmt = copy_format(book, fmt)
out_fmt.set_top(1)

[ivandeex]

+1 for copying formats

[germanbecker]

+1 For copy formats, and may I add a constructor to create a format from a cell

[AAS]

+1

[merlinr68]

+1

[micheller]

+1

[ghost]

+1

[cesaralba]

+1

[mysterfr]

I wrote the following which might be a bit cleaner:

def copy_format(book, fmt):
    properties = [f[4:] for f in dir(fmt) if f[0:4] == 'set_']
    dft_fmt = book.add_format()
    return book.add_format({k : v for k, v in fmt.__dict__.iteritems() if k in properties and dft_fmt.__dict__[k] <> v})

I used it for setting borders, so my use case was (for example):

out_fmt = copy_format(book, fmt)
out_fmt.set_top(1)

Thanks a lot for having posted this !
For the record, in python 3, this becomes:

def copy_format(book, fmt):
    properties = [f[4:] for f in dir(fmt) if f[0:4] == 'set_']
    dft_fmt = book.add_format()
    return book.add_format({k : v for k, v in fmt.__dict__.items() if k in properties and dft_fmt.__dict__[k] != v})```

[jmcnamara]

I'm unlikely to get to implement this. Closing.

[eusoubrasileiro]

+1 for re-open cannot understand why was closed

[woods-chen]

I wrote 2 functions:
add_inherited_format: used to copy a format and add the copied format to the workbook.
update_format update a format in place with another format or a dict of properties.
hope that helps the ones who need them.

_FORMAT_PROPERTIES = {
    'bg_color', 'bold', 'bottom', 'bottom_color', 'diag_border',
    'diag_color', 'diag_type', 'fg_color', 'font_color', 'font_name',
    'font_script', 'font_size', 'font_strikeout', 'hidden', 'indent',
    'italic', 'left', 'left_color', 'locked', 'num_format', 'pattern',
    'reading_order', 'right', 'right_color', 'rotation', 'shrink',
    'text_h_align', 'text_justlast', 'text_v_align', 'text_wrap',
    'top', 'top_color', 'underline'}

def update_format(fmt, update=None, props=None):
    '''
    update the properties of fmt with props (a dict) and the properties
        of `update`.

    Parameters
    -----------------
    fmt: the format to update.
    update: the format to update with.
    props: {property: value} pairs
    '''
    if not update is None:
        for k in _FORMAT_PROPERTIES:
            v = getattr(update, k)
            try:
                v = v.copy()
            except AttributeError:
                pass
            getattr(fmt, 'set_'+k)(v)
    if props:
        for k, v in props.items():
            getattr(fmt, 'set_'+k)(v)
    return fmt

def add_inherited_format(workbook, base=None, props=None):
    '''
    add a new format to workbook, of which the properties are inherited from `base`
        and are updated with props.

    Parameters
    ----------------
    workbook: the workbook to add format to.
    base: the base format to inherit from.
    props: a dict of format properties.
    '''
    _props = {}
    if not base is None:
        for k in _FORMAT_PROPERTIES:
            v = getattr(base, k)
            try:
                _props[k] = v.copy()
            except AttributeError:
                _props[k] = v
    fmt = workbook.add_format(_props)
    # set each property individually instead of updating `_props` with `props`
    # directly before instantiating `fmt`, so `text_v_align` and `text_h_align`
    # will be overrided by `align` property in `props`
    if props:
        for k, v in props.items():
            getattr(fmt, 'set_'+k)(v)
    return fmt

[jmcnamara]

Reopening this issue on adding support for a copy() method in Format.