The load table for the 3D Frame spreadsheet has a column listing the global axis for each load as text (X, Y, or Z). In the VBA version the table is converted to a variant array, and the axes are handled with a Select Case statement. This will not work in Python, because the load data is copied to a Numpy array, which will not handle data of different types. If the data type is not specified then all the data is converted to strings, or if a numeric data type is specified Numpy returns an error.
Converting text to a number based on the ASCII code value can be done easily on the spreadsheet using the CODE() or UNICODE() functions. In this case X, Y, and Z (or x,y,z) are to be converted to 1, 2, or 3 respectively. The ASCII code for W is 87, so the function required is:
- =CODE(UPPER(A1))-87, or =UNICODE(UPPER(A1))-87
In VBA the equivalent function is Asc():
Function TextToNum(Txt As String, Optional Offset As Long = 0)
TextToNum = Asc(Txt) - Offset
End Function
Doing the conversion in Python (using xlwings to transfer the data from Excel) requires a little more work. The column with the text data must be converted to values before converting the data to a Numpy array, but the Excel range, which is converted to a Variant array in VBA, is transferred to Python as a Tuple, which is an immutable object. A single row of data could be converted to a list with:
DistLoads = list(DistLoads)
But a multi-row range will be passed as a tuple of tuples, which the code above will convert to a list of tuples, leaving the data still in immutable form. The map function will convert all the data to list form:
DistLoads = map(list, DistLoads)
The text in the Axis column (Column 2) can then be converted to integers with:
for row in DistLoads: if type(row[1]) == unicode: row[1] = ord(row[1].upper())-87
The resulting list of lists can be converted to a Numpy array with:
if type(LoadA) != np.ndarray: LoadA = np.array(LoadA, dtype=np.float64)
Note that:
- Text is passed from Excel as Unicode data, which is different to the str data type, so:
if type(row[1]) == str:
would always be false. - The ord function converts the text to an integer, but the load data needs to be passed as Numpy float64 values, so the Axis integer values are also converted into doubles
- If data contains mixed integers and doubles (or float64), it will all be converted to doubles. The statement “dtype=np.float64” is therefore optional, but serves as useful documentation.
Newton Excel Bach, not (just) an Excel Blog 