How to iterate over all columns of an excel sheet and change colour of specific cells

[nmjvk]

Dear UiPathers

I have an Excel document “Test.xlsx” which has 15 columns starting from “IR” all the way to “JF”. It is very important it starts and end at these specific columns.

The data in the Test.xlsx comes from a data table variable dt.

Inside the data table variable dt there is a lot of information, however, I am only curious about the information which is incorrect. The excel sheet has to only have numbers in it, the moment something else than numbers show up, as for example letters, then I need to mark that cell red. How can I do this in UiPath?

Ps. I know I can do this inside a Assign variable…

String.Join(Environment.NewLine,dt.AsEnumerable.Where(function(x) x.ItemArray.Any(function(m) System.Text.RegularExpressions.Regex.IsMatch(m.ToString,“[a-zA-z]+”))).Select(function(x) String.Join(Environment.NewLine,x.ItemArray.Where(function(y) System.Text.RegularExpressions.Regex.IsMatch(y.ToString,“[a-zA-z]+”)))))

to see if a cell is all numerical or not.

Pss. Nothing in the data table can change. This means, the data/ information has to be at the same places/ cells as the original excel sheet. So no swaping of columns, rows, cells, deleting or adding something. Only the colour of the individual cells which are incorrect can change.

All help and guidance is gladely appreciated. Have a great day/ night forward.

[mahmoud.zaky]

Hi @nmjvk,
You can iterate on DT and make if condition if this cell has numbers or no and you can use above mentioned regex or you can find below solution in mentioned in URL then if this contains letters you can add color to cell and find solution in below URL hope this answer help you

How to check if a string contains only digits?

HI @EngAnalyst You can just use a regex expression like System.Text.RegularExpressions.Regex.IsMatch(YourString,"[0-9]") this will give your vales in true or false that the string contains digits or not Cheers

How to set colour in particular cell in Excel

Hey Mohan, I created this demo as per your question, please check this: [UiPath Tutorial | Set the Color in Cell UiPath | Lookup Data Table UiPath | ExpoHub]

[Anil_G (Anil Gorthi)]

@nmjvk

As you already have the values that are not numeric…

1. Use a for loop to loop theough each value that is not numeric
2. Use a find/replace in excel and pass the value that you get from loop
3. Find/replace will give cel number…use set range color to change the color of the cell

Cheers

[Yoichi]

HI,

Hope the following sample helps you.

Sample20210104-8L.zip (12.8 KB)

Regards,

[nmjvk]

Hello

Thanks for your reply. I am trying to implement your solution but I get this error

Any idea how to fix this?

Translation:
“Argument ‘Condition’: Compiler error(s) encountered processing expression “not CurrentRow.ByIndex(currentItem-2).ToString.IsNumeric”. Option Strict On prohibits the use of operands of the type Object for the operator ‘-’”

[Yoichi]

Hi,

For now, can you check if TypeArgument of ForEach is Int32?

Regards,

[nmjvk]

Oh wow, that fixed it, thanks so much, I will keep working on your solution and see if I can implement it in my project. Thanks for the clarification

[nmjvk]

I got an unexpecting behaviour when trying to implement your solution. My program ALSO changes colour of the blank cells. I forgot to point out, that cells which has anything else than numbers in it has to be colored. Blank cells and cells with only numbers are accepted and should not change colour.

[Anil_G (Anil Gorthi)]

@nmjvk

Did you try the steps mentioned above?

cheers

[nmjvk]

Dear Anil

Are you referring to what you wrote ealier or what Sir Yoichi wrote?

[Anil_G (Anil Gorthi)]

@nmjvk

What I wrote earlier…As you already have the info of the values that are not numbers then you just need to color them

cheers

[Yoichi]

Hi,

Can you try to modify the condition of IF activity as the following?

(not String.IsNullOrEmpty(CurrentRow.ByIndex(currentItem-1).ToString)) AndAlso (not CurrentRow.ByIndex(currentItem-1).ToString.IsNumeric)

Regards,

[nmjvk]

@Yoichi

(not String.IsNullOrEmpty(CurrentRow.ByIndex(currentItem-1).ToString)) AndAlso (not CurrentRow.ByIndex(currentItem-1).ToString.IsNumeric)

The program runs but does not seem to change the colour of any of my cells. Also, looking at my output tab, I see the same numbers showing up “252 - 266” which is the the 15 cells in every row.

[nmjvk]

Dear Anil

I tried, however, I don’t quite see how to make it work.

[nmjvk]

Nevermind I got it working. I made a space between “And” and “Also”.