Excel File Adaptor

In the initialization process, the Excel Adaptor will try to read and analyze the Excel sheet indicated by the NumSheet parameter.

The adapter will call the GetExcelSheet function, in this way the user can operate on the data without drawing a GetExcelSheet message in the diagram.

• FilePath: represents the full path and name of the Excel file that represents this instance of the Excel adaptor.
• Password(Opt.): indicates the password with which the Excel file will be protected. It is an optional parameter.
• NumSheet: indicates the number of the sheet within the Excel workbook on which we want to work.
• Template: if checked-in, the file will be created or overwriten in any case, no matter if the checkbox 'Create/Overwrite' has been checked-in.
• Template File: this is the name of the template file used for creating the excel file for testing. In case the Template checkbox (see above) is checked-in, this field is mandatory.
• Create/Overwrite: when the checkbox is checked-in, TAST will create a new file or overwrite it if the file already exists.
• Use first line as column name: checking this makes it so the first row is skipped from any of the functions, and is counted as the name of each of the columns. So if you search for the cell in Column 1 and Row 1, it will be the cell in Column 1 and Row 2 instead.

• addSheet: adds a new sheet with the name that the user puts as parameter.

• clearCell: allows the user to delete the value of a cell in the active Excel sheet in the adapter. The row and num parameters indicate the cell.

• clearCellByColumnName: allows the user to delete the value of a cell in the active Excel sheet in the adapter. The parameters row and columnName indicate the cell, where columnName is the value of the first cell in the column to be selected.

• deleteSheet: allows the user to delete an Excel sheet of the current workbook. Using the sheet name or index as input parameters. It returns a Boolean to indicate the operation result. If the deleted sheet was the active sheet in that moment, then the first sheet in the book will be the new active sheet.

• generateFileAsEvidence(): generates a copy of the Excel file in its current state to add as evidence.

• generateSheetFromTastRow: the function copies the TastRowValues received under the Input Row parameter over the active sheet or over a new sheet created with the name given by the New Sheet Name parameter, if it has been populated. In that case the new sheet will remain as active sheet in the book. The copy will be performed over the line number specified by the Target Line parameter when First Line for Header parameter (that will override the indicator Use first line as column names in the adaptor) is deactivated, but in next line if it is activated.

• generateSheetFromTastTable: the function copies the TastTableData received under the Input Table parameter over a new sheet created with the name given by the New Sheet Name parameter. This new sheet will remain as the active sheet in the book. The parameter First Line for Header will override the Use first line as column names check in the adaptor and, if the parameter is active the copy will be performed from the first line in the sheet, but if it isn’t the copy will be performed from the second line in the sheet.

• getActiveSheetIndex: allows you to recover the index of the active sheet of the Excel file.

• getCellValue (CellReference): this function returns the value of a cell with the reference of a said cell (e.g.: C7, C12, D2…); works with uppercase and lower case.

• getColumnByRowSearch(): return a TastDataString, that contains the value of the indicated column to retrieve, if the value matches with searchColumn parameter. As imput parameters there are (SearchRow, SearchValue y ColumnToRetrieve).

• getExcelSheet(SheetNum): reads the Excel worksheet indicated by the NumSheet parameter and returns the results as a variable of type TastTableData to the executor to allow working with the data in later steps of the diagram.

• getNumColumns(): returns the number of columns of the TastTableData generated when reading the Excel sheet. When you introduce new columns in the Excel, you have to put before a setSheet so that it counts the new columns.

• getNumRows(): returns the number of rows of the TastTableData generated when reading the Excel sheet. When you introduce new rows in the Excel, you have to put before a setSheet so that it counts the new rows.

• getNumValueSearchOnColumn: allows the user to search a value on the selected column and it returns the number of times the value appears in the column. In the mapping the user puts the value to find in SearchValue field and the number of column in Column field.

• getNumberOfSheets: allows you to recover the number of sheets in an Excel file.

• getRowByColumnSearch(): returns a TastTableData, that contains the subset of rows in which the column has the indicated value. As imput parameters we have with SearchColumn and SearchValue.

• getTableValue(RowNum, ColNum): returns the value contained in the cell indicated by the Position parameter that is filled with the RowNum and ColNum values.

• getTableValueByColumnName(): allows to retrieve the value in a cell of an excel file, selecting as parameters the name of the column (ColName) and the numbers of rows to retrieve (RowNum).

• getValuesbyPosition(): returns an array of values corresponding to the cell positions indicated by the parameters.

• newFile(FilePath): creates or overwrites a file, with the path and the name indicated by the parameter filePath”.
  If the file doesn´t exist, TAST will create it, as an empty file.
  If the file exists, TAST will delete the current file, and it will create a new empty one.

• renameSheet: the function renames the sheet specified by the Old Sheet Name parameter (wich may contains an existing sheet name in the book or its ordinal), or the current sheet if this parameter hasn’t been populated. The new name given to the sheet will be taken from the New Sheet Name Parameter, and the renamed sheet will remain as the active sheet in the book.

• setCellValue (CellReference, value): this function sets the value of a cell with the reference of a said cell (e.g.: C7, C12, D2…), works with uppercase and lower case.

• setCellValueOneParameter: same as setCellValue, but in one parameter instead of two. Format: cellReference; cellValue.

• setColumnValues(): generates the Column values in the Auxiliary Excel file, based on the value indicated by the parameters Column (Input parameters are ColNum and ValueCell), value1, value2, etc.

• setHeader(): generates the headers in the row 1 Excel file,” based on the value indicated by the parameters ValueHeader1, ValueHeader2, etc.

• setRowValues(): generates the Row values in the Auxiliary Excel file, based on the value indicated by the parameters Row, (Input parameters are RowNum and ValueCell), value1, value2, etc.

• setSheet: allows the user to select the sheet which will be active in the adaptor. Once you set up the sheet as active, all the functions of reading and writing executed, will work on the selected sheet. (Imput parameter “Name”).

• setTableValue(): generates the cell value in the Auxiliary Excel file, based on the value indicated by the parameters RowNum, ColNum, ValueCell. The parameter “Use first line as column name” doesn't work with this function.

• vTableLookUp() : returns a TastDataString, that contains the values of the indicated column to retrieve, for every row that matches the condition. The imput parameters here are ColToSearch, ValueToSearch y ColToRetrieve.

• vTableLookUpArray(): returns a TastRowTableData, which is a list of values located on the indicated column to retrieve, for every row that matches the condition. The imput parameters here are ColToSearch, ValueToSearch y ColToRetrieve.