Hi all,
I am trying to plot a 3D graph using anaconda code in excel. While I succeeded in doing so, the graph is placed over the cells and not inside one, causing figures to stack on top of each other for every recalculation. How do I configure it so that the graph is placed in the cell of the code (or stored so it can be called from a different cell)?
I tried removing plt.show and storing the figure in a variable by returning ax or fig, but that did not work.
btw, integrating python into excel is the best idea ever and I am exited to see where it will go 
Here is my current code:
b_thk = 0.1
w = REF("Inputs!C8")
l = REF("Inputs!B8")
h_diff = REF("Inputs!D8")
C_type = REF("Berekeningen!G3")
def rot_pt(x, y, ang_rad):
cos_t = np.cos(ang_rad)
sin_t = np.sin(ang_rad)
return cos_t * x - sin_t * y, sin_t * x + cos_t * y
def str_sec(ax, l, w, thk, start, h, h_diff=0):
x_s, y_s, z_s = start
h_rad = np.radians(h)
x = [0, l, l, 0, 0]
y = [0, 0, w, w, 0]
x_r, y_r = [], []
for i in range(len(x)):
x_r.append(rot_pt(x[i], y[i], h_rad)[0] + x_s)
y_r.append(rot_pt(x[i], y[i], h_rad)[1] + y_s)
z_b = [z_s, z_s + h_diff, z_s + h_diff, z_s, z_s]
z_t = [z_s + thk, z_s + h_diff + thk, z_s + h_diff + thk, z_s + thk, z_s + thk]
verts_b = [list(zip(x_r, y_r, z_b))]
verts_t = [list(zip(x_r, y_r, z_t))]
ax.add_collection3d(Poly3DCollection(verts_b, facecolors='blue', linewidths=2, zorder=2, zsort='max'))
# ax.add_collection3d(Poly3DCollection(verts_t, facecolors='blue', linewidths=2, zorder=3, zsort='min'))
for i in range(len(x) - 1):
ax.plot([x_r[i], x_r[i]], [y_r[i], y_r[i]], [z_b[i], z_t[i]], color='gray')
ax.add_collection3d(Poly3DCollection([[(x_r[i], y_r[i], z_b[i]),
(x_r[i], y_r[i], z_t[i]),
(x_r[i + 1], y_r[i + 1], z_t[i + 1]),
(x_r[i + 1], y_r[i + 1], z_b[i + 1])]
for i in (0,2)], facecolors='gray', zorder=3))
loc_leg_pos = [(0, 0), (0, w), (l, 0), (l, w)]
for local_x, local_y in loc_leg_pos:
g_x, g_y = rot_pt(local_x, local_y, h_rad)
ax.plot([g_x + x_s, g_x + x_s],
[g_y + y_s, g_y + y_s],
[0, z_s + local_x * np.sin(np.arcsin(h_diff / l))],
color='gray', zorder=1)
if l > 3:
l_int = int(l)
for i in range(0, l_int // 3):
add_leg_pos = l_int / (1 + l_int // 3) * (i + 1)
loc_add_leg_x = add_leg_pos
g_x_l, g_y_l = rot_pt(loc_add_leg_x, 0, h_rad)
g_x_r, g_y_r = rot_pt(loc_add_leg_x, w, h_rad)
ax.plot([g_x_l + x_s, g_x_l + x_s],
[g_y_l + y_s, g_y_l + y_s],
[0, z_s + loc_add_leg_x * np.sin(np.arcsin(h_diff / l))],
color='gray', zorder=1)
ax.plot([g_x_r + x_s, g_x_r + x_s],
[g_y_r + y_s, g_y_r + y_s],
[0, z_s + loc_add_leg_x * np.sin(np.arcsin(h_diff / l))],
color='gray', zorder=1)
return (x_r[2], y_r[2], z_s + h_diff), h
def cur_sec(ax, r_in, r_out, thk, start, h, ang=90):
x_s, y_s, z_s = start
h_rad = np.radians(h)
theta = np.linspace(np.radians(-90), np.radians(-90 + ang), 100)
x_in = r_in * np.cos(theta)
y_in = r_in * np.sin(theta) + r_in
x_out = r_out * np.cos(theta)
y_out = r_out * np.sin(theta) + r_in
x_in_r, y_in_r = rot_pt(x_in, y_in, h_rad)
x_out_r, y_out_r = rot_pt(x_out, y_out, h_rad)
x_in_r += x_s
y_in_r += y_s
x_out_r += x_s
y_out_r += y_s
z_b_i = z_s
z_b_o = z_s
z_t_i = z_b_i + thk
z_t_o = z_b_o + thk
# Draw curved section
for i in range(len(x_in) - 1):
ax.plot([x_in_r[i], x_in_r[i]], [y_in_r[i], y_in_r[i]], [z_b_i, z_t_i], color='gray', zorder=3)
ax.plot([x_out_r[i], x_out_r[i]], [y_out_r[i], y_out_r[i]], [z_b_o, z_t_o], color='gray', zorder=3)
ax.add_collection3d(Poly3DCollection([[(x_in_r[i], y_in_r[i], z_b_i),
(x_in_r[i], y_in_r[i], z_t_i),
(x_out_r[i], y_out_r[i], z_t_o),
(x_out_r[i], y_out_r[i], z_b_o)]
for i in range(len(x_in_r) - 1)], facecolors='blue', zorder=2))
new_h = h + ang
# Add legs if angle exceeds 45 degrees on both sides of the curve
if ang > 45:
# Add leg on the outside of the curve
mid_angle = np.radians(-90 + ang / 2)
mid_x_out = r_out * np.cos(mid_angle) + x_s
mid_y_out = r_out * np.sin(mid_angle) + r_in + y_s
mid_z_out = z_s + thk # Assuming legs start at the top of the curved section
ax.plot([mid_x_out, mid_x_out], [mid_y_out, mid_y_out], [0, mid_z_out], color='gray', zorder=1)
# Add leg on the inside of the curve
mid_x_in = r_in * np.cos(mid_angle) + x_s
mid_y_in = r_in + r_in * np.sin(mid_angle) + y_s
mid_z_in = z_s + thk # Assuming legs start at the top of the curved section
ax.plot([mid_x_in, mid_x_in], [mid_y_in, mid_y_in], [0, mid_z_in], color='gray', zorder=1)
return (x_out_r[-1], y_out_r[-1], z_s), new_h
def set_axes_equal(ax):
x_lim = ax.get_xlim3d()
y_lim = ax.get_ylim3d()
z_lim = ax.get_zlim3d()
x_range = abs(x_lim[1] - x_lim[0])
x_mid = np.mean(x_lim)
y_range = abs(y_lim[1] - y_lim[0])
y_mid = np.mean(y_lim)
z_range = abs(z_lim[1] - z_lim[0])
z_mid = np.mean(z_lim)
rad = 0.5 * max([x_range, y_range, z_range])
ax.set_xlim3d([x_mid - rad, x_mid + rad])
ax.set_ylim3d([y_mid - rad, y_mid + rad])
ax.set_zlim3d([z_mid - rad, z_mid + rad])
def plot_conv():
fig = plt.figure(dpi=150,figsize=(3.65,3.65))
ax = fig.add_subplot(111, projection='3d', computed_zorder=False)
ax.set_box_aspect([1.0, 1.0, 1.0])
start = (0, 0, 1)
h = 0
l1 = l / 2 - np.deg2rad(C_type)
l2 = l / 2 - np.deg2rad(C_type)
dh1 = h_diff / 2
dh2 = h_diff / 2
end_1, h_1 = str_sec(ax, l1, w, b_thk, start, h, h_diff=dh1)
end_2, h_2 = cur_sec(ax, 2, 2 + w, b_thk, end_1, h_1, ang=C_type)
end_3, h_3 = str_sec(ax, l2, w, b_thk, end_2, h_2, h_diff=dh2)
ax.set_xlabel('x (m)')
ax.set_ylabel('y (m)')
ax.set_zlabel('z (m)')
set_axes_equal(ax)
plt.title(REF("Inputs!A8"))
plt.tight_layout()
#plt.show()
return ax
plot = plot_conv()```