Excel file gets corrupted after bulking data with panda

Question

Ok so apparently this is a very simple task, but for some reason it's giving me trouble.

Here's the code:

    marcacoes = pd.read_excel(file_loc, sheet_name="MONITORAMENTO", index_col=None, na_values=['NA'], usecols ="AN")
    x=0
    while x < len(statusclientes):
        if (statusclientes.iloc[x][0] == "Offline"):
            p=marcacoes.iloc[x][0]
            p=p+1
            marcacoes.iat[x,0]= p
            tel_off.append(telclientes.iloc[x][0])
        if (statusclientes.iloc[x][0] == "Indefinido"):
            tel_off.append(telclientes.iloc[x][0])
        x=x+1
    y=0
    with pd.ExcelWriter(file_loc,mode='a',if_sheet_exists='replace') as writer:  
        marcacoes.to_excel(writer, sheet_name='MONITORAMENTO',startcol=37,startrow=5)
        writer.save()

But the problematic part is:

with pd.ExcelWriter(file_loc,mode='a',if_sheet_exists='replace') as writer:  
        marcacoes.to_excel(writer, sheet_name='MONITORAMENTO',startcol=37,startrow=5)
        writer.save()

Since the code runs fine without it. Those specific lines are supposed to dump the dataframe "marcacoes" on an existing excel file, replacing another existing column on the file, but whenever I run this code, that existing excel file becomes corrupted.

I'm pretty sure I'm missing some fundamentals on pandas here, but I cn't find where on documentation this issue is addressed.

EDIT:

I've tried the following code:

wb = openpyxl.load_workbook(file_loc)
        ws = wb['MONITORAMENTO']
        startcol = 37
        startrow = 5
        k=0
        while k < len(marcacoes):
            ws.cell(startrow, startcol).value = marcacoes.iloc[k][0]
            startrow +=1
            k+=1
wb.save(file_loc)

but the same thing happens, now it's caused by the "wb.save(file_loc)" line.

Answer 1

Ok, so I figured it out.

The actual problem came from loading the workbook. documentation says that if you want to load xlsm files with macros on it you have to specify on the function argument.

wb = load_workbook(filename=file_loc, read_only=False, keep_vba=True)

Now it will save properly, without corrupting.