0

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...

Andoni
  • 89
  • 10
  • 2
    Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! See [How much research effort is expected of stackoverflow users?](https://meta.stackoverflow.com/questions/261592) – TheMaster Oct 20 '22 at 12:36
  • 1
    Hint: a first step could be to use regex to extract the column letters and row numbers from your excel range: for example: Columns = re.findall(r'[A-Z]+', xrange) – Swifty Oct 20 '22 at 12:58

2 Answers2

1

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
Andoni
  • 89
  • 10
  • 1
    Perhaps it might be better to add the imported libraries (re and string) at the start of your code. – Swifty Oct 20 '22 at 16:18
1

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"))
Swifty
  • 2,630
  • 2
  • 3
  • 21