Previous post
Download PlotXYZ-xlsb.zip (XL 2007)
Download PlotXYZ-xls.zip (XL 2003 and earlier)
The drawing program presented in the previous post in this series has been modified to plot perspective projections of line drawings defined by 3D coordinates of line end points, and a list of connected points. The coordinates of the viewpoint and the centre point of the plot are entered together with a scale factor. The program then allows the spreadsheet ranges defining the drawing to be selected, or previously selected ranges may be re-used.
The procedure used in perspective projection is most easily visualised by imagining a sheet of glass placed between the veiwer and the scene to be drawn. The glass is placed at unit distance from the eye (say 1 metre), and is perpendicular to the line of sight between the eye and the point that will be the centre of the drawing. If the eye was maintained in a fixed position, and the outline of the scene was drawn on the glass, the resulting 2D image would be the same as will be calculated and plotted in the spreadsheet.
In more detail, the procedure carried out in the spreadsheet is as follows:
- Find the mid-point of the X, Y and Z coordinates of the scene to be plotted.
- The viewpoint is specified relative to the mid-point of the scene. Use the calculated mid-point values to find the true coordinates of the viewpoint.
- Move the Origin to the viewpoint by subtracting the X, Y and Z coordinates of the viewpoint from every point in the coordinate list.
- Assuming that the X and Z axes are horizontal and the Y axis is vertical it is necessary to rotate the axes so that the Z axis extends from the viewpoint through the midpoint of the model:
- Rotate the X and Z axes about the Y (vertical) axis, so that the X coordinate of the mid-point is zero.
- Rotate the Y and Z axes about the X (horizontal transverse) axis, so that the Y coordinate of the mid-Point is zero.
- The projected coordinates of the 2D perspective image (X’ and Y’)are then given by: X’ = X/Z, Y’ = Y/Z
- The projected X’ and Y’ coordinates are then scaled and trimmed using the techniques described in the previous post.
More detail of the process is given here: Imperial College 3D Graphics
Screen shots of images produeced by this program are shown below, and the data used is included in the download files.
A couple of words of warning about this spreadsheet:
- The suspension bridge drawing includes over 3000 data points, and when plotting in Excel 2000 seems to cause a lockup half-way through the process. For this reason I have included a greatly simplified model with the xls file for Excel 2003 and earlier.
- The complex bridge drawing is successfully plotted in Excel 2007, but it is painfully slow. It takes over 30 seconds to draw on my machine, although details (which contain fewer lines) plot much more quickly.
The slow drawing is related to the drawing process, not the projection calculations, which are reasonably fast. Excel 2000 and 2007 (surprisingly) seemed to be equally slow, and as far as I know the solution lies in the hands of Microsoft, but if anyone does know a way to speed up the plotting of complex images I would be glad to hear it.
Cube - Top Left view
Cube - same angle, reduced viewing distance
Cube - zoom in
Suspension Bridge - overall view
Bridge detail x 4
Bridge detail x 10
Bridge Detail x 50
Newton Excel Bach, not (just) an Excel Blog 