Excel file gets corrupted after bulking data with panda-CodePudding

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.

CodePudding user response：

You could try using openpyxl, as such:

from openpyxl import load_workbook

book = load_workbook(file_loc)
writer = pd.ExcelWriter(file_loc, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

marcacoes.to_excel(writer, sheet_name='MONITORAMENTO', startcol=37, startrow=5)
writer.save()

I have used this approach a couple of times and generally it yields good results.

Also make sure your starting fil isn't corrupted. The corruption error can also be caused by pictures, pivot tables, data validation or external connections.

CodePudding user response：

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.