「26 進制」在程序設計中常見於 Excel 欄位編號 (A, B, ..., Z, AA, AB...)、Base32 編碼變體、短鏈接生成等場景。不同於標準進制,Excel 欄位編碼無「0」數字(即為「26 進制無零表示法」或 Bijective Base-26),映射規則:A=1, ..., Z=26, AA=27, AB=28, ..., AZ=52, BA=53, ..., ZZ=702, AAA=703。本文詳解轉換算法、邊界處理、性能優化與通用擴展。
標準進制:value = Σ digit_i * base^i,數字集 {0, 1, ..., base-1}。
Excel 26 進制:value = Σ digit_i * 26^i,數字集 {1, 2, ..., 26}(無 0)。
因此:
n -= 1 後再取模除基。value = Σ (char_val) * 26^pos,char_val = ord(c) - ord('A') + 1。
def number_to_excel_col(n: int) -> str:
"""正整數轉 Excel 列標 (1-indexed)"""
if n <= 0:
raise ValueError("n must be positive integer")
result = []
while n > 0:
n -= 1 # 關鍵:向左借位,將 1..26 映射為 0..25
n, remainder = divmod(n, 26)
result.append(chr(ord('A') + remainder))
return ''.join(reversed(result))
def excel_col_to_number(s: str) -> int:
"""Excel 列標轉正整數"""
if not s:
raise ValueError("empty string")
result = 0
for ch in s.upper():
if 'A' <= ch <= 'Z':
result = result * 26 + (ord(ch) - ord('A') + 1)
else:
raise ValueError(f"Invalid character: {ch}")
return result
# 測試
pairs = [(1, 'A'), (26, 'Z'), (27, 'AA'), (52, 'AZ'), (53, 'BA'), (702, 'ZZ'), (703, 'AAA'), (18278, 'ZZZ')]
for num, col in pairs:
assert number_to_excel_col(num) == col
assert excel_col_to_number(col) == num
print("All tests passed")
原文提供的字典映射法:
dict = {'A':1, 'B':2, ...} # 手動構建字典,繁瑣且易錯
def count_(s):
p = len(s) - 1
count = 0
for x in range(len(s)):
count += get(x) * dict[s[p-x]] # get(x) 計算 26**x
return count
def get(p):
return 26**p
問題:
get(p) 重複計算冪次,O(N²) 時間。p-x 易混淆,可讀性低。
from functools import lru_cache
from typing import List
@lru_cache(maxsize=32)
def _pow26(exp: int) -> int:
return 26 ** exp
def excel_col_to_number_opt(s: str) -> int:
if not isinstance(s, str) or not s:
raise ValueError("Input must be non-empty string")
s = s.strip().upper()
result = 0
for i, ch in enumerate(reversed(s)):
if not 'A' <= ch <= 'Z':
raise ValueError(f"Invalid character '{ch}' at position {len(s)-i}, only A-Z allowed")
result += (ord(ch) - 64) * _pow26(i) # ord('A')=65
return result
# 批量轉換優化:預計算所有可能長度的冪次表
def batch_convert(cols: List[str]) -> List[int]:
max_len = max(len(c) for c in cols) if cols else 0
pow26 = [1]
for _ in range(max_len):
pow26.append(pow26[-1] * 26)
res = []
for s in cols:
val = 0
for i, ch in enumerate(reversed(s.upper())):
val += (ord(ch) - 64) * pow26[i]
res.append(val)
return res
def bijective_encode(n: int, alphabet: str) -> str:
"""任意字符集的無零進制編碼"""
if n <= 0: raise ValueError("n > 0 required")
base = len(alphabet)
if base < 2: raise ValueError("alphabet length >= 2")
res = []
while n > 0:
n -= 1
n, r = divmod(n, base)
res.append(alphabet[r])
return ''.join(reversed(res))
def bijective_decode(s: str, alphabet: str) -> int:
base = len(alphabet)
char_to_val = {ch: i+1 for i, ch in enumerate(alphabet)}
val = 0
for ch in s:
if ch not in char_to_val:
raise ValueError(f"Char {ch} not in alphabet")
val = val * base + char_to_val[ch]
return val
# Excel 專用
EXCEL_ALPHABET = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
def num_to_col(n): return bijective_encode(n, EXCEL_ALPHABET)
def col_to_num(s): return bijective_decode(s, EXCEL_ALPHABET)
# Base32 (RFC 4648 無填充) 示例
BASE32_ALPHABET = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567'
# 注意:標準 Base32 有填充和 0 值,此處僅演示無零變體
openpyxl/pandas 讀寫時列號轉字母。
import pytest
class TestExcelCol:
CASES = [
(1, 'A'), (2, 'B'), (26, 'Z'),
(27, 'AA'), (28, 'AB'), (52, 'AZ'), (53, 'BA'),
(702, 'ZZ'), (703, 'AAA'), (18278, 'ZZZ'),
(16384, 'XFD') # Excel 最大列
]
@pytest.mark.parametrize("num,col", CASES)
def test_roundtrip(self, num, col):
assert number_to_excel_col(num) == col
assert excel_col_to_number(col) == num
def test_invalid_input(self):
with pytest.raises(ValueError):
number_to_excel_col(0)
with pytest.raises(ValueError):
number_to_excel_col(-1)
with pytest.raises(ValueError):
excel_col_to_number("")
with pytest.raises(ValueError):
excel_col_to_number("A1")
with pytest.raises(ValueError):
excel_col_to_number("aBc") # 小寫應轉大寫處理
def test_large_number_performance(self):
import time
start = time.time()
for i in range(1, 100000):
number_to_excel_col(i)
assert time.time() - start < 1.0 # 10萬次 < 1秒
Excel 26 進制是無零進制的經典應用。核心技巧在於編碼時 n -= 1 借位,解碼時累乘累加。掌握通用的 Bijective Base-N 模板,可靈活應對短碼生成、ID 編碼等多種場景。工程上務必加入輸入驗證、大小寫歸一化、異常處理、緩存冪次,並編寫完整測試用例覆蓋邊界 (1, 26, 27, 702, 703, 最大列 XFD=16384)。