Reading an excel file and highligting contents based on condition

Help
1

Hi,
I have started exploring UIpath and as one of the exercise i am planning to the following

Date and Time Temperature Humidity
01-08-19 09:47 25 50
01-08-19 09:57 25 55
01-08-19 10:07 26 55
01-08-19 10:17 27 55
01-08-19 10:27 28 55
01-08-19 10:37 29 60
01-08-19 10:47 24 55
01-08-19 10:57 34 55
01-08-19 11:07 25 55
01-08-19 11:17 46 55
01-08-19 11:27 25 50
01-08-19 11:37 23 20

I have an excel sheet , this in real scenario will be more in numbers, and the scope is to

  • find any logged time which is more than 10 min interval
  • 2nd column any value more than 49
  • 2rd column any value more than 50
    the values crossing the threshold should be highlighted.

Any high level help will be really helpful.

2

This can be done in two ways @balaji_yadav
Solution 1 :

  1. Save the data in datatable by reading the excel using read range activity
  2. Loop through the data and check whether the data exceeds the range using IF condition
  3. If exceeds, you will get the index of the row using datatable.Rows.IndexOf(row) and assign to a variable (range)
  4. Inside then, open the same excel and use Set Range color activity and pass the range as “C” + range.tostring
  5. It will set the range color

Solution 2:

WE can use the VB code to do that . Let me know if you are willing to do that using code :slight_smile:

2 Likes
3

Hello @balaji_yadav
convert the first row to date time datatype so that you can add 10 minutes to it than compare it with dates in the sheet than use if conditions to compare for all the three conditions
take a look at this workflow for better understanding
Temper.xaml (16.7 KB)
tempera.xlsx (9.1 KB)

2 Likes
4

Thanks Vicky for your prompt support. I am able run it with and get the expected result.

Regards
Balaji

5

Hi Hareesh,

Since i am new to This can be elaborate on the following

  • in the second steps of yours how check the data exceed in an datetime format
  • how to do the point 3 and 4
6

You will get the value as row(“Date and Time”).Tostring and it is of type string,

  1. Before If condition, assign the value to a variable of type DateTime and convert the value you got above as DateTime.Parse(variable you assigned value of row)

  2. Use assign activity in THEN and get the index of that row as mentioned above and assign it to a variable

  3. Again within THEN, use excel application scope, and set Range color activity in the body