# clm_generator/excel_to_clm.py
import os
import re
import json
from openpyxl import load_workbook
import xlrd
from jinja2 import Template
class ExcelToCLMConverter:
# ==================== 新增工具方法：大小写安全查询 ====================
def _ci_get(self, data_dict, key):
"""
Case-insensitive 字典查找
"""
for k, v in data_dict.items():
if k.lower() == key.lower():
return v
return None
def _ci_contains(self, data_list, item):
"""
Case-insensitive 判断元素是否在列表中
"""
return any(x.lower() == item.lower() for x in data_list)
# ==================== 原有 parse_mode_cell 方法保持不变 ====================
def parse_mode_cell(self, cell_value):
if not cell_value:
return None
val = str(cell_value).strip()
val = re.sub(r'\s+', ' ', val.replace('\n', ' ').replace('\r', ' '))
val_upper = val.upper()
found_modes = []
# ✅ 改进：使用 match + 允许后续内容（比如 20M），不再要求全匹配
if re.match(r'^11AC\s*/\s*AX', val_upper) or re.match(r'^11AX\s*/\s*AC', val_upper):
found_modes = ['11AC', '11AX']
print(f"🔍 解析复合模式 '{val}' → {found_modes}")
# ======== 一般情况：正则匹配标准模式 ========
else:
mode_patterns = [
(r'\b11BE\b|\bEHT\b', '11BE'),
(r'\b11AX\b|\bHE\b', '11AX'),
(r'\b11AC\b|\bVHT\b', '11AC'), # 自动匹配 11AC 或 VHT
(r'\b11N\b|\bHT\b', '11N'),
(r'\b11G\b|\bERP\b', '11G'),
(r'\b11B\b|\bDSSS\b|\bCCK\b', '11B')
]
for pattern, canonical in mode_patterns:
if re.search(pattern, val_upper) and canonical not in found_modes:
found_modes.append(canonical)
# ======== 提取带宽 ========
bw_match = re.search(r'\b(20|40|80|160)\s*(?:MHZ|M)?\b', val_upper)
bw = bw_match.group(1) if bw_match else None
# fallback 带宽
if not bw:
if all(m in ['11B', '11G'] for m in found_modes):
bw = '20'
else:
bw = '20'
if not found_modes:
print(f"🟡 无法识别物理模式: '{cell_value}'")
return None
return {
"phy_mode_list": found_modes,
"bw": bw
}
def format_phy_mode(self, mode: str) -> str:
"""
自定义物理层模式输出格式：
- 11B/G/N 输出为小写：11b / 11g / 11n
- 其他保持原样（如 11AC, 11BE）
"""
return {
'11B': '11b',
'11G': '11g',
'11N': '11n'
}.get(mode, mode)
def __init__(self, config_path="config.json", output_dir="output"):
self.config = self.load_config(config_path)
self.tx_limit_entries = []
self.output_dir = output_dir
os.makedirs(self.output_dir, exist_ok=True)
def load_config(self, path):
with open(path, 'r', encoding='utf-8') as f:
return json.load(f)
def col_to_letter(self, col):
col += 1
result = ""
while col > 0:
col -= 1
result = chr(col % 26 + ord('A')) + result
col //= 26
return result
def is_valid_power(self, value):
try:
float(value)
return True
except (ValueError, TypeError):
return False
def get_cell_value(self, ws_obj, row_idx, col_idx):
fmt = ws_obj["format"]
if fmt == "xls":
return str(ws_obj["sheet"].cell_value(row_idx, col_idx)).strip()
else:
cell = ws_obj["sheet"].cell(row=row_idx + 1, column=col_idx + 1)
val = cell.value
return str(val).strip() if val is not None else ""
def find_table_header_row(self, ws_obj):
"""查找包含 'Mode' 和 'Rate' 的表头行"""
fmt = ws_obj["format"]
ws = ws_obj["sheet"]
for r in range(15):
mode_col = rate_col = None
if fmt == "xlsx":
if r + 1 > ws.max_row: continue
for c in range(1, ws.max_column + 1):
cell = ws.cell(row=r + 1, column=c)
if not cell.value: continue
val = str(cell.value).strip()
if val == "Mode": mode_col = c
elif val == "Rate": rate_col = c
if mode_col and rate_col and abs(mode_col - rate_col) == 1:
print(f"✅ 找到表头行: 第 {r+1} 行")
return r, mode_col - 1, rate_col - 1 # 转为 0-based
else:
if r >= ws.nrows: continue
for c in range(ws.ncols):
val = ws.cell_value(r, c)
if not val: continue
val = str(val).strip()
if val == "Mode": mode_col = c
elif val == "Rate": rate_col = c
if mode_col and rate_col and abs(mode_col - rate_col) == 1:
print(f"✅ 找到表头行: 第 {r+1} 行")
return r, mode_col, rate_col
return None, None, None
def find_auth_power_above_row(self, ws_obj, start_row):
"""查找 '认证功率' 所在的合并单元格及其列范围"""
fmt = ws_obj["format"]
ws = ws_obj["sheet"]
print(f"🔍 开始向上查找 '认证功率'，扫描第 0 ~ {start_row} 行...")
if fmt == "xlsx":
for mr in ws.merged_cells.ranges:
top_left = ws.cell(row=mr.min_row, column=mr.min_col)
val = str(top_left.value) if top_left.value else ""
if "证功率" in val or "Cert" in val:
r_idx = mr.min_row - 1
if r_idx <= start_row:
start_col = mr.min_col - 1
end_col = mr.max_col - 1
print(f"📌 发现合并单元格含 '证功率': '{val}' → {self.col_to_letter(start_col)}{mr.min_row}")
return start_col, end_col, r_idx
# fallback：普通单元格
for r in range(start_row + 1):
for c in range(1, ws.max_column + 1):
cell = ws.cell(row=r + 1, column=c)
if cell.value and ("证功率" in str(cell.value)):
print(f"📌 普通单元格发现 '证功率': '{cell.value}' @ R{r+1}C{c}")
return c - 1, c - 1, r
else:
for r in range(min(ws.nrows, start_row + 1)):
for c in range(ws.ncols):
val = ws.cell_value(r, c)
if val and ("证功率" in str(val)):
print(f"📌 发现 '证功率': '{val}' @ R{r+1}C{c+1}")
return c, c, r
return None, None, None
def parse_ch_columns_under_auth(self, ws_obj, ch_row_idx, auth_start_col, auth_end_col):
"""
只解析位于 [auth_start_col, auth_end_col] 区间内的 CHx 列
"""
fmt = ws_obj["format"]
ws = ws_obj["sheet"]
ch_map = {}
print(f"🔍 解析 CH 行（第 {ch_row_idx + 1} 行），限定列范围: Col {auth_start_col} ~ {auth_end_col}")
if fmt == "xlsx":
for c in range(auth_start_col, auth_end_col + 1):
cell = ws.cell(row=ch_row_idx + 1, column=c + 1)
val = self.get_cell_value(ws_obj, ch_row_idx, c)
match = re.search(r"CH(\d+)", val, re.I)
if match:
ch_num = int(match.group(1))
ch_map[ch_num] = c
print(f" 👉 发现 CH{ch_num} @ Col{c}")
else:
for c in range(auth_start_col, auth_end_col + 1):
val = self.get_cell_value(ws_obj, ch_row_idx, c)
match = re.search(r"CH(\d+)", val, re.I)
if match:
ch_num = int(match.group(1))
ch_map[ch_num] = c
print(f" 👉 发现 CH{ch_num} @ Col{c}")
if not ch_map:
print("❌ 在指定区域内未找到任何 CHx 列")
else:
chs = sorted(ch_map.keys())
print(f"✔️ 成功提取 CH{min(chs)}-{max(chs)} 共 {len(chs)} 个信道")
return ch_map
def encode_power(self, dbm):
return int(round((float(dbm) + 1.5) * 4))
def merge_consecutive_channels(self, ch_list):
if not ch_list: return []
sorted_ch = sorted(ch_list)
ranges = []
start = end = sorted_ch[0]
for ch in sorted_ch[1:]:
if ch == end + 1:
end = ch
else:
ranges.append((start, end))
start = end = ch
ranges.append((start, end))
return ranges
# ==================== 修改 collect_tx_limit_data ====================
def collect_tx_limit_data(self, ws_obj, sheet_config, header_row_idx, auth_row, auth_start, auth_end, mode_col,
rate_col):
ch_row_idx = auth_row + 2
nrows = ws_obj["sheet"].nrows if ws_obj["format"] == "xls" else ws_obj["sheet"].max_row
if ch_row_idx >= nrows:
print(f"❌ CH 行 ({ch_row_idx + 1}) 超出范围")
return []
# ✅ 提取认证功率下方的 CH 列映射
ch_map = self.parse_ch_columns_under_auth(ws_obj, ch_row_idx, auth_start, auth_end)
if not ch_map:
return []
entries = []
row_mode_info = {} # {row_index: parsed_mode_info}
fmt = ws_obj["format"]
ws = ws_obj["sheet"]
# ======== 第一步：构建 row_mode_info —— 使用新解析器 ========
if fmt == "xlsx":
merged_cells_map = {}
for mr in ws.merged_cells.ranges:
for r in range(mr.min_row - 1, mr.max_row):
for c in range(mr.min_col - 1, mr.max_col):
merged_cells_map[(r, c)] = mr
for row_idx in range(header_row_idx + 1, nrows):
cell_value = None
is_merged = (row_idx, mode_col) in merged_cells_map
if is_merged:
mr = merged_cells_map[(row_idx, mode_col)]
top_cell = ws.cell(row=mr.min_row, column=mr.min_col)
cell_value = top_cell.value
else:
raw_cell = ws.cell(row=row_idx + 1, column=mode_col + 1)
cell_value = raw_cell.value
mode_info = self.parse_mode_cell(cell_value)
if mode_info:
if is_merged:
mr = merged_cells_map[(row_idx, mode_col)]
for r in range(mr.min_row - 1, mr.max_row):
if header_row_idx < r < nrows:
row_mode_info[r] = mode_info.copy()
else:
row_mode_info[row_idx] = mode_info.copy()
else:
for row_idx in range(header_row_idx + 1, ws.nrows):
cell_value = self.get_cell_value(ws_obj, row_idx, mode_col)
mode_info = self.parse_mode_cell(cell_value)
if mode_info:
row_mode_info[row_idx] = mode_info.copy()
# ======== 第二步：生成条目（关键修改区）========
for row_idx in range(header_row_idx + 1, nrows):
mode_info = row_mode_info.get(row_idx)
if not mode_info:
continue
bw_clean = mode_info["bw"]
has_valid_power = False
for ch, col_idx in ch_map.items():
power_val = self.get_cell_value(ws_obj, row_idx, col_idx)
if self.is_valid_power(power_val):
has_valid_power = True
break
if not has_valid_power:
print(f"🗑️ 跳过空行: 第 {row_idx + 1} 行（无任何有效功率值）")
continue
# ---- 遍历每个 phy_mode ----
for phy_mode in mode_info["phy_mode_list"]:
formatted_mode = self.format_phy_mode(phy_mode)
mode_key = f"{formatted_mode}_{bw_clean}M"
# ✅ 改为大小写不敏感判断
if not self._ci_contains(sheet_config.get("modes", []), mode_key):
print(f"⚠️ 忽略不支持的模式: {mode_key}")
continue
# ✅ 改为大小写不敏感查表
rate_set_macro = self._ci_get(sheet_config["rate_set_map"], mode_key)
if not rate_set_macro:
print(f"❌ 找不到 rate_set 宏: {mode_key}")
continue
ch_count = 0
for ch, col_idx in ch_map.items():
power_val = self.get_cell_value(ws_obj, row_idx, col_idx)
if not self.is_valid_power(power_val):
continue
try:
power_dbm = float(power_val)
except:
continue
encoded_power = self.encode_power(power_dbm)
entries.append({
"ch": ch,
"power_dbm": round(power_dbm, 2),
"encoded_power": encoded_power,
"rate_set_macro": rate_set_macro,
"mode": phy_mode,
"bw": bw_clean,
"src_row": row_idx + 1,
"band": sheet_config["band"]
})
ch_count += 1
print(
f"📊 已采集第 {row_idx + 1} 行 → {formatted_mode} {bw_clean}M, {ch_count} 个信道, 使用宏: {rate_set_macro}"
)
return entries
# ==================== 修改 compress_tx_limit_entries ====================
def compress_tx_limit_entries(self, raw_entries, sheet_config):
from collections import defaultdict
modes_order = sheet_config["modes"]
# ✅ 构建小写映射用于排序
mode_lower_to_index = {mode.lower(): idx for idx, mode in enumerate(modes_order)}
range_template = sheet_config["range_macro_template"]
group_key = lambda e: (e["encoded_power"], e["rate_set_macro"])
groups = defaultdict(list)
for e in raw_entries:
groups[group_key(e)].append(e)
compressed = []
for (encoded_power, rate_set_macro), entries_in_group in groups.items():
first = entries_in_group[0]
power_dbm = first["power_dbm"]
mode = first["mode"]
bw = first["bw"]
ch_list = sorted(e["ch"] for e in entries_in_group)
for start, end in self.merge_consecutive_channels(ch_list):
range_macro = range_template.format(
band=sheet_config["band"],
bw=bw,
start=start,
end=end
)
# ✅ 先格式化模式名（关键！）
formatted_mode = self.format_phy_mode(mode)
# ✅ 使用格式化后的名字生成注释
comment = f"/* {power_dbm:5.2f}dBm, CH{start}-{end}, {formatted_mode} */"
mode_key = f"{formatted_mode}_{bw}M"
mode_order_idx = mode_lower_to_index.get(mode_key.lower(), 999)
compressed.append({
"encoded_power": encoded_power,
"range_macro": range_macro,
"rate_set_macro": rate_set_macro,
"comment": comment,
"_mode_order": mode_order_idx
})
# 排序
compressed.sort(key=lambda x: x["_mode_order"])
for item in compressed:
del item["_mode_order"]
return compressed
def clean_sheet_name(self, name):
cleaned = re.sub(r'[^\w\.\=\u4e00-\u9fa5]', '', str(name))
return cleaned
def match_sheet_to_config(self, sheet_name):
cleaned = self.clean_sheet_name(sheet_name)
for cfg in self.config["sheets"]:
for pat in cfg["pattern"]:
if re.search(pat, cleaned, re.I):
print(f"🧹 '{sheet_name}' → 清洗后: '{cleaned}'")
print(f"✅ 匹配成功！'{sheet_name}' → [{cfg['band']}] 配置")
return cfg
print(f"🧹 '{sheet_name}' → 清洗后: '{cleaned}'")
print(f"🟡 未匹配到 '{sheet_name}' 的模式，跳过...")
return None
def convert_sheet_with_config(self, ws_obj, sheet_name, sheet_config):
header_row_idx, mode_col, rate_col = self.find_table_header_row(ws_obj)
if header_row_idx is None:
print(f"🟡 跳过 '{sheet_name}'：未找到 'Mode' 和 'Rate'")
return
auth_start, auth_end, auth_row = self.find_auth_power_above_row(ws_obj, header_row_idx)
if auth_start is None:
print(f"🟡 跳过 '{sheet_name}'：未找到 '认证功率'")
return
raw_entries = self.collect_tx_limit_data(
ws_obj, sheet_config, header_row_idx, auth_row, auth_start, auth_end, mode_col, rate_col
)
if not raw_entries:
print(f"⚠️ 从 '{sheet_name}' 未收集到有效数据")
return
compressed = self.compress_tx_limit_entries(raw_entries, sheet_config)
self.tx_limit_entries.extend(compressed)
print(f"✔️ 成功从 '{sheet_name}' 添加 {len(compressed)} 条压缩后 TX 限幅条目")
def render_from_template(self, template_path, context, output_path):
with open(template_path, 'r', encoding='utf-8') as f:
template = Template(f.read())
content = template.render(**context)
os.makedirs(os.path.dirname(output_path), exist_ok=True)
with open(output_path, 'w', encoding='utf-8') as f:
f.write(content)
print(f"🎉 已生成: {output_path}")
def generate_outputs(self):
if not self.tx_limit_entries:
print("⚠️ 无数据可输出")
return
context = {
"header": "Auto-generated TX Power Limit Table",
"entries": self.tx_limit_entries
}
self.render_from_template(
"templates/tx_limit_table.c.j2",
context,
os.path.join(self.output_dir, "tx_limit_table.c")
)
self.render_from_template(
"templates/clm_macros.h.j2",
context,
os.path.join(self.output_dir, "clm_macros.h")
)
def convert(self, file_path):
ext = os.path.splitext(file_path)[-1].lower()
if ext == ".xlsx":
wb = load_workbook(file_path, data_only=True)
sheets = [{"sheet": ws, "format": "xlsx"} for ws in wb.worksheets]
elif ext == ".xls":
wb = xlrd.open_workbook(file_path)
sheets = [{"sheet": ws, "format": "xls"} for ws in wb.sheets()]
else:
raise ValueError("仅支持 .xls 或 .xlsx 文件")
for i, ws_obj in enumerate(sheets):
sheet_name = wb.sheet_names()[i] if ext == ".xls" else ws_obj["sheet"].title
config = self.match_sheet_to_config(sheet_name)
if config:
self.convert_sheet_with_config(ws_obj, sheet_name, config)
self.generate_outputs()
if __name__ == "__main__":
import sys
script_dir = os.path.dirname(__file__)
os.chdir(script_dir)
converter = ExcelToCLMConverter()
input_file = "input/Archer BE900US 2.xlsx"
if len(sys.argv) > 1:
input_file = sys.argv[1]
converter.convert(input_file)
我自己配置了速率集，应该怎么输入
最新发布
本文介绍了SDM (Switch Data Model) 模板的概念及其在交换机资源管理中的应用。SDM模板能够帮助网络管理员针对不同场景优化交换机资源分配，如路由、VLAN等。文章还详细对比了Desktop和Aggregator两种模式下的资源分配情况，并提供了配置示例。
扫一扫
2711
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
