跳到主要内容

CSV 与 Excel

资产清单、巡检结果、工单导出常以 CSV 或 Excel 形式存在。CSV 用标准库 csv 即可；Excel（.xlsx）需第三方库 openpyxl。

CSV：读取资产表​

import csv
from pathlib import Path

path = Path("data/hosts.csv")
# 示例文件内容：
# hostname,ip,role,env
# web-01,10.0.1.10,web,prod
# db-01,10.0.2.20,db,prod

hosts = []
with path.open(newline="", encoding="utf-8") as f:
    reader = csv.DictReader(f)  # 首行作表头
    for row in reader:
        hosts.append(row)

for h in hosts:
    print(h["hostname"], h["ip"], h["role"])

newline="" 是 csv 模块官方推荐，避免 Windows 下换行异常。

CSV：写入巡检结果​

import csv
from pathlib import Path
from datetime import datetime

results = [
    {"hostname": "web-01", "cpu": 45, "disk": 60, "status": "OK"},
    {"hostname": "web-02", "cpu": 91, "disk": 55, "status": "ALERT"},
]

out = Path("data/inspection.csv")
fieldnames = ["hostname", "cpu", "disk", "status", "checked_at"]

with out.open("w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    now = datetime.now().isoformat(timespec="seconds")
    for row in results:
        writer.writerow({**row, "checked_at": now})

print(out.read_text(encoding="utf-8"))

过滤与聚合​

import csv
from pathlib import Path

path = Path("data/inspection.csv")
alerts = []

with path.open(newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        if row["status"] == "ALERT":
            alerts.append(row["hostname"])

print("告警主机:", alerts)
print("告警数:", len(alerts))

Excel：安装 openpyxl​

# 在项目目录执行
# uv add openpyxl

读取与写入 .xlsx：

from pathlib import Path
from openpyxl import Workbook, load_workbook

xlsx = Path("data/assets.xlsx")

# --- 写入 ---
wb = Workbook()
ws = wb.active
ws.title = "hosts"
ws.append(["hostname", "ip", "owner"])
ws.append(["web-01", "10.0.1.10", "ops"])
ws.append(["db-01", "10.0.2.20", "dba"])
wb.save(xlsx)

# --- 读取 ---
wb2 = load_workbook(xlsx, read_only=True, data_only=True)
ws2 = wb2.active
for row in ws2.iter_rows(min_row=2, values_only=True):
    hostname, ip, owner = row
    print(f"{hostname} {ip} ({owner})")
wb2.close()

read_only=True 适合大表省内存；data_only=True 读取公式单元格的计算结果而非公式本身。

CSV ↔ Excel 简易转换​

import csv
from pathlib import Path
from openpyxl import Workbook

def csv_to_xlsx(csv_path: Path, xlsx_path: Path) -> None:
    wb = Workbook()
    ws = wb.active
    with csv_path.open(newline="", encoding="utf-8") as f:
        for row in csv.reader(f):
            ws.append(row)
    wb.save(xlsx_path)

# csv_to_xlsx(Path("data/hosts.csv"), Path("data/hosts.xlsx"))

选型建议​

格式工具适用
CSVcsv 标准库脚本互导、Git 友好、轻量
XLSXopenpyxl运营手工表、多 Sheet、样式
提示
  • CSV 含逗号或换行的字段需用 csv 模块读写，不要手动 split(",")
  • 敏感资产表不要提交到 Git；脚本里用 .gitignore 排除 data/
  • 超大 Excel 考虑导出为 CSV 再流式处理

小结​

第二阶段完结。你已掌握字符串、文件、JSON、YAML、表格等数据处理基础，可进入 第三阶段：系统自动化，对接真实命令与进程。