1

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.

Mulon
  • 61
  • 6

2 Answers2

1

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.

Rasmus
  • 136
  • 8
  • This does not work, unffortunatelly, same problem. I've tried to use openpyxl without ExcelWriter and the same thing happened. The sheet works fine before I run the code – Mulon Sep 22 '21 at 13:54
  • Could you provide the worksheets with some sample data? – Rasmus Sep 22 '21 at 14:42
  • I kinda figured out ! You were right, the corruption is caused by something on the sheets. I tried a sample with the same data and format, but without images and etc, and it worked – Mulon Sep 22 '21 at 14:48
  • The problem now is that I can't identify what is causing the corruption – Mulon Sep 22 '21 at 14:48
  • I will try to post a sample, but the data is a bit confidential – Mulon Sep 22 '21 at 14:49
  • I don't need the specific datasets, but rather what could be causing the corruption. As I mentioned, pictures, external connections, comments, text boxes, pivots, shapes etc. will all cause this issue. One way to deal with it, is to load your data to a csv file and then import it to the excel document using the "Get Data" column. From here, build a pivot or load it as a table in your desired position. – Rasmus Sep 22 '21 at 14:57
  • I figured that this is probably happening because my file is a xlsm. – Mulon Sep 23 '21 at 12:59
  • Is that a thing ? – Mulon Sep 23 '21 at 13:00
  • Also, I tried your suggestion, and it did not work because there are many tabs – Mulon Sep 23 '21 at 13:17
0

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.

Mulon
  • 61
  • 6