从列号获取 Excel 样式的列名 [英] Get Excel-Style Column Names from Column Number

问题描述

这是在提供行和列 ID 时提供列名称的​​代码，但是当我给出像 row = 1 和 col = 104 这样的值时，它应该返回 CZ，但它返回 D@

This is the code for providing the COLUMN name when the row and col ID is provided but when I give values like row = 1 and col = 104, it should return CZ, but it returns D@

row = 1
col = 104
div = col
column_label = str()
while div:
    (div, mod) = divmod(div, 26)
    column_label = chr(mod + 64) + column_label

print column_label

我的做法有什么问题?

(此代码作为 EXCEL 列的参考，其中我提供了行、列 ID 值并期望 ALPHABETIC 值相同.)

(This code is in reference for EXCEL Columns, where I provide the Row,Column ID value and expect the ALPHABETIC value for the same.)

推荐答案

我觉得我必须承认，正如其他一些人所指出的——他们从未给我留下评论——我的回答的先前版本(你接受了) 有一个错误，使其无法正确处理大于 702 的列号(对应于 Excel 列 'ZZ').因此，为了正确起见，这已在下面的代码中得到修复，现在它包含一个循环，就像许多其他答案一样.

I feel I must admit, as pointed out by a few others—who never left me comments—that the previous version of my answer (which you accepted) had a bug that prevented it from properly handling column numbers greater than 702 (corresponding to Excel column 'ZZ'). So, in the interests of correctness, that's been fixed in the code below, which now contains a loop just like many of the other answers do.

很可能您从未使用过具有足够大列数的先前版本而遇到过该问题.FWIW，MS 规格对于当前版本的 Excel，说它支持最多 16,384 列的工作表(Excel 列 'XFD').

It's quite likely you never used the previous version with large enough column numbers to have encountered the issue. FWIW, the MS specs for the current version of Excel say it supports worksheets with up to 16,384 columns (Excel column 'XFD').

LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

def excel_style(row, col):
    """ Convert given row and column number to an Excel-style cell name. """
    result = []
    while col:
        col, rem = divmod(col-1, 26)
        result[:0] = LETTERS[rem]
    return ''.join(result) + str(row)

if __name__ == '__main__':
    addresses = [(1,  1), (1, 26),
                 (1, 27), (1, 52),
                 (1, 53), (1, 78),
                 (1, 79), (1, 104),
                 (1, 18253), (1, 18278),
                 (1, 702),  # -> 'ZZ1'
                 (1, 703),  # -> 'AAA1'
                 (1, 16384), # -> 'XFD1'
                 (1, 35277039)]

    print('({:3}, {:>10}) --> {}'.format('row', 'col', 'Excel'))
    print('==========================')
    for row, col in addresses:
        print('({:3}, {:10,}) --> {!r}'.format(row, col, excel_style(row, col)))

输出:

(row,       col) --> Excel
========================
(  1,         1) --> 'A1'
(  1,        26) --> 'Z1'
(  1,        27) --> 'AA1'
(  1,        52) --> 'AZ1'
(  1,        53) --> 'BA1'
(  1,        78) --> 'BZ1'
(  1,        79) --> 'CA1'
(  1,       104) --> 'CZ1'
(  1,     18253) --> 'ZZA1'
(  1,     18278) --> 'ZZZ1'
(  1,       702) --> 'ZZ1'
(  1,       703) --> 'AAA1'
(  1,     16384) --> 'XFD1'
(  1,  35277039) --> 'BYEBYE1'

这篇关于从列号获取 Excel 样式的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！