I have the following problem, when I want to copy the data of an excel sheet and besides its formats, I only copy the data.
import pandas as pd
template = pd.read_excel('Template_sugerencia.xlsx', sheet_name='template')
writer = pd.ExcelWriter('Completo.xlsx', engine='xlsxwriter')
template.to_excel(writer, sheet_name='Recomendaciones', startcol=5, startrow=Recom_len, index=False)
writer.save()
Indeed, it is like that. The problem is how Pandas uses writer to Excel. In order to maintain the original formats, when writing for example a particular cell, Pandas should read it first and only update the flat data of it, this at least one of the writers , openpyxl supports it, and this is effectively done in the write_cells() method, but then Pandas applies its own styles to the cell. Avoiding or reconfiguring this from Pandas does not seem like an easy task.
An alternative solution is to write ourselves and not Pandas the dataframe . For example in the following way:
import pandas as pd
import numpy as np
from openpyxl import load_workbook
path="test.xlsx"
sheet="Prueba"
# Leemos un archivo Excel al que le configuramos estilos
wb = load_workbook(path)
ws = wb.get_sheet_by_name(sheet)
# Generamos un dataframe con datos aleatorios
df = pd.DataFrame(np.random.randint(0,100,size=(100, 2)), columns=list('AB'))
# Iteramos por filas y celdas de un dtaframe
r = 2
for row in df.itertuples():
cells = list(row)
c = 1
for cell in cells:
# Actualizamos la celda de la planilla
celldata = ws.cell(row = r, column = c)
celldata.value = cell
c += 1
r +=1
# Salvamos finalmente el archivo
wb.save(path)
This is a functional proof of concept. You should create in the same folder where you run the script, a file test.xlsx , give it the format you want, for example something like this:
Then execute the script and you should notice that the data is "updated" and the styles are maintained. It is important that you understand that this form only updates the data from a certain row and column that we tell you until the last row and column of dataframe , if in the original form the data have more columns or rows, we would not be controlling it, we should do and in any case eliminate these. This should also work for the case of formulas.
Beyond this answer, I told you that Pandas is very flexible when it comes to giving styles to the spreadsheets, analyze it, it might be better for you to give the styles directly from Pandas .