复制整行并保留格式

huangapple
go评论130阅读模式
英文:

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

以下是您要翻译的代码部分：

  1. 这是一个示例，将工作表中的一行中的单元格复制到另一行中。<br>
  2. 此示例将从列A到列J中的第2行的单元格范围复制到向下 'row_offset' 行，这里是第12行。复制包括单元格样式/格式和合并单元格。<br>
  3. <br>
  5.     from openpyxl.utils import rows_from_range
  6.     from openpyxl import load_workbook
  7.     from openpyxl.worksheet.cell_range import CellRange
  8.     from copy import copy
  9.     
  10.     
  11.     def copy_range(range_str, sheet, offset):
  12.         """使用偏移复制单元格值和样式到新行"""
  13.         for row in rows_from_range(range_str):
  14.             for cell in row:
  15.                 if sheet[cell].value is not None:  # 不要复制合并单元格中的其他单元格
  16.                     dst_cell = sheet[cell].offset(row=offset, column=0)
  17.                     src_cell = sheet[cell]
  18.     
  19.                     ### 复制单元格值
  20.                     dst_cell.value = src_cell.value
  21.     
  22.                     ### 复制单元格样式
  23.                     dst_cell.font = copy(src_cell.font)
  24.                     dst_cell.alignment = copy(src_cell.alignment)
  25.                     dst_cell.border = copy(src_cell.border)
  26.                     dst_cell.fill = copy(src_cell.fill)
  27.     
  28.                     dst_cell.number_format = src_cell.number_format
  29.     
  30.     
  31.     def get_merge_list(r_range, r_offset):
  32.         """从现有行创建新单元格合并列表"""
  33.         area = CellRange(r_range)  # 要检查合并单元格的范围
  34.         mlist = []  # 现有行偏移至新行的合并单元格列表
  35.         for mc in ws.merged_cells:
  36.             if mc.coord not in area:
  37.                 continue
  38.             cr = CellRange(mc.coord)
  39.             cr.shift(row_shift=r_offset)
  40.             mlist.append(cr.coord)
  41.     
  42.         return mlist
  43.     
  44.     
  45.     wb = load_workbook("foo.xlsx")
  46.     ws = wb['Sheet1']
  47.     
  48.     row_range = 'A2:J2'  # 要复制的行范围
  49.     row_offset = 10  # 新行的偏移量
  50.     
  51.     ### 为新行上的合并单元格创建范围列表
  52.     new_merge_list = get_merge_list(row_range, row_offset)
  53.     ### 在新行上创建合并单元格
  54.     for nm in new_merge_list:
  55.         ws.merge_cells(nm)
  56.     
  57.     ### 将单元格值复制到新行
  58.     copy_range(row_range, ws, row_offset)
  59.     
  60.     ### 保存工作簿
  61.     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>

  1. from openpyxl.utils import rows_from_range
  2. from openpyxl import load_workbook
  3. from openpyxl.worksheet.cell_range import CellRange
  4. from copy import copy
  5. def copy_range(range_str, sheet, offset):
  6. &quot;&quot;&quot; Copy cell values and style to the new row using offset&quot;&quot;&quot;
  7. for row in rows_from_range(range_str):
  8. for cell in row:
  9. if sheet[cell].value is not None:  # Don&#39;t copy other cells in merged unit
  10. dst_cell = sheet[cell].offset(row=offset, column=0)
  11. src_cell = sheet[cell]
  12. ### Copy Cell value
  13. dst_cell.value = src_cell.value
  14. ### Copy Cell Styles
  15. dst_cell.font = copy(src_cell.font)
  16. dst_cell.alignment = copy(src_cell.alignment)
  17. dst_cell.border = copy(src_cell.border)
  18. dst_cell.fill = copy(src_cell.fill)
  19. dst_cell.number_format = src_cell.number_format
  20. def get_merge_list(r_range, r_offset):
  21. &quot;&quot;&quot; Create a list of new cell merges from the existing row&quot;&quot;&quot;
  22. area = CellRange(r_range)  # Range to check for merged cells
  23. mlist = []  # List of merged cells on existing row offset to the new row
  24. for mc in ws.merged_cells:
  25. if mc.coord not in area:
  26. continue
  27. cr = CellRange(mc.coord)
  28. cr.shift(row_shift=r_offset)
  29. mlist.append(cr.coord)
  30. return mlist
  31. wb = load_workbook(&quot;foo.xlsx&quot;)
  32. ws = wb[&#39;Sheet1&#39;]
  33. row_range = &#39;A2:J2&#39;  # Row range to be copied
  34. row_offset = 10  # Offset to the new row
  35. ### Create a range list for merged cells on new row
  36. new_merge_list = get_merge_list(row_range, row_offset)
  37. ### Create merged cells on new row
  38. for nm in new_merge_list:
  39. ws.merge_cells(nm)
  40. ### Copy cell values to new row
  41. copy_range(row_range, ws, row_offset)
  42. ### Save workbook
  43. wb.save(&quot;foo_out.xlsx&quot;)

复制整行并保留格式

huangapple
  • 本文由 发表于 2023年2月9日 03:05:19
  • 转载请务必保留本文链接：https://go.coder-hub.com/75390577.html
  • openpyxl
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定