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.
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.