How to copy cell from one workbook to an other workbook with its set style?

Question

I have been trying to copy a cell from one workbook to another with formatting only to fail. I have tried numerous suggestion but nothing works.

I have tried:

def copyCellStyle(new_cell,cell):
    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)

then use it like:

new_cell.value=old_cell.value 
copyCellStyle(new_cell,old_cell)

It runs without errors but the job is not done.

I get errors with the other methods in the styles class. When

new_cell._style = copy(old_cell._style)

this is used I get this error while I am saving the copied file.

    Traceback (most recent call last):
  File "C:\Python Programs\test\test2.py", line 40, in <module>
    wb2.save("Test3.xlsx")
  File "C:\Anaconda3\lib\site-packages\openpyxl\workbook\workbook.py", line 392, in save
    save_workbook(self, filename)
  File "C:\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbook
    writer.save()
  File "C:\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 275, in save
    self.write_data()
  File "C:\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 84, in write_data
    stylesheet = write_stylesheet(self.workbook)
  File "C:\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py", line 240, in write_stylesheet
    xf.alignment = wb._alignments[style.alignmentId]
IndexError: list index out of range

Also when I check if cell has style like:

Traceback (most recent call last)
 File "C:\Python Programs\test\test2.py", line 37, in <module>
    if cell.has_style: 
AttributeError: module 'openpyxl.cell' has no attribute 'has_style'

I am using python 3.x.

Can anyone tell me a working proper method to achieve this.

Thanks in advance!

If you are getting errors it is a good practice to also include those exceptions in the question. — Dror Av., May 19 '20 at 14:25
please take a look at [this answer](https://stackoverflow.com/questions/23332259/copy-cell-style-openpyxl) it seems to answer your question. — Dror Av., May 21 '20 at 08:01

Dror Av. · Answer 1 · 2020-05-22T10:59:57.447

You need to understand what does copy(cell._style) does to understand that exception. Behind the scenes each openpyxl workbook has protected attributes for different styles. For example the attribute _alignments will hold a list with the alignments in the workbook, such as these:

_style is a list that only refers to the mentioned attributes by their index, as such:

So, lets take for example a workbook with no alignment styles applied. in that case the _alignments list attribute of the workbook will have only 1 item which is the defualt (empty) alignment so:

len(new_workbook._alignments) == 1

When you copy(cell._style) to the new workbook it tries to get its new style by reffering to the protected attributes styles (alignments, borders, etc.). But, as the new workbook has no styles the reference _alignment[1] will raise an IndexError as the new workbook list has only one item on that list, the default one (which is stored in _alignments[0]).

Your initial approach is the correct one. If you are copying to a new workbook you should iterate over the cells of the sheet copying the value and then the individual styles:

from openpyxl import load_workbook, Workbook
from copy import copy

original_wb = load_workbook('example.xlsx')
original_sheet = original_wb['Sheet1']

new_wb = Workbook()
new_ws = new_wb.active

for row in original_sheet.rows:
    for cell in row:
        new_ws[cell.coordinate] = cell.value
        new_cell = new_ws[cell.coordinate]

        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)

new_wb.save('new.xlsx')

The _style method will not work when trying to copy to a new workbook and is not necessary within the same workbook as you can use workbook.copy_worksheet(documentation).

The problem that I was facing was that my initial approach wasn't working. — cyberGhoul, May 22 '20 at 10:45
@cyberGhoul - Gotchya, corrected the code, and checked it on my own, it works this time. Your initial code should have worked, you might have missed something small. — Dror Av., May 22 '20 at 11:00
Such as? that is data you need to put in the initial question, something like - "I succedded in copying some formats but others like x, y, z aren't working". It is important so you can get a complete answer to the issue. — Dror Av., May 22 '20 at 11:05
You were right. It was referring to another sheet in workbook and hence the error sprouted. Thank you so much for your quick response. I really owe you a lot. By the way, do you have any idea to copy the 'table' format in excel because this doesn't satisfy it. — cyberGhoul, May 22 '20 at 11:24
@cyberGhoul Youre welcome. Regarding tables I can't think about it out of the top of my mind. You can look it up or open a new question and either me or someone else can dive into that. — Dror Av., May 22 '20 at 11:27

How to copy cell from one workbook to an other workbook with its set style?

1 Answers1

Linked