how to calculate a value from other fields in excel sheet using formulas

Question

I'm using xlrd package for python to generate my excel reports. for some fields, they are a calculations from other fields. I'm using

worksheet.write(1, 2,  '178', formatting )
worksheet.write(1, 3,  '100', formatting )

for the field (1,4) is the SUM of (1,2) and (1,3)

I can do that if I used : worksheet.write(1, 4, '=SUM(A1,B1)', formatting )

but in my case I'm using numbers (col, row) instead of letters (A1).

I tried chr(1)+':1' - chr(1)+':2'

but seems like the letters I'm getting don't reflect the numbers.

Any thoughts will be highly appreciated!

The solution I'm using for now is

value1 = '178'
value2 = '100'
worksheet.write(1, 2,  value1, formatting )
worksheet.write(1, 3,  value2, formatting )
worksheet.write(1, 4, value1+value2  , formatting )

but as you see I'm not using built in excel formulas and the excel user won't see where the value came from. like what was the calculation used?. exactly for finance department.

Thanks!

score 2 · Accepted Answer · edited May 23 '17 at 12:03

Here's a simple iterative version that should handle arbitrary column numbers, adapted from the C# code at this answer:

How to convert a column number (eg. 127) into an excel column (eg. AA)

def column_label(column_number):
    column_labels = []
    column_number = column_number + 1
    while column_number > 0:
        column_number, modulo = divmod(column_number - 1, 26)
        column_labels.append(string.uppercase[modulo])
    return ''.join(reversed(column_labels))

This takes a 0-indexed column number, such as those xlrd and xlwt use. I don't love the conversion to 1-indexed before building the names, but I need to think more about the algorithm before replacing it.

for this I had `import string` because it's not imported when python fired up! just a heads up! :) — mongotop, Nov 06 '13 at 18:51

score 1 · Answer 2 · answered Nov 06 '13 at 18:29

1

chr(65)=A in ASCII, so you may need to add 64 to your column. This will of course break if you're going beyond the 26th column.

answered Nov 06 '13 at 18:29

shapiromatron

569
6
11

1

I'd use `string.uppercase[col]`, if handling only numbers up to 25. But either should work. – Peter DeGlopper Nov 06 '13 at 18:31
this is a great solution! but `unfortunately` I'm going beyond 26th columns like `BA` – mongotop Nov 06 '13 at 18:31

how to calculate a value from other fields in excel sheet using formulas

2 Answers2