Pre-calculate Excel formulas when exporting data with python?

Question

The code is pulling and then putting the excel formulas and not the calculated data of the formula.

xxtab.write(8, 3, "=H9+I9")

When this is read in and stored in that separate file, it is stored as "=H9+I9" and not the actual value of what H9 + I9 is. Then when we are importing it back it still is not calculating what H9 + I9 actually is, but instead it is just re-pasting "=H9+I9" into excel.

The intended goal is to have the math done either during export, after it is exported but before it is imported to another file, or during import.

The code should not be just placing another math function/formula into the next excel sheet. It should be putting an answer to whatever was being read.

import xlsxwriter, string, csv, time, datetime, subprocess, os
import urllib, urllib2, requests, openpyxl, sys
import gspread

# grab files
workbook = '/path/to/the/WorkBook.xlsx'

# Setup the spreadsheet
print "Creating workbook"
book = xlsxwriter.Workbook(workbook, {'strings_to_numbers': True})

# set matrix
row = 0  # row number
col = 0  # Column letter A=0 B=1 C=2 D=3...Z=26 ect.
# var.write(row, col, value, format)

# Make the sheet
xxtab = book.add_worksheet('DB Tables')

#Example of some .write commands which write to xxtab. The variables are taken directly from excel and are being read in by xlsl reader. This is stored in another file and is read in by the for loop.
xxtab.write(8, 1, "T_ACCESSRULES")
xxtab.write(8, 3, "=H9+I9")
xxtab.write(8, 7, "=data_dbmetrics!B56")

# The for loop, reads in a .txt file and does multiple .write commands
with open('/path/to/my/file/interns_xxtables.txt', 'rb') as f:
    reader = csv.reader(f, delimiter='\t')
    for c, col in enumerate(reader):
        if(len(col) > 3):
            if (str(col[3]) == "bold"):
                xxtab.write(int(col[0]), int(col[1]), col[2], bold)
            elif (col[3] == "metric"):
                xxtab.write(int(col[0]), int(col[1]), col[2], metric)
            elif (col[3] == "title"):
                xxtab.write(int(col[0]), int(col[1]), col[2], title)
        else:
            xxtab.write(int(col[0]), int(col[1]), col[2])
f.close()

Sorry, I am new to Stack Overflow. I am not sure how else to describe this question. I don't know how to get what I want, I just know what I want is not being achieved with what I have written.

Answer 1

The intended goal is to have the math done either during export, after it is exported but before it is imported to another file, or during import.

XlsxWriter, the module shown writing the Excel file, doesn't evaluate the result of the formulas that it writes. If you calculate the result in your code you can add it when writing the formula:

xxtab.write(8, 3, "=H9+I9", 42)

This is explained in more detail in the Working with Formulas section of the XlsxWriter documentation.

None of the Python Excel file writing modules evaluate formulas. The only modules that could do that are ones that automate Excel such as XlWings.