由于想遍历Excel的列,所以只能写一个可以迭代的函数,使得我可以逐个查看每列。
我的需求是从F列遍历到KZ列,计算某两列的宽度。
使用时的代码是:
# 获取F:KZ的区间
titleRan = getTltColRange("F:KZ")
# 遍历F:KZ的范围
for colNum in titleRan:
colLtr = getColLtr(colNum)
# 分别获取F1, F2, G1 G2.... K1 K2的值
cls = shtModule.range(f"{colLtr}1").value
lv2 = shtModule.range(f"{colLtr}2").value
print(cls, lv2)
核心代码实现, 字母数字互转:
-
将字母转换为数字
使用了分而治之的方式def getColNum(colLtr: str) -> int: """A -> 0, B -> 1, C -> 2, ..., AA->26, ..., KZ -> 311 :param colLtr: the Column Letter of Excel :return the sequence number of Excel column """ if len(colLtr) == 1: return ord(colLtr) - 65 else: return (ord(colLtr[0]) - 64) * 26 + getColNum(colLtr[1:])
-
数字转字母
def getColLtr(colNum: int) -> str:
"""0 -> A, 1 -> B, 2 -> C, ..., 26->AA, ..., 311 -> KZ
:param colNum: the Column number of the Column Letter of Excel mappings
:return the letter of Excel column
"""
if colNum < 26:
return chr(colNum + 65)
else:
return getColLtr(colNum // 26 - 1) + getColLtr(colNum % 26)
- 获取两纵列间迭代范围
def getTltColRange(titleScope):
"""titleScope : A1:B2, in other words is ColA to Col B
return iterable Range"""
titleStart, titleEnd = titleScope.split(":")
# get the letter of titleStart by regex
titleStartLetter = re.findall(r"[A-Z]+", titleStart)[0]
titleEndLetter = re.findall(r"[A-Z]+", titleEnd)[0]
# convert to number
titleStartLetterNum = getColNum(titleStartLetter)
titleEndLetterNum = getColNum(titleEndLetter)
return range(titleStartLetterNum, titleEndLetterNum)