Automating Calculator Application
Hi,
I have an excel sheet that has four columns (num1, num2, operator, result - in order). I want to take the value from column "num1", then the operator and finally num2 and do the calculation on my windows calculator application. The result is to stored in the fourth column i.e. "result". The numbers could have any number of digits. Also there could be many rows. Is there any simple way to do this?
Accepted Solution
Pegasystems Inc.
US
Pegasystems Inc.
US
It is a rather simple automation. To do so you would;
1. Interrogate the required Calculator controls (i.e. the numbers, the operators, the equals button, the clear button, and the result).
2. Add an Excel connector to a Global Container (Connectors tab in Toolbox).
3. Add a StringUtils component to a Global Container (Advanced tab in Toolbox).
4. Create and automation that has an entry point.
5. In this automation, add a ForLoop (Advanced tab in Toolbox).
6. Set the Limit equal to -1. This will make it an infinite loop.
7. Set the Initial to 1 (or 2 if your spreadsheet has a header row).
8. Off of the Yielded from the ForLoop, connect to a Concat method from the StringUtils.
9. Set List0 of the StringUtils.Concat to "A" without the quotes (just the letter A).
10. Pass the Index from the ForLoop into List1 on the StringUtils.Concat.
11. Call a GetCellValue from the Excel Connector and pass the result from the StringUtils.Concat as the parameter.
12. Check the value to see if it is null or empty by calling StringUtils.IsNullOrEmpty.
13. If it is true, then call the Break method on the ForLoop. This means you are finished with the spreadsheet.
14. If it is not null or empty, then essentially repeat the first two steps for the (the StringUtils.Concat and the Excel.GetCellValue) for the first three columns.
It is a rather simple automation. To do so you would;
1. Interrogate the required Calculator controls (i.e. the numbers, the operators, the equals button, the clear button, and the result).
2. Add an Excel connector to a Global Container (Connectors tab in Toolbox).
3. Add a StringUtils component to a Global Container (Advanced tab in Toolbox).
4. Create and automation that has an entry point.
5. In this automation, add a ForLoop (Advanced tab in Toolbox).
6. Set the Limit equal to -1. This will make it an infinite loop.
7. Set the Initial to 1 (or 2 if your spreadsheet has a header row).
8. Off of the Yielded from the ForLoop, connect to a Concat method from the StringUtils.
9. Set List0 of the StringUtils.Concat to "A" without the quotes (just the letter A).
10. Pass the Index from the ForLoop into List1 on the StringUtils.Concat.
11. Call a GetCellValue from the Excel Connector and pass the result from the StringUtils.Concat as the parameter.
12. Check the value to see if it is null or empty by calling StringUtils.IsNullOrEmpty.
13. If it is true, then call the Break method on the ForLoop. This means you are finished with the spreadsheet.
14. If it is not null or empty, then essentially repeat the first two steps for the (the StringUtils.Concat and the Excel.GetCellValue) for the first three columns.
15. Now perform the calculation. You'll need a switch statement to match up the value from the cells to the corresponding button to click. This part may get really complicated with multi digit numbers as you would have to split the strings into each character and click those buttons in sequence. I would be sure to only provide single digit numbers for this endeavor. In reality, if this were required for production, I'd write a C# script to do this step.
16. Once you get your results, pass those into a variable and clear the calculator.
17. Call StringUtils.Concat where List0 is D and List1 is the ForLoopIndex.
18. Pass the results variable and the results of StringUtils.Concat to the SetCellValue method of the Excel connector.
19. Call the Save method of the Excel connector.
Cognizant Technology Solutions
IN
To add a question on Excel here, how will we get the used Row and Column count of Excel through openspan?
Pegasystems Inc.
US
There isn't a great way to get the row and column count as Excel doesn't really track this accurately (i.e. if you insert data into empty rows way below your real area, it will still show those as "used"). The best way is to iterate through each row and then determine when you have recahed the "end" by one or more of those values being empty.
Pegasystems Inc.
US
This is pretty simple. Set up a ForLoop to loop through the rows. Inside of that ForLoop, use another ForLoop with a StringUtils SubString to get each digit in num1 and use a Switch to click the correct button in Calculator. Do the same type of process for operator and num2 and then write the result back to Excel.