Microsoft Excel is a spreadsheet program created by Microsoft that uses tables to record and analyze numerical and statistical data with formulas and functions. A spreadsheet system allows users to organize, format, and calculate data with formulas. Refer to list of Excel activities
Let us understand Excel activities with an example process. Here is an example of generating a payslip by reading the payslip-related details from an Excel file for employees in a company.
DataTable Variables are mainly used to hold the read Excel rows. The following GIF file shows how to add and set parameters for Excel activities used in this example process.
All the Excel operations are handled in a separate workflow.
Above Excel process is called from main process using invoke workflow activity.
Input Excel File details
Input files are shipped with the attached project zip file.
Come, let's create this process in Process Designer:
- Follow the steps from Using File Activity #Step1 to Using File Activity #Step5 to create a new process and get the Excel files name list
Add For Each to iterate through all the Excel files in the input folder.
Add Open Spreadsheet activity in the body of for loop. This activity takes Excel filename as input and creates the instance of the Excel Workbook.
Add Get Sheets activity to get list of sheets.
Add For Each activity to iterate through all the sheets.
Add Read Range activity to read sheet content without header and get the output in the DataTable structure.
Add For Each Row activity to iterate through all the rows.
Follow the steps from Using Word Activity #Step1 to Step4, which replaces the text in the word file with Employee details read from excel
Export above sequence to Snippet
Add invoke workflow activity and sets appropriate arguments.