CODEWITHSUNDEEP
python-3.xxlsxwriter
jjfromnh
jjfromnh

Reputation: 119

XLSXWriter formula across more than one column

I'm trying to apply a simple multiplication formula (B1=B2*B3...C1=C2*C3) for every column in a spreadsheet.

I've reviewed the (fantastic) xlsxwriter documentation, as well as this similar question: XLSXWriter Apply Formula Across Column With Dynamic Cell Reference, but I can't get it to work across multiple columns. Going down a single column is easy using a loop and incrementing the value of the row number, but I'm having trouble applying the same concept across a row (multiple columns).

I'm using xl_rowcol_to_cell() to get the A1 notation of the final cells in the spreadsheet (because it's going to be different every time), but I can't figure out how to plug those variables into a workable formula or array formula.

cell1 = xl_rowcol_to_cell(1, total_columns - 1)
cell2 = xl_rowcol_to_cell(2, total_columns - 1)
cell3 = xl_rowcol_to_cell(3, total_columns - 1)

I was thinking something like this:

worksheet.write_array_formula('B1:%s', '{=PRODUCT(B2:%s,B3:%s)}' % (cell1, cell2, cell3))

But I keep having problems with string formatting, and when I try just plugging in actual values instead of using string formatting I get garbage out of Excel.

Any help would be appreciated.

Upvotes: 1

Views: 1555

Answers (1)

jjfromnh
jjfromnh

Reputation: 119

I misunderstood how array formulas are used. The question I linked had enough information to solve my problem:

for col_num in range(1, total_columns):
    worksheet.write_formula(1, col_num, '=B2:%s*B3:%s' % (cell2, cell3))

Upvotes: 3

Related Questions