Python and openpyxl - how can I copy the style when I "append" a row in a new file?

Question

below my code:

import openpyxl

# file 1:
path = "C:\\Users\\Admin\\Desktop\\TEST.xlsx"
wb_1 = openpyxl.load_workbook(path, read_only=True)
ws_1 = wb_1[wb_1.sheetnames[0]]

# file 2 (it's a new file):
wb_2 = openpyxl.Workbook()
ws_2 = wb_2.active
ws_2.title = "SHEET"

# copy the rows placed in file 1 into the file 2:
def GetList(row):
    list_for_row_values = []
    for cell in row:
        list_for_row_values.append(cell.value)
    return list_for_row_values

for row in ws_1.rows:
    list_for_row_values = GetList(row)
    ws_2.append(list_for_row_values)

wb_2.save("C:\\Users\\Admin\\Desktop\\result.xlsx")

in this example the script copies the rows placed in the first sheet of the TEST.xlsx file into a new one named "result.xlsx". the script works, but it copies only the content of the cells, and not theirs styles. it means that in the new files the wrap text, the original font, etc.. will be not enabled. how can I copy the style too? I tried many instructions but they didn't work. I'm not an expert with openpyxl. can you help me to modify this simple code in order to reach my goal?

Does this answer your question? [copy cell style openpyxl](https://stackoverflow.com/questions/23332259/copy-cell-style-openpyxl) — eNc, Jun 19 '20 at 22:18
no because when I write, I do it from a row point of view and not from a single cell point of view. sorry man but, find another topic with a similar title is not difficult, the main issue is understand if the solutions proposed for the second topic can be useful for my one too. probably I will have to use the "copy" module, as reported in the second topic, but how can I copy the style and at the same time use the `append` method to write the rows in the new document? maybe it's could be simple for you, but, as I already wrote before, I don't have so much experience with openpyxl. — RisoGalloExresso, Jun 19 '20 at 22:57
Every cell can have their own unique style(s). That's why the solution I referenced copies the format for each cell. There is no 'row style' attribute in excel because each cell can have a different style. The solution referenced (which this question is duplicate of) should solve your problem if you implement it in your code. It's not about searching for similar titles, its about understanding and adapting to the problem. If you edit your post with an attempted soln I am sure someone will help. Check out [ask] for info on presenting a reasonable attempt at an answer. — eNc, Jun 19 '20 at 23:09
in my code `list_for_row_values` is a list composed by the content of the cells placed in a row. what about if I want to get also the style and not just the content? if I try to replace the instruction `list_for_row_values.append(cell.value)` with `list_for_row_values.append(cell)` the script doesn't work anymore, python gives me this exception: `ValueError: Cannot convert to Excel`. I really don't understand how to merge the solution given to the second topic, with the use of the `append` method. can you give me a hand please? — RisoGalloExresso, Jun 19 '20 at 23:25

score 2 · Answer 1 · answered Jun 20 '20 at 00:18

My solution presented here is an adaptation from this post, which is the post I mentioned repeatedly in my comments on your question, and by using the openpyxl documentation

This is what my input xlsx looks like:

Here is the code:

import openpyxl

# file 1:
wb_1 = openpyxl.load_workbook(in_path, read_only=True)
ws_1 = wb_1[wb_1.sheetnames[0]]

# file 2 (it's a new file):
wb_2 = openpyxl.Workbook()
ws_2 = wb_2.active

for r in ws_1.rows:
    for c in r:
        ws_2.cell(row=c.row, column=c.column).value = c.value
        ws_2.cell(row=c.row, column=c.column).fill = c.fill
        ws_2.cell(row=c.row, column=c.column).font = c.font
        ws_2.cell(row=c.row, column=c.column).number_format = c.number_format
        ws_2.cell(row=c.row, column=c.column).border = c.border

wb_2.save(out_path)

Here is my output document:

Essentially my inner loop is a duplicate of the code here:

    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)

So partial credit goes to @CharlieClark for his post (link is here again)

If you want people to answer your questions on SO, at least attempt to use the information they provide in the comments. Especially if you are new to python or a particular package. This isn't a programming service, you should at least try and struggle with a solution before claiming your post isn't a duplicate or the question hasn't been already answered.

I'm not 15 anymore. I don't care if you still think that my question is a duplicate or not, I already explained you what I think about it. I really appreciate so much your attempts to help me, but your answer doesn't match my question. What interests me is understand if there is a way to use the `append` method not only to write strings and numbers, but also to write cells, and with cells I mean single objects with values and styles incorpored. — RisoGalloExresso, Jun 21 '20 at 14:05
To be clear, I want to copy a cell in the same way when you do it by hand in excel. when you copy a cell to another one by hand, excel doesn't ask you what kind of styles do you want to copy, it copies the cell and nothing else, with his content and styles of course. — RisoGalloExresso, Jun 21 '20 at 14:06

score 1 · Answer 2 · answered Jun 20 '20 at 17:54

Why u must use the function append ?

In ur case, using the method below may work well:

eg:

import openpyxl,os
from copy import copy

path = '1.xlsx'
wb_1 = openpyxl.load_workbook(path)
ws_1 = wb_1[wb_1.sheetnames[0]]
wb_2 = openpyxl.Workbook()
ws_2 = wb_2.active
ws_2.title = "SHEET"
ws_2.sheet_format = ws_1.sheet_format

for (row, col), source_cell  in ws_1._cells.items():
    cell = ws_2.cell(column=col, row=row)
    cell.value = source_cell.value
    cell.font = copy(source_cell.font)
    cell.fill = copy(source_cell.fill)
    cell._hyperlink = source_cell._hyperlink
    cell.comment = source_cell.comment
wb_2.save('11.xlsx')

Python and openpyxl - how can I copy the style when I "append" a row in a new file?

2 Answers2