How to paste a dataframe over a pre-formatted Python worksheet?

Question

I have a template pre-formatted in Excel this way.

I would like to paste values from a dataframe into pandas on these cells, creating a new file formatted with values and keeping the original file. How do I do that?

Answer 1

import pandas as pd
from openpyxl import load_workbook

df = pd.DataFrame(data={'Dados1': ['Ola','tudo','bom']})

#carrego o Excel com o template pré-formatado 'template.xlsx'
book = load_workbook('template.xlsx')

#defino o writer para escrever em um novo arquivo 'arquivo_editado.xlsx'
writer = pd.ExcelWriter('arquivo_editado.xlsx', engine='openpyxl')

#incluo a formatação no writer
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

#Escrevo com o .to_excel() do pandas
df.to_excel(writer,'Sheet1')

# para escrever só os valores em um lugar específico:
df.to_excel(writer, 'Sheet1', startrow=1, startcol=1, header=False, index=False)

writer.save()

Sources:

https://xlsxwriter.readthedocs.io/working_with_pandas.html

https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas

Answer 2

Maybe if you add sheet= ' ' in book = load_workbook('template.xlsx') work, but I have not tested

that way:

book = load_workbook('template.xlsx', sheet = 'nome_da_aba')

in the same way as if you were going to read an Excel normally in pandas.

pd.read_excel('arquivo.xlsx', sheet = 'nome_da_aba', engine = 'openpyxl')