reading header name of columns and validating it

Help
, ,
1

Hi
i have an excel which i have used excel application scope and now having an datatable “dt” now i have 8 columns which is having headers(column name) now i want to check the column names are equal and sequence from left to right which i hav to check with the string variables i have and if any column not equal highlight tht column with a colour

2

uu.xlsx (8.6 KB)

3

@Palaniyappan @hacky can anyone please check?
i tried this to get column names but i cnt able to put counter inplace of 0
image

4

@Jebarohith19
could we clear some details.

  • Excel reading is done and we do have it available in a datatable.
  • the column names should be checked on name and orders
  • if column names/ orders differs in the original excel the cell with the column name should be colored e.g. red

you told, that the reference columns names are available as a string

Related to the process how is the string brought into the process. By Config or Hardcoded?

1 Like
5

@Jebarohith19
Please find the xaml file, you shall be able to get the head names as a string array as shown in the xaml file.

Kindly mark as solution if you got your answer.

@Jebarohith19_Solution.xaml (5.8 KB)

1 Like
6

hardcoded @ppr and columns to be highlighted in colour if it doesnt meeet certain criteria(like datatype of that column should be number , i dont know how to check the datatype of the column is there any activity)

7

ill check the flow thanks @hacky

8

(From dc In dtnew.Columns.Cast(Of DataColumn)
Select dc.ColumnName).ToArray()
can u explain this? what is dc and cast of function do?

9

@Jebarohith19

a general flow could look like this:

grafik
grafik1151×673 24.1 KB

Retrieving the Columnnames as an String Array (similar to Hacky’s approach)
dtData.Columns.Cast(Of DataColumn).Select(Function (x) x.Columnname).toArray

  • the datacolumnCollection is assigned to cast it into an enumerable of datatype Datacolumn and the columnname will be collected into an string array

then the Indexes of the not matching columnnames is calculated. We can do it with essential activities or with linq like this:

ColumnNames.Select(Function (x, index) New Tuple (Of Int32, String)(index, x)).Where(function (t) Not t.Item2.Equals(ColumnRefNames(t.Item1))).Select(Function (t) t.Item1).toList

over this list can be iterated and the index can be used for marking the cell
grafik

for calculating the column name mapping to an index number following component is usefully

Let us know your open questions

10

i havent used linq query in any of my activities so it has any alternatives?
or where can i learn these linq query to attain knowledge
i can see many people using linq queries but i couldnt gt it

11

Yes of course (I like LINQ very much for prototyping, then I decide to use it or not dependend to the team member skills)

a for each activity will do it and we can use the index output from this activity similiar it is done within the linq statement

12

where i can learn linq?
and how can i check if the columns are of required datatype

13

lets do one by one. Is your initial request answered by Hacky and me now running at your end? If not what is missing?

14

getting column names is satisfied
then checking it is in ccorrect order
then checking if the column is of specific datatype if it is not colour it

15

@Jebarohith19
could look like this

grafik
grafik1606×713 37.2 KB 

col.ColumnName.equals(ColumnRefNames(idx)) And col.DataType = GetType(String)
  • iterate over each column of the datable
  • checks same columnname in ColumnRefNames under the current iteration index if names are same
    AND
  • checks if the Datatype of the datacolumn is e.g. String

If it is not matching then the index will be added to the NotMatchedIdexes list and can be used for the Column Range calculation (the letter Part from e.g B1). As Excel is 1 based in the index to columnname (e.g B=2) and iteration index is 0 based the statement for adding the idx is: idx + 1

16

Thanku so much ill try this

17

@hacky a problem with set range color
set range color needs excelapplication scope but i have used redrange on the front it has some activities when i try to use excelapplicationscope workflow stops because the excel is already in use
what can i do now?

18

@Jebarohith19
I am not sure if I got your question in all. But have a look on the first Excel Application Scope. This activity can give an output to the workbook and this can be used on following Application Scopes. Maybe this helps

grafik

19

there is already a read range activity in use in previous activities(long process contains several activities) so if im using application scope for setrangecolor an excel will be already in use right so it is throwing error like excel already in use
now how do i solve this can i give name of the old datatable in existingworkbook?

20

image
image1366×768 75.2 KB