Python/Excel: Compare the given value with the sum

问题:

Excel Image 1

From "Excel Image 1", I am pulling data into python of Column A and Column B. The goal is to sum the values of Column B, and see whether it is higher or lower than the given sum of 1500. If it is higher or equal to 1500, then there needs to be nothing done. But, if it is lower, then adjust the values that has the word "change" beside them, such that the calculated sum becomes 1500 or more. In our case, the sum is 700, which is lower than 1500.

import xlrd
excel = '/Users/Bob/Desktop/'

wb1 = xlrd.open_workbook(excel + 'assignment2.xlsx')
sh1 = wb1.sheet_by_index(0)

colA,colB = [],[]
for a in range(3,sh1.nrows):
    colA.append(int(sh1.cell(a,0).value))
    colB.append(int(sh1.cell(a,1).value))
print(colA)
print(colB)

excel_sum=(sh1.cell_value(2,1))
print("Given Sum:", excel_sum)

calc_sum = sum(colB)
print(calc_sum)

if calc_sum >= excel_sum:
    print("Good")
#else:
#Need to adjust the values that has the words "change" beside them

Current Output:

[0, 1, 2, 3, 4]
[900, -400, -200, 300, 100]
Given Sum: 1500.0
700

Thanks!

回答1:

You know the delta - excel_sum - calc_sum. So scan column C for a change, and add it to column B.

else:
    for row in range(3, sh1.nrows):
        if sh1.cell(row, 2) == 'change':
            colB[row] = int(sh1.cell(row, 1)) + excel_sum - calc_sum
            break

Of course, I see you using xlrd but not xlwt, so I don't know how you're going to change the value. Do you?