Drawing in Excel 9 – Perspective Projection

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:

  1. Find the mid-point of the X, Y and Z coordinates of the scene to be plotted.
  2. 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.
  3. Move the Origin to the viewpoint by subtracting the X, Y and Z coordinates of the viewpoint from every point in the coordinate list.
  4. 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:
  5. Rotate the X and Z axes about the Y (vertical) axis, so that the X coordinate of the mid-point is zero.
  6. Rotate the Y and Z axes about the X (horizontal transverse) axis, so that the Y coordinate of the mid-Point is zero.
  7. The projected coordinates of the 2D perspective image (X’ and Y’)are then given by: X’ = X/Z, Y’ = Y/Z
  8. 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 - Top Left view


Cube - same angle, reduced viewing distance

Cube - same angle, reduced viewing distance


Cube - zoom in

Cube - zoom in


Suspension Bridge - overall view

Suspension Bridge - overall view


Bridge detail x 4

Bridge detail x 4


Bridge detail x 10

Bridge detail x 10

Bridge Detail x 50

Bridge Detail x 50

This entry was posted in Drawing, Excel, VBA. Bookmark the permalink.

3 Responses to Drawing in Excel 9 – Perspective Projection

  1. Ed B. says:

    I know this is an older post … but, I just saw it. Question: have you tried Google sketchup ? I know it’s not a professional tool for exacting standards, but, can or could be useful for how to ‘see’ in 3d or in perspective. And, it’s quite easy to master. Just a thought.


  2. Pingback: Route Map … | Newton Excel Bach, not (just) an Excel Blog

  3. Pingback: Daily Download 11: Perspective Projection | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.