0

enter image description hereI would like to perform a calculation with XLWT I'm new to coding and bit confused with using for loop to repeat the formula in following columns, please help me understanding the for loop function for the below calculation

Please refer the image, I need a for loop to fill rest of the columns, so that I do not have to type 'w_sheet.write(1,2,xlwt.Formula('A2-B2'))' multiple times

import xlrd
import xlwt
from xlutils.copy import copy

wb = xlrd.open_workbook('Subtract.xls')
rb = copy(rb)
w_sheet = wb.get_sheet(0)
w_sheet.write(1,2,xlwt.Formula('A2-B2'))
w_sheet.write(2,2,xlwt.Formula('A3-B3'))
w_sheet.write(3,2,xlwt.Formula('A4-B4'))
w_sheet.write(4,2,xlwt.Formula('A4-B4'))

wb.save('Subtract.xls')
Raghul
  • 57
  • 1
  • 8
  • Did you mean that the last write should be: `write(4,2,xlwt.Formula('A5-B5'))`? – quamrana Oct 01 '19 at 09:19
  • Hi, Thanks for your reply, actually I need a for loop to enter the formulas in the following columns, I've added an image, please refer the screenshot. Any help will be appreciated. – Raghul Oct 01 '19 at 10:22
  • Please update your question with the example data formatted in a code block, not a link to some image which may disappear in the future. – quamrana Oct 01 '19 at 10:36
  • Also see answers to this question: https://stackoverflow.com/questions/2725852/writing-to-existing-workbook-using-xlwt for examples of using the `write()` method. – quamrana Oct 01 '19 at 10:39
  • Possible duplicate of [writing to existing workbook using xlwt](https://stackoverflow.com/questions/2725852/writing-to-existing-workbook-using-xlwt) – rajah9 Oct 01 '19 at 11:23

1 Answers1

0

Hi try and understand that :D

import xlrd
import xlwt
from xlutils.copy import copy
import os

file_path = "test.xls"

rb = xlrd.open_workbook(file_path, formatting_info=True)
wb = copy(rb)
w_sheet = wb.get_sheet(0)
# For each sheet, do operation
for sheet in rb.sheets():
    # For each row in actual sheet, row += 1 at each operation
    for row in range(sheet.nrows - 1):
        # Set index for operation and result
        index = row + 2
        # Create operation
        operation = 'A' + str(index) + '-B' + str(index)
        # Use operation
        w_sheet.write(index-1, 2, xlwt.Formula(operation))
        # Print for you can understand what it do
        print ("index = " + str(index) + "  operation = " + operation)
# Save in file_path.out.xls
wb.save(file_path + '.out' + os.path.splitext(file_path)[-1])
Timothée Dautrême
  • 365
  • 3
  • 13