Using Python to make Multiplication Tables in Excel

A really easy problem. Here’s the full code.

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
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.

Here’s another solution that I found on github.

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
			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.

Here’s a sample multiplication table for n = 18:


Posted in:

%d bloggers like this: