CODEWITHSUNDEEP
pythonexcelformulacell
lb2019
lb2019

Reputation: 65

How to write Excel formula in cells in python

I try to add Excel formula in some columns within a loop. To make it short : I have an excel spreadsheet in which I generate successfully random amount in the 2 first colums (A and B) for 3000 lines thanks to a loop. Now I would like to write in column C the formula which multiply A with B and in the column D the addition of A + C within the same loop. Thank you very much for your help in advance ! 

import xlsxwriter
import os
import random


path = r"/Users/MAC/Desktop/test.xlsx"
workbook = xlsxwriter.Workbook(path)
worksheet = workbook.add_worksheet("formula")
worksheet.write(0, 0, "Amount")
worksheet.write(0, 1, "Interest")
worksheet.write(0, 2, "Fees")
worksheet.write(0, 3, "Total Amount")

for n in range(1, 3000):
    worksheet.write(n, 0, random.randint(1,150000))
    worksheet.write(n, 1, random.randint(1, 100))
    # worksheet.write(n,2, a*b/100) ??????
    # worksheet.write(n,3, a+c) ??????
workbook.close()

Upvotes: 1

Views: 7632

Answers (1)

abc
abc

Reputation: 11929

You can use worksheet.write_formula.
Example:

for n in range(1, 10):
    worksheet.write(n, 0, random.randint(1,150000))
    worksheet.write(n, 1, random.randint(1, 100))
    cell1 = xl_rowcol_to_cell(n,0)
    cell2 = xl_rowcol_to_cell(n,1)
    worksheet.write_formula(n, 2, f'=({cell1}*{cell2})/100')
workbook.close()

where xl_rowcol_to_cell converts a cell from coordinates to string.

Upvotes: 4

Related Questions