Newton Excel Bach, not (just) an Excel Blog

The Strand7 API provides an interface between the Strand7 Finite Element Analysis program and external software. It works with many different languages, including Python, which uses ctypes to transfer data to and from the API functions. It is essential that data is transferred with the right data types, and that arrays are correctly sized. This post provides a summary of the different data types, with an example of Python code that can be called as a user defined function (UDF) from Excel, using pyxll.

Input of integer, double or Boolean data types may be passed to ctype functions unchanged. In Release 3 versions of Python, strings must be converted using string.encode(). For input Python lists or Numpy arrays an empty ctype array of the required length must first be created, then the values are copied to the array.

Variables passed to a ctype function to return output values must be created in Python as shown above. The returned values must then be converted back to a Python type, using .value or .value.decode(), before being passed back to the spreadsheet.

Typical Python code to call an API function is shown below:

@xl_func
@xl_arg('uID', 'int')
@xl_arg('ResultType', 'int')
@xl_arg('ResultSubType', 'int')
@xl_arg('BeamNum', 'int')
@xl_arg('MinStations', 'int')
@xl_arg('ResultCase', 'int')
@xl_return('numpy_array<var>')
def py_GetBeamResultArray(uID, ResultType, ResultSubType, BeamNum, MinStations, ResultCase):
    """ 
    Returns the specified beam result quantity at several stations along the length of the beam. Additional stations
    are inserted to ensure that the maximum/minimum results are captured.
    :param uID: Strand7 model file ID number.
    :param ResultType: Beam result quantity; see Beam Results for additional information.
    :param ResultSubType: Beam result sub-type; see Beam Results for additional information.
    :param BeamNum: Beam number.
    :param MinStations: Minimum number of stations required.
    :param ResultCase: Result case number.
    """ 
    NumStations = c_int()
    NumColumns = c_int()
    BeamPos = (c_double * kMaxBeamResult)()
    BeamResult = (c_double * kMaxBeamResult)()
    iErr = St7GetBeamResultArray(uID, ResultType, ResultSubType, BeamNum, MinStations, ResultCase, NumStations, NumColumns, BeamPos, BeamResult)
    if iErr:
        return py_ErrStringA(iErr)
    else:
        n = NumStations.value * NumColumns.value
        res = np.array(BeamResult[0:n]).reshape(-1, NumColumns.value)
        pos = np.array(BeamPos[0:NumStations.value]).reshape(-1,1)
        return np.concatenate((pos, res), axis = 1)

The API function, St7GetBeamResultArray, returns two integers and two arrays (the positions along the beam where results are returned, and the result values). The integers and arrays are created in Python, then passed to the API function. The returned ctypes 1D arrays are then converted to Numpy 2D arrays, using .reshape(), and combined into a single array for return to Excel, using np.concatenate.

The function in use is shown in the screenshots below:

The function may return results for the two beam ends:

or for up to 100 sections along the beam: