Technical Articles
How to convert an integer into an Excel column ID and back
I meet this requirement from time to time.
Each time I start from scratch. This time I decided to put a note here.
A pretty simple task is to convert a number, say 3 to a column, like C. Or, not less obvious, 1467 to BDK.
Here’s the code for int to column name conversion (without additional checks) and back:
* <SIGNATURE>---------------------------------------------------------------------------------------+ * | Static Public Method ZB74H_NEXUS_GSHEETS_READWRITE=>GET_COLUMN_FROM_INT * +-------------------------------------------------------------------------------------------------+ * | [--->] IV_COLUMN_NUM TYPE INT4 * | [<-()] RV_RESULT TYPE STRING * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD get_column_from_int. CHECK iv_column_num > 0. DO. DATA(lv_mod) = ( iv_column_num - 1 ) MOD 26. DATA(lv_div) = iv_column_num DIV 26. iv_column_num = lv_div. rv_result = sy-abcde+lv_mod(1) && rv_result. IF iv_column_num <= 0. EXIT. ENDIF. ENDDO. ENDMETHOD. * <SIGNATURE>---------------------------------------------------------------------------------------+ * | Static Public Method ZB74H_NEXUS_GSHEETS_READWRITE=>GET_INT_FROM_COLUMN * +-------------------------------------------------------------------------------------------------+ * | [--->] IV_COLUMN TYPE STRING * | [<-()] RV_RESULT TYPE INT4 * +--------------------------------------------------------------------------------------</SIGNATURE> METHOD get_int_from_column. DO. IF iv_column IS INITIAL. RETURN. ENDIF. FIND iv_column(1) IN sy-abcde MATCH OFFSET DATA(lv_offset). rv_result = rv_result * 26 + lv_offset + 1. iv_column = iv_column+1. ENDDO. ENDMETHOD.
I was little bit confused by blog title. Is it possible to add "Excel column" to specify what is it about? 🙂
Also found one old method which I used for same thing. Similar approach to yours:
Yep, I second Tomas. You could use the comments from his method (input - return...) would be better in place of "3 to a column, like C. Or, not less obvious, 1467 to BDK."
An Excel screenshot too!
Hi Sandra,
Not sure if I get your point. I don't think I need to add a screenshot of an Excel to illustrate an Excel Sheet. Do you think it worth to add it here like a lovely picture with column letters and with numbers? Actually I was amazed to have here 2 comments for a very ordinary blog post! 🙂
I had no feedback for much more complicated solutions.
Kindest regards,
Alex
I post a comment only when I feel it's needed. The important word "Excel" is missing in your title, it took me 30 seconds to understand what you were talking about. The only two people who react, tell you that it's unclear, no comment with "thank you" from other persons as usual... Well, okay, do as you wish anyway 🙂
Hi Tomas,
Updated the title. Not sure if didn't make it worse 🙂
Right, there are some other ways to do the same ans frankly speaking today I would change a bit the code of myself. But it's never ending story and the point was to have this logic somewhere to be able to come back and get easily because 30 secs of googling did not give me a good answer. I found a version with recursion (OMG) and stopped looking other options 🙂
Cheers,
Alex
Are you sure you updated the title? The word "Excel" is still missing 🙂
There's not only the MS Excel table editor. That one is for some money. You can also use Google Sheets where the logic is the same for columns. And there are some more table editors that have the same table structure but are called differently 🙂
It was just that "table column char" could be related to absolutely anything, not even closely related to a "spreadsheet" software. Or an example might help understanding like "(e.g. Z -> 26, AA -> 27)". Anyway, that's your post, and I referenced your useful post in my notes, so thank you 🙂
A little bug with GET_COLUMN_FROM_INT( 26 ), that gives ‘AZ’ instead of ‘Z’.
Before correction:
After correction:
LOL, count me in as a confused one. I was also like "what the heck does this mean" until I realized you mean Excel columns. 🙂
Sorry, the title is still confusing. "Table column" on SCN = immediate association with a transparent table field. May I suggest "How to convert an integer into an Excel column ID and back"?
Even if this code might have broader application than Excel I feel mentioning it explicitly will make the purpose perfectly clear from the start.
Also I'm curious in what business context such requirements occur and whether ABAP2XLSX could help with this?
Thank you!
True! Some time passed and I myself got a little confused. Changed the title. Thank you for your suggestion
For information, it can be achieved by using these 2 methods of ABAP2XLSX: