CODEWITHSUNDEEP
python
Ashu007
Ashu007

Reputation: 795

How to write formula dynamically in excel in multiple cells using xlsxwriter module?

I have created excel workbook contains some sheets and I have added one sheet and I want to use a formula in that sheet taking values from another sheet, for this I'll have to use formula dynamically using a for loop but when I passed cell2 and cell3 as a variable inside the formula I am getting and error:

File "<ipython-input-26-a7cfb4c63c79>", line 8, in <module>
ws2.write_formula(0, 1, '=COUNTIFS(Deviation!%s:%s,"<=-15%")' %(cell2, cell3))

TypeError: not enough arguments for format string`

Here is the relevant code:

for cl in range(1, 32):
    cell2 = xl_rowcol_to_cell(1, cl)
    cell3 = xl_rowcol_to_cell(432, cl)
    ws2.write_formula(0, 1, '=COUNTIFS(Deviation!%s:%s,"<=-15%")' %(cell2, cell3))

Kindly help, how to achieve this

Upvotes: 3

Views: 972

Answers (1)

Patrick Artner
Patrick Artner

Reputation: 51683

Simplify your problem, elimitate the excel stuff and use the string formatting with print alone:

cell2 = "A1"
cell3 = "B22"

# TypeError: not enough arguments for format string
print('=COUNTIFS(Deviation!%s:%s,"<=-15%")' %(cell2, cell3))

Then look into the documentation - your string contains a '%' as literal character - this confuses python.

The documentation tells you to double up on % to print a literal one:

The conversion types are:
[snipp unimportant ones]
'%' -> No argument is converted, results in a '%' character in the result.

cell2 = "A1"
cell3 = "B22"

formatstr = '=COUNTIFS(Deviation!%s:%s,"<=-15%%")' %(cell2, cell3)
print(formatstr)

# ws2.write_formula(0, 1, formatstr) # use formatted string

Voilà. Fixed. Output: 

=COUNTIFS(Deviation!A1:B22,"<=-15%")

Easier would be using str.format or string interpolation (Python 3.x):

print('=COUNTIFS(Deviation!{}:{},"<=-15%%")'.format(cell2, cell3) )
print(f'=COUNTIFS(Deviation!{cell2}:{cell3},"<=-15%%")') # python 3

Upvotes: 3

Related Questions