12
\$\begingroup\$

Note: This is part of a set of demo applications I'm writing for colleagues to show we're not limited to VBA when dealing with Excel. Considering our IT challenges (no admin on-site & no local-admin privileges), this has to work on Portable Python without external libraries. Specifically, the Python 3.7.2(x64) shell from WinPython (on Windows 7 and Excel 2013) is used and while in theory we should be able to use WPPM to install external libraries, so far we're out of luck.

This is a haphazard solution NOT recommended to be used in the field! If you want to fasten a screw, use a screwdriver. Not a hammer. But sometimes you only have hammers in your toolbox.


Part of the reason I'm doing this is that we need to import/export our data from/to external sources in an ugly copy/paste style without proper information pipes. Jumping around in Excel, adding data to fields and doing something with that data. So that's the first thing I made a demo for.

Arguably it's not doing much useful yet, but it shows a lot more of the options available than a Hello, World example. Python has something with knights, so I jump around a bit following a hardcoded Knight's Tour, fill in some data, use a bit of formatting, create some calculations, it's nicely showing what it should.

Oh, and if you think this doesn't look so bad (it does), wait till the next demo.

I may need more functions, perhaps a couple of wrappers, but that seemed overkill at the moment. Enums may be good too, since I seem to do a lot of enumerating anyway. And more constants, though that tends to get messy. I've considered creating tour_ws() and calc_ws(), mainly to cut the code in chunks, but this would be a poor usage of functions. Perhaps passing the instance, worksheets or other objects around is a better idea, not particularly sure what that would look like though.

The pseudo-consts are in the same file since importing from files is bit of an issue with this configuration, it seems. I know I'm not supposed to do it like this.

As usual I'm mainly interested in doing it the right way, although that may not always be possible in this case. My naming is terrible, performance is already satisfactory. I'm not sure whether I should be handling my memory better or that the GC will take care of it. What I can't have, is the program halting its execution because the opened program (Excel) is still open.

knights_tour.py

import win32com.client

"""
Excellent Knight's Tour
"""

# (X, Y)
POSITIONS = [
    (8, 1),
    (7, 3),
    (6, 1),
    (8, 2),
    (7, 4),
    (5, 3),
    (4, 1),
    (2, 2),
    (1, 4),
    (3, 3),
    (2, 1),
    (1, 3),
    (3, 4),
    (2, 6),
    (1, 8),
    (3, 7),
    (2, 5),
    (1, 7),
    (3, 8),
    (4, 6),
    (5, 8),
    (6, 6),
    (4, 5),
    (5, 7),
    (7, 8),
    (8, 6),
    (6, 5),
    (7, 7),
    (8, 5),
    (6, 4),
    (5, 2),
    (4, 4),
    (3, 6),
    (1, 5),
    (2, 7),
    (3, 5),
    (1, 6),
    (2, 8),
    (4, 7),
    (5, 5),
    (6, 3),
    (7, 1),
    (8, 3),
    (7, 5),
    (8, 7),
    (6, 8),
    (5, 6),
    (4, 8),
    (6, 7),
    (8, 8),
    (7, 6),
    (8, 4),
    (7, 2),
    (5, 1),
    (4, 3),
    (3, 1),
    (1, 2),
    (2, 4),
    (3, 2),
    (1, 1),
    (2, 3),
    (4, 2),
    (5, 4),
    (6, 2)
]

# Only formulae accepting range notations are supported
# Oddly enough sending English names also works on Dutch locale
FORMULAE = [
    "AVERAGE",
    "AVEDEV",
    "MIN",
    "MAX"
]

# Offsets required to get to pre-defined characters
# chr(65) = A
# chr(72) = H
# chr(10+55) = chr(65)
CONST_A = 65
CONST_H = 72
OFFSET = 55


def range_format(formula, target, i):
    if i < 9:
        return "={0}('{1}'!{2}{3}:{4}{5})".format(
            formula, target, chr(CONST_A), i, chr(CONST_H), i)
    elif i < 10:
        return ""
    else:
        return "={0}('{1}'!{2}{3}:{4}{5})".format(
            formula, target, chr(i + OFFSET), 1, chr(i + OFFSET), 8)


def main():
    xls_instance = win32com.client.gencache.EnsureDispatch("Excel.Application")
    xls_instance.Visible = 1
    wb = xls_instance.Workbooks.Add()
    wb.ActiveSheet.Name = "Knight Tour"

    # Select default sheet (Excel 2013)
    # Optional. The sheet is already created
    # and as long as only one sheet exists
    # it can be targetted by referring to the instance
    # itself.
    tour_ws = wb.Worksheets("Knight Tour")

    # Width 2.86 by Height 18.75 is exactly 25 by 25 pixels
    tour_ws.Columns("A:H").ColumnWidth = 2.86
    tour_ws.Rows("1:8").RowHeight = 18.75
    tour_ws.Range("A1:H8").FormatConditions.AddColorScale(3)

    # Iterate over all POSITIONS, filling them with their index + 1
    # to compensate for 0-indexed list.
    # For some reason Cells appears to require Y before X.
    for idx, position in enumerate(POSITIONS):
        tour_ws.Cells(position[1], position[0]).Value = idx + 1

    tour_ws.Range("A1:H8").VerticalAlignment = \
        win32com.client.constants.xlCenter
    tour_ws.Range("A1:H8").HorizontalAlignment = \
        win32com.client.constants.xlCenter

    # Create a new worksheet to jot down the demo calculations on
    calc_ws = wb.Worksheets.Add()
    calc_ws.Name = "Calculations"

    for idx, formula in enumerate(FORMULAE):
        for i in range(1, 18):
            calc_ws.Cells(
                str(i), 1 + idx).Value = range_format(formula, tour_ws.Name, i)

    for idx, _ in enumerate(FORMULAE):
        calc_ws.Range(
            "{0}1:{0}17".format(
                chr(idx + CONST_A))).FormatConditions.AddColorScale(3)

if __name__ == "__main__":
    main()

Result

Knight Tour

Calculations

\$\endgroup\$
4
\$\begingroup\$

As you mention, some of this is messy and haphazard, which is a side-effect of writing one-off scripts that have a single behavior they need to perform, instead of something extensible. In that case, this review is going to largely focus on how we make this extensible and use our hammer to build a screwdriver (among other things).


If we start at how we interact with cells and ranges, it becomes pretty clear that we have a lot of stuff going on

  • ASCII constants and offsets
  • Hardcoded row/column limits
  • Hardcoded sizes
  • etc

What I think we want is a well designed ExcelSheetRange class that handles a few things:

  • R1C1 vs A1 reference styles
    • Relative references (A1 or R[1]C[1])
    • Absolute references ($A$1 or R1C1)
    • Semi absolute/relative reference ($A1 or A$1 or R1C[1] or R[1]C1)
  • translation between an index and the Excel Cells
  • efficient range transformations

I imagine you could implement a base class ExcelSheetRange, then at least 2 subclasses (one each for R1C1 and A1). That would be a lot, so I'll skip it, but assuming we can do that then some of this code gets much cleaner:

return "={0}('{1}'!{2}{3}:{4}{5})".format(formula, target, chr(CONST_A), i, chr(CONST_H), i)
# becomes
return "={0}('{1}'!{2})".format(formula, target, ExcelSheetRange('A', i, 'H', i))

tour_ws.Columns("A:H").ColumnWidth = 2.86
#becomes
tour_ws.Columns(ExcelSheetRange("A", 1, "H", 8).column_range) = 2.86

From there, we get a few generalizable problems:

  • Formatting cells
  • putting a value in a cell
  • performing a calculation on a range

You could add some enhancements to this presumed ExcelSheetRange class such as cell_size_in_px, cell_color_scale, cell_value, cell_vertical_alignment, etc. Then a lot of your operations get even easier

range = ExcelSheetRange("A", 1, "H", 8)
range.cell_size_in_px = (25, 25) # abstracts away Microsoft's weird measurement rules
range.cell_color_scale = 3 # Whatever this means; if there is something more useful and understandable to put here then do that

Then if we have a simple apply_to_sheet operation we could just do

range.apply_to_sheet(tour_ws)
# or if you want some dependency injectable method so you can test it, you could do
apply_range_to_sheet(range, tour_ws)

Assuming we've implemented all of this, then the program becomes pretty simple

def main():
    wb, tour_sheet, calc_sheet = get_knight_tour_workbook()

    knight_tour_range = ExcelSheetRange("A", 1, "H", 8)
    knight_tour_range.cell_size_in_px = (
        25,
        25,
    )  # Assume property with a setter that does scaling as appropriate
    knight_tour_range.cell_color_scale = 3
    knight_tour_range.literal_values = [
        (x_pos, y_pos, value)
        for value, (x_pos, y_pox) in enumerate(positions, 1)
    ]  # Assume property with a setter that stores the values in an intermediate array
    knight_tour_range.alignment = AlignmentEnum.Center # Assume this is a property with a setter that can figure out that horizontal and vertical are the same

    calculation_ranges = []

    for i, formula in enumerate(FORMULAE, 1):
        calculation_range = ExcelSheetRange(1, i, 17, i)
        calculation_range.cell_color_scale = 3
        calculation_range.calculated_values = [
            (
                i,
                row,
                get_function_call(
                    row, formula, tour_sheet.Name
                ),
            )
            for row in range(1, 18)
        ]
        calculation_ranges.append(calculation_range)

    apply_range_to_sheet(tour_sheet, knight_tour_range)
    apply_all_ranges_to_sheet(
        calc_sheet, *calculation_ranges
    )

I've left it a bit hand-wavy, and there are definitely additional improvements that can be made, but we've now pretty much separated the Excel manipulations and your actual logic.

If we wanted to get really crazy, and really generic, we could even make our "range" stuff completely independent of Excel, and just lean really heavily on the apply_* functions. Getting even crazier and more generic, we split our "here are calculations that should eventually be performed on these sets of values" and our "here is how things should look", and pass them both into some kind of "tabular data visualization tool" that is generic to actual visualization application, and then make an Excel implementation that quacks the same way. This is probably going too far.


Some nitpicky things

def range_format(formula, target, i):
    if i < 9:
        return "={0}('{1}'!{2}{3}:{4}{5})".format(
            formula, target, chr(CONST_A), i, chr(CONST_H), i)
    elif i < 10:
        return ""
    else:
        return "={0}('{1}'!{2}{3}:{4}{5})".format(
            formula, target, chr(i + OFFSET), 1, chr(i + OFFSET), 8)

This function is a bit clunky as-is:

  • elif i < 10 should just be elif i == 9
  • You can use f-strings here
  • You duplicate the formatting logic as well (also you don't need to specify i twice)

I would probably want to write this:

def range_format(formula, target, i):
    if i == 9:
        return ""

    start_row = chr(CONST_A) if i < 9 else chr(i + OFFSET)
    start_col = i if i < 9 else 1
    end_row = chr(CONST_H) if i < 9 else chr(i + OFFSET)
    end_col = i if i < 9 else 8

    return f"={formula}('{target}'!{start_row}{start_col}:{end_row}{end_col})"

Then, as you mention, you probably want a bit better validation on the allowed functions. An enum would be fine to meet this need.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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