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
uu.xlsx (8.6 KB)
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?
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)
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)
ill check the flow thanks @hacky
(From dc In dtnew.Columns.Cast(Of DataColumn)
can u explain this? what is dc and cast of function do?
a general flow could look like this:
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
for calculating the column name mapping to an index number following component is usefully
Let us know your open questions
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
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
where i can learn linq?
and how can i check if the columns are of required datatype
lets do one by one. Is your initial request answered by Hacky and me now running at your end? If not what is missing?
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
could look like this
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
- 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
Thanku so much ill try this
@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?
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
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?