The handling of Excel files is divided into two areas (reading and writing). This chapter deals with writing values into an Excel file.
In Chapter 6you will find information on creating and working with Excel files.
To write values to an Excel file, a file must first be created. Files can either be created via the Create file action or are created automatically during the first writing process.
Spreadsheets
You can specify one table sheet per action (except for creating 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.
Create file #
| Description | With this action you create a new Excel file. Values can then be written to the file. |
| Required licence | CSV feature or XLSX feature depending on the file format. |
| 1 | File | Select the file you want to create. To configure files: Excel, CSV, PDF files. |
Please note:
Files are created automatically: If you execute an action that writes to a file (e.g. Excel – Write cell) without first explicitly executing the Create file action, the file will be created automatically during the first write operation.
Write cells #
| Description | With this action you can write the values of variables into Excel cells. |
| 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 variables (right) are to be written into which cells (left). |
Write row / column #
| Description | With this action you can write a row or column (e.g. a data record). If you do this more than once, the next free row or column is always selected automatically. |
| 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 to be 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 alignment) in which the first record should be located. |
| 1 | Mapping | In this table you determine in which row or column (depending on the alignment) the value of which variable is to be written. |
Write data block #
| Description | With this action you can write multiple records (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. |
| 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 to be 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 alignment) in which the first record should be 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 | Filter | If you have an array of length 100 in the PLC, for example, but only the first 50 entries are written, you can select here which data is written. To determine whether a data set contains data, the individual values within the data set are checked. A data set contains data as soon as a value deviates from the default value of the data type. Use all: All records are used. No check is performed to see if data is included. Remove empty records: Each record is checked individually and used only if data is contained. For example, if all 100 records are written and only the record at index 50 is empty, it is removed and all other 99 records are written. Continuous records only: Once an empty record is found, it and all others are removed. For example, if all 100 records are written and the record at index 50 is empty. Only the data records from index 0 to index 49 are used, although data record 51 also contains data. |
| 2 | No data available | If there are no records, you can use this option to select whether the trigger should then be aborted with an error or not. |
