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.

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)