Get last column of Excel sheet in Alphanumeric form

Help
,
1

I want to write a new column at the end of my excel sheet. I copied the data in a datatable. From that i am able to get the count of columns from totalColumns= DataTable.Columns.Count.
I need the column in alpha numeric form ie AC12 or Z etc instead of number (200 or 26).
There is a package EPPlus that may be helpful.However, i am not able to find the function that will give me the result.

2 Likes
2

Try this.
Convert.ToChar(dt.Columns.Count + 64)

It’s vb.net so I did a search for the syntax.

EDIT: Oh and AA-ZZ columns might take additional coding.

3

agree

their are two ways to do this :slight_smile:
One is by using Excel Formula
like for example =SUBSTITUTE(ADDRESS(1;colNum;4);“1”;“”)

and other is by mapping :slight_smile:

I have used second approach in past and it works :slight_smile:

int Column_Number= your column number(int);
string columnName = String.Empty;
int modulo;

while (dividend > 0)
{
    modulo = (Column_Number- 1) % 26;
    columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
    dividend = (int)((Column_Number- modulo) / 26);
}

Regards…!!
AKsh

4

I like your coding method.
This is what I got for a 1-liner to use in a UiPath field like Range
If(dt.Columns.Count<=26,Convert.ToChar(dt.Columns.Count + 64),Convert.ToChar(CInt(dt.Columns.Count/26) + 64)+Convert.ToChar(If(dt.Columns.Count mod 26 = 0,26,(dt.Columns.Count mod 26)) + 64))

So you use Int(count/26) for first character and (count mod 26) for second character, basically. If mod = 0 then use 26 for Z.

It could be improved.

1 Like
5

This seems to be promising. Can you please tell where and how to use this code
(Forgive the extra questioning, i’m a rookie :sweat_smile:)
I tried to put it in a variable value- doesn’t seem to work

6

image
image811Ă—68 3.17 KB

So on giving value as 12, 200 etc to variable columns-- i am getting output as “@”

7

Hey @Als

please find the attached workflow :slight_smile: and let me know the feedback on this.
Excel_Column_Name_mapping.xaml (7.2 KB)

Regards…!!
Aksh

17 Likes
8

You ,my friend, are a life saver! It works perfectly!! Thanks :dancer:

PS- I wanted to get the last column-- but seems my excel sheet has rogue data in it-- Apparently the last free column is AE ie 31. But column count is coming 221 :confused:

9

hey @Als,
I figured out what was wrong with my solution. I have added it to @aksh1yadav 's example, so you can check that one out if you’d like also.
Excel_Column_Name_mapping.xaml (8.5 KB)

If you have trouble getting the column number correct, you could do an indexOf of your datatable maybe?
For example,
dt1.Columns.IndexOf(“columnname”)+1

Thanks.

3 Likes
10

@ClaytonM Hey! Its working correctly now :ok_hand:
The problem is that i am not sure what the last header is going to be,it could be blank header for all i know - so need to get the last empty column blindly

11

@Als Ah. Seems like you would need to loop through each Column, filter it to non-empties, then take a count of the rows found.

For example,
n=-1
Do
n=n+1
While ( dt.AsEnumerable().Where(Function(row) row(n).ToString.Trim<>“”).ToArray().Count > 0 and n<dt.Columns.Count-1 )

Untested

Therefore, n would give you the end of the columns. You could also work backwards and initialize n to .Columns.Count with n=n-1 instead of n=n+1

I’m not a fan of looping through rows and columns of a datatable but that’s an option.

Thanks.

1 Like