Interface with MS Excel#

In the new version 2023 a new CADINP interface for writing/reading data to/from an XLSX file is available.

Configure the workbook and worksheet#

To configure the workbook and worksheet, it is required to use predefined SOFiSTiK variables.

  • #XLS.NAME

  • #XLS.SHEET

Example: Set the file “test.xlsx” and “Sheet1”#
LET#XLS.NAME "test.xlsx"  $ define the XLSX file
LET#XLS.SHEET "Sheet1"    $ define the worksheet name

Read data from an Excel file#

To import a cell value from the defined workbook and sheet, it is required to use the new CADINP function:

  • XLS.READ(<row>, <column>).

Below we will import the data from an Excel cell.

+PROG TEMPLATE
LET#XLS.NAME "test.xlsx"  $ define the XLSX file
LET#XLS.SHEET "Sheet1"    $ define the Sheet name

LET#A1 XLS.READ(1,5)      $ read data from cell (row = 1, column = 5)
END

Write data to an Excel file#

To write data to an Excel file (XLSX, XLSM), it is required to use the new CADINP function:

  • XLS.WRITE(<row>, <column>, <value>)

Important

Writing data to an opened file is not possible because the Excel files are opening as read only.

An error may occur in following cases:

  • you may not have write permissions or

  • the file may be open by another application.

Hint

If data is written to the XLSX file from outside, it can happen that Excel does not recognize a change and the formulas that were referenced to the cells are not recalculated with the new values.

Use the key combination CTRL + ALT + SHIFT + F9 to recheck dependent formulas, and then recalculates all cells in all open workbooks, including cells that are not marked as marked as to be calculated.

Below you will find a small example how to write data to a file “test.xlsx” and workbook “Sheet1”.

+PROG TEMPLATE
LET#XLS.NAME "test.xlsx"  $ define the XLSX file
LET#XLS.SHEET "Sheet1"    $ define the worksheet name

LET#RET XLS.WRITE(1,5,10)      $ write value 10 to cell (row = 1, column = 5)
LET#RET XLS.WRITE(3,3,"test")  $ write text "test" to cell (row = 3, column = 3)
LET#RET XLS.WRITE(3,6,3*3)     $ write the result of an equation to cell (row = 3, column = 6)
END

The function XLS.WRITE requires always a return variable. In the example above, the return value will be stored into the variable #RET.

Type of data and error codes#

If you want to find out the type of the data that you are reading from an Excel file (XLSX). Then you may check the #XLS.IER variable value.

#XLS.IER variable value

Description

0

No Error

1

Data Type is Number

2

Data Type is Text

882

Error Unspecified

883

Error File Not Found

884

Error File In Use or Open Failed

885

Error Sheet Not Found or Invalid Sheet Name

886

Error Invalid Cell Address

887

No Supported Data (Empty Cell - return 0 as default value to LET/STO variable)

This information is very helpful in case you are looping through the Excel file.

Hint

Total number of rows and columns on a worksheet is limited to 1048576 rows by 16384 columns.

+PROG TEMPLATE
LET#XLS.NAME "test.xlsx"  $ define the XLSX file
LET#XLS.SHEET "Sheet1"    $ define the worksheet name

LOOP#i
  LET#FOO XLS.READ(#i+1,1)
  PRT#FOO
ENDLOOP #XLS.IER<3 $ loop until empty cell is reached
END