Openpyxl löscht alle Zellwerte und -stile, die unter meinen eingefügten Werten liegen ⇐ Python

[Anonymous]

Ich verwende openpyxl, um eine XLSX-Vorlagendatei zu laden, Daten hinzuzufügen und sie zu speichern. Ich mache das, damit ich die ursprüngliche Formatierung der Datei (Stil, Farben, Layout ...) beibehalten kann. Ein Teil der Informationen, die ich einfügen muss, ist eine Liste, die in einer Tabelle am Anfang der Datei eingefügt wird. Dazu habe ich beschlossen, der Tabelle die erforderliche Anzahl an Zeilen hinzuzufügen und dann die Daten (Zelle für Zelle) in die erstellten Zeilen einzufügen. Wenn ich das mache, verschwindet alles unter der Tabelle, einschließlich Werte, Stile und Farben. Zusammengeführte Zellen behalten ihre Größe und die Bilder bleiben ebenfalls erhalten. Die einzigen in den Zellen unter der Tabelle verbleibenden Werte sind Werte, die ich im Skript eingeführt habe. Die Vorlage hat 4 Blätter (dies geschieht in allen Blättern mit Tabellen), hier ist der Code für eines davon vereinfacht:

Code: Select all

def _fill_oferta_cliente(
self,
ws: Worksheet,
table_data: List[Tuple[Product, ImportationProductLink]],
offer_date,
importation: Importation,
containers: List[ContainersValue],
) -> None:
TABLE_START_ROW = 6
TABLE_START_COL = 3
TABLE_NUM_COLS = 9
TABLE_DATA_LEN = len(table_data)
INSERTED_ROWS = TABLE_DATA_LEN - 1

original_merges = self._capture_merges(ws)

for merge in list(ws.merged_cells):
ws.unmerge_cells(str(merge))

ws.insert_rows(TABLE_START_ROW + 1, INSERTED_ROWS)

for idx, (product, link) in enumerate(table_data):
target_row = TABLE_START_ROW + idx

if idx > 0:
self._copy_row(
ws=ws,
source_row=TABLE_START_ROW,
target_row=target_row,
start_col=TABLE_START_COL,
num_cols=TABLE_NUM_COLS,
)
initial_form_row = 11 + idx
values = [
product.tariff_heading,  # Partida Arancelaria
product.reference,  # Referencia
product.description,  # Producto / Descripción
product.unit_of_measure,  # Unidad de medida
link.units,  # Unidades
f"='F.PRECIO EXTENDIDA'!J{initial_form_row}",  # PRECIO UNITARIO CIF USD
f"='F.PRECIO EXTENDIDA'!S{initial_form_row}",  # IMPORTE CIF USD
f"='F.PRECIO CLIENTES'!J{initial_form_row-1}",  # PRECIO UNITARIO CUP
f"='F.PRECIO CLIENTES'!O{initial_form_row-1}",  # IMPORTE CUP
]

self._write_row(
ws=ws,
row=target_row,
start_col=TABLE_START_COL,
values=values,
)

# containers grid
SQUARE_SECTION_COLUMNS = 6
SQUARE_SECTION_START_ROW = 12
result = len(containers) // SQUARE_SECTION_COLUMNS
print("result")
print(result)
exc = len(containers) % SQUARE_SECTION_COLUMNS
print("exc")
print(exc)
container_square_rows = result + (1 if exc > 0 else 0)
print("container_square_rows")
print(container_square_rows)
ws.insert_rows(SQUARE_SECTION_START_ROW - 1, container_square_rows - 1)
INSERTED_ROWS = INSERTED_ROWS + container_square_rows - 1
for i in range(container_square_rows):
j = 0
max_j = SQUARE_SECTION_COLUMNS * (i + 1)
if (i + 1) == container_square_rows and exc > 0:
max_j = exc
else:
break
while j <  max_j:
container = containers[j]
ws.cell(row=SQUARE_SECTION_START_ROW + i, column=j + 6).value = container.value
j = j + 1

# formulas and static values
ws[f"I{6+TABLE_DATA_LEN}"] = f"=SUM(I6:I{6+INSERTED_ROWS})"
ws[f"I{8+TABLE_DATA_LEN}"] = f"=+I{6+TABLE_DATA_LEN}*H{8+TABLE_DATA_LEN}"
ws[f"K{8+TABLE_DATA_LEN}"] = f"=+K{6+TABLE_DATA_LEN}*H{8+TABLE_DATA_LEN}"
ws[f"K{20+INSERTED_ROWS}"] = f"=+F.PRECIO!I37*H{9+INSERTED_ROWS}"
ws[f"K{21+INSERTED_ROWS}"] = f"=+F.PRECIO!I38*H{9+INSERTED_ROWS}"
ws[f"K{28+INSERTED_ROWS}"] = f"=SUM(K{24+INSERTED_ROWS}:K{27+INSERTED_ROWS})"
ws[f"F{33+INSERTED_ROWS}"] = importation.client.legal_address
ws[f"F{34+INSERTED_ROWS}"] = importation.client.user.phone_number
ws[f"F{35+INSERTED_ROWS}"] = importation.client.user.email
ws[f"F{36+INSERTED_ROWS}"] = importation.client.establishment_denomination
ws[f"F{37+INSERTED_ROWS}"] = importation.delivery
ws[f"F{39+INSERTED_ROWS}"] = importation.client.bank_name
ws[f"F{40+INSERTED_ROWS}"] = importation.client.branch_office
ws[f"F{41+INSERTED_ROWS}"] = importation.client.holder
ws[f"F{42+INSERTED_ROWS}"] = importation.client.account_number
ws["K2"].value = offer_date
adjusted_merges = self._adjust_container_merges(
original_merges,
container_square_rows,
)

self._restore_merges(
ws=ws,
merges=adjusted_merges,
insert_row=7,
inserted_rows=INSERTED_ROWS,
)

def _write_row(self, ws: Worksheet, row: int, start_col: int, values: List) -> None:
for offset, value in enumerate(values):
ws.cell(row=row, column=start_col + offset, value=value)

def _copy_row(
self,
ws: Worksheet,
source_row: int,
target_row: int,
start_col: int,
num_cols: int,
) -> None:
for col_offset in range(num_cols):
col = start_col + col_offset

source_cell = ws.cell(row=source_row, column=col)
target_cell = ws.cell(row=target_row, column=col)

target_cell.value = source_cell.value

if source_cell.has_style:
target_cell.font = copy(source_cell.font)
target_cell.border = copy(source_cell.border)
target_cell.fill = copy(source_cell.fill)
target_cell.number_format = source_cell.number_format
target_cell.alignment = copy(source_cell.alignment)

def _capture_merges(self, ws: Worksheet):
"""
Returns a list of tuples:
(min_row, max_row, min_col, max_col)
"""
merges = []
for mr in ws.merged_cells.ranges:
merges.append((mr.min_row, mr.max_row, mr.min_col, mr.max_col))
return merges

def _restore_merges(
self,
ws: Worksheet,
merges,
insert_row: int,
inserted_rows: int,
):
for min_row, max_row, min_col, max_col in merges:
# If the merge is completely above the insertion point → unchanged
if max_row <  insert_row:
new_min_row = min_row
new_max_row = max_row
# If the merge is completely below → shift down
elif min_row >= insert_row:
new_min_row = min_row + inserted_rows
new_max_row = max_row + inserted_rows
# If it intersects the insertion point → extend
else:
new_min_row = min_row
new_max_row = max_row + inserted_rows

ws.merge_cells(
start_row=new_min_row,
end_row=new_max_row,
start_column=min_col,
end_column=max_col,
)

def _adjust_container_merges(
self,
merges,
container_square_rows: int,
):
adjusted = []

for min_row, max_row, min_col, max_col in merges:
# C12 = columna 3, E12 = columna 5
if min_row == 12 and min_col in (3, 5):
adjusted.append(
(
min_row,
max_row + (container_square_rows - 1),
min_col,
max_col,
)
)
else:
adjusted.append((min_row, max_row, min_col, max_col))

return adjusted

Irgendeine Idee, was das verursacht?