`Worksheet._move_cell` issue with handling row-wide/column-wide styles

[dsonyy]

While using Worksheet.insert_rows, Worksheet.insert_cols (and other ones that rely on Worksheet_move_cell) there is an issue with handling cells with row-wide/column-wide styles.

Code example

import openpyxl as pxl

workbook = pxl.load_workbook('test_in.xlsx')
worksheet = workbook.active

worksheet.insert_rows(1, 1)

workbook.save('test_out.xlsx')

Environment

• openpyxl==3.1.2
• Python 3.10.12
• examples read/written with 7.3.7.2 / LibreOffice Community
• examples read/written with MS Excel 365

Input test_in.xlsx file

test_in.xlsx

Sample file with three lines of content.

• For row 5, A row-wide red background style has been applied.
• For row 10, the first few cells were filled with color.
• For row 15, the row-wide style was applied, and then the style of the first few cells was overwritten.

Expected output

Every line of content with their styles is moved by one row down.

Actual output test_out.xlsx

test_out.xlsx

The contents of the cells are correctly transferred one row below. However, the old row-wide styles for the rows have been completely ignored and have not been cleaned up correctly.

Comments

• The above demonstration uses insert_rows which uses _move_cell to move the cells, so the problem is with _move_cell (and other functions that rely on it).
• In a sense, _move_cell "does" its job, because it naively moves the passed cells and their styles. If we modify the above code, we will notice that the cell whose style should be cleared is (not) actually cleared:

  print(worksheet.cell(row=5, column=1).fill.bgColor.rgb)
  worksheet.insert_rows(1, 1)
  print(worksheet.cell(row=5, column=1).fill.bgColor.rgb)

  Output:

  FFFF0000
  00000000

  I assume there must be some "global row" and "global column" properties that _move_cell doesn't take into account. I didn't invest it further.

[dsonyy]

just realized openpyxl is using this source https://foss.heptapod.net/openpyxl/openpyxl

Reposted issue: https://foss.heptapod.net/openpyxl/openpyxl/-/issues/2144