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.

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.

agree

their are two ways to do this

One is by using Excel Formula

like for example =SUBSTITUTE(ADDRESS(1;colNum;4);â€ś1â€ť;â€śâ€ť)

and other is by mapping

I have used second approach in past and it works

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

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.

This seems to be promising. Can you please tell where and how to use this code

(Forgive the extra questioning, iâ€™m a rookie )

I tried to put it in a variable value- doesnâ€™t seem to work

Hey @Als

please find the attached workflow and let me know the feedback on this.

Excel_Column_Name_mapping.xaml (7.2 KB)

Regardsâ€¦!!

Aksh

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

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

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.

@ClaytonM Hey! Its working correctly now

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

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