Convert an excel or spreadsheet column letter to its number in Pythonic fashion

Question!

Is there a more pythonic way of converting excel-style columns to numbers (starting with 1)?

Working code up to two letters:

def column_to_number(c):
    """Return number corresponding to excel-style column."""
    number=-25
    for l in c:
        if not l in string.ascii_letters:
            return False
        number+=ord(l.upper())-64+25
    return number

Code runs:

>>> column_to_number('2')
False
>>> column_to_number('A')
1
>>> column_to_number('AB')
28

Three letters not working.

>>> column_to_number('ABA')
54
>>> column_to_number('AAB')
54

Reference: question answered in C#

By : paragbaxi


Answers

I'm not sure I understand properly, do you want to "translate" the referenced C# code to python? If so, you were on the right track; just modify it so:

def column_to_number(c):
  """Return number corresponding to excel-style column."""
  sum = 0
  for l in c:
    if not l in string.ascii_letters:
      return False
    sum*=26
    sum =ord(l.upper())-64
  return sum
By : Joubarc


just do :

print ws.Range("E2").Column

call example :

from win32com import client
xl = client.Dispatch("Excel.Application")
wb = xl.Workbooks.Open("c:/somePath/file.xls")
xl.Visible = 1
ws = wb.Sheets("sheet 1")
print ws.Range("E2").Column

result :



Using openpyxl

import openpyxl
(column_string, row) = openpyxl.cell.coordinate_from_string(address)
column = openpyxl.cell.column_index_from_string(column_string) 
By : Chad


This video can help you solving your question :)
By: admin