The py_RC Elastic spreadsheet (last discussed here) has been updated with a new function py_Biax, providing elastic analysis of reinforced concrete sections under combined axial load and biaxial bending. The new spreadsheet and associated Python code can be downloaded from:
The download file includes full open-source Python code. For details of the pyxll package required to link the Python code to Excel see: https://newtonexcelbach.com/python-and-pyxll/
The code and spreadsheet layout are based on the VBA version presented at: Elastic Biaxial Bending
Input for a T-section beam is shown in the screenshot below. The concrete is defined by the coordinates of each corner point, and reinforcement is is detailed in layers, with the coordinates of the ends of each layer:
The angle of the neutral axis is found by iteration, and the estimated angle should be entered in cell E5, then click the “Adjust NA Angle” button. The NA angle and position will be adjusted so that the stress at both ends of the NA is zero:
The function output displays: Concrete, reinforcement and combined section properties:
Concrete stresses at each corner of the section in compression:
Following a comment at https://newtonexcelbach.com/2023/01/20/using-conbeamu/ I have updated the ConBeamU-Template file so that the function to select the input range works the same as in the main spreadsheet. For more details of how the spreadsheet works, see the link above, and to download the new file click on:
Excel recently added the ability to extract text from an image, either on the clipboard or from a selected file. To try this out I used a screenshot of a table with vertically aligned text, from a pdf copy of an AutoCAD file:
The procedure for importing the data is very straightforward. Select Get-Data From Picture on the Data Tab:
Select “Picture From Clipboard” and the process to detect and convert the text will start:
When complete it displays an image of the extracted text, with options to review or paste directly to the spreadsheet:
Unfortunately with vertical text the results were a little disappointing!
Rotating the image through 90 degrees (using IrfanView) the results were much better:
In the screenshot above the data in columns A to C was extracted from the image on the clipboard, which has been pasted in columns E to I. The results are still not perfect, in particular:
Some 1s at the start or end of a number have been missed.
Some zeros have been converted to o.
Spaces have been inserted into some numbers, usually associated with a 1.
In columns J to K the results have been converted to either numbers or #Value, using the Value function.
In columns N to P Value has been used in conjunction with Substitute, to remove any spaces inserted between numbers, so the result can be converted to valid numbers. Note that the results still need to be checked carefully, since there is no way to check where digits have been removed, other than a visual check of the original table.
I have added a new py_Fact function to the py_Scipy module, that returns the factorial of an integer as a float if it is up to 15 digits long, or a string for longer numbers.
The revised code, and the example shown below in the py_Special-Dist.xlsb spreadsheet, can be downloaded from:
@xl_func(category = "Scipy", help_topic="https://docs.scipy.org/doc/scipy/reference/special.factorial.html!0")
@xl_arg('n', 'numpy_array<int>', ndim=1)
@xl_arg('exact', 'bool')
@xl_return('numpy_array<var>')
def py_fact(n, exact = False):
res = scipy.special.factorial(n, exact = exact)
if exact:
for i in range(0, res.shape[0]):
if res[i] > 1E15: res[i] = str(res[i])
return res
If the “exact” argument is set to True, or omitted, the Python function returns a long integer of the required length, but if this is returned to Excel as a number it would be converted to a float, so all the results greater than 1E15 are converted to a string so no digits are lost, and the resulting array is returned to Excel as a variant array.
In the example below the last 13 digits of the strings have been compared with the last 13 digits of the values returned as floats. Because the factorial numbers always have trailing zeros the integer and float results are exactly equal up to 22!, after which the difference rapidly starts to increase.
Recently someone told me that they were going to a birthday party of a neighbour who was turning 106! and I can now point out that this is equal to: 114628056373470835453434738414834942870388487424139673389282723476762012382449946252660360871841673476016298287096435143747350528228224302506311680000000000000000000000000.
I have recently needed code to rotate 2D coordinates, either about the origin, or some specified other point, using pyxll to call the code from Excel. My first attempt was a direct translation of some VBA code, that also worked with 3D coordinates, with rotation about any of the 3 axes:
@xl_func
@xl_arg('Rcoords', 'numpy_array')
@xl_arg('Axis', 'int')
def py_Rotate(Rcoords, Rotation, Axis, result = 0):
return Rotate(Rcoords, Rotation, Axis, result)
def Rotate(Rcoords, Rotation, Axis, result = 0):
# # Rotate about the origin, Rotation in radians
Plane = np.zeros(2, dtype = int)
if Rotation != 0:
if Axis == 1:
Plane[0] = 1
Plane[1] = 2
elif Axis == 2:
Plane[0] = 2
Plane[1] = 0
elif Axis == 3:
Plane[0] = 0
Plane[1] = 1
else:
return "Invalid Axis"
NumRows = Rcoords.shape[0]
# Rotate
for i in range(0, NumRows):
try:
if (Rcoords[i, Plane[0]] == 0 and Rcoords[i, Plane[1]] == 0) == False:
r = (Rcoords[i, Plane[0]]** 2 + Rcoords[i, Plane[1]]** 2)** 0.5
Theta = atan2(Rcoords[i, Plane[1]], Rcoords[i, Plane[0]])
Theta = Theta + Rotation
Rcoords[i, Plane[0]] = r * cos(Theta)
Rcoords[i, Plane[1]] = r * sin(Theta)
except:
pass
if result == 0:
return Rcoords
else:
return Rcoords[0, result-1]
This was greatly simplified, and speeded up, by using 2D coordinates for the input, simplifying the rotation formula, and using Numpy procedures to operate on entire columns, rather than looping through row by row:
To rotate about a point other than the origin the 3 functions above were modified by:
Translating the coordinates to move the rotation point to the origin.
Rotate the coordinates about the origin.
Translate the rotated coordinates back by the same amount.
@xl_func()
@xl_arg('Rcoords', 'numpy_array')
@xl_arg('RotnPt', 'numpy_array')
@xl_arg('Axis', 'int')
def RotateC(Rcoords, Rotation, RotnPt, Axis, result = 0):
# Rotate about RotnPt, Rotation in radians
Tol = 0.000000000001
NumRows = Rcoords.shape[0]
if abs(Rcoords[0, 0] - Rcoords[NumRows-1, 0]) + abs(Rcoords[0, 1] - Rcoords[NumRows-1, 1]) < Tol: NumRows = NumRows - 1
NumRCols = Rcoords.shape[1]
if RotnPt.shape[0] > RotnPt.shape[1]:
Rotnpt2 = RotnPt
NumCCols = RotnPt.shape[0]
RotnPt = np.zeros((1, NumCCols))
RotnPt[0, :] = Rotnpt2[:, 0]
NumCCols = RotnPt.shape[1]
if NumRCols < NumCCols:
NumCols = NumRCols
else:
NumCols = NumCCols
OCoords = np.zeros((NumRows, NumRCols))
for i in range(0, NumRows):
for j in range(0, NumCols):
OCoords[i, j] = Rcoords[i, j] - RotnPt[0, j]
OCoords = Rotate(OCoords, Rotation, Axis)
for i in range(0, NumRows):
for j in range(0, NumCols):
OCoords[i, j] = OCoords[i, j] + RotnPt[0, j]
OCoords[0:NumRows, NumCols:NumRCols] = Rcoords[0:NumRows, NumCols:NumRCols]
return OCoords
@xl_func()
@xl_arg('Rcoords', 'numpy_array')
@xl_arg('RotnPt', 'numpy_array')
@xl_arg('Axis', 'int')
def RotateC2(Rcoords, Rotation, RotnPt, Axis, result = 0):
# Rotate about RotnPt, Rotation in radians; with numpy array calculations
Tol = 0.000000000001
NumRows = Rcoords.shape[0]
if abs(Rcoords[0, 0] - Rcoords[NumRows-1, 0]) + abs(Rcoords[0, 1] - Rcoords[NumRows-1, 1]) < Tol: NumRows = NumRows - 1
NumRCols = Rcoords.shape[1]
if RotnPt.shape[0] > RotnPt.shape[1]:
Rotnpt2 = RotnPt
NumCCols = RotnPt.shape[0]
RotnPt = np.zeros((1, NumCCols))
RotnPt[0, :] = Rotnpt2[:, 0]
NumCCols = RotnPt.shape[1]
if NumRCols < NumCCols:
NumCols = NumRCols
else:
NumCols = NumCCols
OCoords = Rcoords - RotnPt
OCoords = Rotate2(OCoords, Rotation)
OCoords = OCoords + RotnPt
OCoords[0:NumRows, NumCols:NumRCols] = Rcoords[0:NumRows, NumCols:NumRCols]
return OCoords
@xl_func()
@xl_arg('Rcoords', 'numpy_array')
@xl_arg('RotnPt', 'numpy_array')
def RotateCM(Rcoords, Rotation, RotnPt):
# Rotate about RotnPt, Rotation in radians; with numpy array calculations
OCoords = Rcoords - RotnPt
OCoordsr = RotateM(OCoords, Rotation)
OCoordsr = OCoordsr + RotnPt
return OCoordsr
Finally for comparison and to check results some code was adapted from https://gist.github.com/LyleScott/ . Note that this code treats clockwise rotations as positive, whereas the other functions use the standard approach of anti-clockwise rotation being positive.
@xl_func()
@xl_arg('xy', 'numpy_array')
@xl_arg('radians', 'float')
@xl_arg('origin', 'numpy_array', ndim=1)
def rotate_around_point_highperf(xy, radians, origin=(0, 0)):
"""Rotate a point around a given point.
From: https://gist.github.com/LyleScott/
I call this the "high performance" version since we're caching some
values that are needed >1 time. It's less readable than the previous
function but it's faster.
"""
x = xy[:,0]
y = xy[:,1]
offset_x, offset_y = origin[:]
adjusted_x = (x - offset_x)
adjusted_y = (y - offset_y)
cos_rad = cos(radians)
sin_rad = sin(radians)
qx = offset_x + cos_rad * adjusted_x + sin_rad * adjusted_y
qy = offset_y + -sin_rad * adjusted_x + cos_rad * adjusted_y
res = np.zeros((qx.shape[0],2))
res[:,0] = qx
res[:,1] = qy
return res
Results and execution times for rotation of a simple shape are shown in the screen-shot below (click on the image for full-size view):
The functions using matrix multiplication are significantly faster, and this improvement is increased with a longer list of coordinates:
Newton Excel Bach, not (just) an Excel Blog 