3D graphics in Resolver One using OpenGL and Tao, part I

9 September 2009

I’ve been playing around with 3D graphics recently, and decided to find out what could be done using .NET from inside Resolver One. (If you haven’t hear of Resolver One, it’s a spreadsheet made by the company I work for — think of it as Excel on steroids :-)

I was quite pleased at what I managed with a few hours’ work:

To try it out yourself:

Once you’ve loaded up the spreadsheet, a window will appear showing the spinning cube with the Resolver Systems logo that appears in the video. In the spreadsheet itself, the worksheet “Path” contains the list of 2,000 points that the cube follows while it spins; as in the video, these are inially all (0, 0, 0), so the cube just sits in the centre of the window spinning. To make it bounce gently, following a sine curve, enter the following column-level formula by clicking on the header of column C:

=SIN(2 * PI() * (#t#_ / 1000)) * 2.5

You can then try putting the same formula with COS instead of SIN in column B, and with TAN in column D, to get the video’s zooming spiraling effect.

So, how does it work? The bulk of the clever OpenGL stuff is in the IPOpenGL.py support file. I’ll discuss that in a minute, but it’s worth glancing at the Resolver One side first. The post-formulae user code in the spreadsheet works like this:

  • Load up the IPOpenGL.py library,
    from IPOpenGL import CreateBackgroundSpinningBoxWindow
    
  • Ask a cached worksheet (which persists between recalculations) whether it has an OpenGL window stored in cell A1, and if it hasn’t, create one and put it in the cache so that later recalculations will pick it up:
    cachedWS = workbook.AddWorksheet('cache', WorksheetMode.Cache)
    openGLWindow = cachedWS.A1
    if cachedWS.A1 is Empty or cachedWS.A1.done:
        openGLWindow = CreateBackgroundSpinningBoxWindow("OpenGL Window", 1024, 768)
        cachedWS.A1 = openGLWindow
    

    Doing things with a cached worksheet like this means that we have one persistent OpenGL window that always shows the results of the most recent spreadsheet recalculation.

  • Set the initial rate of spin of the spinning box, both on the X and the Y axes. Also set the camera position so that the box is visible and suitably distant. (Try changing these parameters and see how it affects the animation; you’ll need to close the OpenGL window and hit the recalculate button to make the changes take effect.)
        openGLWindow.xspeed = 0.2
        openGLWindow.yspeed = 0.2
        openGLWindow.cameraPos = (0, 0, -12)
    
  • Finally, we set the positions property on the OpenGL window. It uses this internally to determine the path of the spinning cube; it’s just a list of 3-tuples, which is generated from the Path worksheet using a simple list generator that extracts the x, y, and z values from each of the content rows.
    openGLWindow.positions = [(row["x"], row["y"], row["z"]) for row in workbook["Path"].ContentRows]
    

So much for the user code. The IPOpenGL.py support module is a bit more complicated; there are just 314 lines, but that’s still too many for a full walkthough, so I’ll just give the highlights:

  • The first five lines just load up the appropriate DLLs — the Tao framework and Microsoft .NET — and then the library classes that we will use are imported.
  • Next, we define the class that is the main purpose this module: SpinningBoxWindow. The bulk of its __init__ method is simple enough, just setting the fields we’ll use elsewhere, setting up some Form properties so that the window can easily be painted to, and hooking up various events so that we can clean up OpenGL tidily when the window is closed. The method createDrawingContext that we call halfway through is a little more complex, so let’s skip over the event handlers (which are simple enough anyway) and go straight to that.
  • The important thing about createDrawingContext is that it initialises the fields hDC and hRC.
    • hDC is a handle to a device context, used by low-level Windows graphics routines as a representation of the screen area associated with our window. We get it using the code User.GetDC(self.Handle).
    • hRC is an OpenGL rendering context, which you can see as a wrapper around the device context used as a place to render 3D images.
  • killGLWindow is next; it simply shuts things down cleanly when you close the window by releasing the two contexts.
  • resizeGLScene is called when you resize the window (of course) and also when the window’s initially shown, and its purpose is really just to inform the OpenGL subsystem that the size of the window has changed, so when it’s rendering the image it should make it larger or smaller as appropriate.
  • initGL calls a method to load up textures (about which more in a moment) and then sets some initial OpenGL parameters. The most interesting of these are the light-related ones; these say that there is one light for the 3D scene, shedding an ambient (eg. without a specific source) light with RGB values of (0.5, 0.5, 0.5) — that is, fairly dim colourless light — and a diffuse light (more like a spotlight) that has RGB values of (1, 1, 1) — quite bright white light — from the point (0, 0, 2), which is between the screen and the cube. Try changing those values, in the variables lightAmbient, lightDiffuse, and lightPosition, and then reloading the Python module into Resolver One (Reload Modules/Recalculate on the Data menu) to see how you can affect the scene.
  • loadGLTextures does what it says :-) Textures can do many things in OpenGL; here we’re just using them as a simple way of making our spinning cube more interesting. We load up an image file using normal .NET code, and then do some manipulation to put it into a format that OpenGL will like. One interesting bit is the filtering, performed by these two lines:
    Gl.glTexParameteri(Gl.GL_TEXTURE_2D, Gl.GL_TEXTURE_MAG_FILTER, Gl.GL_LINEAR)
    Gl.glTexParameteri(Gl.GL_TEXTURE_2D, Gl.GL_TEXTURE_MIN_FILTER, Gl.GL_LINEAR_MIPMAP_NEAREST)
    

    This scales the 128×128 image so that it will look nice and non-jagged even when shown close-up. If you want to see something other than the Resolver Systems logo on the spinning cube, just change the texture file to point to a different BMP.

  • drawGLScene is where we actually draw the spinning cube; it’s called by the run loop for each and every frame that is displayed, so upwards of 30 times a second. It’s long, but it’s actually very simple.
    • The first thing to do is to clear the 3D space of any junk that was previously there, which we do by calling glClear
    • Next, we set things up to take account of the camera position — that is, the point in 3D space from which we want to view the scene. Drawing operations in OpenGL are done relative to a current position in 3D space (you might like to think of it as being like a cursor), so the simplest way to account for a camera position is to assume that the cursor is starting at the camera position, and then to move (using glTranslatef) in the opposite direction before starting to draw. This means that as we draw shapes, their position is relative to the shifted starting point.
    • Once that’s done, we move again, this time to take account of our current position along the path that was specified for the spinning cube. The path is specified in self.positions, and we use self.positionIndex as the position we’re currently drawing (incrementing it each time we draw a frame), so we just look up the appropriate position from the list and then use glTranslatef again to move (relative from our already-shifted starting position) to move to it.
    • Now we rotate ourselves using glRotatef. Drawing operations are not only performed relative to the current position — there’s a current rotation too. We use this as a way to make the cube that we’re displaying spin as it moves; self.xrot and self.xrot specify the current angle to draw the cube at on the X and Y axes. (We update them later on in this method.)
    • Next, we bind the texture — that is, we say to OpenGL that the shapes we’re about to draw should use the texture that we loaded earlier.
    • And now, we finally draw the cube. We do this by calling a function to say we’re about to draw a sequence of “quads” (GL-speak for quadrilaterals) and then calling glVertex3f in groups of 4. Each group of 4 specifies the four corners of a quad; it also has associated calls for each corner to glTexCoord2f to say how the texture should be applied to the quad, and one call per quad to glNormal3f, which tells OpenGL which way the quad is facing — quads are invisible from one side, and we want to make sure that the six quads that make up the sides of our cube are all facing outwards. What’s really cool here is that as we draw the cube, all of the co-ordinates we use are relative to our current “cursor” position and rotation, which already take account of the camera position, the position for the cube as specified in the self.positions list, and the rotation implied by the cube’s spin. So all we need to do is draw a simple cube with its corners at +/-1 on the X, Y and Z axes, and OpenGL will do all the shifting and rotating maths for us.
    • Once this is done, the cube is drawn, so we update the self.xrot and self.xrot variables used to spin it so that it will be a little more rotated next time, and we’re done.
  • Our next method is run, which is a .NET event loop. Because we want this window to be constantly updating in real-time, regardless of what the rest of the application is doing, we have our own loop, separate from the main Resolver One one. In it, we tell .NET to handle any pending events for our window, then draw the 3D scene, and then swap buffers to display it. (Thanks to the properties we set on the window originally, there are two screen buffers associated with our device context, one visible and one hidden, so we draw into the hidden one and then swap them around — this double-buffering makes the image less jerky.)
  • That completes the SpinningBoxWindow class! The only thing remaining is the factory function CreateBackgroundSpinningBoxWindow, which just creates a SpinningBoxWindow with a run loop on its own thread. There’s nothing exciting to see there.

If you understood all of that, then you’re in a great position to start building OpenGL applications driven by Resolver One data. I’ll be blogging more about this in the future, so watch this space if you want more hints and tips.

If you didn’t understand all of that, leave a comment below and I’ll try to explain it better (and update the description if needs be)!

3 thoughts on “3D graphics in Resolver One using OpenGL and Tao, part I

  1. George Matkovitz

    I think those who are doing event simulation and facility simulation in RO will definitly appreciate a 3D capability in Resolver One. For example it can help them visualize a queue in their production line.

    Thanks for working on these

  2. Nixta

    For one of the contests, I was going to try to use OpenGL to render various data in 3D… Rufus was going to provide the data, and we were going to try to georeference it and make fancy animations using it.

    But we were lazy or stupid or something.

Comments are closed.