Skip to main content

Excel File Adaptor

Spanish

Setup

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.

Initialization Parameters

  • FilePath: Representsrepresents the full path and name of the Excel file that represents this instance of the Excel adaptor.
  • Password(Opt.): Indicatesindicates the password with which the Excel file will be protected. It is an optional parameter.
  • NumSheet: Indicatesindicates the number of the sheet within the Excel workbook on which we want to work.
  • Template: Ifif 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: Thisthis 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: Whenwhen 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: Checkingchecking 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.

Functions

  • addSheet: Addsadds a new sheet with the name that the user puts as parameter.
  • clearCell: Allowsallows 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: Allowsallows 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: Allowsallows 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(): Generatesgenerates a copy of the Excel file in its current state to add as evidence.
  • getActiveSheetIndex: returns the currently active tab.
  • getCellValue (CellReference): Thisthis 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(): Returnreturn 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): Readsreads 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(): Returnsreturns 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(): Returnsreturns 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: Allowsallows 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: returns the number of sheets.
  • getRowByColumnSearch(): Returnsreturns 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):  Returnsreturns the value contained in the cell indicated by the Position parameter that is filled with the RowNum and ColNum values.
  • getTableValueByColumnName(): Allowsallows 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(): Returnsreturns an array of values corresponding to the cell positions indicated by the parameters.
  • newFile(FilePath): Createscreates 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.
  • setCellValue (CellReference, value): Thisthis 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: Samesame as setCellValue, but in one parameter instead of two. Format: cellReference; cellValue.
  • setColumnValues(): Generatesgenerates 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(): Generatesgenerates the headers in the row 1 Excel file,” based on the value indicated by the parameters ValueHeader1, ValueHeader2, etc.
  • setRowValues(): Generatesgenerates 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: Allowsallows 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(): Generatesgenerates 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() : Returnsreturns 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(): Returnsreturns 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.

Back to top