不太优秀的骑士之旅

Question

注意:这是我为同事编写的一组演示应用程序的一部分，以表明我们在处理Excel时并不局限于VBA。考虑到我们的IT挑战(没有现场管理和本地管理权限)，这必须在没有外部库的Portable上工作。具体来说，使用了来自WinPython的Python3.7.2(C0) shell (在Windows7和Excel2013上)，虽然理论上我们应该能够使用WPPM来安装外部库，但到目前为止，我们运气不佳。

这是一个杂乱无章的解决方案，不推荐在现场使用！如果你想固定螺丝钉，用螺丝刀。不是锤子。但有时你的工具箱里只有锤子。

我这么做的部分原因是，我们需要在没有适当信息管道的情况下，以丑陋的复制/粘贴方式从外部源导入/导出数据。在Excel中跳转，将数据添加到字段中，并对这些数据进行处理。这是我做演示的第一件事。

可以说，它并没有多大用处，但它显示了比Hello，World示例更多的可用选项。Python有一些关于骑士的东西，所以我跳过一个硬编码的骑士之旅，填充一些数据，使用一些格式化，创建一些计算，它很好地展示了它应该做什么。

哦，如果你认为这看起来没那么糟糕(确实如此)，那就等到下一个演示吧。

我可能需要更多的功能，也许是几个包装器，但目前看来这有点过分了。Enum可能也很好，因为我似乎做了很多列举。和更多的常数，虽然这往往会变得混乱。我考虑过创建tour_ws()和calc_ws()，主要是为了减少代码块，但这将是函数的糟糕用法。也许传递实例、工作表或其他对象是一个更好的主意，但不是特别确定它会是什么样子。

因为从文件中导入似乎是这个配置的一个问题，所以伪对比在同一个文件中。我知道我不该这样做。

和往常一样，我主要感兴趣的是以正确的方式做这件事，尽管在这种情况下这并不总是可能的。我的名字很糟糕，表现已经令人满意了。我不确定我是应该更好地处理我的记忆，还是GC会处理好它。我不能拥有的是程序停止了它的执行，因为打开的程序(Excel)仍然是打开的。

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()

结果

Answer 1

正如您所提到的，其中有些是杂乱无章的，这是编写一次性脚本的副作用，这些脚本需要执行一次行为，而不是可扩展的。在这种情况下，本综述将主要集中在我们如何使这个扩展，并使用我们的锤子，以建立螺丝刀(和其他事情)。

如果我们从我们如何与细胞和范围相互作用开始，就很明显我们有很多东西在发生。

• ASCII常数和偏移量
• 硬编码行/列限制
• 硬编码尺寸
• 等

我认为我们需要的是一个设计良好的ExcelSheetRange类，它处理一些事情：

• R1C1与A1参考样式
  • 相对参考资料(A1或R[1]C[1])
  • 绝对引用($A$1或R1C1)
  • 半绝对/相对参考($A1或A$1或R1C[1]或R[1]C1)

• 索引和Excel单元格之间的转换
• 有效范围变换

我想您可以实现一个基类ExcelSheetRange，然后至少有两个子类( R1C1和A1各一个)。这将是很多，所以我将跳过它，但是假设我们能够做到这一点，那么下面的代码就会变得更清晰：

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

从这里，我们得到了一些可推广的问题：

• 格式化单元格
• 在单元格中放置值
• 对范围执行计算

您可以向这个假定的ExcelSheetRange类添加一些增强，如cell_size_in_px、cell_color_scale、cell_value、cell_vertical_alignment等。

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

如果我们有一个简单的apply_to_sheet操作，我们就可以

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)

假设我们已经实现了所有这些，那么程序就变得非常简单

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
    )

我给它留了一点手感，而且肯定还有其他的改进可以做，但是我们现在已经将Excel操作和实际逻辑分开了。

如果我们想变得非常疯狂，而且非常通用，我们甚至可以使我们的“范围”内容完全独立于Excel，并且非常依赖于apply_*函数。变得更疯狂、更通用，我们把“这里是最终应该对这些值执行的计算”和我们的“这是事情应该看上去的样子”分开，并将它们传递到某种类型的“表格数据可视化工具”中，这种工具对于实际的可视化应用程序来说是通用的，然后用同样的方式制作一个Excel实现。这可能太过分了。

一些挑剔的东西

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)

这个函数有点笨重，因为它是：

• elif i < 10应该是elif i == 9
• 您可以在这里使用f-strings
• 您还复制了格式化逻辑(而且不需要指定两次i )

我可能会写这个：

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})"

然后，正如您提到的，您可能希望对允许的函数进行更好的验证。enum可以满足这一需求。