How to write to an existing excel file without breaking formulas with openpyxl?
When you write to an excel file from Python in the following manner:
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()
Formulas and links to charts which are in the existing sheets, will be saved as values.
How to overwrite this behaviour in order to preserve formulas and links to charts?
Answer
Openpyxl 1.7 contains several improvements for handling formulae so that they are preserved when reading. Use guess_types=False to prevent openpyxl from trying to guess the type for a cell and 1.8 includes the data_only=True option if you want the values but not the formula.
Want to preserve charts in the 2.x series.