How to select a given number of columns starting from the index of a column on excel

Help
, ,
1

Hi everyone, I need a hand for my problem. in practice I would like to select a certain number of columns on excel starting from the index a starting column. someone could help me with my problem

thank you very much for the attention.
fahad

2

Will you please elaborate your problem ?

3

Hi @momi_fede plz remember to give some examples :slight_smile:

I think you can solve your problem with:

  1. Read from A to Z (First column you need to last)
  2. Getting columns needed:
    list = dataTable.Rows.Cast(of DataRow).Select(function(row) row.ItemArray.Skip(i).Take(j) .concat(row.ItemArray.Skip(k).Take(l)).toArray)
    The result (list ) will be a List with data from column i to i+j and column k to k+l

If you need to add more subgroups of columns you can add more ".concat(row…)

  1. If you need to build a datatable after you get your list just use:
    a) build datatable
    b) for each array in list add data row where the new row is the array (add data row input could be an array)

i wish it helps :slight_smile:

4

image
image1846×529 5.11 KB

for example: I would like to select the columns underlined in blue starting from the column underlined in red

PS. the columns I want to select are unknown

@Sob

5

could I kindly ask you for an example? @Ignacio_Insua_Feito

6

yes give 10 secs:

  1. Read de datatable with read range (C1:H10000) Will read C to H columns

In case you need something more complex for example getting C and E to G

  1. Read de datatable with read range (C1:H10000) Will read C to H columns (this will have a dataTable as result
  2. Assign activty (be aware that the variable list MUST have a defined type → List
    list = dataTable.Rows.Cast(of DataRow).Select(function(row) row.ItemArray.Skip(0).Take(1) .concat(row.ItemArray.Skip(2).Take(3)).toArray)
    First skip can be erased since skip(0) is not doing anything.
  3. in case you need to write it againyou should build data table and add the rows as explained before.

i cant send you a xaml example :S sorry.

7

of course!! thanks for your help

8

if you want to select the D cell to H cell means , simply use Read range and in the given range area ,mention the range as D:H.

image

2 Likes
9

thanks for your answer!! can you also tell me how I can be sure that you are doing this function?

10

Hi @momi_fede

Some basic explanation to enable you to use LinQ.

  1. Object types are important.
    DataTAble is an object type that has a lot of different information inside one of them Rows which is a collection of DataRows. I am not being technically precise in order to help you to understand.

Thats why i use dataTable.Rows.Cast(of DataRow) in order to transform a rows collecion to a List type object.

  1. LinQ allows to work with List (Any IEnumerable which is the real type behind the scenes)

LinQ Functions:

Select/Where/Any/First/Last/skip/take/groupby

All of them must be build as .NameFunction(function(variableNameForEachItem) function to be evaluated)

NameFunction should be one of the functions Select, Where etc…and his type is the same as the data inside the list

The funcion should be a function that results in expected value (type:depends of the function used) and will be evaluated for each row.

The result will be different for each function

First/Last will give you one of the items in the list. the function inside could be empty or be a boolean expresion

Where will give you a list of the items in the list. the function inside must be a boolean expresion

Select: Allows you to transform each line and you can give back any data type and will return a list of objects

Skip/take allows you to take/skip a number of items of the list

Once i explain almost every option in case you need to try your result:

Some usefull expresions to evaluate the results of the expressions explained before are:

message box activity with the next funcionts inside

  1. string.join(Environment.Newline,list.Select(function(row) string.join(“;”,row.tolist) this will print all items in the list in different lines whit all columns separated by commas.

  2. list.Count – number of items

  3. string.join(“,”,list.FirstOrDefault) – the first item separated by commas.

1 Like