In this post I leverage a brilliant Python library by Philip Rideout which draws wireframe graphics to SVG files and then I convert the SVG drawing directives to Shapes on an Excel worksheet.
This means I can take this SVG file of an octahedron
and convert it to this on the Excel worksheet.
Background
On StackOverflow, a question arose about drawing a wireframe box. I had looked into drawing on the worksheet using GDI before but I ruled out that approach. Instead, it is required to draw shapes on the worksheet. GDI still works for drawing on a Form as this Stars and Stripes example demonstrates. Using the macro recorder helps to understand how to build a free form shape but we'd need to write some 3D maths library in VBA to calculate all the vertices etc.
Luckily a brilliant library written by Philip Rideout exists and can do all the business of defining wireframe shapes in terms of vertices and also how the camera is pointing and it will do all the hard mathematics and draw to an SVG file. Then, I give code which parses that SVG file (it is XML after all) and I convert the polygon drawing directives to Excel (Freeform) Shapes.
The Setup
In Visual Studio give yourself a new Python project. Add the svg3d.py file from Github. Also add the example.py file from Github. Set the example.py file to be the file to run on startup. As it stands the code will generate an SVG file of an octahedron, you can see a rendering at the top of this page. It is very good, the fill on the front sides is set to 75% opacity so you can still see the rear faces. The rear faces are drawn first meaning I don't have to worry about which faces are hidden etc.
What is now needed is just a little more code to open an Excel workbook so add the following to the end of the example.py file
class ScreenUpdatingRAII(object):
def __init__(self, app, visible:bool=False):
self.app = app
self.saved = app.ScreenUpdating
app.ScreenUpdating = visible
def restore(self):
self.app.ScreenUpdating = self.saved
self.app = None
def convertSvgToExcelShapes(filename):
import xml.etree.ElementTree as ET
from win32com.client import GetObject,Dispatch
# code below is highly dependent on the child
# structure because xpath was not working for me (my bad)
dom = ET.parse(filename)
rootxml = dom.getroot()
g = rootxml[1] # second child
wb = Dispatch(GetObject(r"C:\Users\Simon\source\repos\WireframeExcelShapes\WireframeExcelShapes\WireframeExcelShapes.xlsx"))
app = Dispatch(wb.Parent)
ws = Dispatch(wb.Worksheets.Item("WireFrame"))
shps = Dispatch(ws.Shapes)
for x in shps:
Dispatch(x).Delete()
idx =0
scale, xoffset, yoffset = 500, 300,300
screenUpdates = ScreenUpdatingRAII(app)
for polygon in g:
# triple nested list comprehension parsing the points by splitting
# first by space then by comma then converting to float
points = [[float(z[0])*scale+xoffset, float(z[1])*scale+yoffset] for z in [y.split(',') for y in [x for x in polygon.attrib['points'].split()]]]
#print(points)
msoEditingAuto,msoSegmentLine, msoFalse, msoTrue = 0,0,0, -1
freeformbuilder=shps.BuildFreeform(msoEditingAuto, points[0][0] , points[0][1])
freeformbuilder.AddNodes(msoSegmentLine, msoEditingAuto, points[1][0] , points[1][1])
freeformbuilder.AddNodes(msoSegmentLine, msoEditingAuto, points[2][0] , points[2][1])
freeformbuilder.AddNodes(msoSegmentLine, msoEditingAuto, points[0][0], points[0][1])
newShp = Dispatch(freeformbuilder.ConvertToShape())
shpFill = Dispatch(newShp.Fill)
shpFill.Visible = msoTrue
shpFill.Transparency = 0.25
shpFill.Solid
shpFill.ForeColor.RGB = 0xFFFFFF
idx=+1
screenUpdates.restore()
pass
filename = "octahedron.svg"
generate_svg(filename)
convertSvgToExcelShapes(filename)First comes a class called ScreenUpdatingRAII() which I use to switch on screen updates whilst drawing. This speeds the code and also kills screen flicker.
Next comes the function convertSvgToExcelShapes() which loads the SVG file into Python's Element tree XML parser. Then using some COM calls will open an Excel workbook which you must have saved before hand, and then accesses a sheet called WireFrame which you must have created beforehand as well! The code deletes any Shapes from that sheet and then proceeds to draw an Excel free form shape for each Polygon element in the SVG file. I haven't really added much value here it was quite straightforward. The dramatic output is 99% to Philip's credit.
However, I am proud of a line of code I did contribute. My triple nested list comprehension parses the string of points co-ordinates, scales and translates (math) them ready for the worksheet...
points = [[float(z[0])*scale+xoffset, float(z[1])*scale+yoffset] for z in [y.split(',') for y in [x for x in polygon.attrib['points'].split()]]]Links
Below is a link to Philip's blog and his Github repo.
