How to check validate each excel excel with specific value and throw error for that specific cell

[gokul1904 (Glory 01)]

Hi team ,

I want to validate an excel sheet for 35 cells first before processing it.
I want to check if each cell contains the desired value.
For example - In below sheet , if B4 is not equal to “def” , I want to throw an error such as - “Cell B4 contains unexcepted value”

How do I achieve this considering code optimization?
I think a switch case would be ideal in this case (validation of 35 excel cells)

Please let me know your views.
If a switch case is to be considered , then how should I code it?

Regards,
Gokul

[supermanPunch (Arpan)]

@gokul1904

I want to validate an excel sheet for 35 cells first before processing it.

Hi @gokul1904 ,

Since the cells to be validated are already known and fixed, we could maybe use an Excel Sheet where we place these Cell Names and it’s value and Add a Status /Check Column.

Something similar to the below :

We could then Read this Excel sheet and loop through this data using For Each Row in Datatable Activity. We could use Read Cell Activity to read the specific cell from the Target file where we need to Check. Compare the value in the Configured sheet with input sheet as shown in the image below. If mismatch, record it in the Status Column.

Based on the Status Column, we could develop the entire Message that needs to be generated for the Exception Throw.

A Linq can be used further to check if there is any value present in the Status Column. If present, we could then concatenate all the Messages and use it in Throw or use a generic message for the Throw.

DT.AsEnumerable.Any(Function(x)String.IsNullOrWhiteSpace(x("Exception").ToString))

Let us know if you were not able to understand the approach.