This tutorial will show you how to transfer data from one Spreadsheet to another using RAX EDITOR. Before we begin, download the sample Spreadsheets here and here. You can change the values if you please. This sample workflow will ask for the user's input (quantity of a product) and then it will automatically search for the products that have or less than the entered quantity in the first Spreadsheet document and then display the result in the second Spreadsheet. Now, follow these steps to learn how to do it:


Part I. Asking for User's Input


Step 1. Create a String variable called quantityString.


Step 2. Drag and drop an InputDialog activity inside the Main Sequence.





Step 3. In the Title input field, write "Quantity".

Step 4. In the Label input field, write "Enter quantity:".



Step 5. In the Result input field in InputDialog's Attribute, set its value to quantityString. The user's input will be stored in this variable.



Step 6. Create an Int32 variable called quantityInput.



Step 7. Drag and drop an Assign activity next to the InputDialog activity.



Step 8. Assign Int32.Parse(quantityString) to quantityInput variable. This will convert the input from String to integer.



Part II. Retrieving the Data from a Spreadsheet and then Displaying it in another Spreadsheet


Step 1. Create a Boolean variable called terminate.


Step 2. Drag and drop a While activity inside the Main Sequence and set its Condition to terminate = False.





Step 3. Drag and drop a Sequence activity inside the Body of While, and then insert an Excel Sequence inside this Sequence.





Step 4. In the File Path input field, search for and then select the Spreadsheet you downloaded earlier named "Products.xlsx"





Step 5. Create an Int32 variable called row with a default value of 2.


Step 6. Create an IEnumerable<Object> called product

a. In order to do so, name the variable as product, click on Variable Type dropdown then choose Browse for Types:


b. Under Type Name, input ienumerable. This would filter the different variable types. You should then choose click on IEnumerable<T> under System.Collections.Generic.


c. Click on System.Collections.Generic.IEnumerable<> dropdown and choose Object. Click on the OK button at the bottom of the window.

You have now declared a variable named product with IEnumerable<Object> as its type.



Step 7. Drag and drop a Read Row activity inside the Excel Sequence.




Step 8. In the Worksheet input field, write "Products".

Step 9. In the Row input field, write row.ToString.



Step 10. In the Values input field in Read Row's Attribute, set its value to product.





Step 11. Drag and drop an Assign activity inside the Excel Sequence.





Step 12. Assign row+1 to the row variable.




Step 13. Drag and drop an If activity outside the Excel Sequence.



Step 14. In the Condition input field, write product(0).ToString = "<EMPTY>"

Our IEnumerable Object, products, stores all of the values for that specific row. You could access the values by its unique number, starting with 0. In this case, to pull the first value, we would be using product(0). The next value would be product(1), and so on, and so forth.

The condition states that RAX EDITOR would check if the first value on that row is <EMPTY>




Step 15. Drag and drop an Sequence inside Then.





Step 16. Drag and drop a Log Message activity inside Sequence and type "Empty Row" since if the Condition returns true, it means the current row from Spreadsheet is empty.



Step 17. Insert an Assign activity inside the Sequence and then assign True to terminate variable.





Step 18. Drag and drop a Sequence activity inside Else.




Step 19. Create an Int32 variable called quantityProduct.


Step 20. Add an Assign activity inside the Sequence and then assign Int32.Parse(product(2).ToString) to the quantityProduct variable.





Step 21. Drag and drop an If activity inside Sequence and set its Condition to quantityProduct <= quantityInput. This condition will check if the actual quantity of the current product is equal or less than the entered quantity by the user.



Step 22. Insert a Log Message inside Else and set the Message to "Quantity Not Below Input".



Step 23. Drag and drop an Excel Sequence inside Then and set the value of File Path to the directory path of the second Spreadsheet you downloaded called Output.



Step 24. Add an Update Row activity inside the Excel Sequence.



Step 25. In the Worksheet input field, type "Products".

Step 26. Create an Int32 variable called row1 with the default value of 2.


Step 27. In the Row input field, type row1.ToString.

Step 28. In the Values input field, type {product(0).ToString,product(1).ToString,product(2).ToString}.



Step 29. Add an Assign activity inside the Sequence and then assign row1+1 to row1 variable.





Your script is now finished! Save it and try to execute the workflow.