Search code examples
rpauipath

How can I add two columns in an excel sheet using the column name instead of giving cell number range to locate the columns in Uipath

I basically want to perform addition of two columns and store the result in third column.

But the challenge is I cannot use the cell number of columns to locate the columns. Instead I have to use the column name/header and when I find the header I have to perform addition operation between values of those column and also for storing the resultant column.

I have to use name of 3rd column and not it's position.

Solution

  • For testing purposes I created an Excel that looks like the following:

    excel before

    Now I created this workflow for you that is handling the addition of the columns first and second and it does not matter in which column they are.

    The fetching of the column is pretty easy. The hard part is determining the destination column as you said that this column might switch the column in the future and should not be determined by the column number.

    Step-by-step:

    Step 1:

    So at the begin you read the Excel file. Write the result into DataTable DT1. Then create variable result_index with content DT1.Columns.IndexOf("result (1+2)").

    workflow part 1

    Step 2:

    Now it's getting complicated. First we iterate through our DataTable DT1. Here we determine the letter of the column. This is done by our previous created result_index variable. Then in a Switch we transform that index into an letter. I think that is pretty straightforward:

    • 2 -> C
    • 3 -> D
    • and so on...

    workflow part 2

    Now that we have that letter we can use that for filling the result column. So for generating the cell number we simply take letter.ToString + row_index.ToString. That stands in our case for "D2". And if you look on the Excel file, you will see that this is the first cell of the result data.

    The addition is very easy, as it just sums up the already known columns by (CInt(row("first")) + CInt(row("second"))).ToString.

    workflow part 2.2

    And finally when it has run you will find that result.

    excel after

    Ideas for improvements:

    1. This is not a perfect solution. A huge improvement would be to not use the Write Cell activity. Instead You need to create another DataTable. Fill that will the result data. And replace the Write Cell with a Write Range activity.

    He you set the DataTable as input.

    This will speed up the filling of the lines, so a huge Excel file is filled much faster.

    1. You could also replace the Switch with a char convert. You can find the idea here on this snippet.