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.