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

Question

Hello Team, 
 We have a requirement to populate data into an excel template which will spawn for multiple sheets. This would be in a process model as we fetch the data to populate from a rest service. 
 We were able to achieve populating data into the excel template using the using "Export Data Store Entity to Excel Smart Service". It works fine for regular mapping. 
 But we are stuck with these 2 requirements. 
 1) We are were not able to dynamically insert rows into output excel sheet in this flow, when needed. There is a requirement to dynamically insert and populate rows in certain places (in the template), which we are not able to get through. 
 2) Also we are looking to see if there are any plugins or ability to dynamically delete redundant sheet from the output excel. 
 Appreciate any inputs and suggestions.

Chirag Singodiya · Answer

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 - 
 
 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*/ 
 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)
 )
) 
 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.

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

Top Replies