Read data from excel sheet and populate on eForms

Sometimes the requirement is to have an AgilePoint eForm in which an Excel file can be uploaded. Once uploaded we need to grab the data in the Excel file and populate it to the different fields in the form.

There are N number of ways of doing this based on Effort VS Reusability Vs Customer’s willingness to pay

Use Case 1: If you have an excel which is created using an excel template where you just need to read certain columns (not multiple rows) to be read out of excel and map them to workflow form then you can use excel services shapes. We have 3 of them

• Excel Read
• Excel Write
• Excel Calculate

For e.g. in following image, named cells from a structured excel is loaded and mapped to fields on the form

Please remember that in order for excel services to parse the metadata out of a excel, your excel needs to have a structure with name given to particular cell and you can read those named cells. However this does not deal with multiple rows but named cells only for e.g. excel form where structure is fixed or may be a excel sheet which is used for calculations where you know which columns you are exactly interested in reading/writing. We have clients using these already within workflow and is one of our standard demos. So e.g. is that upload an excel and kickoff a process and as first step in process read data from certain cells in excel and map to process variables and then use it in workflow to show it on forms to move data to other places. No custom dev needed for this.

Please note that this functionality is available for regular excel sheet as well since AgilePoint NX supports the Excel Read and Excel Write activities are available outside SharePoint using the activities available in Document Activity group

Use Case 2: If you need to read multiple rows out of excel and for each row you wish to kick off a process instance, reading it in process in not an efficient way since there can be a lot of rows. Better way to handle such situation is to either write a remote event receiver which listens to file drop event in Office 365/SharePoint and parses excel as per your business rules and loop through excel rows and for each row call AgilePoint REST/WCF API to kickoff a process. This will go very fast for bulk rows. Same can be done using a custom WCF service which can monitor excel library and when file is found process its record and for each record call AgilePoint API to pass the row data and kickoff a process. So even this is possible but you would have to use some C# code to parse multiple rows in excel and pass those to AgilePoint through API.

Use Case 3: If user says he/she wants to open a form and upload file over there and read values, use file upload control which uploads file to the target system like SharePoint, BOX, Dropbox, One Drive, Google Drive, Local file system etc. and through JavaScript check once file is uploaded then enable a button next to this control (call it “Populate Data”) which invokes a custom REST service where it goes out and gets data using REST lookup and bind to multiple controls just like any other REST lookup. In this REST service write logic for how to parse excel as per your needs as part of PS. This works fine if you are not having a lot of such processes and you want complete control over parsing logic and excel structure.

I would say most of them will go for Use case 3 if excel services is not an option. Build parsing REST service through PS and with some minimal JS you can achieve this is less time.