英文:
PYTHON - Copy entire row with formatting
问题
我是新手在Python和Openpyxl。
我正在尝试复制一行中的一系列单元格（包括它们的格式，知道有些单元格是合并的）到同一工作表中的另一行。
谢谢。
英文:
I am new in Python and Openpyxl.
I am trying to copy a range of cells (with their formatting knowing some cells are merged) from a row to an other row on the same worksheet.
Thanks
答案1
得分: 1
以下是您要翻译的代码部分：
这是一个示例，将工作表中的一行中的单元格复制到另一行中。<br>
此示例将从列A到列J中的第2行的单元格范围复制到向下 'row_offset' 行，这里是第12行。复制包括单元格样式/格式和合并单元格。<br>
<br>
from openpyxl.utils import rows_from_range
from openpyxl import load_workbook
from openpyxl.worksheet.cell_range import CellRange
from copy import copy
def copy_range(range_str, sheet, offset):
"""使用偏移复制单元格值和样式到新行"""
for row in rows_from_range(range_str):
for cell in row:
if sheet[cell].value is not None: # 不要复制合并单元格中的其他单元格
dst_cell = sheet[cell].offset(row=offset, column=0)
src_cell = sheet[cell]
### 复制单元格值
dst_cell.value = src_cell.value
### 复制单元格样式
dst_cell.font = copy(src_cell.font)
dst_cell.alignment = copy(src_cell.alignment)
dst_cell.border = copy(src_cell.border)
dst_cell.fill = copy(src_cell.fill)
dst_cell.number_format = src_cell.number_format
def get_merge_list(r_range, r_offset):
"""从现有行创建新单元格合并列表"""
area = CellRange(r_range) # 要检查合并单元格的范围
mlist = [] # 现有行偏移至新行的合并单元格列表
for mc in ws.merged_cells:
if mc.coord not in area:
continue
cr = CellRange(mc.coord)
cr.shift(row_shift=r_offset)
mlist.append(cr.coord)
return mlist
wb = load_workbook("foo.xlsx")
ws = wb['Sheet1']
row_range = 'A2:J2' # 要复制的行范围
row_offset = 10 # 新行的偏移量
### 为新行上的合并单元格创建范围列表
new_merge_list = get_merge_list(row_range, row_offset)
### 在新行上创建合并单元格
for nm in new_merge_list:
ws.merge_cells(nm)
### 将单元格值复制到新行
copy_range(row_range, ws, row_offset)
### 保存工作簿
wb.save("foo_out.xlsx")
希望这能帮助您理解代码的功能。如果您有任何问题，请随时提问。
英文:
This is an example to copy cells from one row to another within the same worksheet.<br>
This example takes a range of cells on row 2 from column A to column column J and duplicates to 'row_offset' rows down, in this case row 12. The duplication includes cell style/formatting and merged cells.<br>
<br>
from openpyxl.utils import rows_from_range
from openpyxl import load_workbook
from openpyxl.worksheet.cell_range import CellRange
from copy import copy
def copy_range(range_str, sheet, offset):
""" Copy cell values and style to the new row using offset"""
for row in rows_from_range(range_str):
for cell in row:
if sheet[cell].value is not None: # Don't copy other cells in merged unit
dst_cell = sheet[cell].offset(row=offset, column=0)
src_cell = sheet[cell]
### Copy Cell value
dst_cell.value = src_cell.value
### Copy Cell Styles
dst_cell.font = copy(src_cell.font)
dst_cell.alignment = copy(src_cell.alignment)
dst_cell.border = copy(src_cell.border)
dst_cell.fill = copy(src_cell.fill)
dst_cell.number_format = src_cell.number_format
def get_merge_list(r_range, r_offset):
""" Create a list of new cell merges from the existing row"""
area = CellRange(r_range) # Range to check for merged cells
mlist = [] # List of merged cells on existing row offset to the new row
for mc in ws.merged_cells:
if mc.coord not in area:
continue
cr = CellRange(mc.coord)
cr.shift(row_shift=r_offset)
mlist.append(cr.coord)
return mlist
wb = load_workbook("foo.xlsx")
ws = wb['Sheet1']
row_range = 'A2:J2' # Row range to be copied
row_offset = 10 # Offset to the new row
### Create a range list for merged cells on new row
new_merge_list = get_merge_list(row_range, row_offset)
### Create merged cells on new row
for nm in new_merge_list:
ws.merge_cells(nm)
### Copy cell values to new row
copy_range(row_range, ws, row_offset)
### Save workbook
wb.save("foo_out.xlsx")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论