Get the Column Index Name From Excel

Help Activities
, , ,
1

I want to get the column Index Name which is (G,H,I,J Etc… for the column name of Given Month)

If my condition said May-22 i need to get the ‘H’ as ColumnIndexName

Thank you

image
image890×200 15.1 KB

2

Hi @Chaitan

→ Use the assign activity and create a int32 datatype variable called columnindex.

columnindex = (From col As DataColumn In YourDataTable.Columns
                  Where col.ColumnName = "May-22"
                  Select YourDataTable.Columns.IndexOf(col)).FirstOrDefault()

→ The above linq used to get the column index which matches the condition.
→ After that use one more assign activity to get the index in alphabets.

- Assign -> Output = Convert.ToChar(YourDataTable.Columns.Indexof(columnindex)+65).ToString

Note - YourDataTable is the datatable variable change it in your code with datatable variable.

Hope it helps!!

3

As of newer times, we can use the Helper methods:

And do have also the benefit, that we are not limited after Z instead of the Convert.ToChar(65 + X Approach

calculate the position by

  • using datatable and the ordinal property from the data column (DataTable approach)
    OR
  • retrieve first line from excel and calculate position index from there
4

we keep in mind that above can be decomplexed to:
myColPos = dtDataVar.Columns(“May-22”).Ordinal

And
myExcelColLetter = UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(myColPos + 1)

5

Hi @Chaitan

→ Read Range Workbook
Output-> dt
→ Use below syntax in Assign:

Output = If(dt.Columns.Contains("May-22"), Convert.ToChar(65 + dt.Columns.IndexOf("Date")).ToString(), "")

Output is of DataType System.String

Hope it helps!!