How to write to an existing excel file without breaking formulas with openpyxl?

Question

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?

Solution 1

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.

Solution 2

In excel:

  Home --> Find & Select --> Replace

  Replace All: "=" with "spam"

In python:

  Run python script to update excel sheets

In excel:

  Replace All: "spam" with "="

Solution 3

Here I address the "preserve the formulas" part of the question only.

I tried using openpyxl 1.8, which did successfully read the formulas, but when I tried to save a copy it broke. (The breakage appeared to be related to the styles, not the formulas.)

In any event, what I recommend (until openpxyl comes a tad further) is to map the formulas to a new xlsxwriter.Workbook object. I've had success using that module to create new xlsx workbooks (with formatting and formulas), and without knowing how well the formats will translate from the openpyxl object to the xlsxwriter one, I believe it will be a viable solution for preserving at least the formulas.

Now, doing this (which I wanted to and did myself) is NOT super simple because of shared formulas. I had to write a tool that 'de-shares' these shared formulas, transposes them, and applies them to each cell that refers to it.

One might first think that this approach creates inefficiencies by adding a bunch of formulas where previously there were just references to an existing formula. However, I tried writing these 'redundant' formulas with xlsxwriter and then reading that sheet back in with openpyxl again. I discovered that the formulas again were read in as shared, so either xlsxwriter or the Excel application itself is doing this optimization. (One could easily figure out which, of course; I just haven't yet.)

I'd be happy to post my solution for desharing and transposing if it would be helpful iff there's demand; currently it's integrated into a larger module and I'd have to create a standalone version. Generally speaking though, I used the shunting yard tool in the tokenizer discussed in ecatmur's response to this question to parse the formula, which is the hardest part of transposing them (which of course you have to do if you want to infer what the shared formula will look like in another 'host cell').

Solution 4

I know this is an older thread, but it took me a while to find a solution - xlwings allows you to write to one tab and retain charts on another.

The follow example opens an existing workbook, updates the data a chart is based on, and saves as a new version.

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

View more solutions

Comments

[Adam Morris]

With data_only=False, formulas should be preserved with openpyxl's save: book.save('filename.xlsx'). I'm not sure how pandas works, but formula's are written differently from cell values, so if they're supported you'll most likely need to identify these cells in some way.

[Adam Morris]

Correct - if the cell has an attribute t='shared' and a 'ref':'A1:B3', then excel will copy this shared formula when loading. Openpyxl 1.8.3 should preserve shared formulas.

[Tinkinc]

Anybody know why this doesnt work? It worked 2 days ago for me and now it doesnt do anything but launch the excel and close it.

[virtualxtc]

@Tinkinc did your copy of Excel expire?