The handling of Excel files is divided into two areas (reading and writing). This chapter deals with reading values from an Excel file.
In Excel, CSV, PDF files you will find information on creating and working with Excel files.
To be able to use values from an Excel file, the file must first be read in. After reading in the file, all values are kept in the working memory and the file is not accessed again.
An Excel file can either be read explicitly by the Read file action or is read automatically the first time a value (cell) is accessed.
Spreadsheets
You can specify one table sheet per action (except for reading in an Excel file). If you do not specify a table sheet, the first table sheet is automatically used.
The name (not index) is used to identify the spreadsheet. This is displayed in Excel at the bottom.
Name of the spreadsheet: Product B
If you want to use several spreadsheets in a trigger, you can create a separate action in the trigger for each spreadsheet.
Read in file #
| Description | With this action you read in an Excel file. Afterwards, the values (cells) of the file can be written into variables. |
| Required licence | CSV feature or XLSX feature depending on the file format. |
| 1 | File | Select the file you want to read in (Excel, CSV, PDF files). |
Please note:
Read in files automatically: If you execute an action that accesses the contents of an Excel file (e.g. Excel – Read cell) without first explicitly executing the Read file action, the file is read in automatically the first time it is accessed. All contents of the Excel file are kept in the working memory until a new file is read in.
Read cells #
| Description | With this action you can write the values of individual cells of a previously read Excel file into variables. |
| Required licence | CSV feature or XLSX feature depending on the file format. |
| 1 | File | Select the Excel file (Excel, CSV, PDF files). |
| 2 | Spreadsheet | Enter the name of the spreadsheet. This option is only available for files in XLSX format. For more information on using spreadsheets: see Excel – Read. |
| 3 | Allocations | In this table you can define which cell values (right) are to be written into which variables (left). |
Read row / column #
| Description | With this action you can read a row or column (e.g. a data record). In case of multiple execution, the next data record is always used. |
| Required licence | CSV feature or XLSX feature depending on the file format. |
| 1 | File | Select the Excel file (Excel, CSV, PDF files). |
| 2 | Spreadsheet | Enter the name of the spreadsheet. This option is only available for files in XLSX format. For more information on using spreadsheets: see Excel – Read. |
| 1 | Alignment | Specify how the records are arranged in the Excel file. Row by row: Each record is in a line and the records are one below the other. Column-wise: Each record is in a row and the records are next to each other. |
| 2 | Line of the first data set | Specify the row or column (depending on the orientation) in which the first record is located. |
| 3 | Selection of the next data set | Select how the next record is to be selected. Automatic: When you execute the trigger the first time, the first data record is read in, the second time the second data record is read in and so on. This means that you always receive the next data set automatically when you execute the trigger. Manual: If you want to determine the selection of the data set yourself, you can specify a variable. This variable must then contain the index of the next data set. Index always starts at 1 for the first data set. |
| 1 | Allocations | In this table you determine into which variable the value of which column or row (depending on the alignment) is to be written. |
You then have the option of describing three variables with values.
| 1 | Total number | Indicates the number of records available. |
| 2 | Current index | Indicates the index of the current data set. Index of the first data set is always 1. |
| 3 | Next index | Indicates the index of the next record. Next Index = Current Index + 1 When the last record has been read, a 1 is written in the index of the next record. |
Read data block #
| Description | With this action you can read a multiple record (block) in one operation. A variable of the type: Array of Struct is required for this operation. |
| Required licence | CSV feature or XLSX feature depending on the file format. |
Please note:
Only existing data records are written to the PLC: If you want to write data from an Excel file into your PLC, you needed a structure variable (Array[0.. 199] of Struct) for this. This variable in the PLC has a certain length. If, for example, there are 100 data records in the Excel file and the PLC variable is 200 long, only the first 100 structs in the array of the PLC are overwritten (0 to 99). The addresses 100 to 199 are not overwritten. If there is still data from a previous file with more records in the PLC, it will not be overwritten. Therefore, before requesting data from a file, you should write default values to the entire structure variable in the PLC.
Use all variables of the structs: To write the data as a block to the PLC, it is recommended to have all variables of the structs written to the PLC by the DataSuite.
OPC UA overwrites unused variables: If you do not have variables of the structs written to the PLC by the DataSuite, they will still be overwritten during an OPC UA connection!
| 1 | File | Select the Excel file (Excel, CSV, PDF files). |
| 2 | Spreadsheet | Enter the name of the spreadsheet. This option is only available for files in XLSX format. For more information on using spreadsheets: see Excel – Read. |
| 1 | Alignment | Specify how the records are arranged in the Excel file. Row by row: Each record is in a line and the records are one below the other. Column-wise: Each record is in a row and the records are next to each other. |
| 2 | Line of the first data set | Specify the row or column (depending on the orientation) in which the first record is located. |
| 1 | Data source strcture | Select the option here that corresponds to your data structure. Array of structures: E.g. one “Array[0..99] of Struct”. One array per value: E.g. several “Array[0.99] of Byte”. |
Array of structures #
Select this option if you use in array where each record is stored as a structure.
| 1 | Structure variable | Select a variable of the Array of Struct type. |
| 2 | Mapping | In this table you can assign the individual columns or rows to the elements (variables) of the structs |
One array per value #
Select this option if you use a separate array for each parameter.
| 1 | Mapping | Here you can assign the respective columns or rows of the Excel file to the individual arrays. |
| 1 | Total number of available records | If the number of records in your file varies, you can use this option to have the number of records written to a variable. |
| 2 | No records available | If you read in a file that does not contain any data, you can use this option to select whether the trigger should be aborted with an error or not. |
