Write to excel template - insert rows dynamically in excel template in process model

YOu can generate HTML documents for each one of the sheets, and after use Excel Tools to convert that documents into Excel.

And use Excel utilities to merge files deleting duplicates.

community.appian.com/.../excel-tools

 Excel Utility 

Another option would be to genereate a Json, and use Json to excel plugin to generate your file, and delete duplicates in that json....

Anyway, I would suggest to clean data before inserting into excel documents

Thanks for your response. But we have no issue in populating the excel template using "Export Data Store Entity to Excel Smart Service". But this wouldn't insert rows into excel sheet.

As the excel template is 'quite a template' with its own formats and could change periodically - we don't want to replicate that into an HTML - which will inflict lot of work into Appian and push us to do change the HTML consistently.

Feel free to suggest an alternatives if you come across.

Thanks again for your time for thinking through this.

Approach: Generate Excel cell positions dynamically based on data and create a list of cell positions and values.

Steps to dynamically insert rows in Excel using Export Data Store Entity to Excel Smart Service -

1. Create a rule to generate cell positions for Excel export dynamically:
   Rule Name: PA_generatePositionsForExcelExport
   

   if(
       a!isNullOrEmpty(ri!rowNumber),
       ri!startingPosition,
       concat(
           stripwith(ri!startingPosition, "0123456789"),
           tointeger(ri!startingPosition) + (ri!rowNumber - 1)
       )
   )
   
       /*Input -> startingPosition: A1 and rowNumber: 4*/
       /*Output -> A4*/

   
   
   
2. Create a rule for excel cell positions and values
    

   a!localVariables(
     local!data: { { id: 1, name: "John" }, { id: 2, name: "Tom" } },
     local!startingPosOfId: "A1",
     local!startingPosOfName: "B1",
     local!mapping: a!forEach(
       items: local!data,
       expression: {
         a!map(
           position: rule!PA_generatePositionsForExcelExport(
             startingPosition: local!startingPosOfId,
             rowNumber: fv!index
           ),
           value: fv!item.id
         ),
         a!map(
           position: rule!PA_generatePositionsForExcelExport(
             startingPosition: local!startingPosOfName,
             rowNumber: fv!index
           ),
           value: fv!item.name
         )
       }
     ),
     a!map(
       positions: index(local!mapping, "position", null),
       value: index(local!mapping, "value", null)
     )
   )

   
   
   
3. Use the rule created in step 2 as inputs in Export Data Store Entity to Excel Smart Service
   
   Input: Custom Cell Positions - index(rule!PA_excelCellPositionsAndValues, "positions", null)
   Input: Custom Cell Values - index(rule!PA_excelCellPositionsAndValues, "values", null)

For the second requirement, you can use the Delete Excel Sheet smart service from the SQL to Excel plugin.