Python 复制单元格样式 openpyxl
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23332259/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
copy cell style openpyxl
提问by FotisK
I am trying to copy a sheet, default_sheet, into a new sheet new_sheetin the same workbook.
我正在尝试将工作表 , 复制default_sheet到new_sheet同一工作簿中的新工作表中。
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 单元格中？
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.
我目前正在使用 openpyxl 1.8.2，但我想切换到 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()])
回答by Charlie Clark
As of openpyxl 2.5.4, python 3.4: (subtle changes over the older version below)
从 openpyxl 2.5.4、python 3.4 开始：（对下面旧版本的细微变化）
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
对于 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
回答by Avik Samaddar
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')
回答by ldrg
The StyleableObjectimplementation 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.
该StyleableObject实现将样式存储在单个列表中_style，并且单元格上的样式属性实际上是该数组的 getter 和 setter。您可以单独实现每种样式的副本，但这会很慢，特别是如果您像我一样在繁忙的内部循环中执行此操作。
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 WorksheetCopyclass does it in the _copy_cellsmethod.
FWIW 这就是优化WorksheetCopy类在_copy_cells方法中的执行方式。
