1
df = pd.DataFrame(df)
df.to_excel("Lista FATF.xlsx", index=False, startrow=1)

When the code runs everything that already is on the excel file vanishes, i don't want it to happen. I want to just add things to the excel file, not delete the ones that already are there.

Sávio Carvalho
  • 37
  • 2

3 Answers3

1

To do that you need to explicitly create an ExcelWriter in append mode :

with pd.ExcelWriter("path_to_file.xlsx", mode="a", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Sheet3")

An Excel file isn't a text file. It's a ZIP package containing multiple XML files. You can't just append data to a ZIP file or even an XML file. The file has to be opened, updated and then saved again.

When the ExcelWriter is created it's actually opening and reading the Excel file. The changes are made in memory and when it closes, the entire package is compressed and written back to disk.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
0

From my understanding of your question:

you can't append things to an excel file, you would need to load that file first and then make amendments.

what you can do, is once you load the original dataframe: simply concat the new one to the old:

Merged = pd.concat(original_dataframe, new_dataframe, ignore_index = True)

the concat function simply appends the new dataframe to the new one. if you want to do a merge then you need to use pd.merge.

CatDad
  • 114
  • 6
0

You can use xlwings to insert DataFrames to an existing excel file, e.g.:

import pandas as pd
import xlwings as xw

path = r"test.xlsx"

df = pd._testing.makeDataFrame()

wb = xw.Book(path)
ws = wb.sheets[0]

ws.used_range[-1:,:].offset(row_offset=1).value = df
wb.save(path)

Just specify the sheetname with wb.sheets["example"] and the location in the worksheet where the DataFrame should be inserted. For example, ws.used_range[-1:,:].offset(row_offset=1).value = df appends the DataFrame to the existing data and ws.range("B5").value = df inserts the data at cell B5.

mouwsy
  • 1,457
  • 12
  • 20