Excel with cells empty when I copy and paste into another excel with python - Stack Overflow

admin管理员组

文章数量:1418700

I have a code that copy specific cells from an xlsx and paste in another excel (which was previously xls but saved as xlsx now) with a searching criteria.
The code has to look for the first empty cell in column Q and paste data in column F starting in row 14000 [in the excel I past the data].
The problem is that, when data is pasted it's always pasted in row 14000, and there is still data in that cell.
When I try to look the content in Python, there is a empty cell, according to Python, but not with Excel.

This is my code;
it copies a cell, initially asking for month and year to identify the archive.

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

def copiar_datos_con_estilos_condicional(mod_path, p_es_path, MM, AA, start_row=14000):
MM = int(MM)
AA = int(AA)
leap_years = {24, 28, 32, 36, 38, 42, 44, 46, 54}

    
    if MM in {1, 3, 5, 7, 8, 10, 12}:
        end_row = 41
    elif MM in {4, 6, 9, 11}:
        end_row = 40
    elif MM == 2:
        end_row = 39 if AA in leap_years else 38
    else:
        raise ValueError(f"Mes {MM} no válido.")
    
    mod_wb = load_workbook(mod_path)
    mod_ws = mod_wb["P"]
    
    p_es_wb = load_workbook(p_es_path)
    p_es_ws = p_es_wb.active
    
    initial_start_row = start_row
    while p_es_ws.cell(row=start_row, column=17).value is not None:
        start_row += 1
    
    first_empty_value = p_es_ws.cell(row=start_row, column=17).value  # Expected to be None
    
    source_start_row = 13
    source_start_col = 3
    target_start_col = 6
    
    copied_data = []
    
    for i, row in enumerate(range(source_start_row, end_row + 1)):
        row_data = []
        for j, col in enumerate(range(source_start_col, 15)):
            source_cell = mod_ws.cell(row=row, column=col)
            target_cell = p_es_ws.cell(row=start_row + i, column=target_start_col + j)
            
            target_cell.value = source_cell.value
            row_data.append(source_cell.value)
            
            if source_cell.fill and source_cell.fill.fill_type:
                target_cell.fill = PatternFill(
                    fill_type=source_cell.fill.fill_type,
                    start_color=source_cell.fill.start_color.rgb,
                    end_color=source_cell.fill.end_color.rgb
                )
        copied_data.append(row_data)
    
    p_es_wb.save(p_es_path)
    
    print(f"Data copied from '{mod_path}' to '{p_es_path}'.")
    print(f"First empty row detected in P_ES: {start_row}, value in Q: {first_empty_value}")
    print(f"Source rows copied: 13 to {end_row}.")
    print(f"Data pasted in '{p_es_path}' starting at row {start_row}.")
    print("Copied data:")
    for row in copied_data:
        print(row)
    print("-")


if __name__ == "__main__":
    MM = input("Enter MM (e.g., '01'): ")
    AA = input("Enter AA (e.g., '24'): ")
    
    mod_paths = [
        f"Modificados\\Mod_{MM}_{AA}_ES01_A_procesado.xlsx",
        f"Modificados\\Mod_{MM}_{AA}_ES05_A_procesado.xlsx",
        f"Modificados\\Mod_{MM}_{AA}_ES06_A_procesado.xlsx"
    ]
    
    p_es_paths = [
        "TODOS\\P_ES01.xlsx",
        "TODOS\\P_ES05.xlsx",
        "TODOS\\P_ES06.xlsx"
    ]
    
    for mod_path, p_es_path in zip(mod_paths, p_es_paths):
        copiar_datos_con_estilos_condicional(mod_path, p_es_path, MM, AA)

I checked the content of the cell at row 14000 & column Q and it returns None, but I can see data.
So I tried a random solution. I opened Excel where the data is pasted and saved the document again and after that I closed and ran the code again. This time it works, pasting in row 14650!
The problem is I don't want to save each document that doesn't work.
When I run the code again in files which I didn't save again, it pastes in row 14030 and row 14000 is not empty this time.

本文标签： Excel with cells empty when I copy and paste into another excel with pythonStack Overflow