CODEWITHSUNDEEP
python-2.7formulaxlsxwriter
Stephanie
Stephanie

Reputation: 45

Python xlsxwriter: Adjust excel reference cells in loop when creating formula

I'm trying to include a formula column in an excel spreadsheet written in a loop. However, the formula is copying to each of the cells exactly the same.

The script below will produce the formula =IF(E2>0,(G2+H2)/E2,0) for all the cells. Is there a way to get it to update each row so that, for example, in row 16 the column will read =IF(E16>0,(G16+H16)/E16,0)?

for iRow, stVa in enumerate(dLocP):

    stVb,stVc,stVd = dLocP[stVa]

    Cworksheet.write(iRow + 1,0,stVa)
    Cworksheet.write(iRow + 1,1,stVb)
    Cworksheet.write(iRow + 1,2,stVc)
    Cworksheet.write(iRow + 1,3,stVd)
    Cworksheet.write_formula(iRow + 1, 4, '=IF(E2>0,(G2+H2)/E2,0)')

Thank you!

Upvotes: 0

Views: 2389

Answers (1)

jmcnamara
jmcnamara

Reputation: 41584

XlsxWriter has some functions for creating cell references:

from xlsxwriter.utility import xl_rowcol_to_cell

cell = xl_rowcol_to_cell(1, 2)  # C2

However, in this case it would probably be easier just to do simple string formatting:

for row_num in range(4):
    formula = '=IF(E%d>0,(G%d+H%d)/E%d,0)' % tuple([row_num + 1] * 4)

Or with new style format():

for row_num in range(4):
    formula = '=IF(E{}>0,(G{}+H{})/E{},0)'.format(*([row_num + 1] * 4))

In either case the output would look like this:

=IF(E1>0,(G1+H1)/E1,0)
=IF(E2>0,(G2+H2)/E2,0)
=IF(E3>0,(G3+H3)/E3,0)
=IF(E4>0,(G4+H4)/E4,0)

Update: Added working example like original above:

for iRow, stVa in enumerate(dLocP):

    stVb,stVc,stVd = dLocP[stVa]

    Cworksheet.write(iRow + 1,0,stVa)
    Cworksheet.write(iRow + 1,1,stVb)
    Cworksheet.write(iRow + 1,2,stVc)
    Cworksheet.write(iRow + 1,3,stVd)

    formula = '=IF(E{}>0,(G{}+H{})/E{},0)'.format(*([iRow + 2] * 4))
    Cworksheet.write_formula(iRow + 1, 4, formula)

Upvotes: 2

Related Questions