Write list of integers into excel horizontally

Help Activities
1

Hi all,

I have a list of integers with 16 elements.
E.g: List VerifyList (int32(16){1,1,1,0,0,0,0,0,1,1,1,0,0,0,0,0}.
I want to write this list of integers into an Excel horizontally but for every 4 numbers it writes, it goes to the next row and continues writing 4 more and going to the next row and doing the same.

For eg: This excel shows how I want the list to be written, and the values correspond to the element in the list.
image

How do I go about this, I have used write range but it rights vertically down. I don’t want to hardcode specific elements of integer according to a cell as it will take a lot of effort. Any help is appreciated thanks.

2

@Joecom101

Welcome to the community…

Try this

Create a datatable say dt using build datatable with 4 columsn integer type

  1. Use for loop with enumerable.Range(0,arr.Count/4).ToList
  2. Inside loop use add datarow with array row as {arr(Cint(currentitem*4)),arr(Cint(currentitem*4)+1), arr(Cint(currentitem*4)+2), arr(Cint(currentitem*4)+3)} and datatable as dt
  3. After loop use write range with dt

Assumption the number elements will be always multiple of 4…if you want it more dynamic then we can use two loop and no assumption of 4 would be there

1 Like
4

Hi @Joecom101 ,

You could also maybe check with the below Expression :

DT = (From i In Enumerable.Range(0,CInt(integerList.Count/4)).Select(Function(x)x*4).ToArray
Let ra = integerList.Skip(i).Take(4).Cast(Of Object).ToArray
Select dt.Clone.Rows.Add(ra)).CopyToDatatable

Implementation :

image
image850×401 15.5 KB

Debug Visuals :
image

1 Like
5

Hi, thanks for the answer. This solution works great for my scenario.
Can I also ask if I have 4 of these integerLists, which part of the expression do I have to edit? So for e.g the List1 goes into column1, List2 goes into column2, List3 into column3, List4 into column4. Will this work if I have all 4 lists in one DataTable and I use a write range activity to write it out.

image
image1025×103 2.06 KB

6

Also for example if I wanted to use write range activity to write in excel the list of integers horizontally like this.
What is the expression to use?

image
image1028×22 800 Bytes

7

@Joecom101 ,

Could you show us your implementation done so far with the Expressions provided, I believe we can tweek it a bit to get the required Output.

Considering the assumption that you have used the methods provided above, at last you have also used the Write Range Activity to write the Output.

So with this kind of Setup, we could first perform a Loop using a For Each activity over the List of Integer List created or prepared, the methods mentioned above should be within the For Each activity loop, where each item/list from the for each is given as Input to the Expression.

Now to write Side by Side in the Output sheet, you would require to know after which column we need to write the next integer list, since it is always 4 columns for each integer list.

We could do such handling using the For Each’s Index property, and using it we should be able to also get the Starting Column Letter/Range for Each List.

The Setup should look like below :

image
image788×498 24.6 KB

The Expression used in the Range property :

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(if(index=0,(index+1),(index*4)+1)).ToString+"1"

Here, index is the variable created as the For Each’s Index Property.

The IntegerList is initialised in the below way :

IntegerList = {new Integer(){1,1,1,0,0,0,0,0,1,1,1,0,0,0,0,0},new Integer(){1,1,1,0,0,0,0,0,1,1,1,0,0,0,0,0},new Integer(){1,1,1,0,0,0,0,0,1,1,1,0,0,0,0,0}}
8

This works great! Also, if I want the list to not be split into 4 columns but written in one row just like this, how do I do that?

image
image1028×22 800 Bytes

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.