This code creates a simple spreadsheet, using the CwMatrix widget, a contributed widget created at Cogent Real-Time Systems, Inc. The program uses a dynamic library, which requires that Gamma be called from it's own directory. For example, if Gamma is in your /usr/cogent/bin/ directory, you would start this program with the following command:

$ /user/cogent/bin/phgamma matrix.g

There are only three spreadsheet functions implemented in this example, though more could be easily added. They are:

sum (range) produces the sum of a given range. The range is of the form A4:B7, and can be entered by typing =sum( followed by highlighting the range, followed by typing ) into a spreadsheet cell, or by just typing in the range.

Example: =sum(A4:B7)

db (name) reflects the value of a Cascade DataHub point. This allows the example to display data from any live process that has a driver to the Cascade DataHub.

Example: =db(valve_1)

dbout (name,range) writes a value to the Cascade DataHub. The name is the Cascade DataHub point name, and the range indicates the cell whose value will be transmitted, which must be entered as a range from the cell to itself. This command will transmit its value whenever the sheet is recomputed.

Example: =dbout(valve_1_alarm, B5:B5)

The code starts here:

#!/usr/cogent/bin/phgamma

/*
 * This is a simple spreadsheet application written for QNX Photon(TM) with
 * the Gamma (TM) programming language.  It is not intended to be either
 * complete or correct, but merely to act as a demonstration of how to use
 * the CwMatrix widget.
 *
 * This code is provided free of charge or license by Cogent Real-Time
 * Systems Inc.  You are hereby given permission to use and modify this
 * code without restriction of any kind.
 */

/*
 * If we are running a Gamma executable that does not have the CwMatrix
 * widget defined, load the dynamic object libraries necessary to support
 * it.


if (undefined_p (CwMatrix))
{
  if (!dyna_add_lib ("/usr/cogent/lib/photon_s.dlb"))
    {
      princ ("Could not load dynamic library 'photon_s.dlb'\n");
      exit_program (1);
    }
  if (!dyna_add_lib ("/usr/cogent/lib/phwidgets.dlb"))
    {
      princ ("Could not load dynamic library 'phwidgets.dlb'\n");
      exit_program (1);
    }
}
 */
/*
 * Include the necessary utility libraries to enhance the Photon widget
 * set.  These files are found in /usr/cogent/lib
 */
require_lisp ("PhotonWidgets");
require ("PopupMenu");

/*
 * Declare some global variables.  The := syntax tells Gamma that only
 * the first attempt to set the variable will be honored.  This is useful
 * if you decide to re-read the source file while the application is
 * running.  It also allows you to set alternate values for these
 * variables through another program or from the command line prior to
 * loading this file.
 */

MENUHEIGHT := 34;
YOFFSET := 20;
XOFFSET := 20;
FormulaCells := make_array(0);

/*
 * Declare a sub-class of PtButton that has an extra resource called
 * sequence.  This sequence indicates the column or row number for which
 * this button is the header.  We will add DivButton widgets to dividers
 * for the row/col headers.
 */
class DivButton PtButton
{
  sequence;
}

/*
 * Declare a callback function for PtDivider to be called when the user
 * resizes a row.  We only need to resize the rows on either side of the
 * divider boundary, and then resize the last row in case the entire
 * divider has changed size.
 */
function cbDividerSetHeights (mat)
{
  local		left, right, sizes = widget.divider_sizes, n = length(sizes);
  
  left = cbinfo.divider.left;
  right = cbinfo.divider.right;
  mat.SetRowHeight (left.sequence, left.dim.h +
		    left.border_width * 2, 0);
  mat.SetRowHeight (right.sequence, right.dim.h +
		    right.border_width * 2, 0);
  mat.SetRowHeight (n - 1, sizes[n - 1].y - sizes[n - 1].x, 0);
  mat.FlushDamage ();
}

/*
 * Declare a callback function for PtDivider to be called when the user
 * resizes a column.
 */
function cbDividerSetWidths (mat)
{
  local		left, right, sizes = widget.divider_sizes, n = length(sizes);
  
  left = cbinfo.divider.left;
  right = cbinfo.divider.right;
  mat.SetColumnWidth (left.sequence, left.dim.w +
		      left.border_width * 2, 0);
  mat.SetColumnWidth (right.sequence, right.dim.w +
		      right.border_width * 2, 0);
  mat.SetColumnWidth (n - 1, sizes[n - 1].y - sizes[n - 1].x, 0);
  mat.FlushDamage ();
}

/*
 * Create the row label divider and buttons.  This is only called at
 * startup.
 */
function RowLabels (mat)
{
  local		i, divider, button;
  
  divider = new (PtDivider);
  divider.SetPos (0,YOFFSET + MENUHEIGHT);
  divider.SetDim (XOFFSET,mat.dim.h);
  divider.group_orientation = Pt_GROUP_VERTICAL;
  divider.divider_flags = cons (Pt_DIVIDER_RESIZE_BOTH, nil);
  PtAttachCallback (divider, Pt_CB_DIVIDER_DRAG,
		    `cbDividerSetHeights (@mat));
  
  for (i=0; i<mat.matrix_rows; i++)
    {
      button = new (DivButton);
      button.sequence = i;
      button.text_string = string (i);
      button.border_width = 0;
      button.margin_height = 0;
      button.margin_width = 0;
      button.text_font = "helv10";
      button.border_width = 2;
      button.SetDim (XOFFSET - 2 * button.border_width,
		     mat.RowHeight (i) - 2 * button.border_width);
    }
  PtRealizeWidget (divider);
  
  /* Since we cannot predict the size of the final button, we must
     adjust the final matrix row to match the button size. */
  mat.SetRowHeight (mat.matrix_rows - 1, button.dim.h +
		    2 * button.border_width, 1);
}

/*
 * Create the column label divider and buttons.  This is only called at
 * startup.
 */
function ColumnLabels (mat)
{
  local		i, divider, button;
  
  divider = new (PtDivider);
  divider.SetPos (XOFFSET,MENUHEIGHT);
  divider.SetDim (mat.dim.w,YOFFSET);
  divider.group_orientation = Pt_GROUP_HORIZONTAL;
  divider.divider_flags = cons (Pt_DIVIDER_RESIZE_BOTH, nil);
  PtAttachCallback (divider, Pt_CB_DIVIDER_DRAG,
		    `cbDividerSetWidths (@mat));
  
  for (i=0; i<mat.matrix_cols; i++)
    {
      button = new (DivButton);
      button.sequence = i;
      button.text_string = i < 26 ? char('A' + i) :
	string ("A", char('A' + i % 26));
      
      button.border_width = 0;
      button.margin_height = 0;
      button.margin_width = 0;
      button.text_font = "helv10";
      button.border_width = 2;
      button.SetDim (mat.ColumnWidth (i) - 2 * button.border_width,
		     YOFFSET - 2 * button.border_width);
      
    }
  PtRealizeWidget (divider);
  /* Since we cannot predict the size of the final button, we must
     adjust the final matrix column to match the button size. */
  mat.SetColumnWidth (mat.matrix_cols - 1, button.dim.w +
		      2 * button.border_width, 1);
}

/*
 * Compute the position of a menu button.  This is handy since we need
 * this position in order to determine where to put a pop_up menu.
 */
function MenuButtonPos (widget)
{
  local		pos;
  
  pos = PtGetAbsPosition (widget);
  pos.y += widget.dim.h + widget.border_width * 2;
  pos;
}

/*
 * Construct the template for the "Effects" menu, accessible through the
 * Effects button in the upper left corner.  This template does create an
 * actual PtMenu until you call its Instantiate(...) member function.
 */
function CreateEffectMenu (mat)
{
  local		emenu, textcolormenu, fillcolormenu;
  
  emenu = new (PopupMenu);
  emenu.AddItem ("Border On", nil, `cbBorder(@mat,t), nil, nil);
  emenu.AddItem ("Border Off", nil, `cbBorder(@mat,nil), nil, nil);
  emenu.AddItem ("-", nil, nil, nil, nil);
  emenu.AddItem ("Justify Left", nil, `cbJustify(@mat,Cw_CELL_LEFT),
		 nil, nil);
  emenu.AddItem ("Justify Right", nil, `cbJustify(@mat,Cw_CELL_RIGHT),
		 nil, nil);
  emenu.AddItem ("Justify Center", nil, `cbJustify(@mat,Cw_CELL_CENTER),
		 nil, nil);
  emenu.AddItem ("-", nil, nil, nil, nil);
  emenu.AddItem ("Invert", nil, `cbInvert(@mat, 1), nil, nil);
  emenu.AddItem ("Un-Invert", nil, `cbInvert(@mat, 0), nil, nil);
  emenu.AddItem ("-", nil, nil, nil, nil);
  
  fillcolormenu = new (PopupMenu);
  fillcolormenu.AddItem ("red", nil,
			 `cbFillColor(@mat,0xff0000), nil, nil);
  fillcolormenu.AddItem ("green", nil,
			 `cbFillColor(@mat,0x00ff00), nil, nil);
  fillcolormenu.AddItem ("blue", nil,
			 `cbFillColor(@mat,0x0000ff), nil, nil);
  fillcolormenu.AddItem ("white", nil,
			 `cbFillColor(@mat,0xffffff), nil, nil);
  fillcolormenu.AddItem ("black", nil,
			 `cbFillColor(@mat,0x000000), nil, nil);
  
  textcolormenu = new (PopupMenu);
  textcolormenu.AddItem ("red", nil,
			 `cbTextColor(@mat,0xff0000), nil, nil);
  textcolormenu.AddItem ("green", nil,
			 `cbTextColor(@mat,0x00ff00), nil, nil);
  textcolormenu.AddItem ("blue", nil,
			 `cbTextColor(@mat,0x0000ff), nil, nil);
  textcolormenu.AddItem ("white", nil,
			 `cbTextColor(@mat,0xffffff), nil, nil);
  textcolormenu.AddItem ("black", nil,
			 `cbTextColor(@mat,0x000000), nil, nil);
  
  emenu.AddItem ("Text Color", nil, textcolormenu, nil, nil);
  emenu.AddItem ("Fill Color", nil, fillcolormenu, nil, nil);
  
  emenu;
}

/*
 * Various functions to be called when the menu buttons are selected.
 */

/* Turn on/off borders around the currently selected region */
function cbBorder (mat, on)
{
  local		flags = on ? Cw_CELL_ALL_BORDERS_THICK : 0;
  
  mat.OutlineRange (mat.matrix_range, flags, Cw_CELL_ALL_BORDERS_THICK);
  mat.FlushDamage();
}

/* Justify left/right/center in the currently selected region */
function cbJustify (mat, flag)
{
  mat.JustifyRange (mat.matrix_range, flag);
  mat.FlushDamage();
}

/* Invert text/background colors in the currently selected region */
function cbInvert (mat, yesno)
{
  mat.InvertRange (mat.matrix_range, yesno);
  mat.FlushDamage();
}

/* Set the background color in the currently selected region */
function cbFillColor (mat, color)
{
  local		row, col, range, cell;
  
  range=mat.matrix_range;
  for (row=range.ul.y; row<=range.lr.y; row++)
    {
      for (col=range.ul.x; col<=range.lr.x; col++)
	{
	  if (cell = mat.Cell (row, col))
	    cell.fill_color = color;
	}
    }
}

/* Set the text color in the currently selected region */
function cbTextColor (mat, color)
{
  local		row, col, range, cell;
  
  range=mat.matrix_range;
  for (row=range.ul.y; row<=range.lr.y; row++)
    {
      for (col=range.ul.x; col<=range.lr.x; col++)
	{
	  if (cell = mat.Cell (row, col))
	    cell.text_color = color;
	}
    }
}

/*
 * This is a callback function that is called when the user begins to
 * edit the contents of a cell.  This gives us an opportunity to insert
 * a different text string from the one shown for editing purposes.  In
 * this case, if the cell has a formula, edit the formula instead of the
 * visible text.
 */
function cbBeginEdit ()
{
  if (cbinfo.matrix.cell.formula)
    cbinfo.matrix.text_string = string ("=",cbinfo.matrix.cell.formula);
}

/*
 * This is a callback function that is called when the text in a cell
 * has changed.  We check to see whether this is a formula, and if so
 * attempt to parse it and evaluate it.
 * In any case, we must re-compute the contents of the sheet in case
 * this edit has affected something elsewhere.
 */
function cbTextChange ()
{
  local		answer, str, _parser_throws_error_ = t;
  
  str = cbinfo.matrix.text_string;
  if (str[0] == '=')
    {
      str = substr(str,1,-1);
      SetFormula (cbinfo.matrix.cell, str);
      EvaluateCell (widget, cbinfo.matrix.cell.row, cbinfo.matrix.cell.col);
    }
  else
    SetFormula (cbinfo.matrix.cell, nil);
  Recompute (widget);
}

/*
 * This is a bsearch comparison function that orders the cells in row/column
 * order.  If we wanted to evaluate in column/row order, we would modify
 * this function to suit.
 */
function CmpCellPos (!c1, !c2)
{
  if (c1.row < c2.row)
    -1;
  else if (c1.row == c2.row)
    c1.col - c2.col;
  else
    1;
}

/*
 * Add a cell containing a formula to the global formula array.  We do this
 * so that when we recompute the sheet, we only have to visit those cells
 * that are known to have a formula.  Since a sheet is usually sparse, this
 * can save a lot of time.
 */
function AddFormulaCell (cell)
{
  local		found;
  
  found = bsearch (FormulaCells, cell, CmpCellPos);
  if (undefined_p (car(found)))
    insert (FormulaCells, cdr(found), cell);
}

/*
 * Remove a cell from the global formulas array.
 */
function RemoveFormulaCell (cell)
{
  local		found;
  
  found = bsearch (FormulaCells, cell, CmpCellPos);
  if (!undefined_p(car(found)))
    delete (FormulaCells, cdr(found));
}

/*
 * Set the formula for a cell.  By offering this single entry point for
 * modifying the formula, we can ensure that we know which cells to
 * look at when we recompute the sheet.
 */
function SetFormula (cell, str)
{
  if (!str || str == "")
    RemoveFormulaCell (cell);
  else
    AddFormulaCell (cell);
  cell.formula = str;
}

/*
 * Evaluate the formula of a cell, and set the cell's visible text string
 * to the result.  We attempt this once using the exact formula text.  If
 * it fails, we append a semicolon to the formula and try again.  If there
 * is an error, we do something cheesy to get rid of some of the error
 * message, since it could be much too long for the cell on-screen.
 */
function EvaluateCell (mat, row, col)
{
  local		answer, str, _parser_throws_error_ = t;
  
  if (cell = mat.ExistingCell (row, col))
    {
      str = cell.formula;
      if (str && str != "")
	{
	  try
	    {
	      answer = eval_string (str,t);
	    }
	  catch
	    {
	      try
		{
		  answer = eval_string (string (str, ";"),t);
		}
	      catch
		{
		  answer = string_split(_last_error_, ":", 3);
		  if (length(answer) == 4)
		    answer = car(reverse(answer));
		  else
		    answer = _last_error_;
		}
	    }
	  cell.text_string = string (answer);
	}
    }
}

/*
 * This is an exhaustive recomputation function that looks to see if a
 * cell has any data, and then attempts to evaluate it.  It is not very
 * efficient, so we don't use it.  It is just here for demonstration.
 */
function Recompute (mat)
{
  local			row, col, cell;
  
  for (row = 0; row < mat.matrix_rows; row ++)
    {
      for (col = 0; col < mat.matrix_cols; col ++)
	{
	  if (cell = mat.ExistingCell (row, col))
	    {
	      EvaluateCell (mat, row, col);
	    }
	}
    }
}

/*
 * This is the real recomputation function.  We keep track of which cells
 * contain a formula so we only have to deal with those.  It cannot help
 * but be faster.  The FormulaCells are sorted in row/col order, so we
 * do not need to do anything fancy to get the evaluation order correct.
 */
function Recompute (mat)
{
  with cell in FormulaCells do
    {
      EvaluateCell (mat, cell.row, cell.col);
    }
}

/*
 * A little Gamma cheat.  : (colon) is a function taking two arguments.  We
 * override it here, and just have it generate a rectangle from its args.
 * Now we can express ranges as A2:B5 without breaking the Gamma syntax.
 */
function \: (!x,!y)
{
  local		rect = new (PhRect);
  SpreadsheetToPoint (string(x), rect.ul);
  SpreadsheetToPoint (string(y), rect.lr);
  rect;
}

/*
 * Convert a number to an alpha number for the column designation.
 */
function ConvertToAlpha (num)
{
  num < 26 ? char('A' + num) :
    string ("A", char('A' + num % 26));
}

/*
 * Convert a range rectangle into a A2:B4 style designation.
 */
function cbRangeConvert ()
{
  local			str, rect = cbinfo.matrix.cur_range;
  
  str = string (ConvertToAlpha(rect.ul.x), rect.ul.y, ":",
		ConvertToAlpha(rect.lr.x), rect.lr.y);
  cbinfo.matrix.text_string = str;
}

/*
 * The mainline.  Here we set up interprocess communication and build the
 * main screen.
 */
function main ()
{
  local		w, mat, menubar, mbutton, emenu;
  
  init_ipc ("matrix","matrix");
  PtInit (nil);
  
  w = new (PtWindow);
  w.SetDim (400, 300);
  w.resize_flags = cons (Pt_RESIZE_X_INITIAL | Pt_RESIZE_Y_INITIAL, -1);
  menubar = new (PtMenuBar);
  menubar.SetDim (menubar.dim.w, MENUHEIGHT - 2 * menubar.border_width);
  mbutton = new (PtMenuButton);
  mbutton.text_string = "Effects";
  
  PtRealizeWidget (w);
  
  PtSetParentWidget (w);
  mat = new (CwMatrix);
  mat.matrix_rows = 10;
  mat.matrix_cols = 10;
  mat.SetArea (XOFFSET, YOFFSET + MENUHEIGHT, 400 - XOFFSET,
	       300 - (YOFFSET + MENUHEIGHT));
  mat.anchor_flags = cons (Pt_LEFT_ANCHORED_LEFT | Pt_RIGHT_ANCHORED_RIGHT |
			   Pt_TOP_ANCHORED_TOP | Pt_BOTTOM_ANCHORED_BOTTOM,
			   -1);
  PtAttachCallback (mat, Cw_CB_MATRIX_BEGIN_EDIT, `cbBeginEdit());
  PtAttachCallback (mat, Cw_CB_MATRIX_TEXT_CHANGE, `cbTextChange());
  PtAttachCallback (mat, Cw_CB_MATRIX_RANGE_CONVERT, `cbRangeConvert());
  
  PtRealizeWidget (mat);
  PtSetParentWidget (w);
  RowLabels(mat);
  PtSetParentWidget (w);
  ColumnLabels(mat);
  
  emenu = CreateEffectMenu(mat);
  PtAttachCallback (mbutton, Pt_CB_ARM,
		    `(@emenu).Instantiate (@mbutton,
					   MenuButtonPos(@mbutton)));
  
  PtMainLoop ();
}

/* ---------------------- Spreadsheet functions ---------------- */

/*
 * This section contains functions that are callable from a spreadsheet
 * cell.  We need to specify these specially, as they must expect to
 * take a range as input.  Unfortunately, this means that we cannot
 * simply use the functions built into Gamma.
 */

function SpreadsheetToPoint (str, point)
{
  local		i, row, col, colstr;
  
  for (i=0; str[i] >= 'A' && str[i] <= 'Z'; i++);
  colstr = substr(str,0,i);
  row = number (substr(str,i,-1));
  if (i==1)
    col = str[0] - 'A';
  else
    col = (str[0] - 'A') * 26 + str[1] - 'A';
  point.x = col;
  point.y = row;
  point;
}

/*
 * Functions usable in the spreadsheet.  There are global variables
 * cell and mat defined during these function.
 *
 * A range is a PhRect representing the upper left and lower right
 * corners of the range.
 *
 * In a spreadsheet cell, you would see:  =sum(A5:B6)
 */

function sum (range)
{
  local		rect = range, row, col, cell, result=0;
  
  for (row = rect.ul.y; row <= rect.lr.y; row++)
    {
      for (col = rect.ul.x; col <= rect.lr.x; col++)
	{
	  if (cell = mat.ExistingCell(row,col))
	    {
	      result += number(cell.text_string);
	    }
	}
    }
  result;
}

/*
 * Exception function that updates a cell when a &datahub; point
 * change occurs, and then recomputes the spreadsheet.
 */
function exCellException(cell)
{
  cell.text_string = string (value);
  Recompute(mat);
}

/*
 * A spreadsheet function that causes a cell to be attached to a
 * &datahub; point.
 * In the cell you would see:  =db(tank_level)
 * Notice that the point name is unevaluated, so you do not need to
 * use the # or ` syntax to protect the point name.
 */
function db (!name)
{
  local		sym = symbol(name);
  if (!getprop (sym,#registered))
    {
      setprop (sym, #registered, t);
      register_exception (sym, `exCellException(@cell));
    }
  eval(sym);
}

/*
 * A spreadsheet function that writes a value to the &datahub;.
 * This function transmits a value from the top-left corner of the given
 * range to the named point every time it is evaluated.  This will not
 * transmit the value of the current cell, since this would be a bit of
 * a chicken-and-egg problem.  The result in the cell will be the point
 * name on success, or an error message on failure.
 */
function dbout (!name,range)
{
  local		sym = symbol(name);
  local		rect = range, outcell;
  
  if (outcell = mat.ExistingCell (rect.ul.y, rect.ul.x))
    {
      write_point (sym, number(outcell.text_string));
      name;
    }
  else
    "No value";
}