Info
Content

SPR.FMT

PSIONICS FILE - SPR.FMT
=======================
Format of Spreadsheet files
Last modified 1997-08-14
===========================

This document describes the layout of SPR files for the SH3 spreadsheet.

A data file (also called a database file) begins with a 22 byte header of the
following form:
  Offset  0 (cstr): "SPREADSHEET"
  Offset 16 (word): format version number
  Offset 18 (word): offset value (meaning unknown)
  Offset 20 (word): OPL runtime version number

The version numbers and offset value are all zero.

The rest of the file consists of records. All records have the form:
  Offset  0 (word): type of record
  Offset  2 (word): size of data portion in bytes (L)
  Offset  4 to L+3: data portion

Unless stated otherwise, records may appear in any order. Record types 11 and
12 should not appear in Series 3 spreadsheets - they were used by the MC. Types
13 onwards are new in the Series 3.

Unless stated otherwise, a cell reference is two words - column then row - and
a range reference is four words - left, top, right, bottom, in that order.
Each word is intepreted as follows. If the word is N, then:
      0 <= N <= $1FFF: Nth row or column (so 0 is row 1 or column A)
           N =  $8000: this row or column
  $8000 <  N <  $9FFF: row N-$8000 below or column N-$8000 to right
  $E000 <  N <= $FFFF: row $10000-N above or column $10000-N to left
Values with the top bit set are only permitted in formulae. In some other
circumstances a reference containing all $FFFF values is used for "none".


Record type 1 holds formulae. A formula must come before any cell (record type
2) that accesses it, and the relative order of type 1 records should be
preserved. The record takes the form:
  Offset  0 (word): number of records using this formula
  Offset  2 (byte): length of formula (F)
  Offset  3 to F+2: formula

A formula is stored using a Reverse Polish notation. In other words, to
evaluate a formula, start with an empty stack, and then scan the formula in
order; when finding an operand, push it on the stack, while when finding an
operator, take its arguments off the stack, apply it, and push the answer on
the stack.

Operators are represented by single bytes:
  1 = <      5 = <>     9 = *     13 = -     17 = &
  2 = <=     6 = =     10 = /     14 = NOT
  3 = >      7 = +     11 = **    15 = AND
  4 = >=     8 = -     12 = +     16 = OR
(all take two operands except 12 to 14; 17 is string concatenate).

Delimiters are represented by single bytes:
  18 = (     These are not strictly needed, but allow the original
  19 = )     entered formula to be recreated for editing
  20 = ,
  @The above 3 need testing@
  21 = end of formula

Operands are represented by a byte followed by the value of the operand:
  22 = real (8 bytes)
  23 = word (2 bytes)
  24 = qstr
  25 = cell reference
  26 = range reference

Functions with no arguments or a fixed list of arguments are represented by
single bytes; the operands are treated exactly as for operators. In the
following list, the arguments are shown as "n" for numeric, "s" for string,
and "r" for range.

  27 ERR              55 LOG(n)           83 STRING(n,n)
  28 FALSE            56 LOWER(s)         84 CTERM(n,n)
  29 NA               57 MINUTE(n)        85 DATE(n,n)
  30 PI               58 MONTH(n)         86 DAVG(r,n,r)
  31 RAND             59 N(r)             87 DCOUNT(r,n,r)
  32 NOW              60 PROPER(s)        88 DMAX(r,n,r)
  33 TRUE             61 ROWS(r)          89 DMIN(r,n,r)
  34 ABS(n)           62 S(r)             90 DSTD(r,n,r)
  35 ACOS(n)          63 SECOND(n)        91 DSUM(r,n,r)
  36 ASIN(n)          64 SIN(n)           92 DVAR(r,n,r)
  37 AT(s)            65 SQRT(n)          93 FIND(s,s,n)
  38 ATAN(n)          66 TAN(n)           94 FV(n,n,n)
  39 CELLPOINTER(n)   67 TIMEVALUE(s)     95 HLOOKUP(n,r,n)
  40 CHAR(n)          68 TRIM(s)          96 IF(n,n,n)
  41 CODE(s)          69 UPPER(s)         97 INDEX(r,n,n)
  42 COLS(r)          70 VALUE(s)         98 MID(s,n,n)
  43 COS(n)           71 YEAR(n)          99 PMT(n,n,n)
  44 DATEVALUE(s)     72 ATAN2(n,n)      100 PV(n,n,n)
  45 DAY(n)           73 CELL(n,r)       101 RATE(n,n,n)
  46 EXP(n)           74 EXACT(s,s)      102 SIN(n)
  47 HOUR(n)          75 IRR(n,n)        103 TERM(n,n,n)
  48 INT(n)           76 LEFT(s,n)       104 TIME(n,n,n)
  49 ISERR(r)         77 MOD(n,n)        105 VLOOKUP(r,n,n)
  50 ISNA(r)          78 NPV(n,n)        106 DDB(n,n,n,n)
  51 ISNUM(r)         79 --------        107 REPLACE(s,n,n,s)
  52 ISSTR(r)         80 REPEAT(s,n)     108 SYD(n,n,n,n)
  53 LEN(s)           81 RIGHT(s,n)
  54 LN(n)            82 ROUND(n,n)

Functions with a variable list of arguments are more complex. The call is
built up as follows:
  - a START byte
  - the arguments
  - an END byte
  - the number of arguments (a byte)
The arguments are just placed in order. An argument is either:
  - the value of the argument, followed by an ARG byte
  - a RANGE byte followed by a range reference
Note that all arguments other than ranges, including cell references, work
by evaluating the argument in the normal way; ARG in effect says to pop the
next argument off the stack.

The values of the four special bytes differ for each function:

                START    END     ARG    RANGE
    AVG()        120     112     136     128
    CHOOSE()     121     113     137     129
    COUNT()      122     114     138     130
    MAX()        123     115     139     131
    MIN()        124     116     140     132
    STD()        125     117     141     133
    SUM()        126     118     142     134
    VAR()        127     119     143     135


Record type 2 describes a cell. It has the following format:
  Offset  0 to   3: cell reference
  Offset  4 (byte): flags:
    Bits 0 to 2: cell contents type:
      0 = blank
      1 = real constant
      2 = text constant
      3 = word constant
      5 = real formula
      6 = text formula
    Bits 3 to 4: text alignment: 0 = repeat, 1 = left, 2 = right, 3 = centre
    Bit 5:       set for left aligned numerics, clear for right aligned
    Bit 6:       set if the cell has changed since the last recalculation
    Bit 7:       should not be altered, used by natural order sort
  Offset  5 (byte): format
    Bits 0 to 3: number of digits in display format
      For special formats, this selects the special format:
         0 = bargraph,  1 = general, 5 = show formulae, 6 = hidden, 9 = date,
        11 = time,     15 = default
    Bits 4 to 6: display format: 0 = fixed,      1 = scientific,  2 = currency,
                                 3 = percentage, 4 = triad/comma, 7 = special
    Bit 7:       set if the cell is protected

This is then followed by a value block and then optionally a font byte.
The value block depends on the contents type:
  Type 0 (blank) has no value block
  Type 1 (real constant):
    Offset  6 (real): value
  Type 2 (text constant):
    Offset  6 (qstr): value
  Type 3 (word constant):
    Offset  6 (word): value
  Type 5 (real formula):
    Offset  6 (word): index of formula
    Offset  8 (real): current value
  Type 5 (text formula):
    Offset  6 (word): index of formula
    Offset  8 (qstr): current value
The index of a formula is its position in the set of type 1 records. So 0
means the first type 1 record, 1 the second type 1 record, and so on.

The font byte is simply a number from 0 to 3, giving the font of the cell.


Record type 3 describes column widths, and appears for each column not of the
default width. The record takes the form:
  Offset  0 (byte): column number
  Offset  1 (byte): width


Record type 4 describes the default column width:
  Offset  0 (word): default width


Record type 5 holds general status information:
  Offset  0 (byte): flags
    Bit 0:       set if automatic recalculate is on
    Bit 1:       set if protection override is on
    Bit 2:       set if a cell has been deleted since the last recalculation
    Bit 3:       set if table recalculation is on
    Bits 4 to 7: unused, always zero
  Offset  1 (byte): unused, always zero
  Offset  2 (byte): default numeric display format
  Offset  3 (byte): default alignment for new cells
The default numeric display format uses the same encoding as cells do, with
bit 7 always being zero. The default alignment uses the same encoding as the
flags of a cell, with bits 0 to 2, 6, and 7 always being zero.


Record type 6 holds information about the current state of the display:
  Offset  0 to   7: range reference of titles
  Offset  8 to  11: cell reference of top left displayed cell excluding titles
  Offset 12 to  19: range reference of selected range
  Offset 20 to  23: cell reference of cursor
  Offset 24 (byte): non-zero if grid lines are to be displayed, zero if not
  Offset 25 (byte): zero if zero values are to be displayed, non-zero if not


Record type 7 describes a named cell or range:
  Offset  0 (cstr): name
  Offset 16 to  23: range reference
  Offset 24 (word): type: 25 = cell, 26 = range


Record type 8 describes a range to be offered for selective printing:
  Offset  0 to   7: range reference
There may be any number of such records.


Record type 9 describes the criterion and database ranges for the database
commands. If this record occurs more than once, the last one will be used.
The record takes the form:
  Offset  0 to   7: criterion range reference
  Offset  8 to  15: database range reference


Record type 10 describes information for the table commands. If this record
occurs more than once, the last one will be used. The record takes the form:
  Offset  0 to   7: table range reference
  Offset  8 to  11: input cell 1 reference
  Offset 12 to  15: input cell 2 reference, or $FFFF if only one input cell


Record type 11 describes the print setup. It should not appear in Series 3
spreadsheets - it was used by the MC. The record takes the form:
  Offset  0 (byte): flags
    Bit 0:       set if values are shown, clear if formulae are shown
    Bit 1:       set if hidden cells are shown
    Bit 2:       set if column separators are shown
    Bit 3:       set if headers are shown
    Bits 4 to 7: unused, always zero
  Offset  1 (byte): unused, always zero


Record type 12 describes the printer font. It should not appear in Series 3
spreadsheets - it was used by the MC. The record takes the form:
  Offset  0 (byte): flags
    Bit 0:       set for bold
    Bits 1 to 2: unused, always zero
    Bit 3:       set for double height
    Bits 4 to 7: unused, always zero
  Offset  1 (byte): unused, always zero
  Offset  2 to  17: font name


Record type 13 describes a graph (see Psion-/):
  Offset   0 (cstr): name (see Psion-E)
  Offset  16 to  23: range reference for data range A (see Psion-R)
  Offset  24 to  31: range reference for data range B
  Offset  32 to  39: range reference for data range C
  Offset  40 to  47: range reference for data range D
  Offset  48 to  55: range reference for data range E
  Offset  56 to  63: range reference for data range F
  Offset  64 to  71: range reference for data range X
  Offset  72 to  79: range reference for labels for range A (see Psion-B)
  Offset  80 to  87: range reference for labels for range B
  Offset  88 to  95: range reference for labels for range C
  Offset  96 to 103: range reference for labels for range D
  Offset 104 to 111: range reference for labels for range E
  Offset 112 to 119: range reference for labels for range F
  Offset 120 (byte): format of range A
  Offset 121 (byte): format of range B
  Offset 122 (byte): format of range C
  Offset 123 (byte): format of range D
  Offset 124 (byte): format of range E
  Offset 125 (byte): format of range F
    All the formats (see Psion-L) are encoded in the same way:
    Bit 0:       set if lines are shown
    Bit 1:       set if symbols are shown
    Bits 2 to 7: unused, always zero
  Offset 126 (byte): alignment of label range A
  Offset 127 (byte): alignment of label range B
  Offset 128 (byte): alignment of label range C
  Offset 129 (byte): alignment of label range D
  Offset 130 (byte): alignment of label range E
  Offset 131 (byte): alignment of label range F
    Label range alignments (see Psion-B) are:
    0 = centre, 1 = right, 2 = below, 3 = left, 4 = above
  Offset 132 (byte): x-axis scaling (see Psion-A)
    Bit 0:       set for manual upper range, clear for automatic upper range
    Bit 1:       set for manual upper range, clear for automatic upper range
    Bits 2 to 7: unused, always zero
  Offset 133 (byte): x-axis format (see Psion-A), as for cell format (record
    type 2) but with bit 7 always zero
  Offset 134 (real): x-axis lower limit (see Psion-A)
  Offset 142 (real): x-axis upper limit (see Psion-A)
  Offset 150 (byte): y-axis scaling, as for x-axis
  Offset 151 (byte): y-axis format, as for x-axis
  Offset 152 (real): y-axis lower limit, as for x-axis
  Offset 160 (real): y-axis upper limit, as for x-axis
  Offset 168 (byte): graph type (see Psion-F)
    0 = scatter, 1 = bar, 2 = pie, 4 = line, 5 = stack-bar
  Offset 169 (byte): grid flags (see Psion-F)
    Bit 0:       set if horizontal grid lines visible
    Bit 1:       set if vertical   grid lines visible
    Bits 2 to 7: unused, always zero
  Offset 170 (byte): colour, encoding unknown
  Offset 171 (byte): ranges enabled (see Psion-R)
    Bits 0 to 5: set if ranges A to F respectively are enabled
    Bit 6:       set if range X is enabled
    Bit 7:       unused, always zero
  Offset 172 (byte): range labels enabled (see Psion-B)
    Bits 0 to 5: set if ranges A to F respectively are enabled
    Bits 6 to 7: unused, always zero
  Offset 173 (byte): other flags
    Bits 0 and 1: font size: 0 = auto, 1 = small, 2 = big (see Psion-F)
    Bits 2 and 3: pie labels: 0 = none, 1 = percent, 2 = values (see Psion-F)
    Bit 4:        set for 3D graphs (see Psion-F)
    Bit 5:        set if Y-axis title enabled (see Psion-I)
    Bit 6:        set if X-axis title enabled (see Psion-I)
    Bit 7:        set if titles enabled (see Psion-T)
  Offset 174 (word): unused

At offset 176 onwards are 10 cstrs. These are, in order:
  title first line
  title second line
  x-axis title
  y-axis title
  range A legend
  range B legend
  range C legend
  range D legend
  range E legend
  range F legend
The titles are limited to 40 characters, and the range legends to 20.


Record type 14 indicates which graph is current:
  Offset  0 (word): index of current graph
(the index is the count of type 13 records, so 0 means the first type 13
record in the file, and so on).


Record type 15 describes the fonts used. It consists of 4 font records, which
correspnd to fonts 1 to 4. Each font record has the format:
  Offset  0 to  5: unknown


Record type 16 holds information about printer set-up, and is identical to
record type 2 in Word files (see WORD.FMT).


Record type 17 describes the printer driver. It is identical to record type 2
in Word files:
  Offset  0 (byte): printer driver model number
  Offset  1 (cstr): printer driver library
A printer driver library can support several similar printers; the model number
specifies which is selected.


Record types 18 and 19 hold the header and footer text respectively as a cstr.


Record type 20 holds additional information about the screen. It will be
ignored if it does not directly preceed record type 6. It has the format:
  Offset  0 (byte): flags
    Bit 0:       set if grid labels are shown
    Bit 1:       set if small font is in use
    Bits 2 to 7: unused, always zero
  Offset  1 (byte): ignored (always zero)


Record type 22 indicates that the file is encrypted. It must be the first
record in the file. It has the format:
  Offset  0 to   8: encryption key check value
  Offset  9 to  15: copy of offset 9 to 15
  Offset 16 to  17: the result of encrypting two zero bytes


Encryption
----------

Spreadsheet files can be encrypted with a password on some systems. If so,
this fact is indicated by a type 22 record.

The password is used to generate two 9 byte sequences, called the key value
and the key check value; there is no obvious relationship between the two
sequences. The key check value is written into the type 22 record, while
the key value is used for the actual encryption. The key value is generated
with the system call GenMaskInit; there is no documentation of the algorithm
used to generate the check value, and it is not the same as used by Word.

[Note: different passwords may generate the same key value but different key
check values, or vice versa.]

Encryption is carried out using the system call GenMaskEncrypt: the data
section of each record (other than the type 22 record) is encrypted in the
order they occur in the file, with offset 16 of the encryption control block
being zero before encrypting the first record. The type and length of the
records are not encrypted.
No Comments
Back to top