How to transform excel range (e.g. AA1:BA2) to a [rows,columns] list?-CodePudding

I am coding a python function that has as input parameter certain excel range (e.g. "A1:B2"). How can I make python return a list of nº rows and nº columns from here?? (for instance if A1:B2 is the input, this list shall return [2,2])

This is what I did:

def return_rows_columns_from_excel_range(excel_range):
# import string
# import re
# excel range could be something like "A1:C15"
alphabet = list(string.ascii_uppercase)

first_cell = excel_range.split(":")[0]
second_cell = excel_range.split(":")[1]

column_1 = re.search('\D ',first_cell)[0]
column_2 = re.search('\D ',second_cell)[0]

row_1 = int(re.search('\d ',first_cell)[0])
row_2 = int(re.search('\d ',second_cell)[0])

columns = alphabet.index(column_2) - alphabet.index(column_1)   1
rows = row_2 - row_1   1
output = [rows,columns]

return output

This is valid if ranges are like "A1:C15", but if the range is like "AA1:AC13" I am not very sure how to tackle it...

CodePudding user response：

Another solution, using Regex as I mentioned earlier:

import re

def column_to_number(my_string):
    x = 0
    for i in range(len(my_string)):
        x  = 26**(len(my_string) - i - 1)*(ord(my_string[i])-64)
    return x
    
def dim(xrange):
    
    columns = re.findall(r'[A-Z] ', xrange)
    width = column_to_number(columns[1]) - column_to_number(columns[0])   1
    
    lines = re.findall(r'[0-9] ', xrange)
    height = int(lines[1]) - int(lines[0])   1
    
    return [height, width]
    
    
    
print(dim("A1:E32"))
    
print(dim("T5:KA5"))

CodePudding user response：

This is the most straightforward solution that I've come up with:

import re
import string
def return_rows_columns_from_excel_range_v1(excel_range):
    
    def col2num(col):
        # Thx https://stackoverflow.com/a/12640614/8972132
        num = 0
        for c in col:
            if c in string.ascii_letters:
                num = num * 26   (ord(c.upper()) - ord('A'))   1
        return num
    
    first_cell = excel_range.split(":")[0]
    second_cell = excel_range.split(":")[1]
    
    column_1 = re.search('\D ',first_cell)[0]
    column_2 = re.search('\D ',second_cell)[0]
    
    row_1 = int(re.search('\d ',first_cell)[0])
    row_2 = int(re.search('\d ',second_cell)[0])

    columns = col2num(column_2) - col2num(column_1)   1
    rows = row_2 - row_1   1
    output = [rows,columns]
    
    return output