copy cell style openpyxl

Question

I am trying to copy a sheet, default_sheet, into a new sheet new_sheet in the same workbook.

I did managed to create a new sheet and to copy the values from default sheet. How can I also copy the style of each cell into the new_sheet cells?

new_sheet = workbook.create_sheet()
new_sheet.title = sheetName
default_sheet = workbook.get_sheet_by_name('default')
new_sheet = workbook.get_sheet_by_name(sheetName)
for row in default_sheet.rows:
    col_idx = float(default_sheet.get_highest_column())
starting_col = chr(65 + int(col_idx))
for row in default_sheet.rows:
    for cell in row:
        new_sheet[cell.get_coordinate()] = cell.value
        <copy also style of each cell>

I am at the moment using openpyxl 1.8.2, but i have in mind to switch to 1.8.5.

One solution is with copy:

from copy import copy, deepcopy

new_sheet._styles[cell.get_coordinate()] = copy(
        default_sheet._styles[cell.get_coordinate()])

I found a way with copy, but i am not sure if it is the best way and it doesn't copy everything like width/height of a cell! — FotisK, Apr 28 '14 at 06:58
Yes, you need to use copy. Each worksheet keeps a dictionary of cell styles which can be copied. But really you want to try using the 1.9 branch which has a much cleaner interface for this kind of thing. — Charlie Clark, Apr 28 '14 at 18:39

score 54 · Answer 1 · edited Jun 19 '18 at 14:51

54

As of openpyxl 2.5.4, python 3.4: (subtle changes over the older version below)

new_sheet = workbook.create_sheet(sheetName)
default_sheet = workbook['default']

from copy import copy

for row in default_sheet.rows:
    for cell in row:
        new_cell = new_sheet.cell(row=cell.row, column=cell.col_idx,
                value= cell.value)
        if cell.has_style:
            new_cell.font = copy(cell.font)
            new_cell.border = copy(cell.border)
            new_cell.fill = copy(cell.fill)
            new_cell.number_format = copy(cell.number_format)
            new_cell.protection = copy(cell.protection)
            new_cell.alignment = copy(cell.alignment)

For openpyxl 2.1

new_sheet = workbook.create_sheet(sheetName)
default_sheet = workbook['default']

for row in default_sheet.rows:
    for cell in row:
        new_cell = new_sheet.cell(row=cell.row_idx,
                   col=cell.col_idx, value= cell.value)
        if cell.has_style:
            new_cell.font = cell.font
            new_cell.border = cell.border
            new_cell.fill = cell.fill
            new_cell.number_format = cell.number_format
            new_cell.protection = cell.protection
            new_cell.alignment = cell.alignment

edited Jun 19 '18 at 14:51

pbarill

640
8
16

answered Jan 17 '16 at 12:19

Charlie Clark

18,477
4
49
55

8

I'm using openpyxl 2.4.1, `cell.font` or `cell.border` is an instance of `StyleProxy`, if save workbook with that type, you will get an exception. You must copy it to new cell, like this: `new_cell.font = copy(cell.font)` – dawncold Jan 21 '17 at 14:51
1

THANK YOU dawncold, was wondering why I was getting a "non hashable type" error. – otocan Aug 18 '17 at 10:49
In `2.5.3`, `fill`, `protection` and `alignment` must also be copied. – Jorge Leitao May 13 '18 at 04:26
Edited to add slight modifications needed with recent openpyxl, including changes mentioned by everyone. Some changes in cell attributes too. Thanks a bunch Charlie Clark, that formed the basis of what I needed to [merge xlsx files](https://stackoverflow.com/questions/15793349/how-to-concatenate-three-excels-files-xlsx-using-python). – pbarill Jun 19 '18 at 15:13
1

Worth noting that workbooks have had a `copy_worksheet` method for a while now and this copy styles for you. – Charlie Clark Jul 02 '18 at 14:27
@CharlieClark I don't think you can use `copy_worksheet` here, see [this warning](https://openpyxl.readthedocs.io/en/stable/api/openpyxl.workbook.workbook.html#openpyxl.workbook.workbook.Workbook.copy_worksheet). – Stef Oct 02 '19 at 15:33
This question is about copying worksheets within the same workbook so there will be no problem. – Charlie Clark Oct 04 '19 at 10:10
1

This doesn't work for merged cells, and the dimensions of cells are not kept. – wbzy00 Apr 07 '21 at 01:43
1

this is not an exact copy, quite a mess when I tried it. not aligned properly – greendino Dec 13 '21 at 19:44

score 26 · Answer 2 · answered Jul 11 '18 at 22:51

26

The StyleableObject implementation stores styles in a single list, _style, and style properties on a cell are actually getters and setters to this array. You can implement the copy for each style individually but this will be slow, especially if you're doing it in a busy inner loop like I was.

If you're willing to dig into private class attributes there is a much faster way to clone styles:

if cell.has_style:
    new_cell._style = copy(cell._style)

FWIW this is how the optimized WorksheetCopy class does it in the _copy_cells method.

answered Jul 11 '18 at 22:51

ldrg

4,150
4
43
52

`NamedStyles` make much more sense if you wish to use the same style in multiple cells. – Charlie Clark Jul 12 '18 at 13:57
2

Nice code. Exactly what I need for the moment. But indeed, in some cases `NamedStyles` might be a better approach – Jean-Francois T. Feb 28 '19 at 16:06
Thanks for the single line answer – Vineesh TP Sep 10 '19 at 18:30
1

this didn't work for me, using openpyxl 3.0.9 – netotz Sep 30 '21 at 17:24
Nothing about how openpyxl stores styles for cells in the 3.x branches has changed, any bugs are from your code. – ldrg Jun 13 '23 at 15:39

Avik Samaddar · Answer 3 · 2016-02-22T18:10:24.103

3

May be this is the convenient way for most.

    from openpyxl import load_workbook
    from openpyxl import Workbook
    read_from = load_workbook('path/to/file.xlsx')
    read_sheet = read_from.active
    write_to = Workbook()
    write_sheet = write_to.active
    write_sheet['A1'] = read_sheet['A1'].value
    write_sheet['A1'].style = read_sheet['A1'].style
    write_to.save('save/to/file.xlsx')

edited Feb 22 '16 at 18:10

answered Feb 22 '16 at 17:54

Avik Samaddar

411
4
7

2

That is not all styles, [this answer](http://stackoverflow.com/a/34838233/831142) is better, but `copy` must be used. – dawncold Jan 21 '17 at 15:01

score 1 · Answer 4 · answered Feb 16 '23 at 18:09

I organized the answers above and the code below works for me. (it copies the cell value and the cell format)

from openpyxl import load_workbook
from copy import copy

wb = load_workbook(filename = 'unmerge_test.xlsx') #your file name
ws = wb['sheet_merged'] #your sheet name in the file above

for group in list(ws.merged_cells.ranges):
    min_col, min_row, max_col, max_row = group.bounds
    cell_start = ws.cell(row = min_row, column = min_col)
    top_left_cell_value = cell_start.value

    ws.unmerge_cells(str(group))

    for i_row in range(min_row, max_row + 1):
        for j_col in range(min_col, max_col + 1): 
            ws.cell(row = i_row, column = j_col, value = top_left_cell_value)
            #copy the cell format
            ws.cell(row = i_row, column = j_col).alignment = copy(cell_start.alignment)
            ws.cell(row = i_row, column = j_col).border = copy(cell_start.border)
            ws.cell(row = i_row, column = j_col).font = copy(cell_start.font)

wb.save("openpyxl_unmerged.xlsx")

Hope this helps!

copy cell style openpyxl

4 Answers4

Linked