Quick guide v1.2.0


cell references on spreadsheet
  • are always preceeded by '=' and may be part of a function argument
  • they can be either lower or uppercase - f34 or F34
  • when provided as arguments to functions a range can be used - c1:d2 expands to c1,c2,d1,d2
  • behaviour is different in certain circumstances:
  • if cell column F, row 34 contains the value '=0.1' - the equals sign tells the parser an expression follows
  • =f34 returns 0.1 ie the numeric value
  • =F34 returns "0.1" the string representation of the contents

    expressions and constant definitions always start with '=' evaluation can be toggled on or off by ALT-'=' for selection or globally
  • examples :
  • =12.3, =tau=7.295 or =2.1*sin(0.12) or ="a string constant

    numeric functions start with a lower case letter - =sin(0.1)

    string functions start with an uppercase letter - Strcat("hello ",A1)

    relative copy and paste can be done with cell references
  • except for cell references followed immediately by any non-printing character not reserved
  • =f34 copied and pasted to the next column to the right gives =g34
  • =F34 gives =G34
  • =f34$ gives =f34 (absolute copy and paste)

    string constants and functions
  • Strcat(str1,str2,.....)
  • Toupper(str);
  • Tolower(str);
  • Left(str,n) -selects leftmost n chars
  • Right(str,n) - selects rightmost n chars
  • Substr(str,s,e) or Mid(str,s,e)- selects chars starting at index s (1 origin) to e
  • Spaces(n) - returns n spaces - can be inserted between two other strings
  • Sp1 - is a string constant equal to a single space

    string Parser
  • valid operators are '+' - for concatenation and '-' for substring subtraction
  • "one + two -> onetwo
  • "onetwo - one -> two
  • "one + Sp1 + two -> one two

    cells: accessing/storing data inside user functions
    several ways to do this
  • cells[r][c].cval("data") - stores 'data' in row r, col c (0 origin)
  • string temp = cells[r][c].cval(); -reads data in row r, col c (0 origin)

    also can use cell() (note spelling) variations and overloaded forms which
    make use of conventional cell addressing.


    for storing data and doing things with cells (1 origin)

  • cell(int r, int c, string val) NB 0 origin addressing only here
  • cell(int r, const char*col, string val) NB 1 origin with mixed argument types for r,c
  • cell(int r, const char*col, double val) eg cell(3,"d","example")
  • cell(const char* row,int c, string val) eg cell("3",4,"example")
  • cell(const char *cellref,string val) eg cell("c3","example")
  • cell(const char * firstcell,const char * lastcell, string val) eg cell("c3",e5","example")
  • cell(int r, int c, int val)
  • cell(const char *cellref,int val)
  • cell(int r, int c, double val)
  • cell(const char *cellref,double val)
  • cellcolour(int r, const char*col, Fl_Color clr)
  • cellcolour(const char* row, int c, Fl_Color clr)
  • cellcolour(const char *cellref,Fl_Color clr)
  • cellcolour(const char * firstcell,const char * lastcell, Fl_Color clr) //range of cells
  • cellfontsize(int r, const char*col, unsigned val)
  • cellfontsize(const char* row, int c, unsigned val)
  • cellfontsize(const char *cellref,unsigned val)
  • cellfontsize(const char * firstcell,const char * lastcell, unsigned val) //range of cells
  • cellfont(int r, const char*col, Fl_Font font)
  • cellfont(const char* row, int c, Fl_Font font)
  • cellfont(const char *cellref,Fl_Font font){
  • cellfont(const char * firstcell,const char * lastcell, Fl_Font font) //range of cells
  • cellalign(int r, const char*col, Fl_Align aln)
  • cellalign(const char* row, int c, Fl_Align aln)
  • cellalign(const char *cellref,Fl_Align aln)
  • cellalign(const char * firstcell,const char * lastcell, Fl_Align aln) //range of cells
  • cellprec(int r, const char*col, int val)
  • cellprec(const char* row, int c, int val)
  • cellprec(const char *cellref,int val)
  • cellprec(const char * firstcell,const char * lastcell, int val) //range of cells


    • lock, unlock, hide, unhide

  • celllock(int r, const char*col)
  • celllock(const char* row, int c)
  • celllock(const char *cellref)
  • celllock(const char * firstcell,const char * lastcell) //range of cells
  • cellunlock(int r, const char*col) {cells[r-1][label_to_int(col)].unlock();}
  • cellunlock(const char* row, int c) {cells[atoi(row)-1][c-1].unlock();}
  • cellunlock(const char *cellref)
  • cellunlock(const char * firstcell,const char * lastcell) //range of cells
  • cellhide(int r, const char*col)
  • cellhide(const char* row, int c)
  • cellhide(const char *cellref)
  • cellhide(const char * firstcell,const char * lastcell) //range of cells
  • cellunhide(int r, const char*col)
  • cellunhide(const char* row, int c)
  • cellunhide(const char *cellref)


    • fetching data from individual cells

  • string cell(string cellref) NB 1 origin
  • const char * ccell(int r, int c) const
  • const char* ccell(const char * cellref)
  • const char * ccell(string &cellref)
  • double fcell(int r,int c) const fetches a double floating point number
  • double fcell(int r,const char *col)
  • double fcell(string &cellref)
  • double fcell(const char * cellref)
  • int icell(int r,int c) const fetches an integer number
  • int icell(int r,const char *col)
  • int icell(string &cellref)
  • int icell(const char * cellref)

    data files
  • normally saved in subdirectory 'data' as gzipped files with suffix .sps.gz (or if not compressed .sps)
  • older files may have some cells locked, if so, use unlock all in the View menu

    writing user functions
    these work in a similar fashion to macros but are compiled


    for manual execution :

  • provide a menu item for the function including a case number
  • provide a case instance in sprsht-5-menu-functions
  • run function by selecting the menu item


    • for auto run and updating :

  • add menu item in sprsht-6-menuitems, with its own case number
  • add the function definition or function call in sprsht-5-menu-functions (under the relevant case number)
  • provide a function definition if not already defined in sprsht-4-userfns
  • add a function header near the beginning of sprsht-1-classes.
  • To facilitate automatic updating if input data is changed on the spreadsheet include a function definition in sprsht-3a-userfns
  • and a simple call to the function in the relevant area (see existing examples). Two calls bracketting any input data area
  • can be more responsive as cell updating depends on a redraw of the displayed cells (cells out of view are not updated).


    two user functions are provided by way of example - homeloan (auto run) and pipeflow (manual execution)
    allfns, which is read in as a data file, tests all built-in non-user functions directly on the spreadsheet

    Next