Ansible学习笔记--python编写读取excel的dynamic inventory

Spreadsheets are common inputs for early automation projects. Ansible can consume that data through a dynamic inventory script, provided the workbook has a stable schema and the generated JSON is validated before use.

This walkthrough uses a two-host documentation inventory. None of the names or addresses refer to a live environment.

Define the workbook

Create inventory.xlsx with a worksheet named hosts and these columns:

The host addresses, proxy address, and resolver address come from documentation-only ranges. The realm is the reserved example value EXAMPLE.TEST.

Convert rows to dynamic inventory JSON

Install the workbook reader in an isolated environment:

python3 -m venv .venv
. .venv/bin/activate
python -m pip install openpyxl

Create excel_inventory.py:

#!/usr/bin/env python3
import argparse
import json
from pathlib import Path

from openpyxl import load_workbook

REQUIRED_COLUMNS = {
    "group",
    "inventory_name",
    "ansible_host",
    "proxy",
    "resolver",
    "realm",
}


def read_rows(path: Path) -> list[dict[str, str]]:
    workbook = load_workbook(path, read_only=True, data_only=True)
    worksheet = workbook["hosts"]
    values = worksheet.iter_rows(values_only=True)
    headers = [str(value).strip() for value in next(values)]

    missing = REQUIRED_COLUMNS.difference(headers)
    if missing:
        raise ValueError(f"missing columns: {sorted(missing)}")

    rows = []
    for values_row in values:
        row = dict(zip(headers, values_row, strict=True))
        if not row["inventory_name"]:
            continue
        rows.append({key: str(value).strip() for key, value in row.items()})
    return rows


def build_inventory(rows: list[dict[str, str]]) -> dict:
    inventory: dict = {"_meta": {"hostvars": {}}}

    for row in rows:
        group = row.pop("group")
        host = row.pop("inventory_name")
        inventory.setdefault(group, {"hosts": []})["hosts"].append(host)
        inventory["_meta"]["hostvars"][host] = row

    return inventory


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--list", action="store_true")
    parser.add_argument("--host")
    args = parser.parse_args()

    inventory = build_inventory(read_rows(Path("inventory.xlsx")))
    if args.host:
        output = inventory["_meta"]["hostvars"].get(args.host, {})
    else:
        output = inventory
    print(json.dumps(output, indent=2, sort_keys=True))


if __name__ == "__main__":
    main()

Make the script executable and inspect its output:

chmod 700 excel_inventory.py
./excel_inventory.py --list | python -m json.tool
./excel_inventory.py --host web-01.example.test | python -m json.tool

The generated inventory groups the two hosts while keeping host-specific variables under _meta.hostvars:

{
  "_meta": {
    "hostvars": {
      "db-01.example.test": {
        "ansible_host": "192.0.2.22",
        "proxy": "http://198.51.100.10:3128",
        "realm": "EXAMPLE.TEST",
        "resolver": "203.0.113.53"
      },
      "web-01.example.test": {
        "ansible_host": "192.0.2.21",
        "proxy": "http://198.51.100.10:3128",
        "realm": "EXAMPLE.TEST",
        "resolver": "203.0.113.53"
      }
    }
  },
  "database": {
    "hosts": ["db-01.example.test"]
  },
  "web": {
    "hosts": ["web-01.example.test"]
  }
}

Validate before running playbooks

ansible-inventory -i ./excel_inventory.py --list
ansible-inventory -i ./excel_inventory.py --graph
ansible all -i ./excel_inventory.py --list-hosts

For production use, validate addresses and group names, reject duplicate inventory names, and store credentials in Ansible Vault or another secret manager rather than adding them as spreadsheet columns. Treat the workbook as configuration: restrict access, review changes, and keep generated inventory output out of source control.