Ranges and expressions 3
Spreadsheet programming
Last updated
Spreadsheet programming
Last updated
While the graphs produced by spreadsheet applications are great, the visualization capabilities of CAD environments are superior. We will thus try to bridge between them using drawing instructions. The objective is to achieve a type of functionality which is neither supported by the spreadsheet nor the CAD environments alone, but greater than both. We will make a generative 3D graphing calculator!
Issuing drawing instruction in CAD applications is conventionally achieved via mouse interactions, i.e. clicking the toolbar or menu buttons and selecting locations in the viewport. Alternatively, we can issue instructions via the command line interface (CLI) where the name of the command is entered, followed by actions involving inputting values using the keyboard exclusively. As an example, the demonstration above uses the “point” command. A few things to note here:
The CLI has an auto-complete functionality which allows you to partially type the command name and quickly confirm it or explore other commands with similar text components.
To execute a command you need to issue a line-termination keystroke which in this scenario is either the “enter”, “space” or “tab” keys. These are considered white-space characters separating phases of a command’s action sequence.
Most commands are interactive. This means they require user input to perform an operation. Those inputs are expected in a particular format, otherwise the command fails.
To cancel a command you may press the “escape” button at any time, while any white-space character implies “proceed to the next step”. Pressing “enter” in an empty command line, executes the last command successfully used.
For the “point” command, 3D coordinates of a point must be expressed as real numbers, using the dot “.” symbol for decimal separator, and comma “,” for the x, y and z coordinate delimiters.
Note that the coordinates shall not contain any white-space character e.g. “1.2, 3.4, 5.6” because those are reserved as instruction terminators. The correct way to issue point coordinates is “1.2,3.4,5.6” without spaces.
Knowing that we can issue commands purely via text, we can use the spreadsheet to translate the data produced earlier to CAD instructions. The goal is to read numerical values from the angle and cosine columns and convert them into text such that the CAD application can understand as drawing instructions: “point {angle},{cosine},0″.
In a new column named “command” we expand the keyword “point” for every row. Each row represents one point instruction. We then create a new column named “coordinates” were we expand the expression seen below. This contains a composite expression for converting numbers to text and combining multiple text fragments, also known as strings, into a single text value.
The fixed function requires a numerical value and the number of decimal digits to translate into text. For example, fixed( 3.1415, 2 ) → 3.14 i.e. keep only two decimal points. Here we convert the angle and cosine values from columns C and D to text using 5 decimal places of precision.
The concat function requires two or more text values which are joined into one text value. For example concat( “a”, “b”, “c” ) → “abc”. The process of joining multiple string values is known as concatenation, hence .
In the same fashion as the CAD command line interface, we follow conventions when composing complex expressions in spreadsheets:
To compose functions we use the mathematical notation of "function( list of variables )”. In math we write for functions of multiple variables and for functional compositions. In computing we use more descriptive names and the term argument, or parameter, instead of variable for values passed ie. supplied for evaluation, to a function.
Commas “,” are used for separating arguments passed to a function. For example, cos(angle) requires one numerical argument, while fixed(number, digits) requires two. Some functions such as concat( … ) or max( … ) we used earlier can process an arbitrary number of arguments; we call those variable-argument functions.
To demarcate a text value, also known as a string literal, within an expression, we use the double quote " character to enclose the text value’s contents. We need two double quote characters, one for starting and one for ending a string. If you think about it, we need some special character otherwise, how could we differentiate a sequence of characters from being part of the expression or the text value?
To draw the commands generated, we just need to select the “command” and the “coordinates” columns (without the column labels) and copy/paste them into the CAD system via the clipboard. The reason this works is that the spreadsheet inserts a tab character (the character mapped to the tab key) between each column and a new line character (the character mapped to the enter key) for each end of row.
To verify this claim, you may open a simple text editor in Rhino using the command ‘Notes’ and paste the contents there. From the perspective of the command line interface, all we are doing here is just typing these commands really really fast.
As long as we adhere to the command line expectations i.e. proper use of white space and various text delimiters, we can automate every available command. To verify this we can replace the “coordinates” label with “polyline” and append at the end of the coordinates column the word “enter” to terminate the command.
The “polyline” command draws a polygonal curve by joining consecutive points by linear segments. Once issued, it asks the user for selecting points, on-screen or via point coordinates, until the “enter” or “space” key is pressed, or right mouse button clicked, upon which the command is completed and a polygonal line is drawn.
By now we have already surpassed the capabilities of the CAD system itself as there is no native command for arbitrary mathematical graph plotting! Sure you can draw lines, circles, ellipses and parabolas but there is no sine/cosine or even more complex formula tools. This is the first computational design tool you have developed, a set of capabilities beyond the default or intended use-case of your host environment.
The polygonal curve produced looks like the cosine curve but its is only a discrete approximation thereof. To achieve higher resolution we have two options: (a) increase the number of samples by adding more rows, or (b) request the CAD system draw a smooth curve using the points we computed. To increase the number of samples, we just need to select the last row of our table and drag-expand it as needed.
Alternatively, we may replace the “polyline” command with either the “curve” or “interpcrv” command. The “curve” command approximates the points using a polynomial of degree 3, or any order you prefer, but the curve constructed does not pass through the points. The interpolated curve command “interpcrv” does exactly that; it forces the polynomial curve to pass through the supplied points.
These curves are known as Bezier curves, when the number of points equals the curve’s degree plus one; B-Spline curves, when we fix the degree but use arbitrary numbers of points; or NURBS curves, when we want to express conic sections e.g. circles, ellipses, parabolas and hyperbolas using ratios of polynomials. Polynomials are prevalent in computer graphics because they can approximate any curve, planar or spatial, and they are fast to compute.
Practice
There are tons of commands that require input of point coordinates. Explore some of them to familiarize yourself with various geometric drawing methods. By clicking any toolbar or menu command you can observe in the command line the equivalent text representation and sequence of arguments required.
A sequence of points maps very intuitively to the concept of a curve. What about creating surfaces? Try to produces triples of points and issue triangle commands to generate mesh i.e. triangulated surface geometries. Hint: you need just more columns.
