open - write excel with python

¿Cómo escribir en un archivo de Excel existente sin romper fórmulas con openpyxl?

Question

Cuando escribe en un archivo excel de Python de la siguiente manera:

import pandas
from openpyxl import load_workbook

book = load_workbook(''Masterfile.xlsx'')
writer = pandas.ExcelWriter(''Masterfile.xlsx'') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_filtered.to_excel(writer, "Main", cols=[''Diff1'', ''Diff2''])

writer.save()

Las fórmulas y los enlaces a los gráficos que se encuentran en las hojas existentes se guardarán como valores.

¿Cómo sobrescribir este comportamiento para preservar las fórmulas y enlaces a los gráficos?

Answer 1

Aquí abordo la parte de "preservar las fórmulas" de la pregunta solamente.

Intenté usar Openpyxl 1.8, que sí leí las fórmulas con éxito, pero cuando intenté guardar una copia se rompió. (La rotura parecía estar relacionada con los estilos, no con las fórmulas).

En cualquier caso, lo que recomiendo (hasta que openpxyl llegue un poco más lejos) es asignar las fórmulas a un nuevo objeto xlsxwriter.Workbook. He tenido éxito usando ese módulo para crear nuevos libros de trabajo xlsx (con formato y fórmulas), y sin saber qué tan bien se traducirán los formatos del objeto openpyxl al xlsxwriter, creo que será una solución viable para preservar al menos las formulas

Ahora, hacer esto (lo que yo quería hacer y hacer) no es muy simple debido a las fórmulas compartidas . Tuve que escribir una herramienta que ''comparte'' estas fórmulas compartidas, las transpone y las aplica a cada celda que se refiere a ellas.

Uno podría pensar primero que este enfoque crea ineficiencias al agregar un conjunto de fórmulas donde anteriormente solo había referencias a una fórmula existente. Sin embargo, traté de escribir estas fórmulas ''redundantes'' con xlsxwriter y luego volver a leer esa hoja con openpyxl nuevamente. Descubrí que las fórmulas nuevamente se leían como compartidas, por lo que xlsxwriter o la aplicación de Excel está haciendo esta optimización. (Uno podría fácilmente descubrir cuál, por supuesto, aún no lo he hecho).

Estaría encantado de publicar mi solución para desharing y transposición si sería útil si hay demanda; actualmente está integrado en un módulo más grande y tendría que crear una versión independiente. Sin embargo, en términos generales, utilicé la herramienta yarda de maniobras en el tokenizador discutido en la respuesta de ecatmur a esta pregunta para analizar la fórmula, que es la parte más difícil de transponerlas (que por supuesto debes hacer si quieres inferir cuál es la fórmula compartida se verá como en otra ''célula huésped'').

Answer 2

El problema de la fórmula se ha resuelto aquí

ejecuta esto para obtener la última versión

hg clone https://bitbucket.org/ericgazoni/openpyxl
cd openpyxl/
hg up 1.8
python setup.py develop

Answer 3

En excel:

  Home --> Find & Select --> Replace

  Replace All: "=" with "spam"

En Python:

  Run python script to update excel sheets

En excel:

  Replace All: "spam" with "="

Answer 4

Openpyxl 1.7 contiene varias mejoras para manejar fórmulas para que se conserven al leer. Use guess_types=False para evitar que openpyxl intente adivinar el tipo para una celda y 1.8 incluye la opción data_only=True si quiere los valores pero no la fórmula.

Desea conservar gráficos en la serie 2.x.

Answer 5

Sé que este es un hilo más antiguo, pero me tomó un tiempo encontrar una solución: xlwings te permite escribir en una pestaña y conservar gráficos en otra.

El siguiente ejemplo abre un libro de trabajo existente, actualiza los datos en los que se basa un gráfico y los guarda como una nueva versión.

import xlwings as xw
import pandas as pd

#create DF
months = [''2017-01'',''2017-02'',''2017-03'',''2017-04'',''2017-05'',''2017-06'',''2017-07'',''2017-08'',''2017-09'',''2017-10'',''2017-11'',''2017-12'']
value1 = [x * 5+5 for x in range(len(months))]
df = pd.DataFrame(value1, index = months, columns = [''value1''])
df[''value2''] = df[''value1'']+5
df[''value3''] = df[''value2'']+5

#load workbook that has a chart in it
wb = xw.Book(''C://data//bookwithChart.xlsx'')

ws = wb.sheets[''chartData'']

ws.range(''A1'').options(index=False).value = df

wb = xw.Book(''C://data//bookwithChart_updated.xlsx'')

xw.apps[0].quit()