Excel Integration with SOFiSTiK FEA via CADINP

19 May 2024 • Posted by Smiljan Tukić

Video & Introduction

By loading the video, you accept YouTube's privacy policy.

Video URL: https://youtu.be/0c8c2prgo0Y

In this blog article you will learn how to use the Excel CADINP interface in SOFiSTiK FEA. We’ll illustrate this feature with a small example, demonstrating how to read node properties from an Excel file (.XLSX).

hint

The Excel Import/Export feature requires SOFiSTiK FEA version 2023 or newer. Please upgrade if you’re using an older version.

Example

Download

example_1.zip

Consider a scenario where you require to read the node coordinates from an Excel file (XLSX). To address this requirement, the FEA Excel Interface feature can be utilized.

This example will demonstrate how to apply your data directly within SOFiMSHA or SOFiMSHC.

In addition, you’ll learn how to leverage Result Viewer in conjunction with the read functionality, as well as how to easily write data to an Excel file.

Configure Workbook and Worksheet

CADINP input offers the variables XLS.NAME and XLS.SHEET to specify the workbook and worksheet you wish to work with. You can change these variables anywhere in your CADINP input. They work like any other variable, but the interface uses them specifically to determine the workbook path and sheet name.

So by definining following:

LET#XLS.NAME "dummyname.xlsx"  $ define the XLSX file
LET#XLS.SHEET "Sheet1"         $ define the Sheet name

The program will read data from ‘dummyname.xlsx’ and sheet ‘Sheet1’. If your workbook isn’t in the project folder (database directory), then you may provide an absolute path of the XLSX file.

Read Data from Excel (.XLSX)

With the workbook and sheet defined, you’re ready to import data into SOFiSTiK. To read data from specific cells in your Excel file, use the CADINP function XLS.READ(<row>,<column>)

where:

<row> - refers to the row number within the workbook and worksheet
<column> - refers to the column number within the workbook and worksheet

If you want to read, e.g. a value from row = 2 and column = 10, then you can do it by following:

LET#A1 XLS.READ(2,10)      $ read data from cell (row = 2, column = 10)

To switch between sheets or workbooks, change the values of the XLS.NAME and XLS.SHEET variables.

LET#XLS.NAME "dummyname.xlsx"  $ define the XLSX file

$ Read data from Sheet1
LET#XLS.SHEET "Sheet1"    $ define the Sheet name
LET#A1 XLS.READ(2,10)     $ read data from cell (row = 2, column = 10)

$ Read data from Sheet2
LET#XLS.SHEET "Sheet2"    $ define the Sheet name
                          $ after this input, the XLS.READ() function
                          $ _ will use "Sheet2" as worksheet
LET#B1 XLS.READ(4,10)     $ read data from cell (row = 4, column = 10)
LET#B2 XLS.READ(8,9)      $ read data from cell (row = 8, column = 9)

To read data from a dynamically changing Excel file (XLSX), use a loop along, with the available return values to iterate through rows of varying heights.

Available return values are:

Return 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)

The return value is stored in variable XLS.IER.

hint

Please refer to the official SOFiSTiK FEA documentation for the return values.

As shown above, a valid cell will always return 0, 1, or 2. We will use this as a condition in the loop.

The idea is to loop over the cells until we find an empty cell, where the return value will be larger than 2.

+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

Write Data to Excel (.XLSX)

To write data into an Excel file (.XLSX), use the CADINP function XLS.WRITE(<row>,<column>,<value>).

where:

<row> - refers to the row number within the workbook and worksheet
<column> - refers to the column number within the workbook and worksheet
<value> - specifies the value, to be written to the cell

LET#XLS.NAME "dummyname.xlsx"     $ define the XLSX file
LET#XLS.SHEET "Sheet1"            $ define the Sheet name

$ Write data to Sheet1
LET#RET XLS.WRITE(2,10,10)        $ write value 10 to row = 2, column = 10
LET#RET XLS.WRITE(2,11,"ABC")     $ write text "ABC" to row = 2, column = 11

LET#A 10
LET#B 3
LET#RET XLS.WRITE(2,12,#A*#B)     $ write result of expression "ABC"
                                  $ to row = 2, column = 12

The XLS.WRITE function together with loops offer a convenient way to repeatedly write data to Excel sheets.

Important

Ensure the workbook is closed before writing data to an XLSX file.

Important

When external data is written to an XLSX file, Excel may not automatically recalculate formulas referencing those cells. To ensure proper recalculation, use the shortcut CTRL + ALT + SHIFT + F9 to force a complete recalculation of all cells in active workbook.

—

Tags: SOFiSTiK Excel Interoperability Interface XLSX FEA CADINP