Write multiple formulas to excel sheet with xlsxwriter in while loop

user_3816829 0
Viewed 207

I am trying to populate a formula in multiple cells of a sheet using xlsxwriter with Python 2.7. I need to pass in the formula as a string that includes both single and double quotes as the following: 'F2','=IF(E2=MIN($E$2:$E$545),E2,"")' where the row values are stored in variables in the script as formularowcount and rowcount.

I am getting this exception: AttributeError: 'NoneType' object has no attribute 'group'.

The code:

while rowcount > formularowcount:
profiledatasheet.write_formula("'F"+str(formularowcount)+"','=IF(E"+str(formularowcount)+"=MIN($E$2:$E$"+str(rowcount)+"),E"+str(formularowcount)+",\"""\")'")

I've tried to store the string itself and pass it in, but get the same exception:

formulavalue = "'F"+str(formularowcount)+"','=IF(E"+str(formularowcount)+"=MIN($E$2:$E$"+str(rowcount)+"),E"+str(formularowcount)+",\"""\")'"
        profiledatasheet.write_formula(formulavalue)

However, if I print the string out in debug and paste the output into the debug console, it works and returns 0:

print(formulavalue)
'F2','=IF(E2=MIN($E$2:$E$545),E2,"")'

profiledatasheet.write_formula('F2','=IF(E2=MIN($E$2:$E$545),E2,"")')
0

Any ideas as to what is wrong with my syntax?

xlsxwriter python
1 Answers

Try this (if you use 3)

profiledatasheet.write_formula(
    f'F{formularowcount}',
    f'=IF(E{formularowcount}=MIN($E$2:$E${rowcount}),E{formularowcount},"")'
)

For python 2 something like

profiledatasheet.write_formula(
'F{0}'.format(formularowcount),
'=IF(E{0}=MIN($E$2:$E${1}),E{0},"")'.format(formularowcount, rowcount)

)

user_13125530
Related