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

Cuando escribe en un archivo de 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 cuadros que se encuentran en las hojas existentes, se guardarán como valores.

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

Openpyxl 1.7 contiene varias mejoras para el manejo de 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 desea los valores pero no la fórmula.

Quiere preservar los gráficos en la serie 2.x.

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 "=" 

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

Intenté usar openpyxl 1.8, que leyó con éxito las fórmulas, pero cuando intenté guardar una copia se rompió. (La ruptura 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) es asignar las fórmulas a un nuevo objeto xlsxwriter.Workbook. He tenido éxito al usar 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 de xlsxwriter, creo que será una solución viable para preservar al menos las fórmulas.

Ahora, hacer esto (lo que yo quería y yo mismo) NO es super simple debido a las fórmulas compartidas . Tuve que escribir una herramienta que “descompone” estas fórmulas compartidas, las transpone y las aplica a cada celda que se refiere a ellas.

Primero se podría pensar que este enfoque crea ineficiencias al agregar un montón de fórmulas donde antes solo había referencias a una fórmula existente. Sin embargo, intenté escribir estas fórmulas “redundantes” con xlsxwriter y luego leer esa hoja de nuevo con openpyxl de nuevo. Descubrí que las fórmulas nuevamente se leyeron como compartidas, por lo que o bien xlsxwriter o la aplicación Excel en sí están haciendo esta optimización. (Uno podría fácilmente averiguar cuál, por supuesto; todavía no lo he hecho).

Me complacería publicar mi solución para la eliminación y la transposición si fuera útil si hubiera demanda; actualmente está integrado en un módulo más grande y tendría que crear una versión independiente. En general, sin embargo, utilicé la herramienta de derivación en el tokenizador analizado en la respuesta de ecatmur a esta pregunta para analizar la fórmula, que es la parte más difícil de transponerlos (lo cual, por supuesto, debe hacer si desea inferir cuál es la fórmula compartida se verá como en otra ‘célula huésped’).

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 retener 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()