How to fill value in excel dynamically

[c3f1e68294fdcf4a1f0a817ca (Rakesh Gupta)]

How to fill in the value on run time in Excel.

E.g. If the input is { “1200,51.5”, “1206,5”, “1265,1”, “1206,8”, “1200,20.5” }
then
51.5 will go under Regular at C15
5 will go under Training at D15
1 will go under OT / CTO at E15
8 will go under Training at D16 ( As we have already filled at D15 above )
20.5 will go under Regular at C16 ( As we have already filled at C15 above )

Here 1200 Code belongs to the Regular Column
1206 Code belongs to the Training Column
12065 Code belongs to the OT / CTO Column
Like that, there is a Code for each column

The template format is fixed.

Excel after filling in data will look like the below:

Template.xlsx (14.3 KB)

[Anil_G (Anil Gorthi)]

@c3f1e68294fdcf4a1f0a817ca

if you are getting array then loop through array

Initially have a intindex variable initiated with value as 15 and type integer

then use str.Split({","},StringSplitOptions.None)(0).Equals("1200")

Use write cell with cell as "C" + intindex.ToString and write the values str.Split({","},StringSplitOptions.None)(1)

Use the similar condition for each column

After all incrmenet the intindex

cheers

[c3f1e68294fdcf4a1f0a817ca (Rakesh Gupta)]

I may get or not get 1200, I mean
If I am getting 1200 then 51.5 the value will be in the Regular Column, same for all.
I may get any code( like 1200), for each Column we have different code value.

[Anil_G (Anil Gorthi)]

@c3f1e68294fdcf4a1f0a817ca

Okay instead of IF use switch activity with currentItem.Split({","},StringSplitOptions.None)(0)

and each case can be each column number like 1200,1501 etc…and inside each case use write cell accordingly instead of C change to D etc

cheers

[c3f1e68294fdcf4a1f0a817ca (Rakesh Gupta)]

Not getting, can you please elobrate?

[Anil_G (Anil Gorthi)]

@c3f1e68294fdcf4a1f0a817ca

Please looks at this

cheers

[c3f1e68294fdcf4a1f0a817ca (Rakesh Gupta)]

you are a Gem, Superb.
One last doubt, Can we assign multiple codes for Single Column, I mean Can I assign 1200,1300,1250 all these three codes to the Regular column ( Column C in this case) or do I have to write the switch case three times?
Similarly, for other columns also like Code 1201,1401,1562 and 1587 for the Training Column.

[Anil_G (Anil Gorthi)]

@c3f1e68294fdcf4a1f0a817ca

If you want to use switch…then you need to add as different cases but ehat to do inside case will be same across the similr cases

Else we can use if condition where we can give multiple conditions like {"1234,"1200","1341"}.Contains(currentItem.Split({","},Stringsplitoptions.None)(1)) which will check for all the conditions at one and we cna use on then side the required write cell…and in else another condition for other column

Both the ways work

Cheers

[aslam_ali]

Above code was very well explained.
Please do some small modification according to your requirement in the above code.
1- Since you required to updated in 3 columns C, D and E then create 3 counter variable out of foreach loop with default value associated with each columns. eg x=15, y=15 and z=15.
2- Move counter increment code inside your case block and increment counter variable associated to column C or D or E. so than next time only increment will be happen in same column position.

[c3f1e68294fdcf4a1f0a817ca (Rakesh Gupta)]

Getting this error:

[Anil_G (Anil Gorthi)]

@Anil_G

{“1234,“1200”,“1341”}.Contains(currentItem.Split({”,"},Stringsplitoptions.None)(1))

@c3f1e68294fdcf4a1f0a817ca

missed a bracket added it please correct

cheers