3
\$\begingroup\$

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?

\$\endgroup\$

2 Answers 2

4
\$\begingroup\$

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.

\$\endgroup\$
1
  • \$\begingroup\$ In youre corrected example it should be sheet[ind] = col - 1. And thanks i didnt know about the f-strings \$\endgroup\$
    – Sandro4912
    Dec 12, 2018 at 18:31
1
\$\begingroup\$

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)
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.