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

问题描述

这是提供行和列ID时提供COLUMN名称的代码，但是当我给出如$ code> 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

我在做什么有什么问题？

What is wrong with what I am doing?

（此代码是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屋！