Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Feature request] Retrieve column letter #2091

Open
mullimanko opened this issue Nov 7, 2022 · 4 comments · May be fixed by #2305
Open

[Feature request] Retrieve column letter #2091

mullimanko opened this issue Nov 7, 2022 · 4 comments · May be fixed by #2305

Comments

@mullimanko
Copy link

mullimanko commented Nov 7, 2022

Hi,

I would like to suggest adding a function or an argument to retrieve the column letter from a given range. Retrieving the column number is possible with .column in xlwings, but retrieving the column letter is currently not supported with built-in functions (as far as I know). It is possible with the fiddly line
sheet["A1"].address.split("$")[1]

However, it would be smoother to add a built-in function, for example, openpyxl has the function .get_column_letter (and .column_index_from_string), which is used to convert a column index into a column letter, e.g. 3 -> “C” (and “C” -> 3, respectively).

I think adding an argument to .column could be an user-friendly solution.

Cheers
mullimanko

@fzumstein
Copy link
Member

Thanks for your suggestion! For what use case do you need this?

@mullimanko
Copy link
Author

mullimanko commented Nov 8, 2022

This is nice to check if the column retrieved in xlwings actually matches the intended column that exists in the associated open Microsoft Excel program.

Another use case is to specify a range based on another computed range:

col_letter = ws["A1"].expand().last_cell.offset(row_offset=0, column_offset=1).address.split("$")[1]
row_number = 5

ws.range("A2:" + str(col_letter) + str(row_number))

I also just realized that the following is the same:
sheet["A1"].address.split("$")[1] == sheet["A1"].address[1]

sheet["A1"].address[1] is shorter than sheet["A1"].address.split("$")[1] but it is not obvious that one can do that.

@fzumstein
Copy link
Member

If you have the column index, it's usually easier to just work with indices all the way instead of doing cumbersome conversions and string concatenations:

col_ix = ws["A1"].expand().last_cell.offset(row_offset=0, column_offset=1).column
sheet.range((2, 1), (5, col_ix))  # 1-based indexing

For 0-based indexing, use Python's slicing:

sheet[start_row_ix:end_row_ix, start_col_ix:end_col_ix]

@mullimanko
Copy link
Author

mullimanko commented Nov 9, 2022

Both ways to specify ranges (with indices and with letters) are perfectly fine, there is no way that is superior to the other.
The main thing is that it is accommodating to give people the freedom of choice between column numbers and column letters. Some prefer column numbers, others prefer column letters for easier cognitive handling. Since Microsoft Excel uses letters for numbering columns (for example, demonstrable with the command .address), it seems obvious to offer this letter notation also in .column of xlwings.

Here a less cumbersome version of my last example:

col_letter = ws["A1"].expand().last_cell.offset(row_offset=0, column_offset=1).address.split("$")[1]
row_number = 5

ws.range(f"A2:{col_letter}{row_number}")

@philipdp123 philipdp123 linked a pull request Jul 20, 2023 that will close this issue
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants