Cycles. Import Excel files
Using cycles in business process. Read data from Excel files.
To understand the further process, it is necessary to digress a bit into the theory and understand the principle of the cycles. Previously, all our processes went only forward. They could choose a specific fork (on the If-Else and Switch blocks), but we knew for sure that each block would be executed only once; there would be no going back.
Cycles provide the ability to repeat a particular section multiple times. The process is created so that the selected sequence of blocks is executed over and over again. Until the condition for terminating the loop is met.
AppMaster gives you the ability to use three different kinds of cycles. The principle of their work is absolutely the same. The condition for terminating the loop is the only thing that distinguishes them from each other.
- Loop. The simplest of all cycles. Its Times (Integer) parameter sets a fixed value for how many times the given loop should be executed.
- While loop. A loop that will run until a certain condition is met. This condition is specified in the Condition (Boolean) parameter. At the beginning of each loop, the Condition value is checked; if it is True, then the loop is executed; if it is False, then the loop stops. The Limit (Integer) parameter sets the limit on the number of repetitions of the loop to ensure that the loop does not run forever. When it is reached, the loop will end, regardless of the value of Condition.
- For each loop. Loop designed to work with arrays. It takes an array as input and allows you to perform a certain operation on each array element. Runs as many times as there are elements in the array.
Each cycle has two output streams.
- Loop Body. The loop body itself. That process, which will be repeated the necessary number of times.
- Completed. The thread will run when the loop ends. Specifies what to do next after the loop is finished. By the way, any cycle can be completed on command without waiting for the initial conditions to be met. To do this, you just need to use the Break Loop block in the right place. It will immediately transfer the stream to Completed.
Read XLSX File
For analysis the xlsx file, we will use the Read XLSX File block. Its principle of operation is almost similar to the For each loop block. It represents the table file as an array of strings. And each row, in turn, is also an array, but already an array of columns of the given row.
The xlsx file itself is presented in a similar form. The first column contains the name of the section, and the second contains the information that this section contains. At the same time, we do not know in advance the line where the information is written at all; it can appear in any arbitrary place. In this example, the biography is indicated on the second line, and the login is on the eighth, but the lines and the order in which they are written can be any.
The business process starts by receiving a file as an input parameter. Please note that it is the file data type, that is, the file ID, that is transmitted and not the entire file. We pass the same file to the Read XLSX File block.
Each row, in this case, is an analog of Loop Body. This means that the loop will be executed as many times as there are rows in the xlsx file. At the same time, each cycle, we receive information about which row is being processed now (Row number), as well as what columns are in this row (Row columns).
Our task is to check the first cell of each row. We expect to find a cell that says "Login" or "Bio." This way, we will understand that we have found a column with the necessary information.
To do this, we need an Array Element block with an index of 0. This is how we get the contents of the first cell of the string. After that, we use the Switch block to find the desired match (Login, Bio).
If no such match is found, then the loop goes to the next iteration, checking the next row. If found, then we understand that the second cell of this row should contain the information we need and use the Array Element block again (but with index 1) to get this information.
It remains only to save this information. After all, if you do not do this right away, then the cycle will simply go on, and the received data will be lost.
The best solution in this situation would be to use variables. Their meaning lies in the fact that certain values are not transferred immediately from one block to another but are stored in memory and used if necessary.
There are two blocks for this:
- Variable block. It can be any number, string, or even an array of objects. It is important to understand that this block itself does nothing; it has no input parameters. It serves only to announce the fact that a place is allocated in memory for writing some value, with the possibility to later refer to it again and find out this value.
- The Set Variable block is designed to write a new value to a variable.
In our case, we will define two variables of type String and write the found values into them, in one place Login, and in the second Bio. At the end of the loop, we only need to pass the value from these variables to the end block.
Now it's time to create an endpoint for the finished business process and thereby make it available for use on the web application front-end.
As a result business process should be this:
There are finishing touches to complete the creation of the application. Let's go back to the frontend business process and finish it. We need:
- Pass the xlsx file model for saving to the server.
- Expand the result and get the file ID.
- Pass this ID to the previously created endpoint POST /xlsx-data/
- Use the received data to display in the appropriate fields (and also to reset the Disable parameter, opening up the possibility for editing).
If everything was done correctly, the final version should look like this after loading information from files.