How to insert items after every row in excel file

Hi Folks, could you please provide a xaml file solution to the below query. I would highly appreciate you send out the xaml file on this thread :slight_smile:


I have an excel file and need to perform certain excel operations on it.
Excel File :
Master_File(2).xlsx (9.9 KB)

The source file looks like below.

I want to add NET, PAY, NON-PAY AND INCOME after every row present like below :


Additionally, I want the Region ID column to add 6 digits after every row item, when,
Column data is NET regional id be 102031000000,
PAY regional id should be 102031100000,
NON-PAY regional id should be 102031200000,
INCOME regional id should be 102031300000.


So the final excel must look like below:


You can follow the steps

  1. Build a datatable with 6 columns and name it outdt
  2. read datatable into indt
  3. Use for each row in datatable activity with indt
  4. Inside loop use add datarow and give datatable as outdt and array row as {currentRow(0).ToString,currentRow(1).ToString,currentRow(2).ToString + "000000",currentRow(3).ToString,currentRow(4).ToString,"Net"}
  5. Similar to step 4 add 3 more add data row activities and change values as needed
  6. Use write range activity and write the outdt back to excel


With essentials

  • create / build second table - dt2
Code2 Category
000000 NET
100000 PAY
200000 NON PAY
300000 INCOME
  • read range your excel - dt1

  • Add Datacolumn - Add the Category Column, if not already present to dt1

  • Assign activty: dt3 = dt1.Clone

  • For each row in datatable | orow in dt1
    |-- * For each row in datatable | irow in dt2
    |----- * Assign Activity: newRow = dt3.NewRow
    |----- * Assign Activity: newRow.ItemArray = orow.ItemArray
    |----- * Assign Activity: newRow("Regional ID") = newRow("Regional ID").toString & irow(Code2).toString
    |----- * Assign Activity: newRow(“Category”) = irow(“Category”)
    |-- * Add Data Row: newRow to dt3

  • write range - dt3

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