Now, we need to create an n by n multiplication table in excel using a python program, where n is an arbitrary, positive integer. We will do this using the openpyxl module. Firstly, we’ll initialise our notebook and set our current sheet to Sheet, which is the default active sheet.

wb = openpyxl.Workbook()
sheet = wb['Sheet'] # or wb.active
n = int(input('enter n| '))

For the purposes, a ‘bold’ font style object has also been created. I will assign this to the row and column headings later.

for i in range(2, 2+n):
sheet['A' + str(i)].value = i-1
sheet['A' + str(i)].font = boldFont
sheet[get_column_letter(i) + '1'].value = i-1
sheet[get_column_letter(i) + '1'].font = boldFont

Above, all I’ve done is create row and column headings for all integers upto and including n, with each heading being written in bold characters. Now, time to create the table.

for i in range(2, sheet.max_row + 1):
for j in range(2, sheet.max_column+1):
sheet[get_column_letter(j) + str(i)].value = sheet[get_column_letter(j) \
+ '1'].value * sheet['A' + str(i)].value

Making the table is really simple. What I’ve done is iterate over each row heading, and then over each column heading inside the row, then find the product of these two and assign it to the required cell. You could also do this using excel formulas, but I think this solution is reasonably efficient.

for rowNum in range(1, number+2):
for colNum in range(1, number+2):
if rowNum==1 and colNum==1:
sheet.cell(row=rowNum, column=colNum).value=''
elif rowNum==1:
sheet.cell(row=rowNum, column=colNum).value = colNum-1
sheet.cell(row=rowNum, column=colNum).font = boldFont
elif colNum==1:
sheet.cell(row=rowNum, column=colNum).value=rowNum-1
sheet.cell(row=rowNum, column=colNum).font = boldFont
else:
sheet.cell(row=rowNum, column=colNum).value = (rowNum-1)*(colNum-1)

It is shorter, but I don’t think it’s that elegant due to the usage of if/else statements. But that’s just personal bias. Instead of iterating over headings and content cells differently, the author here iterates every single cell (including 0,0) in one for loop.

You must log in to post a comment.