How can we add value to specific rows or specific merged rows

Help Studio
1

Hello Everyone…

I’m just trying to write the data into an excel sheet with specific format, But I don’t know whether we can do it or not. If the answer is YES, please let me know.

For example,
I have some items like BAT, TEETH SET, BALL, TOY, SHAVING KIT, WHEEL CHAIR, TRIMMER, WALKING STICK.

I want to filter this data in category wise… as shown below.

image

Thanks in Advance…

2

Hi @Praw1n,

You want to assign a category according to the meaning of the data you read, I understand correctly, right?

To do this, you need to use cognitive services. You can get free trial tokens for Google and AWS. You can check the link and get started.

Regards,
MY

3

thanks for the reply @muhammedyuzuak

Not exactly…

If I want to write the data in a format as it is in image which I shared earlier…
How can I do that using excel automation dynamically.

4

Categorisation is something that requires decision making and rule based inputs

Only then it can be categorised. So that part has to be handled by us

And to write in merged cells
Have a view on this

Hope this would help you resolve this

Cheers @Praw1n

1 Like
5

Thanks for the reply @Palaniyappan

I think my question was conveyed in a wrong way…

Suppose I have an excel sheet as
image

Here I want to add BALL and TOY to Kids row… as shown below
image

Can we add like this…???

Regards,
Prawin

6

Hi @Praw1n ,

According to your Statement, you would want to add Items to the Category which is also being inputted by you. In that case, you would not require to perform any extra methods to Group the Items to their respective categories.

To Approach this Solution, you could follow the Method/Steps presented below :

  1. First, we read the Excel Sheet as a Datatable using Read Range Activity. We would then Require to create a Dictionary which would hold your Category as the Key and the Values as the Items to be Added which is done as below :
    image

We also get the Column which we would require to Merge in Excel, the Column "Category" which is the "A" Column Letter in Excel.

  1. Next, We add the New Items from the Dictionary to the Datatable with it’s corresponding category. Then arrange/Sort the Datatable rows according to the Column "Category".

    image
    image496×522 15.5 KB

  2. We create a Datatable to hold the Cell Range values that needs to be Merged in Category Column.
    image

  3. Next, We populate the Cell Range Datatable with the Index/Row Number Values that could be used for Merging Cells using a Linq Query :
    image

We also Write the Modified Datatable to the Excel, so we can Perform the Merge Operation on the Cells.

  1. For Performing the merging Operation, I have used Interop.Excel inside an Invoke Code Activity. We could also use Other Components from Marketplace.
    image

Below is the Example Workflow :
Excel_AddRows_MergeCells.zip (11.4 KB)

The Cell Range to Merge Will be Presented in the below way :
image

Let us know if this does not work.