Spreadsheet

Table of contents


Introduction

The spreadsheet is a table of cells, which are arranged in rows and columns. Cells can contain numbers, texts or formulas. The concept is wel known from programs like M$-Excel, Lotus 1-2-3 .
VISUAL uses spreadsheets as an universal intermediate layer between data sources (PLC drivers, data bases, HMI screens) and data consumers (HMI screens, data loggers).

Numbering conventions

There can be more than one spreadsheet. Spreadsheet are numbered therefore. Numbering starts with 1 up to the highest number. Each sheet between 1 and the highest number used must exist. There may not be gaps.
Columns are numbered by uppercase letters starting with 'A'.Column 'Z' is followed by 'AA' ,'AZ' is followed by 'BA', 'ZZ' by 'AAA'. Rows are numbered by integers beginning with 1. The total numbers of coulmns and rows is arbitraryly fixed to 10000, giving up to 100 million cell positions per sheet. The highest (last) column is therefore 'NTP'.

Recalculation

Other than office applications, VISUAL recalculates all formulas after a fixed time. The time intervall can be configured per sheet. Currently the only way to configure it is manipulating the corresponding .sht file with a text editor

Cell types

Only non-emty cells have a reprensentation in memory. Cells can be created by entering data in a viewer/editor window or by loading a spreadsheet (.sht) file. Loading is done on program start. Once loaded, there is no unlaoding procedure.
According to the data stored, cells can be of text, number or formula type. Of number and formula cells there are variants that store a time stamp of the last change.
The type is determined by the input data, when manually inserting data. A string that can be converted to a number by C's atof() function creates a number cell. A string beginning with a plus sign or an equal sign will be interpreted as a formula.
Everything else is a text, with one exception:
Formulas and Numbers may be preceeded by an asterisk '*'. This will create the variants with timestamp.
Text cells mainly exist to store comments and structuring information in the spreadsheet. In recipe handling, they may be used for textual data from the recipe, which is then passed through to HMI screens. Number and formula cells can be input to formulas. Only number cells can take external input from device drivers. Device drivers or other external data sources will NEVER create cells. PLC drivers, HMI client and other modules rely on cells already existing. This, is for me a point in stablety. I do not want automatic memory allocation at runtime.

Hints

There is no syntax check for formulas. No "syntax error messages" will be displayed.
. Normally, you cannot get VISUAL easily to abort on malformed formulas. Just expect strange calculation results. You may check internal representation in these cases. There is allways the possibility that the parser is still buggy!

Labels

Every cell may be assigned a label. Labels may be used as a comment to describe the contents of a cell. The Label is also copied into the name field of the header of data logging files. From there, it is displayed in trend graphs. Another usage of labels is to store an output string for alarm messages. Use Alt-L to edit labels.

Viewers and Editors

There are currently two versions of spreadsheet viewer/editors:
Both will finally provide the same features.

To use the text based version, start visual with:
If you don't want the text based viewer/editor, start visual with:

Integrated Editor

Your configuration file (visual.cfg) should contain a line to load lang_xx.so according to the disired language.
In the spreadsheet menu you will find commands to edit or get information about a spreadsheet. Most of them can only be used within an open spreadsheet window. Open a spreadsheet window using the open command or the F3 hotkey.

Web based Editor

Your configuration file (visual.cfg) must contain a line to load server2.so for this to work.
Load sheet.html example page into your Browser. JAVA needs to be enabled. Depending on your configuration, it may be necessary to adjust host and port parameters for the applet
In the spreadsheet menu You will find commands to edit or get information about a spreadsheet. Most of them can only be used within an open spreadsheet window. Open a spreadsheet window using the open command or the F3 hotkey.
The commands are:
- Save:Stores the actual spreadsheet contents to a file.
- Edit:Edit cell contents
- Label:Edit a Label
- Search cells: Submenu containing search commands.
- Statistics: Opens a window that displays the number of used {cells:Zelle}.
- Options: Differnet display modes for sheet and cell contents.
- Next sheet: Display or create next sheet.
- Previous sheet: Display previous sheet in this window.
- Set all numbers 0
- Set all flags: Sets all flags so cells are regarded as modified. Will force i/o drivers to transmit the data.
- Area: Submenu that contains commands to define, copy, save and load areas.
- Mark formula sources: Marks all cells which values are used in a formula.
- Clear Cell
- Mark Cell
- Unmark all Undos the marking of cells
- List marked Opens a window that displays a list of the marked cells

Navigation

Both Editors provide the same navigation commands and keys. Use cursor keys to move the cell cursor (the green bar). Page Up and Page down step so many lines forward or backward as are displayed in the window. Ctrl-left and ctrl-right keys will do the same in horizontal direction. To go to another sheet, use plus and minus keys. If you try to access a non existing sheet, you will be offered to create a new one.

Every cell contains a flag word with 32 flags. The flag word is set when the cell's contents changes. Every flag bit is read by a certain part of the program. If a task has processed the new value, it clears its flag to mark it as done. The value of the flag word can be read using the {function:rbFunktion} FLAGS. The single bits have the following meanings:

This is set whenever the calculation thread detects a change in value.
bits 3 to 31value is new to the (thread style) client with this mask.
valChangedC 0x0004value is new to (callback style) client modules
flSelfRef 0x0002this cell contains a formula that refers to itself
flMarked 0x0001This cell is marked (by user interface).
valChangedAll 0xFFFFFFFC

Every cell contains a status word with 8 flags. The status word is set, when the cell contents changes. The staus word is used to test the integrity and consistency of data. The value of the status word can be read using the {function:rbFunktion} STATUS. The single bits have the following meanings:

Value not up to date because of:
stDirty0x8Numerical value is not up to date. This is true when a numerical cell that has source type external input is loaded from a file.
stCalcError 0x4numerical value results from erroneous calculation
stNetError 0x2 numerical value is invalid because of network error
stMissing 0x1 formula uses nonexisting cell
Options for the display of the spreadsheet contents:
Number of decimals to display.
Display Results or Formulas.
Enable {Flags:rbFlags} and {Status:rbStatus} display.
The column witdh.
Enter directly the Coordinates of an area

This command sets all numerical values to zero. This can be helpful to achieve a well defined state at startup.

This command sets all flag bits to 1. This can be helpful to force transfer of all data to external devices.

.topic Tasks Diese Programm benutzt Multitasking, um verschiedene Aufgaben scheinbar gleichzeitig zu erledigen. Diese Aufgaben sind vor allem die Kommunikation mit externen Ger„ten und die {Datenaufzeichnung}. Folgende Tasks werden erzeugt:

VISUAL uses multitasking/multithreading to execute different tasks quasiparallel. Tasks are:
Main TaskInit, load configuration, run user interface.
CalcThreadRecalculate formulas an call module callbacks.
PLC-DriversDo communication with external devices.
Server mainInit server and create port server tasks for each client.
Server accepterAccept incoming connections.
Server port serverDo communication with a single client.

Show next spreadsheet in this window. If there is no next sheet, you are offered to create it.

Show previous spreadsheet in this window. If there is no previous sheet, command has no effect.

The spreadsheet cursor is the green bar, which you see in the spreadsheet window. You can move it with the cursor keys(text&applet) or by clicking on another cell (applet only). In the lower part of the spreadsheet window you find additional information about the cell under the cursor. This cell is the one that will be affected by edit, label, delete commends etc..

Im Eingabefenster sehen Sie Informationen zu der {Zelle}, auf der der {Cursor:ZCURSOR} gerade steht Die {Koordinaten} der Zelle Den {symbolischen Namen:SYMBOLIK} der Zelle Den {Typ:Zelltyp} der Zelle bei Zahlen : die Darstellung mit allen Stellen bei Formeln : die {Formel:FORMEL} bei Texten : den vollst„ndigen Text wenn bei Zahlen, die nicht vom Benutzer eingegeben wurden, wird angezeigt, wo sie herkommen. Wenn Sie die Tasten Alt und E drcken, wird im Eingabefenster Zeile eingeblendet, in der sie den Inhalt der Zelle {„ndern:EDITCELL} k”nnen.

A cell can store numbers, formulas or text depending on it's type or it can be empty. For empty cells, there is no cell object (no memory used). A cell is indentified by it's coordinate. The cell type is set automatically when data is entered by hand. Only number cells can take external input from device drivers. Number and formula cells can be input to formulas. Text cells are mainly there to store comments and structuring information in the spreadsheet. Cells are only created by loading a .sht file on start or by manually editing them. PLC drivers, HMI client and other modules rely on cells already existing. This, is for me a point in stablety. I do not want automatic memory allocation at runtime.

Formulas define calculations. A formula must start with a plus sign (or the compination "*+" for timed formulas). Formulas consist of operators, numbers, functions and cell coordinates.
Examples:
+A1+A2 calculates the sum of A1 and A2
+3.2*DF7 calculates 3.2 * contents of DF7
+3.2*SINA7 calculates 3.2 * sinus A7 Warning!
+3.2*SIN(A7) calculates 3.2 * sinus A7 because
+COSA7 calculates 3.2 * cosinus A7 hinders the usage of column COS
+COS(A7) calculates 3.2 * cosinus A7 a next version of the parser will accept only function arguments in parantheses.
Cells A1 , A2, DF7 must be of the number or formula type.

If an error occurs in recalculation of the spreadsheet, the calculation will proceed without a program abort. The reason of the error is marked in the mathError field of formula cells.
The user interface shows the word "ERROR" (or the language dependent equivalent) instead of a value. If You move the cursor to this cell, the detail window will show the reason for that error in plain text.
Possible errors:
ceNone0No error
ceDiv0 1 Division by 0
ceLogNeg 2Logarithm from negative number
ceWrongType 3A cell used in the formula is not numerical or does not track time, if required
ceMissing 4 a cell used in the formula does'nt exist
ceRootNeg 5 root from negative number
ceSucc 6 a cell used in the formula is in a state of error
ceMissingSheet 7The sheet from which the formula imports a cell does'nt exist
.TOPIC OPERATOR In addition to the arithmetic operators + , - , * , and the brackets ( ) also {logical operators:LOGIC} AND , OR ,XOR and > , <, = , <> can be used. {Formulas:FORMEL} {cells:Zelle}

The operators AND, OR, and XOR combine the bits of the integer part of their operands. Examples: 7OR8 = 15,
7.3 OR 8.999 = 15
7 AND 3 = 3
5 XOR 3 = 6
When cells are tested to provide a condition, every non-zero value means TRUE, 0 FALSE. When dealing with conditions in the spreadsheet, use multilication for logical AND, additon for logical OR.
Example:
Switch in HMI provides 1 if ON, 0 if OFF.This goes to cell S3. You may want to have a variable setpoint from potentiometer in P1 for ON state and a fixed one in P2 for OFF state.
Use formula =P1*S3+P2*(S3<>0).

The comparative operators >, < = and <> (not equal) provide the result +1 if the comparision is right and 0 if it is not.

supported functions:
ABS : Absolute value
SIGN : -1 for numbers below zero, 0 for 0, 1 for number greater 0
INT : Integer ( not rounded ) use INT (..+0.5) to round
SQR : Square root
EXP : Exponential function
LN : natural logarithm
LOG : Logarithm base 10
SIN : Sine Calculated in radians
COS : Cosinus
TAN : Tangens
ASIN : Arcus Sine Calculated in radians
ACOS : Arcus Cosine
ATAN : Arcus Tangens
DIFF: Change since last calculation
{ERR:rfERR} : Error code of a cell
STUNDEN : Hour of current time
MINUTEN : Minutes
SEKUNDEN : Seconds
JAHR : Year of current date
MONAT : Month
TAG : Day
WOTAG : Day of week
{BILDNR:rfBildNr} : Number of process picture page shown
MAX : Greatest value from a list
MIN : Smallest value from a list
TIME : Time of last update of a cell (system timer)
DUR : Time since last update of a cell (system timer)
STATUS : Status field of a cell
FLAGS : Flags of a cell
.Topic rfERR ERR cell coordinates The function ERR returns the number of an error which occured in calculating this cell. Errorcodes are: {Formula:FORMEL} {Functions:rbFUNKTION} {Spreadsheet:Rechenblatt} {Help index:Hilfeindex} .Topic rfSCAL SCAL(Term1, Term2, Term3) The function SCAL calculates: SCAL = Term1 * (Term3 - Term2)/100 + Term2 This is useful to fit the percentage value from a {potentiometer:bePoti} to a range Term2 to Term3. Example: P1 contains a potentiometer position (0-100%). The Formula SCAL(P1, 300, 500) converts it into a value between 300 and 500. {Formula:FORMEL} {Functions:rbFUNKTION} {Spreadsheet:Rechenblatt} {Help index:Hilfeindex} .Topic rfDIFF The function DIFF returns the difference between the new value and the result of the last calculation. If the {cell:Zelle} A1 has contained A1 3 before the recalculation and now contains 4.2 ,DIFFA1 will return 1.2. {Formula:FORMEL} {Functions:rbFUNKTION} {Spreadsheet:Rechenblatt} {Help index:Hilfeindex} .topic rbCommand Short commands for the {spreadsheet:Rechenblatt}: Alt B Define {area:rbBEREICH} Alt C Clear {cell:Zelle} Alt E {Edit:REdit} cell contents Alt G Go to cell Alt K Copy area Alt M {mark:RMARK}cell Alt N {Edit:REdit} {labels:SYMBOLIK} {Spreadsheet:Rechenblatt}

Areas

Areas are a helper construct in the user interface. An area is an rectangular part of the spreadsheet. It is defined by the upper left and bottom right corners. Mark both cells at the corners to define one.
You can
Copy
Mark
Clear
Save
Load
an area. Long as a valid area is defined it is shown in the spreadsheet windows status line.

Spreadsheet status line

The top line in the spreadsheet window shows the following things:
RES / FOR indicates what is shown now for formulas, either FORmulas or RESults .
Save ! appears, if the spreadsheet has been changed since last saving.
Area: Z1...Z2 appears, if a valid area has been defined. Z1 is the left upper, Z2 the right bottom corner.

The (currently used) .sht file format:

General remarks:
All strings are enclosed in single quotes.
Currently, the complete definiton of a cell has to be on a sigle line.
This gets important when you edit .sht files manually or modify them using scripts or third party programs.
In case of changes to this file format, I hope to provide apropriate conversion utilities.
opening object tagobject properties
object typeCoordinatevalueprev.valueLabel Contents (text/formulas) or source Type (numbers) closing object tag Remarks
<SHEET> 1000000 Sheet object's only property is the delay between calculations.
<TCELL> 'A1' 0 0 '' 'THE BASIC CONCEPT:' </TCELL> The text cell stores value and prevValue that is never used.
<FCELL> 'H1' -0.738462 -0.738462 '' 'min(h5,h8,h5)' </FCELL>
<NCELL> 'S1' 4.521 4.521 '' 1 </NCELL> See Sources type tablefor the meaning of the numbers.
<TCELL> 'A2' 0 0 '' 'Suppose we get a value from' </TCELL>
<FCELL> 'G18' 5 5 'day of the week' 'dow' </FCELL>
<FCELL> 'A19' 0 0 'myfile,"This is a test for %2.7f %g %e at %2.0f:%2.0f:%2.0f\n",G4,G8,G4,A18,B18,C18' '(sekunden mod 3)' </FCELL> Here, the label is specially (mis-)used to provide format information to the log_text.so module.
<NCELL> 'B19' 12 12 '' 0 </NCELL>
<NCELL> 'C19' 8 8 '' 0 </NCELL>
<NCELL> 'A20' 0 0 '' 1 </NCELL>
<NCELL> 'C20' 8 8 '' 0 </NCELL>
<NCELL> 'C21' 8 8 '' 0 </NCELL>
<NCELL> 'C22' 8 8 '' 0 </NCELL>
</SHEET>

Internal formula representation

When creating a formula cell, the string representation is parsed and translated into a form that I shall call "Tokenized Polish Notation" or TPN for short. Tokenized means that each arithmetic operation and each function and some special operations are represented by tokens. Reverse polish notation is a form to denote arithmetic operation steps for a stack oriented machine. You may know this from Hewlett Packard calculators.
The addition 7+4 is written as:
    store 7
    store 4
    add
Where "store" means "store data in the top element of a FIFO stack" and "add" means "take top element from stack, add it to the new top element". Top element Of Stack (TOS) will then contain the result. The advantage of this representation is that no parenthesis are necessary to determine the order of execution. This allows straight forward calculation and results in fast execution.
Unary functions (with one argument, like sin() or sqrt()) work on the TOS.
The TPN representation can be displayed using the corresponding option from Spreadsheet/Options menu. The above addition would be shown as:
PUSH 4.0 PUSH 7.0 ADD
Note that there is also MUL, but no SUB or DIV. Substraction 7-4 is done as:
PUSH 4.0 -ToS PUSH 7.0 ADD, where -ToS is changing sign of TOS.
Division 7/4 is done as:
PUSH 4.0 1/ToS PUSH 7.0 MUL
There is a special datatype for TPN representation in defined in pString.h and pString.cc. pString is much like a string, but is not terminated by #0. Instead, it uses a length count. There are special functions that copy the binary represantation of doubles and Coordinate types to and from pString, which contains them as their byte sequences. Evaluation of TPN in recalculation of spreadsheets is done by function evalTPN().
The TPN representation is NOT saved when the spreadsheet is stored to an .sht file.
    Advantage: .sht-files are human readable and editable.
    Drawback : Loading is slowed down by parsing everything again.
Possible solution:
Introduce a binary file containing TPN representaion for each formula.
If binary is older than .sht, reparse and rebuild binary on load, else take TPN from binary.

Structure of a basic cell:

class cell:public base {
coord k;// position (row,column) in spreadsheet
double value, // actual value
prevalue; // value from previous calculation
unsigned int flags, // bits mark "changed" condition for some purposes
cflags, // bits mark subscription by client process
state; // error conditions
string Label;
}

Structure of a formula cell:

class fCell:public tCell {//the text field has the original input
pString TPN// the tokenized postfix notation
fCell *nextFormula// a pointer to build a linked list
}

Structure of a formula cell with time stamp:

class fTimeCell:public fCell {
time_t time//linux timestamp of last change
}

How is value change detected?

All numerical and formula cells have a field named prevVal. A change is detected, when after all calculations prevVal is not equal to value.