用python做Excel配置工具：可视化编辑器

发表于 更新于 分类于 阅读次数：

设计目的

  1. 部分模块需同时维护多张表，配置繁琐
  2. 配置可读性比较差，容易遗漏
  3. 配置存在规律配置，工具实现方便

为什么选择python

  1. VBA维护起来比较麻烦，可拓展性没那么好
  2. python可拓展性强，AI解决方案较为齐全

设计思路

  1. 读取Excel，能够读取其字段名、值
  2. 写入Excel，能够保留格式、做到增删改查
  3. 具体模块的高定制配置，符合直觉的自动补充配置
    • 比如显示时装的多个属性、多语言自动生成且可修改
    • 可配置的报错提示
    • 不考虑批量修改
  4. 最好可以便于拓展到其他模块

具体的设计步骤

步骤一：通用的读写模块

步骤二：模块化分析和设计

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
import os
import sys
import threading
import webbrowser
from pathlib import Path
from flask import Flask, send_from_directory, jsonify, request

# 允许被打包为单 exe 后，静态目录定位
BASE_DIR = Path(getattr(sys, "_MEIPASS", Path(__file__).parent))
WEB_DIR = BASE_DIR / "web"

from backend.api_impl import API

api = API()

app = Flask(__name__, static_folder=str(WEB_DIR), static_url_path="")

@app.route("/")
def index():
    return send_from_directory(str(WEB_DIR), "index.html")

@app.get("/api/ping")
def ping():
    return jsonify({"ok": True})

@app.post("/api/set_root")
def set_root():
    data = request.get_json(force=True)
    root = data.get("root", "")
    ok, msg = api.set_root(root)
    return jsonify({"ok": ok, "msg": msg, "root": api.root})

@app.get("/api/scan")
def scan():
    files = api.scan_excels()
    return jsonify({"ok": True, "files": files, "root": api.root})

@app.post("/api/open_in_excel")
def open_in_excel():
    data = request.get_json(force=True)
    path = data.get("path", "")
    ok, msg = api.open_in_excel(path)
    return jsonify({"ok": ok, "msg": msg})

@app.post("/api/get_sheets")
def get_sheets():
    data = request.get_json(force=True)
    path = data.get("path", "")
    ok, sheets, msg = api.get_sheets(path)
    return jsonify({"ok": ok, "sheets": sheets, "msg": msg})

@app.post("/api/read_used_range")
def read_used_range():
    data = request.get_json(force=True)
    path = data.get("path", "")
    sheet = data.get("sheet", "")
    ok, payload, msg = api.read_used_range(path, sheet)
    return jsonify({"ok": ok, "data": payload, "msg": msg})

@app.post("/api/update_cell")
def update_cell():
    data = request.get_json(force=True)
    path = data.get("path", "")
    sheet = data.get("sheet", "")
    row = int(data.get("row"))
    col = int(data.get("col"))
    value = data.get("value", None)
    ok, msg = api.update_cell(path, sheet, row, col, value)
    return jsonify({"ok": ok, "msg": msg})

@app.post("/api/insert_row_copy")
def insert_row_copy():
    data = request.get_json(force=True)
    path = data.get("path", "")
    sheet = data.get("sheet", "")
    row = int(data.get("row"))
    where = data.get("where", "above")  # "above" or "below"
    ok, msg = api.insert_row_copy(path, sheet, row, where)
    return jsonify({"ok": ok, "msg": msg})

@app.post("/api/delete_row")
def delete_row():
    data = request.get_json(force=True)
    path = data.get("path", "")
    sheet = data.get("sheet", "")
    row = int(data.get("row"))
    ok, msg = api.delete_row(path, sheet, row)
    return jsonify({"ok": ok, "msg": msg})

@app.post("/api/save_workbook")
def save_workbook():
    data = request.get_json(force=True)
    path = data.get("path", "")
    ok, msg = api.save_workbook(path)
    return jsonify({"ok": ok, "msg": msg})

@app.post("/api/search_all")
def search_all():
    data = request.get_json(force=True)
    keyword = data.get("keyword", "").strip()
    deep = bool(data.get("deep", False))
    results = api.search_all(keyword, deep=deep)
    return jsonify({"ok": True, "results": results})

def open_browser():
    url = "http://127.0.0.1:5173"
    try:
        webbrowser.open(url)
    except Exception:
        pass

if __name__ == "__main__":
    threading.Timer(1.0, open_browser).start()
    app.run(host="127.0.0.1", port=5173, debug=False)