openpyxl: Formulas read as blanks in some (key use) cases

My code downloads a .xlsx file from google drive (using pydrive), finds some blank cells with pandas, and fills in those blank cells with openpyxl.

When I open the openpyxl altered file, everything looks great. However, when I use the pandas read_excel function, all cells that have equations are read as blanks. I suspect the issue is with openpyxl because when I preview the file on drive, those cells are blank. There is no issue with a file that openpyxl hasn't touched.

It looks like my issue is very similar to this one, but since my objective is just to leave the formulas untouched (I only want to fill blank cells), I don't really want to parse the formulas and I'm not really sure how or if to apply Felipe's fix.

I'd like to be able to download the file to plot it with bokeh, and users and python will both be editing the program, so I'd really like pandas to be able to read the equations whether its a user modified file or an openpyxl modified file. The equations in the file were click and drag "shared equations", and I'd like to keep it that way if possible so ideally I'd like to avoid using data_only=True. I tried specifying data_only=False, but this didn't appear to change anything.

I'm using openpyxl 2.3.5 2.4, and I keep excel closed when code is running.

Versions of the file before and after openpyxl modification are available here.

My code is here, all openpyxl code is isolated to : # Import Libraries import datetime import imp import os import pandas as pd from openpyxl import load_workbook from itertools import islice # Relative imports for bokeh interaction

dl = imp.load_source('downloader', os.getcwd() +
                      '/Project/downloader.py')
gdu = imp.load_source('googledriveutils', os.getcwd() +
                      '/Project/googledriveutils.py')
remove_file = gdu.remove_file
find_folderid = gdu.find_folderid
get_file_list = gdu.get_file_list


# Define constants
COL_LABEL = 'nProbe - '
# TODO: ORP PROBE: REVISE THIS DATE when orp probe is added
IGNORE_BEFORE = pd.to_datetime('5.24.2016')
PROBE_DICT = {'DO (mg/L)': 'DO mg/L',
              'pH': 'pH',
              'NH4+ (mgN/L)': 'Ammonium',
              'ORP (mV)': 'ORP mV'}
TS = 'nTimestamps'


def save_to_workbook(newval,
                     date,
                     header,
                     rows_to_skip=12,
                     wbname='temp.xlsx',
                     sheet_name='Reactor Data'):
    wb = load_workbook(wbname)
    ws = wb[sheet_name]
    for cell in ws[rows_to_skip+1]:
        # TODO: Error if header isn't found
        if cell.value == header:
            colno = cell.col_idx
            break

    for row in ws.iter_rows(min_row=rows_to_skip+1, min_col=1, max_col=1):
        for cell in row:
        # TODO: Error if date isn't found
            if cell.value == date:
                rowno = cell.row
                break

    ws.cell(row=rowno, column=colno).value = newval
    wb.save(wbname)


    return df





def find_r1masterfile():
    # Navigate through the directories
    wlab_fid = find_folderid('Winkler Lab', 'root')
    kp_fid = find_folderid('KathrynsProjects', wlab_fid)
    amxrct_fid = find_folderid('Anammox Reactor', kp_fid)
    trials_fid = find_folderid('Reactor Trials', amxrct_fid)
    # List files in directory
    file_list = get_file_list(trials_fid)
    for afile in file_list:
        if afile['title'] == 'AMX RCT.xlsx':
            # Return the file we asked for
                return afile
        # TODO: error if there was no file with that name


def save_r1masterfile(csv, rows_to_skip=12, filename='temp.xlsx', sheet_name='Reactor Data'):
    # Get the file we want
    master_file = find_r1masterfile()
    try:
        master_file.GetContentFile(filename)
    except Exception, e:
        print "Warning: Something wrong with file R1 Master File."
        print str(e)
        # TODO: add an email alarm to responsible user

    if csv:
        return master_file
    else:
        # convert to dataframe
        wb = load_workbook(filename, data_only=True)
        ws = wb[sheet_name]
        print ws["B14"].value
        data = ws.values
        data = list(data)[rows_to_skip:]
        cols = list(data[0])
        del cols[0]
        del data[0]
        idx = [r[0] for r in data]
        data = (islice(r, 1, None) for r in data)
        df = pd.DataFrame(data, index=idx, columns=cols)
        print df.dropna(how='all')
        remove_file(filename)
        return df


def upload_r1masterfile(filename='temp.xlsx'):
    # Get the file we want
    master_file = find_r1masterfile()
    try:
        master_file.SetContentFile(filename)
        master_file.Upload()
    except Exception, e:
        print "Warning: Something wrong with file R1 Master File."
        print str(e)
        # TODO: add an email alarm to responsible user


def populate_r1masterfile(rows_to_skip=12, filename='temp.xlsx'):
    # Get the R1 master file as a file
    save_r1masterfile(True)
    # Convert the juicy stuff to a dataframe
    masterdf = pd.read_excel(filename,
                             sheetname='Reactor Data',
                             encoding="utf-16",
                             skiprows=rows_to_skip,
                             sep='t',
                             index_col='Date',
                             keep_default_na=False,
                             na_values=['-1.#IND', '1.#QNAN', '1.#IND',
                             '-1.#QNAN', '','N/A', '#NA', 'NA'
                             'NULL', 'NaN', '-NaN', 'nan', '-nan'])
    # Find what we will populate with probe data
    # Find timestamps
    ts_columns = [col for col in masterdf.columns if TS in col]
    tsdf = masterdf[ts_columns]
    # Find probes, ignore before given date
    probe_columns = [col for col in masterdf.columns if COL_LABEL in col]
    probedf = masterdf[probe_columns]
    probedf = probedf[masterdf.index > IGNORE_BEFORE]
    # Find Indices and column labels of blank values
    stackdf = probedf.stack(dropna=False)
    empty = stackdf[stackdf.isnull()].index.tolist()

    # For each blank look for the probe, time & date of cycle, and return val
    for each in empty:
        probe, time = each[1].split(COL_LABEL)
        time = tsdf.loc[each[0], time+TS]
        ts = each[0]+pd.DateOffset(hour=time.hour, minute=time.minute)
        val = dl.get_val_from(1, ts, PROBE_DICT.get(probe))
        probedf.set_value(each[0], each[1], val)
        # Save that value to the workbook
        save_to_workbook(val, each[0], each[1])
    upload_r1masterfile()
    print 'Master file updated. ' + str(datetime.datetime.now())
    remove_file('temp.xlsx')
    return probedf

UPDATE

I modified my code as per Charlie's suggestions (updated above). But I'm still getting Nones in the dataframe. To provide a more specific example, why is it when I run this code:

from openpyxl import load_workbook

wb = load_workbook('AMX RCT mod.xlsx', data_only=True)
ws = wb['Reactor Data']
print 'Value of B14 Formula is: ' + str(ws["B14"].value)

With this file, I get back?:

Value of B14 Formula is: None

Is there a workaround?