CODEWITHSUNDEEP
pythonexcelxlsxwriter
RG S
RG S

Reputation: 33

xlsxwriter Formula using python variables and dynamic cell referencing

I am trying to create a batch of xls files to feed into a Monte Carlo simulation. I am using xlsxwriter but I want my fourth column to use some python variables and some cell referencing from the sheet. Any idea how I can do this? For example, my formula in the fourth column would be much simpler if I could used the stored python version but instead I hard coded it all in.

In the fourth column formula, Instead of 5 I want to insert a random variable t I have already calculated in python

import numpy as np 
import xlsxwriter

#travel time as uniform (days)
low=4
high=8
t=np.random.uniform(low,high,size=1)


# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('demo.xls')
worksheet = workbook.add_worksheet()

# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 20)

# Write headers
#First column
worksheet.write('A1', 'DAYD')
#days = range(1,13)
worksheet.write_column('A2', range(1,13))
worksheet.write_column('A14', range(1,13))
worksheet.write_column('A26', range(1,13))
worksheet.write_column('A38', range(1,13))
worksheet.write_column('A50', range(1,16))


#Second Column
#crop=['TA' for i in range(64)]
worksheet.write('B1', 'CROP')
worksheet.write_column('B2', ('TA' for i in range(63)))

#Third Column
worksheet.write('C1', 'QUAL')
worksheet.write_column('C2', ([2]*12))
worksheet.write_column('C14', ([3]*12))
worksheet.write_column('C26', ([4]*12))
worksheet.write_column('C38', ([5]*12))
worksheet.write_column('C50', ([1]*15))

#fourth Column
worksheet.write('D1', 'COL')
worksheet.write_array_formula('D2:D64', '{=42 +((124-42)/(1+((EXP(**(5)***A2:A64*(124-42)))*(124-(16.949*EXP((-0.025)*C2:C64))/((16.949*EXP((-0.025)*C2:C64)-42)))))}')

workbook.close()

Upvotes: 0

Views: 1241

Answers (1)

wirrell
wirrell

Reputation: 60

Use string formatting syntax to add the variable into the string you are writing.

E.g. assuming t is the random variable you want to add and that is it a float:

formula_string = '{=42 +((124-42)/(1+((EXP(%f*A2:A64*(124-42)))*(124-(16.949*EXP((-0.025)*C2:C64))/((16.949*EXP((-0.025)*C2:C64)-42)))))}' % t

worksheet.write_array_formula('D2:D64', formula_string)

See also String formatting in python 2.7

If you want a different random variable in each cell then you can do

for i in range(2,65):
    t = np.random.uniform(low,high,size=1)
    formula_string = '{=42 +((124-42)/(1+((EXP(%f*A2:A64*(124-42)))*(124-(16.949*EXP((-0.025)*C2:C64))/((16.949*EXP((-0.025)*C2:C64)-42)))))}' % t
    location = 'D%d' % i
    worksheet.write_array_formula(location, formula_string)

Upvotes: 0

Related Questions