python/openpyxl-未添加总计行

Question

我正在尝试使用 openpyxl 将 python 脚本生成的数据输出到 xlsx 文件。

我想使用类似于文档中示例的代码来定义还具有总计行的表:

from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active

data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears',   2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges',  500,  300,  200,  700],
]

# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
    ws.append(row)

tab = Table(displayName="Table1", ref="A1:E5", totalsRowShown=True)
ws.add_table(tab)
wb.save('test_table.xlsx')

However - nothing changes in the generated excel-file. There is no Totals row once I open the file in Excel

我知道我仍然需要为总计行中的每个单元格提供公式，但首先我需要创建它。

感谢您的帮助!

Answer 1

您需要使用与 Excel XLSX 文件完全相同的值手动创建总计行和表格列。您可以保存 Excel XLSX 文件并检查工作表 XML 和表 XML，以便确定必要的值。

试试这个:

#!/usr/bin/env python

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableColumn, TableStyleInfo
from openpyxl.worksheet.filters import AutoFilter
from openpyxl.writer import theme, excel


def update_theme():
    color = {
        "1F497D": "44546A",
        "EEECE1": "E7E6E6",
        "4F81BD": "5B9BD5",
        "C0504D": "ED7D31",
        "9BBB59": "A5A5A5",
        "8064A2": "FFC000",
        "4BACC6": "4472C4",
        "F79646": "70AD47",
        "0000FF": "0563C1",
        "800080": "954F72"
    }

    xml = theme.theme_xml
    for original, replacement in color.items():
        xml = xml.replace(f"val=\"{original}\"", f"val=\"{replacement}\"")

    theme.theme_xml = xml
    excel.theme_xml = xml


def autosize_columns(worksheet):
    def value_of(value):
        return (str(value) if value is not None else "")

    for cells in worksheet.columns:
        length = max(len(value_of(cell.value)) for cell in cells)
        column_letter = get_column_letter(cells[0].column)
        worksheet.column_dimensions[column_letter].width = length

    return worksheet


def create_test_workbook():
    # Initialize workbook and worksheet.
    workbook = Workbook()
    properties = workbook.properties
    properties.title = "Test"
    properties.creator = None

    sheet = workbook.active
    sheet.title = "Test"

    table_name = "TestTable"

    # Add data.
    headers = ["First", "Second", "Third", "Fourth"]
    sheet.append(headers)

    sheet.append(["a", "b", "c", "d"])
    sheet.append(["e", "f", "g", "h"])

    sheet.append(["Total", "", "", f"=SUBTOTAL(103,{table_name}[{headers[3]}])"])

    # Add table.
    style = TableStyleInfo(
        name="TableStyleMedium2",
        showFirstColumn=False,
        showLastColumn=False,
        showRowStripes=True,
        showColumnStripes=False
    )

    table_columns = tuple(TableColumn(id=h, name=header) for h, header in enumerate(headers, start=1))
    total_column = table_columns[0]
    total_column.totalsRowLabel = "Total"
    count_column = table_columns[3]
    count_column.totalsRowFunction = "count"

    max_column = sheet.max_column
    max_column_letter = get_column_letter(max_column)
    max_row = sheet.max_row
    table = Table(
        displayName=table_name,
        ref=f"A1:{max_column_letter}{max_row}",
        autoFilter=AutoFilter(ref=f"A1:{max_column_letter}{max_row - 1}"),
        tableStyleInfo=style,
        totalsRowShown=True,
        totalsRowCount=1,
        tableColumns=table_columns
    )

    sheet.add_table(table)

    # Adjust column sizes.
    autosize_columns(sheet)

    # Set the active cell under the table.
    active_cell = f"A{max_row + 1}"
    selection = sheet.sheet_view.selection[0]
    selection.activeCell = active_cell
    selection.sqref = active_cell

    return workbook


def main(*args):
    update_theme()

    workbook = create_test_workbook()
    workbook.save("Test.xlsx")
    workbook.close()


if __name__ == "__main__":
    main()