Lotus Development Corporation's 1-2-3(TM) and Symphony(TM) perform user selected operations upon a data matrix that is termed a "worksheet".
Worksheet files are such matrices stored on disk.
A worksheet file is an unbroken sequence of binary coded records defining a single worksheet.
Both 1-2-3 and Symphony accept externally created data files if the files are in the worksheet file format. Other programs can decode and process worksheet files created by 1-2-3 or Symphony.
The following document provides information required to create or access a worksheet file by describing the records used to create a worksheet file. It is assumed that the reader is familiar with Lotus products and has ready access to 1-2-3 or Symphony documentation.
Note that the worksheet files for 1-2-3 and Symphony are similar, but not necessarily interchangeable. 1-2-3 and Symphony share some record types, but also have record types unique to that product. Symphony can read 1-2-3 records, but 1-2-3 cannot read Symphony records.
The information contained in this document has been released into the public domain and is not considered to be confidential or proprietary although still the copyright and property of Lotus Development Corporation. All efforts have been made to ensure that this information is clear and useful since Lotus will not be providing customer assistance with this booklet. Lotus will, however, incorporate any necessary corrections if they are reported in writing to:
Worksheet files are organized as an unbroken sequence of variable length binary records. Each record consists of a 4-byte header followed by the record body. The header defines the record's type and length, as the example below shows.
The header's composition is as follows:
| Byte Number | Byte Description |
|---|---|
| 0,1 | Record type code |
| 2,3 | Record body length (bytes) |
Example: Record Header
| Record | Record | |
|---|---|---|
| Type | Length | |
| Byte Number | 00 01 | 02 03 |
| Hex Code | 1C 00 | 20 00 |
| Decimal Equivalent | 28 | 32 |
The record body can be of many different types; most have predetermined length, but some vary in length.
The record type code is 28.
In a hex dump of the file, the record type appears as 1C 00h, noting that the 8086/88 stores the most significant byte of word in the higher memory address.
The record length is 32 bytes. In a hex dump of the file, the record length appears as 20 00h.
Record types with Column/Row Coordinates
Some record types contain column/row coordinates to identify a cell, or one of the two points that define a range. Numbering starts at zero in the upper left corner of the worksheet. For example:
Cell A1 = column 0, row 0
This section describes the different record types found in 1-2-3 and Symphony.
There are to Quick Reference tables ordered by Opcode and by Product, followed by a detailed reference section ordered by Opcode. In the reference section, there are examples for the more commonly used records.
It is assumed that the reader is familiar with 1-2-3 or Symphony and has access to Lotus' documentation.
| Type | Code (hex) | Length (bytes) | Description |
|---|---|---|---|
| BOF | 0 | 2 | Beginning of file |
| EOF | 1 | 0 | End of file |
| CALCMODE | 2 | 1 | Calculation mode |
| CALCORDER | 3 | 1 | Calculation order |
| SPLIT | 4 | 1 | Split window type |
| SYNC | 5 | 1 | Split window sync |
| RANGE | 6 | 8 | Active worksheet range |
| WINDOW1 | 7 | 31 | Window 1 record |
| COLW1 | 8 | 3 | Column width, window 1 |
| WINTWO | 9 | 31 | Window 2 record |
| COLW2 | A | 3 | Column width, window 2 |
| NAME | B | 24 | Named range |
| BLANK | C | 5 | Blank cell |
| INTEGER | D | 7 | Integer number cell |
| NUMBER | E | 13 | Floating point number |
| LABEL | F | variable | Label cell |
| FORMULA | 10 | variable | Formula cell |
| TABLE | 18 | 25 | Data table range |
| ORANGE | 19 | 25 | Query range |
| PRANGE | 1A | 8 | Print range |
| SRANGE | 1B | 8 | Sort range |
| FRANGE | 1C | 8 | Fill range |
| KRANGE1 | 1D | 9 | Primary sort key range |
| HRANGE | 20 | 16 | Distribution range |
| KRANGE2 | 23 | 9 | Secondary sort key range |
| PROTEC | 24 | 1 | Global protection |
| FOOTER | 25 | 242 | Print footer |
| HEADER | 26 | 242 | Print header |
| SETUP | 27 | 40 | Print setup |
| MARGINS | 28 | 10 | Print margins code |
| LABELFMT | 29 | 1 | Label alignment |
| TITLES | 2A | 16 | Print borders |
| GRAPH | 2D | 437 | Current graph settings |
| NGRAPH | 2E | 453 | Named graph settings |
| CALCCOUNT | 2F | 1 | Iteration count |
| UNFORMATTED | 30 | 1 | Formatted/unformatted print |
| CURSORW12 | 31 | 1 | Cursor location |
| WINDOW | 32 | 144 | Symphony window settings |
| STRING | 33 | variable | Value of string formula |
| PASSWORD | 37 | 4 | File lockout (CHKSUM) |
| LOCKED | 38 | 1 | Lock flag |
| QUERY | 3C | 127 | Symphony query settings |
| QUERYNAME | 3D | 16 | Query name |
| 3E | 679 | Symphony print record | |
| PRINTNAME | 3F | 16 | Print record name |
| GRAPH2 | 40 | 499 | Symphony graph record |
| GRAPHNAME | 41 | 16 | Graph record name |
| ZOOM | 42 | 9 | Orig coordinates expanded window |
| SYMSPLIT | 43 | 2 | Nos. of split windows |
| NSROWS | 44 | 2 | Nos. of screen rows |
| NSCOLS | 45 | 2 | Nos. of screen columns |
| RULER | 46 | 25 | Named ruler range |
| NNAME | 47 | 25 | Named sheet range |
| ACOMM | 48 | 65 | Autoload.comm code |
| AMACRO | 49 | 8 | Autoexecute macro address |
| PARSE | 4A | 16 | Query parse information |
Quick Reference by Product: 1-2-3 only
| Type | Code (hex) | Length (bytes) | Description |
|---|---|---|---|
| SPLIT | 4 | 1 | Split window type |
| SYNC | 5 | 1 | Split window sync |
| WINDOW1 | 7 | 31 | Window 1 record |
| WINTWO | 9 | 31 | Window 2 record |
| COLW2 | A | 3 | Column width, window 2 |
| NAME | B | 24 | Named range |
| QRANGE | 19 | 25 | Query range |
| PRANGE | 1A | 8 | Print range |
| SRANGE | 1B | 8 | Sort range |
| KRANGE1 | 1D | 9 | Primary sort key range |
| KRANGE2 | 23 | 9 | Secondary sort key range |
| FOOTER | 25 | 242 | Print footer |
| HEADER | 26 | 242 | Print header |
| SETUP | 27 | 40 | Print setup |
| MARGINS | 28 | 10 | Print margins code |
| TITLES | 2A | 16 | Print borders |
| GRAPH | 2D | 437 | Current graph settings |
| NGRAPH | 2E | 453 | Named graph settings |
Quick Reference by Product: 1-2-3 and Symphony
| Type | Code (hex) | Length (bytes) | Description |
|---|---|---|---|
| BOF | 0 | 2 | Beginning of file |
| EOF | 1 | 0 | End of file |
| CALCMODE | 2 | 1 | Calculation mode |
| CALCORDER | 3 | 1 | Calculation order |
| RANGE | 6 | 8 | Active worksheet range |
| COLW1 | 8 | 3 | Column width |
| BLANK | C | 5 | Blank cell |
| INTEGER | D | 7 | Integer number cell |
| NUMBER | E | 13 | Floating point number |
| LABEL | F | variable | Label cell |
| FORMULA | 10 | variable | Formula cell |
| TABLE | 18 | 25 | Data table range |
| FRANGE | 1C | 8 | Fill range |
| HRANGE | 20 | 16 | Distribution range |
| PROTEC | 24 | 1 | Global protection |
| LABELFMT | 29 | 1 | Label alignment |
| CALCCOUNT | 2F | 1 | Iteration count |
| UNFORMATTED | 30 | 1 | Formatted/unformatted print |
| CURSORW12 | 31 | 1 | Cursor location |
Quick Reference by Product: Symphony only
| Type | Code (hex) | Length (bytes) | Description |
|---|---|---|---|
| WINDOW | 32 | 144 | Symphony window settings |
| STRING | 33 | variable | Value of string formula |
| PASSWORD | 37 | 4 | File lockout (CHKSUM) |
| LOCKED | 38 | 1 | Lock flag |
| QUERY | 3C | 127 | Symphony query settings |
| QUERYNAME | 3D | 16 | Query name |
| 3E | 679 | Symphony print record | |
| PRINTNAME | 3F | 16 | Print record name |
| GRAPH2 | 40 | 499 | Symphony graph record |
| GRAPHNAME | 41 | 16 | Graph rocord name |
| ZOOM | 42 | 9 | Orig coordinates expanded window |
| SYMSPLIT | 43 | 2 | Nos. of split windows |
| NSROWS | 44 | 2 | Nos. of screen rows |
| NSCOLS | 45 | 2 | Nos. of screen columns |
| RULER | 46 | 25 | Named ruler range |
| NNAME | 47 | 25 | Named sheet range |
| ACOMM | 48 | 65 | Autoload. comm code |
| AMACRO | 49 | 8 | Autoexecute macro address |
| PARSE | 4A | 16 | Query parse information |
| Record Type | Code | Body length |
|---|---|---|
| BOF | 0 (00H) | 2 bytes |
Record Description: Beginning of file
Used by both 1-2-3 and Symphony.
| Byte Number | Byte Description |
|---|---|
| 0-1 | file format revision number |
| 1028 (0404h) = 1-2-3 file | |
| 1029 (0405h) = Symphony file |
Example
Record Header Record Body
Record Record BOF
Type Length
Byte Number 0 1 2 3 0 1
Hex Code 00 00 02 00 04 04
Dec.Equivalent 0 2 1028
| Record Type | Code | Body length |
|---|---|---|
| EOF | 1 (01H) | 0 bytes |
Record Description: End of file
Used by both 1-2-3 and Symphony
| Byte Number | Byte Description |
|---|---|
| -no record body- | |
Example
Record Header
Record Record
Type Length
Byte Number 0 1 2 3
Hex Code 01 00 00 00
Decimal Equivalent 1 0
Note: End of file is ony a header. EOF has a record length of 0; therefore, no record body follows.
| Record Type | Code | Body length |
|---|---|---|
| CALCMODE | 2 (02h) | 1 byte |
Record Description: Calculation method
Used by both 1-2-3 and Symphony.
| Byte Number | Byte Description |
|---|---|
| 0 | 0 = Manual mode |
| FF = automatic |
| Record Type | Code | Body length |
|---|---|---|
| CALCORDER | 3 (03H) | 1 BYTE |
Record Description: Calculation order
Used by both 1-2-3 and Symphony
| Byte Number | Byte Description |
|---|---|
| 0 | 0 = natural |
| 1 = by column | |
| FF = by row |
| Record Type | Code | Body length |
|---|---|---|
| SPLIT | 4(04h) | 1 byte |
Record Description: Split window type
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0 | 0 = not split |
| 1 = vertical split | |
| FF = horizontal split |
| Record Type | Code | Body length |
|---|---|---|
| SYNC | 5(05h) | 1 byte |
Record Description: Split window sync
This determines whether the two screens in 1-2-3's split-screen feature will move together with the cursor.
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0 | 0 = not synchronized |
| FF = synchronized |
| Record Type | Code | Body length |
|---|---|---|
| RANGE | 6(06h) | 8 bytes |
Record Description:
Range of cells written to worksheet file. If the worksheet file was created using a File Save command, then this range describes the active area with trailing blank columns and rows removed. If the worksheet file was created using a File Xtract command, then this range describes the extract range with trailing blank columns and rows removed. If there is no data in the range, the starting column is set to -1.
Used by both 1-2-3 and Symphony.
| Byte Number | Byte Description |
|---|---|
| 0-1 | starting column |
| 2-3 | starting row |
| 4-5 | ending column |
| 6-7 | ending row |
Example
Record Header rt_range Record Body
Record Record Starting Starting Ending Ending
Type Length Column Row Column Row
Byte Number 0 1 2 3 0 1 2 3 4 5 6 7
Hex Code 06 00 08 00 00 00 00 00 01 00 03 00
Dec.Equivalent 6 8 0 0 1 3
The record displays the worksheet range as A1...B4.
| Record Type | Code | Body length |
|---|---|---|
| WINDOW1 | 7(07h) | 31 bytes |
Record Description: Window 1 record
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-1 | cursor column position |
| 2-3 | cursor row position |
| 4 | format (see Appendix A, Cell Format Encoding) |
| 5 | unused (0) |
| 6-7 | column width |
| 8-9 | number of columns on screen |
| 10-11 | number of rows on screen |
| 12-13 | left column |
| 14-15 | top row |
| 16-17 | number of title columns |
| 18-19 | number of title rows |
| 20-21 | left title column |
| 22-23 | top title row |
| 24-25 | border width column |
| 26-27 | border width row |
| 28-29 | window width |
| 30 | unused (0) |
| Record Type | Code | Body length |
|---|---|---|
| COLW1 | 8(08h) | 3 bytes |
Record Type Description
Column width Used by both 1-2-3 and Symphony.
In 1-2-3, this record contains the width of a column Window 1. In symphony, it contains width information for the Window Record that it follows.
| Byte Number | Byte Description |
|---|---|
| 0-1 | column |
| 2 | width |
| Record Type | Code | Body length |
|---|---|---|
| WINTWO | 9(09h) | 31 bytes |
Record Description: Window 2 record
Used in 1-2-3 only.
| Byte Number | Byte Description | |||||||
|---|---|---|---|---|---|---|---|---|
| 0-1 | cursor column position | |||||||
| 2-3 | cursor row position | |||||||
| 4 | format (see Appendix A, Cell Format Encoding) | |||||||
| 5 | unused (0) | |||||||
| 6-7 | column width | |||||||
| 8-9 | number of columns on screen | |||||||
| 10-11 | number of rows on screen | |||||||
| 12-13 | left column | |||||||
| 14-15 | top row | |||||||
| 16-17 | number of title columns | |||||||
| 18-19 | number of title rows | |||||||
| 20-21 | left titile column | |||||||
| 22-23 | top title row | |||||||
| 24-25 | border width column | |||||||
| 26-27 | border width row | |||||||
| 28-29 | window width | |||||||
| 30 | unused (0) | |||||||
| Record Type | Code | Body length |
|---|---|---|
| COLW2 | 10(0Ah) | 3 bytes |
Record Description: Column width, Window 2
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-1 | column |
| 2 | width |
| Record Type | Code | Body length |
|---|---|---|
| NAME | 11 (OBh) | 24 bytes |
Record Description: Name of range
The worksheet contains one record for each range name. Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-15 | NULL terminated ASCII string |
| 16-17 | Starting column |
| 18-19 | Starting row |
| 20-21 | Ending column |
| 22-23 Ending row |
Example
Record Header
Record Record
Type Length
Byte Number 0 1 2 3
Hex Code 0B 00 18 00
Decimal Equivalent 11 24
Record Body
Range Name (Text)
Decimal Equivalent Expressed in ASCII Text
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
52 45 56 45 4E 55 45 53 00 00 00 00 00 00 00 00
R E V E N U E S / Unfilled Names Area
(cont.) Record Body
Starting Starting Ending Ending
Column Row Column Row
16 17 18 19 20 21 22 23
00 00 00 00 01 00 03 00
0 0 1 3
Range name is REVENUES (encompasses A1 to B4).
| Record Type | Code | Body length |
|---|---|---|
| BLANK | 12(0Ch) | 5 bytes |
Record Description: Blank cell
Blank cell records appear only for those cells that are protected, or do not have the default format.
Unprotected blank cells with the default format are omitted from the worksheet file.
Used by both 1-2-3 and Symphony.
| Byte Number | Byte Description |
|---|---|
| 0 | format (see Appendix A, Cell Format Encoding) |
| 1-2 | column |
| 3-4 | row |
Example
Record Header Record Body
Record Record
Type Length Format Column Row
Byte Number 0 1 2 3 0 1 2 3 4
Hex Code 0C 00 05 00 22 05 00 0A 00
Dec. Equivalent 12 5 34 5 10
This record displays cell in location F11 (column 5, row 10).
| Record Type | Code | Body length |
|---|---|---|
| INTEGER | 13(ODh) | 7 bytes |
Record Description: Integer number cell
An integer cell holds a single integer value in the range -32767....+32767 (decimal).
Used by both 1-2-3 and Symphony.
| Byte Number | Byte Description |
|---|---|
| 0 | format (see Appendix A, Cell Format Encoding) |
| 1-2 | column |
| 3-4 | row |
| 5-6 | integer value |
Example
Record Header Record Body
Record Record
Type Length Format Column Row Integer
Byte Number 0 1 2 3 0 1 2 3 4 5 6
Hex Code 0D 00 07 00 00 00 00 00 00 DD 04
Dec. Equivalent 13 7 0 0 0 1245
This example displays the integer 1245 located in cell A1 (column 0, row 0). When reading a 2-byte integer, the lower byte appears first. For example, DD04h is actually 04DDh (1245 decimal).
| Record Type | Code | Body length |
|---|---|---|
| NUMBER | 14 (0Eh) | 13 bytes |
Record Description: Floating point number
Used by both 1-2-3 and Symphony
| Byte Number | Byte Description |
|---|---|
| 0 | format |
| 1-2 | column |
| 3-4 | row |
| 5-12 | value(IEEE long real; 8087 double-precision floating-point format) |
Example The following describes a 64-bit long real format.
S Exponent Fraction
63 62 52 51 0
MSB LSB
S 1-bit Sign field
0 = +
1 = -
Exponent 11-bit Exponent field
Exponent is binary, excess 1023(base 10). Thus, the true exponent is: 2^(exponent -1023).
Fraction 52-bit Fraction field
An implied leading 1 bit is at the beginning of the fraction. The implied binary point is between the implied 1 bit and the Most Significant Bit (MSB) of the fraction field.
Special NA: S = 1 Exponent = 7FF Fraction = 0
Values ERR: S = 0 Exponent = 7FF Fraction = 0
STRING: S = 0 Exponent = 7FF Fraction = non-zero
(Symphony only)
| Record Type | Code | Body | length |
|---|---|---|---|
| LABEL | 15(0Fh) | variable |
Record Description: Label cell
Used by both 1-2-3 and Symphony.
| Byte Number | Byte Description |
|---|---|
| 0 | format (see Appendix A, Cell Format Encoding) |
| 1-2 | column |
| 3-4 | row |
| 5+ | NULL terminated ASCII string; 240 bytes maximum |
Example
Record Header Record Body
Record Record
Type Length Format Column Row Label
Byte Number 0 1 2 3 0 1 2 3 4 5 6 7 8 9 10
Hex Code 0F 00 0B 00 F5 00 00 00 00 27 50 41 55 4C 00
Dec. Equivalent 15 11 245 0 0 P A U L
This example is a label record located at A1 (column 0, row 0).
This record contains the word 'PAUL. Byte 5 is always one of the following format prefixes:\'"^.
This record varies in relation to the amount of text stored in the cell.
A user can enter up to 240 characters in one cell.
| Record Type | Code | Body length |
|---|---|---|
| FORMULA | 16(10H) | variable |
Record Description: Formula cell
Used by both 1-2-3 and Symphony.
Formulas are compiled in Reverse Polish Internal Notation. By creating formulas in 1-2-3 or Symphony, and dumping them as hex bytes, the formula compilation logic can be deciphered.
Table 1 describes the available Opcodes and functions. The functions are discussed in greater detail in the 1-2-3 and Symphony documentation.
Appendix B discusses Lotus' proprietary formula compiler in greater detail.
| Byte Number | Byte Description |
|---|---|
| 0 | format (see Appendix A, Cell Format Encoding |
| 1-2 | column |
| 3-4 | row |
| 5-12 | formula numeric value (IEEE long real; see NUMBER) |
| 13-14 | formula size (bytes) |
| 15+ | for code (see Table 1, Formula Opcodes); Reverse Polish Internal Notation; 2048 bytes maximum |
Table 1-a Formula Compiler Opcode Table, Format
| Dec | Hex | Operation | Description |
|---|---|---|---|
| 0 | 0 | constant | Code is followed by an 8 byte IEEE Long Real Floating Point Number |
| 1 | 1 | variable | Code followed by 4 byte coordinate Byte 0,1 = Column Byte 2,3 = Row |
| 2 | 2 | range | Code followed by 8 byte range Byte 0,1 = Start column Byte 2,3 = Start row Byte 4,5 = End column Byte 6,7 = End row |
| 3 | 3 | return | End of formula |
| 4 | 4 | parentheses | Indicates presence of parentheses in original formula. It is ignored during recalculation. |
| 5 | 5 | 2 byte integer | Followed by 2 byte signed constant integer. |
The above Opcodes will define the type and length of information that follows the Opcode. Opcode 3 defines the end of the formula.
For example:
Opcode 0 is followed by an 8 byte floating point number.
Opcode 1 is followed by a 4 byte coordinate.
Opcode 2 is followed by an 8 byte range specifier.
Opcode 5 is followed by a 2 byte signed integer.
Table 1-b Formula Compiler Opcode Table, Operations
| Dec | Hex | Operation | Description |
|---|---|---|---|
| 8 | 8 | unary - | Negation |
| 9 | 9 | + | Addition |
| 10 | A | - | Subtraction |
| 11 | B | * | Multiplication |
| 12 | C | / | Division |
| 13 | D | ^ | Exponentiation; ie. 3^2 is(3x3) |
| 14 | E | = | Equal to |
| 15 | F | < > | Not equal to |
| 16 | 10 | < = | Less than or equal to |
| 17 | 11 | > = | Greater than or equal to |
| 18 | 12 | < | Less than |
| 19 | 13 | > | Greater than |
| 20 | 14 | #AND# | Logical AND |
| 21 | 15 | #OR# | Logical OR |
| 22 | 16 | #NOT# | Logical NOT |
| 23 | 17 | unary + | (Ignored during recalculation |
| 31 | 1F | na | @Na not applicable |
| 32 | 20 | err | @Err error |
| 33 | 21 | abs | @abs (x) Absolute value of x |
| 34 | 22 | int | @int (x) Integer value of x |
| 35 | 23 | sqrt | @sqrt (x) Square root of x |
| 36 | 24 | log | @log (x) Log base 10 of x |
| 37 | 25 | ln | @ln (x) Log base e of x |
| 38 | 26 | pi | @pi |
| 39 | 27 | sin | @sin (x) Sine of x |
| 40 | 28 | cos | @cos (x) Cosine of x |
| 41 | 29 | tan | @tan (x) Tangent of x |
| 42 | 2A | atan2 | @atan2 (x) 4 quadrant arc tangent of x |
| 43 | 2B | atan | @atan (x) 2 quadrant arc tangent of x |
| 44 | 2C | asin | @asin (x) Arc sine of x |
| 45 | 2D | acos | @acos (x) Arc cosine of x |
| 46 | 2E | exp | @exp (x) Exponential anti-log of x |
| 47 | 2F | mod | @mod (x,y)X Mod Y |
| 48 | 30 | sel | @Choose (x,v0,v1...vN) Match a list item. |
| 49 | 31 | isna | @isna (x)x = NA then 1 (true) |
| 51 | 33 | false | @false Return 0 |
| 52 | 34 | true | @true Return 1 |
| 53 | 35 | rand | @rand Generate random number between 0 and 1 |
| 54 | 36 | date | @date (Y,M,D) Generate the days since 1/1/1900 (Y = 0-199, M = 1-12, D = 1-31) |
| 55 | 37 | today | @today Output serial date number from cpu's clock |
| 56 | 38 | pmt | @pmt (princ, int, term)Payment |
| 57 | 39 | pv | @pv (pmt, int, term) Present value |
| 58 | 3A | fv | @fv (pmt, int, term) Future Value |
| 59 | 3B | if | @if (argument, them else) Boolean if |
| 60 | 3C | day | @day (x) Print day of the month from a serial date number |
| 61 | 3D | month | @month (x) Print month of the year from a serial date number |
| 62 | 3E | round | @round (x,d) Round number x to d decimal places |
The above Opcodes are variable, constant and argument related.
For example:
@sqrt (9) is the square root of the constant 9
@sqrt (A1) is the square root of the variable A1
@sqrt ((A1*2)/3) is the square root of the argument (A1*2)/3
(Note that the argument ((A1*2)/3) will be processed before the @sqrt function.)
Table 1-c Formula Compiler Opcode Table, Multiple Arguments
| Dec | Hex | Operation | Description |
|---|---|---|---|
| 80 | 50 | sum | @sum (range and/or cell and/or constant) Use commas to separate arguments |
| 81 | 51 | avg | @avg (range and/or cell and/or constant) Use commas to separate arguments |
| 82 | 52 | cnt | @cnt (range and/or cell and/or constant) Use commas to separate arguments |
| 83 | 53 | min | @min (range and/or cell and/or constant) Use commas to separate arguments |
| 84 | 54 | max | @max (range and/or cell and/or constant Use commas to separate arguments |
| 85 | 55 | vlookup | @Vlookup (x, range, offset) X = Cell address or constant, range = Table, Offset = Row in Table |
| 86 | 56 | npv | @npv (int, range) Net present value; Int = interest, Range = cash flows |
| 87 | 57 | var | @var (range) Variance of all items in list |
| 88 | 58 | std | @std (range) Standard deviation of all items in list |
| 89 | 59 | irr | @irr (guess,range) Guess = % estimate; Range = range of cash flows |
| 90 | 5A | hlookup | @hlookup, (x, range, offset) X = Cell address or constant, range = Table, Offseet = row in Table |
| 91 | 5B | dsum | Database statistical functions |
| 92 | 5C | avg | Database statistical functions |
| 93 | 5D | dcnt | Database statistical functions |
| 94 | 5E | dmin | Database statistical functions |
| 95 | 5F | dmax | Database statistical functions |
| 96 | 60 | dvar | Database statistical functions |
| 97 | 61 | dstd | Database statistical functions |
The above Opcodes deal specifically with ranges and multiple arguments. For example: @sum (A1...A10, B25, 9) contains a range, a variable and a constant as the arguments.
All function Opcodes which accept a variable number of arguments are followed by a 1-byte argument count.
Table 1-d Operator Precedence Table
| Operator | Unary Precedence | Binary Precedence |
|---|---|---|
| + | 6 | 4 |
| - | 6 | 4 |
| * | na | 5 |
| / | na | 5 |
| ^ | na | 7 |
| = | na | 3 |
| < > | na | 3 |
| < = | na | 3 |
| > = | na | 3 |
| < | na | 3 |
| > | na | 3 |
| #and# | na | 1 |
| #or# | na | 1 |
| #not# | 2 | na |
A Note on the Decompiler
The algorithm for the formula decompiler was taken verbatim from:
Writing Interactive Compilers and Interpreters, P.J. Brown, John Wiley and Sons, 1979. See chapter 6.2. The algorithm itself is described on pages 216 and 217.
This algorithm is also described in the following article:
More on the Re-creation of Source Code from Reserve Polish, P.J. Brown, Software Practice and Experience, Vol 7, 545-551 (1977).
| Record Type | Code | Body length |
|---|---|---|
| TABLE | 24 (18h) | 25 bytes |
Record Description: Table range
Used by both 1-2-3 and Symphony. In 1-2-3, the record refers to Data Tables 1 and 2. In Symphony, it refers to What-if Tables 1 and 2.
| Byte Number | Byte Description | |
|---|---|---|
| 0 | 0 = no table 1 = Table 1 2 = Table 2 | |
| 1-2 | Table Range | starting column |
| 3-4 | starting row | |
| 5-6 | ending column | |
| 7-8 | ending row | |
| 9-10 | Input Cell 1 | starting column |
| 11-12 | starting row | |
| 13-14 | ending column | |
| 15-16 | ending row | |
| 17-18 | Input Cell 2 | starting column |
| 19-20 | starting row | |
| 21-22 | ending column | |
| 23-24 | ending row | |
| Record Type | Code | Body length |
|---|---|---|
| QRANGE | 25 (19h) | 25 bytes |
Record Description: Query range
Used in 1-2-3 only.
| Byte Number | Byte Description | |
|---|---|---|
| 0-1 | Input ranges | starting column |
| 2-3 | starting row | |
| 4-5 | ending column | |
| 6-7 | ending row | |
| 8-9 | Output ranges | starting column |
| 10-11 | starting row | |
| 12-13 | ending column | |
| 14-15 | ending row | |
| 16-17 | Criteria | starting column |
| 18-19 | starting row | |
| 20-21 | ending column | |
| 22-23 | ending row | |
| 24 | Command | 0 = no command |
| 1 = find | ||
| 2 = extract | ||
| 3 = delete | ||
| 4 = unique | ||
| Record Type | Code | Body length |
|---|---|---|
| PRANGE | 26 (1Ah) | 8 bytes |
Record Description: Print range
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-1 | starting column |
| 2-3 | starting row |
| 4-5 | ending column |
| 6-7 | ending row |
| Record Type | Code | Body length |
|---|---|---|
| SRANGE | 27 (1Bh) | 8 bytes |
Record Description: Sort range
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-1 | starting column |
| 2-3 | starting row |
| 4-5 | ending column |
| 6-7 | ending row |
| Record Type | Code | Body length |
|---|---|---|
| FRANGE | 28 (1Ch) | 8 bytes |
Record Description: Fill range
Used by both 1-2-3 and Symphony.
| Byte Number | Byte Description |
|---|---|
| 0-1 | starting column |
| 2-3 | starting row |
| 4-5 | ending column |
| 6-7 | ending row |
| Record Type | Code | Body length |
|---|---|---|
| KRANGE | 29 (1Dh) | 9 bytes |
Record Description: Primary sort key range
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-1 | starting column |
| 2-3 | starting row |
| 4-5 | ending column |
| 6-7 | ending row |
| 8 | Order: 0 = descending order FF = ascending order |
| Record Type | Code | Body length |
|---|---|---|
| HRANGE | 32 (20h) | 16 bytes |
Record Description: Distribution range
Used by both 1-2-3 and Symphony.
| Byte Number | Byte Description | |
|---|---|---|
| 0-1 | Values range | starting column |
| 2-3 | starting row | |
| 4-5 | ending column | |
| 6-7 | ending row | |
| 8-9 | Bin range | starting column |
| 10-11 | starting row | |
| 12-13 | ending column | |
| 14-15 | ending row | |
| Record Type | Code | Body length |
|---|---|---|
| KRANGE2 | 35(23h) | 9 bytes |
Record Description: Secondary sort key range
Use in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-1 | starting column |
| 2-3 | starting row |
| 4-5 | ending column |
| 6-7 | ending row |
| 8 | Order; 0 = descending order FF = ascending order |
| Record Type | Code | Body length |
|---|---|---|
| PROTEC | 36(24h) | 1 byte |
Record Description: Global protection
Used by both 1-2-3 and Symphony.
| Byte Number | Byte Description |
|---|---|
| 0 | 0 = global protection OFF 1 = global protection ON |
| Record Type | Code | Body length |
|---|---|---|
| FOOTER | 37(25h) | 242 bytes |
Record Description: Print footer
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-242 | NULL termination ASCII string |
| Record Type | Code | Body length |
|---|---|---|
| HEADER | 38(26h) | 242 bytes |
Record Description: Print header
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-242 | NULL terminated ASCII string |
| Record Type | Code | Body length |
|---|---|---|
| SETUP | 39(27h) | 40 bytes |
Record Description: Print setup
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-40 | NULL terminated ASCII string |
| Record Type | Code | Body length |
|---|---|---|
| MARGINS | 40(28h) | 10 bytes |
Record Description: Print margins code
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0-1 | left margin |
| 2-3 | right margin |
| 4-5 | page length |
| 6-7 | top margin |
| 8-9 | bottom margin |
| Record Type | Code | Body length |
|---|---|---|
| LABELFMT | 41 (29h) | 1 byte |
Record Description: Label alignment
Used by both 1-2-3 and Symphony
| Byte Number | Byte Description |
|---|---|
| 0 | 27h = left |
| 22h = right | |
| 5Eh = center |
| Record Types | Code | Body length |
|---|---|---|
| TITLES | 42(2Ah) | 16 bytes |
Record Description: Print borders
Used in 1-2-3 only.
| Byte Number | Byte Description | |
|---|---|---|
| 0-1 | Row border | starting column |
| 2-3 | starting row | |
| 4-5 | ending column | |
| 6-7 | ending row | |
| 8-9 | Column border | starting column |
| 10-11 | starting row | |
| 12-13 | ending column | |
| 14-15 | ending row | |
| Record Type | Code | Body length |
|---|---|---|
| GRAPH | 45(2Dh) | 437 bytes |
Record Description: Current graph settings
Used in 1-2-3 only.
Table 2 Graph Record Structure
| Byte Number | Byte Description | |
|---|---|---|
| 0-1 | X Range | starting column |
| 2-3 | starting row | |
| 4-5 | ending column | |
| 6-7 | ending row | |
| 8-9 | A Range | starting column |
| 10-11 | starting row | |
| 12-13 | ending column | |
| 14-15 | ending row | |
| 16-17 | B Range | starting column |
| 18-19 | starting row | |
| 20-21 | ending column | |
| 22-23 | ending row | |
| 24-25 | C Range | stating column |
| 26-27 | starting row | |
| 28-29 | ending column | |
| 30-31 | ending row | |
| 32-33 | D Range | starting column |
| 34-35 | starting row | |
| 36-37 | ending column | |
| 38-39 | ending row | |
| 40-41 | E Range | starting column |
| 42-43 | starting row | |
| 44-45 | ending column | |
| 46-47 | ending row | |
| 48-49 | F Range | starting column |
| 50-51 | starting row | |
| 52-53 | ending column | |
| 54-55 | ending row | |
| 56-57 | A Labels | starting column |
| 58-59 | starting row | |
| 60-61 | ending column | |
| 62-63 | ending row | |
| 64-65 | B Labels | starting column |
| 66-67 | starting row | |
| 68-69 | ending column | |
| 70-71 | ending row | |
| 72-73 | C Labels | starting column |
| 74-75 | starting row | |
| 76-77 | ending column | |
| 78-79 | ending row | |
| 80-81 | D Labels | starting column |
| 82-83 | starting row | |
| 84-85 | ending column | |
| 86-87 | ending row | |
| 88-89 | E Labels | starting column |
| 90-91 | starting row | |
| 92-93 | ending column | |
| 94-95 | ending row | |
| 96-97 | F Labels | starting column |
| 98-97 | starting row | |
| 100-101 | ending column | |
| 102-103 | ending row | |
| 104 | Graph type | 0 = XY, 1 = bar, 2 = pie, 4 = line, 5 = stacked bar |
| 105 | Grid | 0 = none, 1 = horizontal, 2 = vertical, 3 = both |
| 106 | Color | 0 = black-white, FF = color |
| 107 | A Range line format | 0 = none, 1 = line, 2 = symbol, 3 = line-symbol |
| 108 | B Range line format | 0 = none, 1 = line, 2 = symbol, e = line-symbol |
| 109 | C Range line format | 0 = none, 1 = line, 2 = symbol, 3 = line-symbol |
| 110 | D Range line format | 0 = none, 1 = line, 2 = symbol, 3 = line-symbol |
| 111 | E Range line format | 0 = none, 1 = line, 2 = symbol, 3 = line-symbol |
| 112 | F Range line format | 0 = none, 1 = line, 2 = symbol, 3 = line-symbol |
| 113 | A Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 114 | B Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 115 | C Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 116 | D Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 117 | E Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 118 | F Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 119 | Scale | 0 = auto, FF = manual |
| 120-127 | X lower limit in floating point format | |
| 128-135 | X upper limit in floating point format | |
| 136 | Y scale | 0 = automatic, FF = manual |
| 137-144 | Y lower limit in floating point format | |
| 145-152 | Y upper limit in floating point format | |
| 153-192 | First title | |
| 193-232 | Second title | |
| 233-272 | X title | |
| 273-312 | Y title | |
| 313-332 | A legend | |
| 333-352 | B legend | |
| 353-372 | C legend | |
| 373-392 | D legend | |
| 393-412 | E legend | |
| 413-432 | F legend | |
| 433 | X format | |
| 434 | Y format | |
| 435-436 | Skip factor | |
| Record Type | Code | Body length |
|---|---|---|
| NGRAPH | 46 (2EH) | 453 bytes |
Record Description: Named current graph settings
Used in 1-2-3 only.
Table 3 NGraph Record Structure
| Byte Number | Byte Description | |
|---|---|---|
| 0-15 | Name | NULL terminated ASCII string |
| 16-17 | X Range | starting column |
| 18-19 | starting row | |
| 20-21 | ending column | |
| 22-23 | ending row | |
| 24-25 | A Range | starting column |
| 26-27 | starting row | |
| 28-29 | ending column | |
| 30-31 | ending row | |
| 32-33 | B Range | starting column |
| 34-35 | starting row | |
| 36-37 | ending column | |
| 38-39 | ending row | |
| 40-41 | C Range | starting column |
| 42-43 | starting row | |
| 44-45 | ending column | |
| 46-47 | ending row | |
| 48-49 | D Range | starting column |
| 50-51 | starting row | |
| 52-53 | ending column | |
| 54-55 | ending row | |
| 56-57 | E Range | starting column |
| 58-59 | starting row | |
| 60-61 | ending column | |
| 62-63 | ending row | |
| 64-65 | F Range | stating column |
| 66-67 | starting row | |
| 68-69 | ending column | |
| 70-71 | ending row | |
| 72-73 | A Labels | starting column |
| 74-75 | starting row | |
| 76-77 | ending column | |
| 78-79 | ending row | |
| 80-81 | B Labels | starting column |
| 82-83 | starting row | |
| 84-85 | ending column | |
| 86-87 | ending row | |
| 88-89 | C Labels | starting column |
| 90-91 | starting row | |
| 92-93 | ending column | |
| 94-95 | ending row | |
| 96-97 | D Labels | starting column |
| 98-99 | starting row | |
| 100-101 | ending column | |
| 102-103 | ending row | |
| 104-105 | E Labels | starting column |
| 106-107 | starting row | |
| 108-109 | ending column | |
| 110-111 | ending row | |
| 112-113 | F Labels | starting column |
| 114-115 | starting row | |
| 116-117 | ending column | |
| 118-119 | ending row | |
| 120 | Graph type | 0 = XY, 1 = bar, 2 = pie, 4 = line, 5 = stacked bar |
| 121 | Grid | 0 = none, 1 = horizontal, 2 = vertical, 3 = both |
| 122 | Color | 0 = black-white, FF = color |
| 123 | A Range line format | 0 = none, l = line, 2 = symbol, 3 = line-symbol |
| 124 | B Range line format | 0 = none, 1 = line, 2 = symbol, 3 = line-symbol |
| 125 | C Range line format | 0 = none, 1 = line, 2 = symbol, 3 = line-symbol |
| 126 | D Range line format | 0 = none, 1 = line, 2 = symbol, 3 = line-symbol |
| 127 | E Range line format | 0 = none, 1 = line, 2 = symbol, 3 = line-symbol |
| 128 | F Range line format | 0 = none, 1 = line, 2 = symbol, 3 = line-symbol |
| 129 | A Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 130 | B Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 131 | C Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 132 | D Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 133 | E Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 134 | F Range data label alignment | 0 = center, 1 = right, 2 = below, 3 = left, 4 = above |
| 135 | X scale | 0 = auto FF = manual |
| 136-143 | X lower limit in floating point format | |
| 144-151 | X upper limit in floating point format | |
| 152 | Y scale | 0 = automatic FF = manual |
| 153-160 | Y lower limit in floating point format | |
| 161-168 | Y upper limit in floating point format | |
| 209-224 | First title | |
| 225-248 | Second title | |
| 249-288 | X title | |
| 289-328 | Y title | |
| 329-348 | A legend | |
| 349-368 | B legend | |
| 369-388 | C legend | |
| 389-408 | D legend | |
| 409-428 | E legend | |
| 429-448 | F legend | |
| 449 | X format | |
| 450 | Y format | |
| 451-452 | Skip factor | |
| Record Type | Code | Body length |
|---|---|---|
| CALCCOUNT | 47(2Fh) | 1 byte |
Record Description: Iteration count
Used in 1-2-3 and Symphony.
| Byte Number | Byte Description |
|---|---|
| 0 | Iteration count |
| Record Type | Code | Body length |
|---|---|---|
| UNFORMATTED | 48(30h) | 1 byte |
Record Description: Formatted/unformatted print
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0 | 0 = formatted 1 = unformatted |
| Record Type | Code | Body length |
|---|---|---|
| CURSORW12 | 49(31h) | 1 |
Record Description: Cursor location
Used in 1-2-3 only.
| Byte Number | Byte Description |
|---|---|
| 0 | 1 = cursor in Window 1 2 = cursor in Window 2 |
| Record Type | Code | Body length |
|---|---|---|
| WINDOW | 50(32h) | 144 bytes |
Record Description: Window record structure
Used in Symphony only.
Table 4 Window Record Structure
| Byte Number | Byte Description | |
|---|---|---|
| 0-15 | Window name | NULL terminated ASCII string |
| 16-17 | Cursor position | column |
| 18-19 | row | |
| 20 | Format | (see Appendix A, Cell Format Encoding) |
| 21 | Unused | |
| 22-23 | Column width | |
| 24-25 | Total number of columns | |
| 26-27 | Total number of rows | |
| 28-29 | Non-Title Home Position | column |
| 30-31 | row | |
| 32-33 | Number of title columns | |
| 34-35 | Number of title rows | |
| 36-37 | Left title column | |
| 38-39 | Top title row | |
| 40-41 | Home position column | |
| 42-43 | Home position row | |
| 44-45 | Number of screen columns | |
| 46-47 | Number of screen rows | |
| 48 | Hidden Status | 0 = hidden, FF = not hidden |
| 49 | Previous window | 0 = SHEET, 1 = DOC, 2 = GRAPH, 3 = COMM, 4 = FORM, 5 = APPLICATION |
| 50 | Border display | 0 = cell, FF = no cell |
| 51 | Border display lines | 0 = lines, FF = no lines |
| 52-53 | Window Range | starting column |
| 54-55 | starting row | |
| 56-57 | ending column | |
| 58-59 | ending row | |
| 60-61 | Offset | |
| 62 | Insert mode flag | 0 = OFF, non-zero = ON |
| 63-78 | Graph name | |
| 79 | Window type | 0 = SHEET, 1 = DOC, 2 = GRAPH, 3 = COMM, 4 = FORM, 5 = APPLICATION |
| 80 | Automatic display mode | "a" = automatic, else = manual |
| 81 | Forms filter | 0 = filter, non-zero = no filter |
| 82-97 | Associated form name | |
| 98-99 | Forms current record | |
| 100 | Space display | 0 = no spaces, non-zero = spaces |
| 101 | Line spacing | 1 = 1 space, 2 = 2 spaces, 3 = 3 spaces |
| 102 | Justify type | "l" = left, "r" = right, "c" = cent, "e" = even |
| 103-104 | Right Margin | 0-FO characters = right margin FF = no user-defined right margin; use default value |
| 105-106 | Left Margin | 0-FO characters = left margin |
| 107-108 | Tab interval | |
| 109 | CR display | 0 = soft, non-zero = hard |
| 110 | Auto-justify on copy/move | 0 = no, non-zero = yes |
| 111-126 | Associated application name | |
| 127-143 | Reserved Application Area | |
Record Description: Value of string formula
Used in Symphony only.
Record Description: File lockout (CHKSUM)
This is proprietary information.
Used in Symphony only.
Record Description: Lock Flag
Used in Symphony only.
Record Description: Query settings
Used in Symphony only.
Table 5 Query Record Structure
Record Description: Current Query Name
Used in Symphony only.
Record Description: Print record
Used in Symphony only.
Table 6 Print Record Structure
Record Description: Current Print Record Name
Used in Symphony only.
Record Description: Graph record
Used in Symphony only.
Table 7 Symphony Graph Record Structure
Record Description: Current Graph Record Name
Used in Symphony only.
Record Description: Original coordinates expanded window
Used in Symphony only.
Record Description: Number of split windows
Used in Symphony only.
Record Description: Number of screen rows
Used in Symphony only.
Record Description: Number of screen columns
Used in Symphony only.
Record Description: Name ruler range
Used in Symphony only.
Record Description: Named sheet range
Used in Symphony only.
Record Description: Autoload communications file
Used in Symphony only.
Record Description: Autoexecute macro address
Used in Symphony only.
Record Description: Query parse information
Used in Symphony only.
The first byte of a content-related record contains a cell format code.
Format is determined at the bit level.
Table 8 Cell Format Encoding
This appendix describes the internal workings of the formula compiler. The
compiler transforms an ASCII string of characters representing a formula to
its Reverse Polish code. The basic algorithm utilizes and SR parser (SR =
shift and reduce). The aim of the parser is to apply a set of reduction
rules which embody the syntax of the compiler to an input string. Formula
code is compiled to a temporary buffer.
Lexicon Analysis
A lexical analyzer breaks up the input string into lexical units called
tokens. A token is a substring of the original input string operand,
operator, or special symbol (such as comma, parentheses, etc.) In addition,
the lexical analyser supplies two special tokens, "beginning of formula"
(boform) and "end of formula" (eoform), to facilitate the compilation
process. The lexical analyzer identifies and processes literals (both
number and string), cell and range references, operators, and function
calls. It assigns a unique code to each distinct operator, function, or
type of operand.
A function with no arguments is treated like a number.
Syntax Analysis
The syntactical analysis of a formula is accomplished by processing a list
of tokens in left-to-right order. A stack called the syntax is also used
during the syntactical scan. The basic algorithm is as follows:
Repeat the following steps:
1) Get the next token
2) If the token is a literal or cell reference:
3) If the token is a range reference:
4) Otherwise push the token code for the token on the syntax stack.
For each syntax rule, if the pattern on the top of the syntax matches the
rule pattern take the action associated with the rule and start scanning
from the beginning for any additional rules which may apply.
When a token code is pushed on the syntax stack, an additional word of
zeros is also pushed on the stack. This is used when compiling function
calls to hold the function's argument count.
Rule Matching
A relatively small number of rules are used to process formulas of arbitrary
complexity. If a rule matches the top of the syntax stack, then the
compiler takes a specific action and rule scanning starts again with the
first rule. Each rule matches certain patterns on the syntax stack. A
typical rule might be: if the top of the stack is the token for right
parenthesis, and the next-to-top is a number, and the second form the top
is a left parenthesis, then pop the top three items from the syntax stack
and push the number on the syntax stack.
This rule can be more succinctly represented as:
The following are the syntax rules used to process formulas. Note that the
order of the rules is important. The rules for compilation of operators
used additional tables which assign a precedence number and opcode to each
legal unary and binary operator. Thus, for example, there is a single
token code for minus sign (-), but there are two opcodes one for unary
minus and one for binary minus. In addition, these two operators, while
lexically identical, also have different precedence. In general, operators
of higher precedence will be performed before operators of lower precedence
are performed left-to-right. All special operators (boform, eoform,
parentheses, comma, etc.) are implicitly assigned a precedence of zero.
Rule 1 Termination test
Table 9 Operator Precedence Table
Example:
Using the above rules, we can now see how a particular formula is
compiled. Let us consider the following formula:
Rule 5 applies again, and the opcode for + is compiled, reducing the stack
to boform, number, eoform. Rescanning finds a match on rule 1 which
compiles a return opcode and terminates. The final compiled code is thus:
A Note on the Decompiler
The algorithm for the formula decompiler was taken verbatim from:
Writing Interactive Compilers and Interpreters, P.J. Brown, John Wiley and
Sons, 1979. See chapter 6.2. The algorithm itself is described on pages
216 and 217.
This algorithm is also described in the following article.
More on the Re-creation of Source Code from Reverse Polish, P.J. Brown,
Software Practice and Experience, Vol 7, 545-551 (1977).
WORKSHEET COLUMN DESIGNATORS
Most records within the 1-2-3 Condensed Worksheet format are specified
with column/row designators (for example, column 0, row 0 equals A1). When
determining the column designator, the table below will help make
conversion easier.
The worksheet shown below was created in 1-2-3 and saved to disk.
The example shown below is a partial hex dump of this worksheet file. By
reading each record header, you can determine the type of record you are
encountering. The record header will also tell you the length of that
follows the header. By analyzing the record header, you can read the
records you want and skip unrelated records.
The following supplements the discussion of the FORMULA record
type in the Worksheet File Format document.
Formula Record Type
You may redistribute this document freely provided you retain both the original Lotus copyright message and the © Tim Colgate above. No responsibility can be accepted for any errors or omissions in this document. Please send corrections and suggestions to me.
Record Type Code Body length STRING 51(33h) variable
Byte Number Byte Description 0 format (see Appendix A, Cell Format Encoding) 1-2 column 3-4 row 5+ NULL terminated ASCII string
Record Type Code Body length PASSWORD 55(37h) 4 byte
Byte Number Byte Description -- not available --
Record Type Code Body length LOCKED 56(38h) 1 byte
Byte Number Byte Description 0 0 = OFF
1 = ON
Record Type Code Body length QUERY 60(Ch) 127 bytes
Byte Number Byte Description 0-15 Name NULL terminated ASCII string 16-17 Input range starting column 18-19 starting row 20-21 ending column 22-23 ending row 24-25 Output Range starting column 26-27 starting row 28-29 ending column 30-31 ending row 32-33 Criteria Range starting column 34-35 starting row 36-37 ending column 38-39 ending row 40-41 Form Entry starting column 42-43 starting row 44-45 ending column 46-47 ending row 48-49 Form Def. Range starting column 50-51 starting row 52-53 ending column 54-55 ending row 56-57 Report Output starting column 58-59 starting row 60-61 ending column 62-63 ending row 64-65 Report Header starting column 66-67 starting row 68-69 ending column 70-71 ending row 72-73 Report Footer starting column 74-75 starting row 76-77 ending column 78-79 ending row 80-81 Table Range starting column 82-83 starting row 84-85 ending column 86-87 ending row 88-89 Input Cell starting column 90-91 starting row 92-93 ending column 94-95 ending row 96-97 1st Key range starting column 98-99 starting row 100-101 ending column 102-103 ending row 104-105 2nd Key range starting column 106-107 starting row 108-109 ending column 110-111 ending row 112-113 3rd Key range starting column 114-115 starting row 116-117 ending column 118-119 ending row 120 Last command 0 = no command, 1 = find, 2 = extract, 3 = delete, 4 = unique 121 1st Key order 0 = descending order, FF = ascending order 122 2nd Key order 0 = descending order, FF = ascending order 123 3rd Key order 0 = descending order, FF = ascending order 124 Report number of records 0 = multiple, FF = single 125 Number of records 0 = multiple, FF = single 126 Marks 0 = yes, FF = no
Record Type Code Body length QUERYNAME 61(3Dh) 16 bytes
Byte Number Byte Description 0-15 NULL terminated ASCII string
Record Type Code Body length PRINT 62(3Eh) 679 bytes
Byte Number Byte Description 0-15 Print setting name NULL terminated ASCII string 16-17 Source range starting column 18-19 starting row 20-21 ending column 22-23 ending row 24-25 Row border starting column 26-27 starting row 28-29 ending column 30-31 ending row 32-33 Column border starting column 34-35 starting row 36-37 ending column 38-39 ending row 40-41 Destination starting column 42-43 starting row 44-45 ending column 46-47 ending row 48 Print format 0 = as displayed, non-zero = formulas 49 Page breaks 0 = yes, non-zero = no 50 Line spacing 51-52 Left Margin 53-54 Right Margin 55-56 Page length 57-58 Top 59-60 Bottom of page 61-101 Setup string NULL terminated ASCII string 102-342 Header NULL terminated ASCII string 343-584 Footer NULL terminated ASCII string 585-600 Source database name NULL terminated ASCII string 601 Attribute 0 = no, non-zero = yes 602 Space compression 0 = no, non-zero = yes 603 Print destination 0 = printer, 1 = file, 2 = range 604-605 Starting page 606-607 Ending page 608-677 Destination filename NULL terminated ASCII string 678 Wait 0 = no, non-zero = yes
Record Type Code Body length PRINTNAME 63(3Fh) 16 bytes
Byte Number Byte Description 0-15 NULL terminated ASCII string
Record Type Code Body length GRAPH2 64(40h) 499 bytes
Byte Number Byte Description 0-15 Name NULL terminated ASCII string 16-17 X Range starting column 18-19 starting row 20-21 ending column 22-23 ending row 24-25 A Range starting column 26-27 starting row 28-29 ending column 30-31 ending row 32-33 B Range starting column 34-35 starting row 36-37 ending column 38-39 ending row 40-41 C Range starting column 42-43 starting row 44-45 ending column 46-47 ending row 48-49 D Range starting column 50-51 starting row 52-53 ending column 54-55 ending row 56-57 E Range starting column 58-59 starting row 60-61 ending column 62-63 ending row 64-65 F Range stating column 66-67 starting row 68-69 ending column 70-71 ending row 72-73 A Labels starting column 74-75 starting row 76-77 ending column 78-79 ending row 80-81 B Labels starting column 82-83 starting row 84-85 ending column 86-87 ending row 88-89 C Labels starting column 90-91 starting row 92-93 ending column 94-95 ending row 96-97 D Labels starting column 98-99 starting row 100-101 ending column 102-103 ending row 104-105 E Labels starting column 106-107 starting row 108-109 ending column 110-111 ending row 112-113 F Labels starting column 114-115 starting row 116-117 ending column 118-119 ending row 120 Graph type 0 = XY, 1 = bar, 2 = pie, 4 = line, 5 = stacked bar 121 Grid 0 = none, 1 = horizontal, 2 = vertical, 3 = both 122 Color 0 = black-white, FF = color 123 A Range line format 0 = none, l = line, 2 = symbol, 3 = line-symbol 124 B Range line format 0 = none, 1 = line, 2 = symbol, 3 = line-symbol 125 C Range line format 0 = none, 1 = line, 2 = symbol, 3 = line-symbol 126 D Range line format 0 = none, 1 = line, 2 = symbol, 3 = line-symbol 127 E Range line format 0 = none, 1 = line, 2 = symbol, 3 = line-symbol 128 F Range line format 0 = none, 1 = line, 2 = symbol, 3 = line-symbol 129 A Range data label alignment 0 = center, 1 = right, 2 = below, 3 = left, 4 = above 130 B Range data label alignment 0 = center, 1 = right, 2 = below, 3 = left, 4 = above 131 C Range data label alignment 0 = center, 1 = right, 2 = below, 3 = left, 4 = above 132 D Range data label alignment 0 = center, 1 = right, 2 = below, 3 = left, 4 = above 133 E Range data label alignment 0 = center, 1 = right, 2 = below, 3 = left, 4 = above 134 F Range data label alignment 0 = center, 1 = right, 2 = below, 3 = left, 4 = above 135 X scale 0 = auto FF = manual 136-143 X lower limit in floating point format 144-151 X upper limit in floating point format 152 Y scale 0 = automatic FF = manual 153-160 Y lower limit in floating point format 161-168 Y upper limit in floating point format 209-224 First title 225-248 Second title 249-288 X title 289-328 Y title 329-348 A legend 349-368 B legend 369-388 C legend 389-408 D legend 409-428 E legend 429-448 F legend 449 X format 450 Y format 451-452 Skip factor 453 X scale flag (x1K) 0 = ON, FF = OFF 454 Y scale flag(x1K) 0 = ON, FF = OFF 455 suppress 0 = no, else = yes 456-463 Bar origin (float) 464-471 X linear scale (float) 472-479 Y linear scale (float) 480 X log scale 481 Y log scale 482 graph region color X hue code 483 A hue code 484 B hue code 485 C hue code 487 D hue code 488 F hue code 489-490 Y width 491-498 Aspect (float)
Record Type Code Body length GRAPHNAME 65 (41h) 16 bytes
Byte Number Byte Description 0-15 NULL terminated ASCII string
Record Type Code Body length ZOOM 66 (42h) 9 bytes
Byte Number Byte Description 0 iszoom? 0 = no, 1 = yes 1-2 X coordinates 3-4 Y coordinates 5-6 column depth 7-8 row depth
Record Type Code Body length SYMSPLIT 67 (43h) 2 bytes
Byte Number Byte Description 0-1 number of split windows
Record Type Code Body length NSROWS 68 (44h) 2 bytes
Byte Number Byte Description 0-1 number of screen rows
Record Type Code Body length NSCOLS 69 (45h) 2 bytes
Byte Number Byte Description 0-1 Number of screen columns
Record Type Code Body length RULER 70 (46h) 25 bytes
Byte Number Byte Description 0-15 Name NULL terminated ASCII string 16-17 Range starting column 18-19 starting row 20-21 ending column 22-23 ending row 24 Range type 0 = single cell, 1 = range
Record Type Code Body length NNAME 71 (47h) 25 bytes
Byte Number Byte Description 0-15 Name NULL terminated ASCII string 16-17 Range starting column 18-19 starting row 20-21 ending column 22-23 ending row 24 Range type 0 = single cell, 1 = range
Record Type Code Body length ACOMM 72 (48h) 65 bytes
Byte Number Byte Description 0-64 Path name to Autoload file: NULL terminated ASCII string
Record Type Code Body length AMACRO 73 (49h) 8 bytes
Byte Number Byte Description 0-1 starting column 2-3 starting row 4-5 ending column 6-7 ending row
Record Type Code Body length PARSE 74 (4Ah) 16 bytes
Byte Number Byte Description 0-1 Parse range starting column 2-3 starting row 4-5 ending column 6-7 ending row 8-9 Review range starting column 10-11 starting row 12-13 ending column 14-15 ending row
APPENDIX A: Cell Format Encoding
Bit number Description Code Meaning
7 protection 1 protected
0 unprotected
4,5,6 format type 0 fixed
1 scientific notation
2 currency
3 percent
4 comma
5 unused
6 unused
7 special
0,1,2,3 number of decimal 0-15
places decoded
(if format type = 0-6)
special format type 0 +/-
(if format type = 7) 1 general
2 day-month-year
3 day-month
4 month-year
5 text
(Symphony only) 6 hidden
(Symphony only) 7 date;hour-min-sec
(Symphony only) 8 date;hour-min
(Symphony only) 9 date;intnt'l1
(Symphony only) 10 date;intnt'l1
(Symphony only) 11 time;intnt'l1
(Symphony only) 12 time;intnt'l2
13-14 unused
15 default
EXAMPLE
Currency format, two decimal places, unprotected cell
Bit Number 7 6 5 4 3 2 1 0
Binary Code 0 0 1 0 0 0 1 0
---------
Format Type ---------------
Protection Number of Decimal Places
or Special Format
The byte number is 0.
The hex code is 22.
Example
Special format, month-year, protected cell
Bit Number 7 6 5 4 3 2 1 0
Binary Code 1 1 1 1 0 1 0 0
---------
Format Type -------------
Protection Number of Decimal Places
or Special Format
The byte number is 0.
The hex code is F4.
APPENDIX B: The Formula Compiler
a) Push the number code on the syntax stack
b) Push the number code on the syntax stack
a) Compile code to push the range reference
b) Push the range code on the syntax stack
Stack
Before After Action
)
number
( number none
The Rules
Stack
Before After Action
eoform Output a return code to compile buffer
number Return, indicating successful compile
boform
Rule 2 Function argument processing
Stack
Before After Action
' Error if range argument illegal for
number or range function.
( ( Increment argument count on stack
function function
Rule 3 Process final function argument
Stack
Before After Action
) Error if range argument illegal for
number or range function.
( Increment argument count on stack
function number Compile function opcode
If list function, compile argument
count; otherwise error is wrong
argument count.
Rule 4 Parenthesis removal
Stack
Before After Action
) Compile parenthesis opcode
number
( number
operator operator
Rule 5 Binary operators
Stack
Before After Action
op2 If binary op<binary op, rule does
number not match. Otherwise, compile opcode
op1 op2 for operator op1.
Rule 6 Unary operators
Stack
Before After Action
op2 I unary op<binary op, rule does
number op2 not match. Otherwise, compile opcode.
op1 number for operator op 1.
Rule 7 Error detection
Stack
Before After Action
eoform Return indicating unsuccessful compile
Operator Unary Precedence Binary Precedence + 6 4 - 6 4 * na 5 / na 7 ^ na 3 = na 3 < > na 3 < = na 3 > = na 3 < na 3 > na 3 #and# na 1 #or# na 1 #not# 2 na
3+5*6
This is broken up by the lexical analyzer into seven tokens.
boform
3
+
5
*
6
eoform
The syntax scans proceed as follows until a matching rule is found:
Stack
boform number + number
boform number +
boform number
boform
Compile buffer
push 3 push 3 push 3
push 5
At this point, rule 5 is invoked, but since the precedence of boform is
zero, no action is taken.
Stack
* number
number *
+ number
number +
boform number
boform
Compile buffer
push 3 push 3
push 5 push 5
push 6
At this point, since the binary precedence of + is lower than the binary
precedence of *, rule 5 does apply, and the opcode for * is compiled. The
stack is reduced by replacing number * number by number and scan is made,
but no further rule applies.
Stack
number eoform
+ number
number +
boform number
boform
Compile buffer
push 3 push 3
push 5 push 5
push 6 push 6
push 3
push 5
push 6
*
+
return
Column Hex Dec Column Hex Dec Column Hex Dec A 0 0 BA 34 52 DA 68 104 B 1 1 BB 35 53 DB 69 105 C 2 2 BC 36 54 DC 6A 106 D 3 3 BD 37 55 DD 6B 107 E 4 4 BE 38 56 DE 6C 108 F 5 5 BF 39 57 DF 6D 109 G 6 6 BG 3A 58 DG 6E 110 H 7 7 BH 3B 59 DH 6F 111 I 8 8 BI 3C 60 DI 70 112 J 9 9 BJ 3D 61 DJ 71 113 K A 10 BK 3E 62 DK 72 114 L B 11 BL 3F 63 DL 73 115 M C 12 BM 40 64 DM 74 116 N D 13 BN 41 65 DN 75 117 O E 14 BO 42 66 DO 76 118 P F 15 BP 43 67 DP 77 119 Q 10 16 BQ 44 68 DQ 78 120 R 11 17 BR 45 69 DR 79 121 S 12 18 BS 46 70 DS 7A 122 T 13 19 BT 47 71 DT 7B 123 U 14 20 BU 48 72 DU 7C 124 V 15 21 BV 49 73 DV 7D 125 W 16 22 BW 4A 74 DW 7E 126 X 17 23 BX 4B 75 DX 7F 127 Y 18 24 BY 4C 76 DY 80 128 Z 19 25 BZ 4D 77 DZ 81 129 AA 1A 26 CA 4E 78 EA 82 130 AB 1B 27 CB 4F 79 EB 83 131 AC 1C 28 CC 50 80 EC 84 132 AD 1D 29 CD 51 81 ED 85 133 AE 1E 30 CE 52 82 EE 86 134 AF 1F 31 CF 53 83 EF 87 135 AG 20 32 CG 54 84 EG 88 136 AH 21 33 CH 55 85 EH 89 137 AI 22 34 CI 56 86 EI 8A 138 AJ 23 35 CJ 57 87 EJ 8B 139 AK 24 36 CK 58 88 EK 8C 140 AL 25 37 CL 59 89 EL 8D 141 AM 26 38 CM 5A 90 EM 8E 142 AN 27 39 CN 5B 91 EN 8F 143 AO 28 40 CO 5C 92 EO 90 144 AP 29 41 CP 5D 93 EP 91 145 AQ 2A 42 CQ 5E 94 EQ 92 146 AR 2B 43 CR 5F 95 ER 93 147 AS 2C 44 CS 60 96 ES 94 148 AT 2D 45 CT 61 97 ET 95 149 AU 2E 46 CU 62 98 EU 96 150 AV 2F 47 CV 63 99 EV 97 151 AW 30 48 CW 64 100 EW 98 152 AX 31 49 CX 65 101 EX 99 153 AY 32 50 CY 66 102 EY 9A 154 AZ 33 51 CZ 67 103 EZ 9B 155
Column Hex Dec Column Hex Dec FA 9C 156 HA DO 208 FB 9D 157 HB D1 209 FC 9E 158 HC D2 210 FD 9F 159 HD D3 211 FE AO 160 HE D4 212 FF A1 161 HF D5 213 FG A2 162 HG D6 214 FH A3 163 HH D7 215 FI A4 164 HI D8 216 FJ A5 165 HJ D9 217 FK A6 166 HK DA 218 FL A7 167 HL DB 219 FM A8 168 HM DC 220 FN A9 169 HN DD 221 FO AA 170 HO DE 222 FP AB 171 HP DF 223 FQ AC 172 HQ EO 224 FR AD 173 HR E1 225 FS AE 174 HS E2 226 FT AF 175 HT E3 227 FU BO 176 HU E4 228 FV B1 177 HV E5 229 FW B2 178 HW E6 230 FX B3 179 HX E7 231 FY B4 180 HY E8 232 FZ B5 181 HZ E9 233 GA B6 182 IA EA 234 GB B7 183 IB EB 235 GC B8 184 IC EC 236 GD B9 185 ID ED 237 GE BA 186 IE EE 238 GF BB 187 IF EF 239 GG BC 188 IG FO 240 GH BD 189 IH F1 241 GI BE 190 II F2 242 GJ BF 191 IJ F3 243 GK CO 192 IK F4 244 GL C1 193 IL F5 245 GM C2 195 IM F6 246 GN C3 195 IN F7 247 GO C4 196 IO F8 248 GP C5 197 IP F9 249 GQ C6 198 IQ FA 250 GR C7 199 IR FB 251 GS C8 200 IS FC 252 GT C9 201 IT FD 253 GU CA 202 IU FE 254 GV CB 203 IV FF 255 GW CC 204 GX CD 205 GY CE 206 GZ CF 207 ANALYSIS OF 1-2-3 WORKSHEET FILE
Key:
A2..A5 Named Range (code 11)
EXAMPLE A2: Label (code 15)
100 A3: Integer (code 13)
12.5 A4: Number (code 14)
87.5 A5: Formula (+A3-A4)
(code 16)
362B:0100 06 00 08 00 00 00 00 00 00 00
362B:0110 04 00 2F 00 01 00 01 02 00 01 00 FF 03 00 01 00
362B:0120 00 04 00 01 00 00 05 00 01 00 FF 07 00 1F 00 00
362B:0130 00 01 00 71 00 09 00 08 00 14 00 00 00 00 00 00
362B:0140 00 00 00 00 00 00 00 04 00 04 00 48 00 00 0B 00
362B:0150 18 00 54 45 53 54 00 00 00 00 00 00 00 00 00 00
362B:0160 00 00 00 00 01 00 00 00 04 00 18 00 19 00 00 FF
362B:0170 FF 00 00 FF FF 00 00 FF FF 00 00 FF FF 00 00 FF
362B:0180
362B:05C0
362B:05D0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
362B:05E0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
362B:05F0 00 00 00 00 71 71 01 00 0F 00 0E 00 FF 00 00 01
362B:0600 00 27 45 58 41 4D 50 4C 45 00 0D 00 07 00 FF 00
362B:0610 00 02 00 64 00
362B:0620 10 00 1B 00 FF 00 00 04 00 00
362B:0630 00 00 00 00 E0 55 40 0C 00 01 00 80 FE BF 01 00
362B:0640 80 FF BF 0A 03
ADDENDUM to WORKSHEET FILE FORMAT Document (2-19-85):
Byte Number Byte Description 0 format 1-2 column 3-4 row 5-12 formula numeric value (IEEE long real) 13-14 formula size (bytes) 15+ formula code, compiled Reverse-Polish internal notation; max of 2048 bytes
Opcode Explanation 0 push constant...followed by 8 byte # 1 push variable...followed by 4 byte column,row...a relative coordinate has its most significant bit set 2 push range...followed by 8 byte begin/end column,row 3 end of formula 4 parentheses 5 integer constant...followed by 2 byte integer 6 push string...followed by null terminated string 7 unused 8 unary - 9 binary + 10 binary - 11 * 12 / 13 ^ 14 = 15 <> 16 <= 17 >= 18 < 19 > 20 #AND# 21 #OR# 22 #NOT# 23 unary 24 & 25..30 unused 31 @na 32 @err 33 @abs 34 @int 35 @sqrt 36 @log 37 @ln 38 @pi 39 @sin 40 @cos 41 @tan 42 @atan2 43 @atan 44 @asin 45 @acos 46 @exp 47 @mod 48 @sel 49 @isna 50 @iserr 51 @false 52 @true 53 @rand 54 @date 55 @now 56 @pmt 57 @pv 58 @fv 59 @if 60 @day 61 @month 62 @year 63 @round 64 @time 65 @hour 66 @minute 67 @second 68 @isnumber 69 @isstring 70 @length 71 @value 72 @fixed 73 @mid 74 @chr 75 @ascii 76 @find 77 @datevalue 78 @timevalue 79 @cell pointer 80 @sum..followed by 1 byte >>> # of args 81 @avg..followed by 1 byte >>> # of args 82 @cnt..followed by 1 byte >>> # of args 83 @min..followed by 1 byte >>> # of args 84 @max..followed by 1 byte >>> # of args 85 @vlookup 86 @npv 87 @var 88 @std 89 @irr 90 @hlookup 91 @dsum 92 @davg 93 @dcnt 94 @dmin 95 @dmax 96 @dvar 97 @dstd 98 @index 99 @cols 100 @rows 101 @repeat 102 @upper 103 @lower 104 @left 105 @right 106 @replace 107 @proper 108 @cell 109 @trim 110 @clean 111 @s 112 @v 113 @streq 114 @call 115 @indirect