How to write cell in excel

Help Studio
, ,
1

Scenario:

I have an excel whiich is read into a datatable which I am looping via foreach datarow - DT
I have another datatable which I am looping to get the columnnames - DTImported

Both DT and DTImport has same set of columnnames and in same sequence. Only, DT has few extra columns added.

Now, I want to write DT data into the DT excel sing write cell activity but I am not able to find out how to traverse thorugh the Cell range like for eg:

DT has column name called : Account number
Now, DTImport has to be read and the Account number value from DTImport has to be written inside the corrosponding cell of DT. How to get the Cell number for it like A2/B3/C5 ?

2

Hi @Sumit_Ghosh1

You can update the values in your datatable DT and then write it to Excel using write range.
How would you find the corresponding Row in DT? Do both datatables have same rows?

3

Hello @Sumit_Ghosh1

If there are any unique identifier in both the tables, can you try with Join datatable activity and use Left join?

Thanks

4

I can but DT excel is having exiting data as well. Using write range will cause it to overwrite

5

@Sumit_Ghosh1
It won’t overide since all your data is already in the Datatable DT. Take a look at this:
BlankProcess C.zip (366.0 KB)

6

However, one catch

I want to read single row from DT datatable and want to upload that datarow to the DT1 excel. How can we address that as write range will take the whole DT as input?

7

Hi @Sumit_Ghosh1 ,

Could you maybe address your issue / problem statement with a Help of Sample Input Data and Output Data, You could provide us Screenshots of the same or the excel files itself.

This would help us get rid of mid confusions that would happen and lead the right approach/solution.

Basically you seem to update DT with the value received from DTImport, a preferable first try would be with the below Steps :

  1. Use For Each Row in Datatable Activity to loop through DT.
  2. Next, Use Look Up Datatable Activity to find the corresponding row in DTImport based on a Key Value (Look Up Value and Look Up Column need to be specified) that would be matched between two datatables. The Target Column should be specified as "Account Number" as that would be the value you would want to update in DT. Also specify/assign the rowIndex property to a variable.
  3. Using the rowIndex we can decide whether there was a match found or not using an If Activity with expression as below :
Not(rowIndex = -1)
  1. Next, In then clause we can update the Account Number column in DT by using an Assign Activity with the value received from Look Up Datatable Activity .
row("Account Number") = accountNumber

The Skeleton/workflow would be in the below manner :

image
image1044×466 32.9 KB

If the above explanation doesn’t help, Do provide us with some data as suggested.