Excel Multiplication Table Maker

Question

I did the following exercise from here to try out the openpyxl module for generating excel files:

Create a program that takes a number N from the command line and creates an NxN multiplication table in an Excel spreadsheet.

Row 1 and column A should be used for labels and should be in bold

Here is the code:

multiplication_table.py

"""
Makes a Excel mulitplication table with user input.
e.g. input = 4
Output in Excel should look like this:
 A B C D E -> column
1 1 2 3 4 -> titel column in bold (same for row)
2 1 1 2 3 4
3 2 2 4 6 8
4 3 3 6 9 12
5 4 4 8 12 16
r
o
w
"""
import sys
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font
def valid_input(input_string: str) -> bool:
 """Checks if input is valid to use for the excel file"""
 return input_string.isdigit() and int(input_string) >= 1
def get_number_from_user() -> int:
 """Asks user until he puts a valid input string."""
 while True:
 input_string: str = input("Enter positive number to make"
 " Excel chart\n")
 if valid_input(input_string):
 return int(input_string)
def get_max_number() -> int:
 """
 Asks from the command line or the user to get the max number for
 the excel file
 """
 if len(sys.argv) > 1:
 input_string: str = sys.argv[1]
 if valid_input(input_string):
 return int(input_string)
 return get_number_from_user()
def make_label_column(sheet, max_number: int, font: Font):
 """Make label column containing enumeration until max_number and
 variable font"""
 for number in range(1, max_number + 1):
 sheet['A' + str(number+1)].font = font
 sheet['A' + str(number+1)] = number
def make_label_row(sheet, max_number: int, font: Font):
 """Make label row containing enumeration until max_number and
 variable font"""
 for number in range(1, max_number + 1):
 sheet[get_column_letter(number+1) + "1"].font = font
 sheet[get_column_letter(number+1) + "1"] = number
def make_multiplication_field(sheet, max_number: int):
 """Make a field in excel with max_number*max_number elements"""
 for num_x in range(1, max_number + 1):
 for num_y in range(1, max_number + 1):
 sheet[get_column_letter(num_x + 1)
 + str(num_y+1)] = num_x*num_y
def save_workbook_excel_file(workbook):
 """Trys to save created data to excel file"""
 try:
 workbook.save('result.xlsx')
 except PermissionError:
 print("No permission to save file.")
def make_excel_table():
 """Main loop to generate excel multiplication file"""
 workbook = openpyxl.Workbook()
 sheet = workbook['Sheet']
 max_number: int = get_max_number()
 font_with_bold = Font(name='Times New Roman', bold=True)
 make_label_column(sheet, max_number, font_with_bold)
 make_label_column(sheet, max_number, font_with_bold)
 make_multiplication_field(sheet, max_number)
 save_workbook_excel_file(workbook)
if __name__ == "__main__":
 make_excel_table()

Let me know what you think about the code. Is it good to read? What would you improve?

I tried the program with big numbers and it gets rather slow. Is there a way to fill the rows more efficiently?

Answer 1

Run your comments and any UI text through spellcheck. In particular, your problem statement had a few errors in it.

for number in range(1, max_number + 1):
 sheet['A' + str(number+1)].font = font
 sheet['A' + str(number+1)] = number

Is this not equivalent to:

for col in range(1, max_number + 1):
 ind = f'A{col+1}'
 sheet[ind].font = font
 sheet[ind] = col

Note that you should never call something "number". Call it what it actually does (it's a column index). Also, factor out common expressions to a variable. The same applies in your code elsewhere.

I browsed through the openpyxl.utils.cell package and couldn't find anything like this: I suggest that you write a utility function that accepts two zero-based coordinate integers and outputs a ready-to-use row/column cell reference.

• Sandro4912: In youre corrected example it should be sheet[ind] = col - 1. And thanks i didnt know about the f-strings

Answer 2

There's a bug which hides in the make_excel_table function. During refactoring I called by accident 2 times the column method and not the row method.

It should be:

def make_excel_table():
 ...
 font_with_bold = Font(name='Times New Roman', bold=True)
 make_label_column(sheet, max_number, font_with_bold)
 make_label_row(sheet, max_number, font_with_bold)
 make_multiplication_field(sheet, max_number)
 save_workbook_excel_file(workbook)