Reading values from an Excel file is probably one of the most useful features of RAX EDITOR especially in automating repetitive office works. This tutorial will show you how to read values from an Excel file using RAX EDITOR. We will be using the sample Excel Worksheet "Sample File" from the tutorial Excel - Entering Values in Excel and we will be reading the values in that sample file.
Follow these steps to read values from the Excel Worksheet using RAX EDITOR:
Step 1. Open RAX EDITOR and create a new workflow.
Step 2. Drag an Excel Sequence activity and drop it inside the Main Sequence.
Step 3. On the File Path input field, paste the directory path of the "Sample File" from the Excel - Entering Values in Excel tutorial and enclose it with quotation marks ("").
Step 3. Drag a Read Cell activity and drop it inside the Excel Sequence. This activity will read the value on the cell at Row 1 and Column A of the Sample File.
Step 4. Create a variable. As default, it will be named variable1.
Click on the Read Cell activity on your workflow then enter this variable in the Value Output attribute.
Step 5. Drag a Write Line activity and drop it inside the Excel Sequence after the Read Cell activity. Set its Text to variable1. This should display the retrieved value from the Read Cell activity.
Step 6. Add a Read Column activity inside the Excel Sequence. Type "Customers" in the Worksheet input field and "A" in the Column. This means this activity will retrieve the values from the Column A in Worksheet named Customers. In our Sample Sheet, the column contains the names of the customers.
Step 7. Create an IEnumerable<Object> variable called empName.
a. Create a new variable named empName then for the Variable Type click on Browse for Types.
b. On the Type Name input box, type ienumerable.
c. Click on IEnumerable<T> under System.Collections.Generic.
d. Select Object from the dropdown list beside the System.Collections.Generic.IEnumerable text.
Afterwards click on the OK button at the bottom right of the pop-up window.
The retrieved values from the Read Column will be stored in this variable by setting the value of Values Output attribute to empName.
Step 8. The values stored inside IEnumerable Object variable in our example are stored starting with the value 0.
So if you want to access the first value stored you would use empName(0), the next value would be empName(1), and so on and so forth.
Add a Write Line activity inside the Excel Sequence. Since the Write Line activity only accepts String inputs, we would need to convert our value to String.
Set its Text to empName(1).ToString. This will display the second instance of the empName variable.
Step 9. Add a Read Row activity inside the Excel Sequence. Let's say you want to get the email address of Nicole Rivers. Do it by changing the value of Worksheet to "Customers" and Row to "3" since her information is in the Row 3.
Step 10. Create another IEnumerable<Object> variable called empMail.
Click on the Read Row activity then go to the Attributes panel. Look for Values under the Attributes of the Read Row activity and type in empMail.
Step 11. Insert another Write Line activity and set its Text to empMail(3).ToString. This will display Nicole Rivers' email address in the Output panel.
Once you're done, save your workflow and execute it. The Output panel should now look like this:
There you go! Now you know how to read values from Excel spreadsheets! Cheers!