«EXCEL» MODULE
«Excel» module is the biggest module in the application. This module is made for processing Excel files in the background, not visible for the user. The module will sequentially run the list of commands given to it.
Module interface
The module window consists of several sections: «Add command», «General settings», «Command list» and a file preview window.
The preview window allows you to view all sheets of the downloaded file using tabs with sheet names and scroll bar. It is also possible to enter your values into the cells, apply different formatting, but these changes will not be saved, that is, this feature is available only for «fitting».
## «General settings» section ### Upload file«General settings» section consists of the «File name» field and «Upload file» and «Select cell» buttons.
We recommend starting work with the Excel module by selecting a work file. After that, After that, as the file is selected by the “Download file” button, you need to wait a few seconds and the specified file will be displayed in the preview window.
## «Add command» and «Command list» section«Add command» section is the main section of the module. It creates actions that mimic the work of the user, for example, such as reading a certain range of cells, then transfer it to another sheet, delete duplicates and build a pie chart. It this case 4 actions were described. These 4 actions must be sequentially selected and added to the «Command list» section - a table showing all the actions that will be performed by this module. The number of fields in this section is not always the same, it depends on a selected action in the first field.
«Add», «Edit» and «Delete» buttons are designed to work with the «Add command» and «Command list» sections.
### Adding a commandTo add a command click the «Add» button. It puts the created action into the “Command list” table.
When choosing a row from the list of commands, information about the selected action will show up in the «Add command» section.
In addition to the specified functionality, the “Add” button is used to copy existing actions from the list of commands. To do that you need to select a row from the list of commands and click the «Add» button. Thus, if you need to perform an action several times, just copy it other than creating it again.
### Editing a commandThe «Edit» button is designed for editing commands. To change a command, select it from the list of commands, make all changes in the «Add command» section, then click «Edit» to save the changes.
### Changing the order of actions«Up» and «Down» buttons are designed to change the order of actions. Buttons are located to the right side of the list of commands.
Changing the order of actions is necessary when it is necessary to add an action that was not foreseen immediately and was not included in the list of commands. In this case you can add an action using the normal way, then just move it wherever you want.
### Deleting a commandThe «Delete» button deletes the selected row from the list of commands.
### Turning off a commandIf you don’t wanna delete a command, because for example you wanted to see how the robot works without it, but then add it back to work, you can disable a command by right-clicking on the corresponding line and turn it off or, conversely, turn it on.
### Selecting a cellThe button «Select cell» works for each action in its own way, but it has one meaning - it fills the «Cells» or «Range" fields (depending on which one is used in the selected action) with the name of the cell/range selected in the preview window and enters in the «Sheet» field the name of the sheet on which the selected cells are located.In some actions, only one cell is filled by clicking the button, although a range has been selected - this means that in this action the use of a range is impossible.
In some actions, the “Select Cell” button fills in the numbers of columns, rows. When working with files, it is recommended to use it to minimize the occurrence of syntax errors.
### Saving a commandTo save a command in the Excel module press the «Save» button in the bottom right corner.
## Actions available in the moduleActions are divided into groups for more comfortable navigation through the drop-down list. If you know the name of an action, you can enter it into the «Action» field, and the software will try to give you the action you need. ### «Work with data» group #### Action «Read data»
This action allows the robot to read from one specific cell or from a range of cells. If only one cell is specified then the «Element» type variable will be made, but if a range of cells is specified then the «Table» type variable will be made.
#### Action «Record data»This action allows you to record the specified value in one or in a range of cells.
#### Action «Formula»This action makes the robot write a formula in a specified cell (or a range of cells) and return the result to the specified variable. The «Variable» field can be left blank, if the result of a formula is not important.
#### Action «Get formula»The action returns the formula from the cell to the specified variable.
#### Action «Replace the formula with its value»This action replaces the formula in the cell or range of cells with its result.
### «Work with rows» group #### Action «Add row»Action adds a row to the specified location.
#### Action «Delete row»Action deletes the specified row.
#### Action «Get row count»The action returns the number of the last used row in the sheet to the specified variable.
#### Action «Get the number of filtered rows»Action returns the number of visible rows with the applied filter to the specified variable.
### «Work with columns» group #### Action «Add column»Action adds a new column into the specified location.
#### Action «Delete column»Action deletes the specified column.
#### Action «Get the column count»The action returns the number of the last used column in the sheet.
### «Work with sheets and files» group #### Action «Create a new sheet»The action creates a new sheet with a specified name.
#### Action «Delete sheet»Action deletes a sheet with a specified name.
#### Action «Clear sheet»Action clears data from the cells. Action doesn’t clear the formatting!
#### Action «Create a new file»Action creates a new file in the specified folder with specified name and filename extension (if the filename extension is not specified, .xlsx file will be created.) If the «Sheet» field is filled, then the first sheet of the file will be named as specified in the «Sheet» field, if not it will leave it by default.
#### Action «Fill in a sheet, based on an Excel file»This action fills the sheet of the current file by analogy with the sheet of some other file. In the «Sheet» field the sheet of the current file is specified.
#### Exporting to PDFAction allows to save excel files in the PDF format using standard WIndows methods, meaning that the division of the sheet of the Excel file into sheets in the PDF document will be performed according to the standard settings specified in the Excel file (the default is A4 sheet size, with normal fields).
The name of the file and its directory will coincide with the exported file. ### «Search» group #### Action «Find data»
Searches for the specified row in the specified sheet and returns the address of the first cell found.
#### Action «Search for a row by column values»This action searches and returns the number of the first row found, in the specified columns of which the specified values are written.
In this case, a row will be searched, in column A - 03/23/2020, and in column B - Ufa. #### Action «Find sheet»
Searches for a sheet in which there is a cell with the value specified in the «Row» field and returns its name to the specified variable.
### Group «Other functions» #### Action «Shift cell»Moves a cell on the specified number of rows and columns, and then returns the result to the variable (in this case, the result is D13).
#### Action «Apply filter»This action adds a filter to the table specified on a sheet, the cell from which is specified in the «A cell from the range of values» field. The column which you want to apply a filter to is written in the «Column» field. You can only filter a single column. If you need to apply several filters to one table, then you need to add an action to each filter within the same Excel module. The function by which the comparison should take place is selected from the drop-down list in the «Function» field, the value with which the comparison will be performed during filtering is indicated in the «Value» field.
#### Action «Delete filter»The action deletes all filters from the sheet.
#### Action «Add sorting»The action adds sorting to the specified range by the selected column. Depending on the checkbox, it is determined whether the column is sorted in descending or ascending order. You can only sort a single column.
#### Action «Clear the range»The action deletes all cell values and formatting in the specified range.
#### Action «Get unique column values»Similar to the «Delete duplicates» action in Excel, this action returns only the unique values of the specified columns from the range. The finished table can be inserted both into the same sheet and into another sheet, but in the same range. Depending on the presence of duplicates, the number of rows may be reduced.
#### Action «Subtotal»The action adds an Excel «Subtotals» operation to the specified sheet to the specified range.
The «For columns» field matches the «Add totals by» field in Excel (Marked green), the «Each time a column changes» field matches the similar field in Excel, but must be filled in with the name of the Excel column (A, B , ... or 1, 2, ...) (highlighted in red), the «Functions» field is filled with the operation that should be applied to all of the selected columns (highlighted in blue).
#### Action «Pivot table»Adds a summary table to the specified sheet and range (the «Sheet, range for insertion» field) based on the table data from the «Sheet, source range» field.
In the «Fields in columns», «Fields in values» and «Fields in rows» fields the column names of the source table (and not the Excel table) are entered, as in Excel:«Fields in columns» match the names of the columns of the table whose values should be located in the columns of the pivot table (highlighted in green);
«Fields in values» сmatch the names of the columns of the table whose values should be located in the values of the pivot table (highlighted in red);
«Fields in rows» match the names of the columns of the table whose values should be located in the rows of the pivot table (highlighted in blue);
#### Action «Copy and paste cell/range»This action copies files and formatting from one file and inserts it to another. You can «stretch» formulas using this action (see. «Action «Formula»).
#### Action «Show as percentage»Action converts the selected range into percentage.
#### Action «Pie chart»Action creates a pie chart on a specified sheet. Values of the chart are taken from the range specified in the «Cells» field, a range with value names is indicated in the «range of names» field. In the «Location» field the borders of the location of the pie chart are indicated, especially the upper left corner and the lower right corner, separated by commas.
#### Action «Graph»The action creates a line graph with one or more rows.A row must be indicated in the «Range of values» field as a one-dimensional range (one column or one row). If there are more than one row, then following rows must be indicated through «;», as indicated in Figure. 2.5.35. There should be a single range of arguments, and must be indicated through «;». «Cells with row names» are filled with cell names through «;», their number should be equal to the number of rows, the first cell displays the name of the first row, etc. The cell in which the upper left corner of the graph will be located is recorded in the insertion cell.
#### Action «Value check»The action restricts the types of data that can be entered into selected cells by selecting the proposed rules. In the «Sheet» field, a sheet is entered, the cells of which you wanna restrict, in the «Cells» field, a range of cells is entered on which the values will be checked when entering. In the «Data type» field one of the offered data types is chosen – integer or real number, list, date, text length, time. «Operation» field consists of the list of available functions for creating a checking rule – equal, not equal, greater, less, greater or equal, less or equal, between and out. When using the «List» data type filling in the «Operation» field is not required, therefore you can’t edit it.
Last field changes depending on the data type – for the «List» data type - «Data source», for all others – «Criterias (through ;)». The «Data source» field is filled with one-dimensional range of values (range within a single column or single row), which will be specified in the list (if the list is static, then specify an absolute link). The «Criteria (through ;)» field is filled with values against which the verification rule will apply.
The «Error message» field is filled with the error text that will be displayed, if a value is entered in the cell that does not satisfy the rule set for it. This field is optional. If you leave it blank, then the following error message will appear: «The entered value is incorrect. The set of values that can be entered in a cell is limited».
### «Formatting» group #### Action «Conditional formatting»The action formats the specified cells on the selected sheet, depending on whether they satisfy the specified condition. In the «Sheet, range» field, a sheet and range or a single cell from the sheet are entered, separated by commas, to which conditional formatting will be applied. The «Filling color» and «Text color» indicate the colors with which the cell will be filled and the text in it will be colored, if it satisfies the condition. The condition is made of the function selection and the indication of a value, for comparison. Available functions - equal, not equal, greater, less, greater or equal, less or equal, formula. If the «formula» function is chosen, then you need to enter a formula in Excel format (starting with an «=» symbol) in the «Comparison value» field.
#### Action «Color of the cell/range»The action changes the fill color of the text in the specified range/cell.
#### Action «Width of column(s)»Action changes the width of the specified columns. Columns can be separated by a comma, you can put in their numbers instead of their names (starting from 1). Width is specified using the same unit of measure as in Excel.
#### Action «Height of row(s)»Action changes the length of rows. Length is specified using the same unit of measure as in Excel.
#### Action «Merging cells»Action merges cells on the selected sheet. Module has 3 types of merging:
● simple merging – merges columns as well as range columns;
● row merging;
● column merging.
#### Action «Clear formatting»Action clears the formatting in the specified range of the selected sheet.
#### Action «Cell format»Action changes the format of the specified cell. By default, all values entered in Excel by the studio have a string format, therefore, for the correct calculation of formulas or sorting, it is necessary to change the format of the data to your desired one.
#### Action «Number format»The action displays the number according to certain rules of the Excel number format.
You can view all formats in Excel, just right click on a cell – «Cell format» – tab «Number» - (all formats):
The most commonly used formats are suggested if you hover your cursor over the field to enter a format.
#### Action «Alignment»The action applies the specified alignment in the specified cells. You can choose one of two things in the «General position» field - apply alignment vertically or horizontally. You can select the alignment in the «Align content» field. Three types of alignment are offered: by left/right edge, by the top/bottom edge and by the center.
#### Action «Font»Action changes the font, the style or the size of selected cells on the specified sheet. Module offers all default Microsoft Office fonts.
#### Action «Borders»Action adds or changes the cell borders of the selected cells on the specified sheet. This action involves configuring the borders - external, internal; only the right one, etc., the color of the borders and their style - thick, thin, dashed line, etc.