PC World 2000 February

home *** CD-ROM | disk | FTP | other *** search

/ PC World 2000 February / PCWorld_2000-02_cd.bin / Software / Servis / FFE / SPREADSH.SWG / 0004_Lotus WKS.pas / LOTUS.ZIP / LOTUS.DOC

Wrap

Text File | 1997-05-04 | 112.9 KB | 3,186 lines

WORKSHEET FILE FORMAT FROM LOTUS INTRODUCTION AND QUICK REFERENCE Copyright(c) 1984, Lotus Development Corporation 161 First Street Cambridge, Massachusetts 02142 (617) 492-7171 Electronic Edition, December, 1984 All Rights Reserved PREFACE 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: Lotus Development Corporation Worksheet File Format 161 First Street Cambridge, MA 02142 WORKSHEET FILE FORMAT 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 Header Record Record Type Length Byte Number 0 1 2 3 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 SUMMARY OF RECORD TYPES 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. Quick Reference by Opcode 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 Quick Reference by Opcode (continued) Type code (hex) Length (bytes) Description 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 PRINT 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 WINDOW 1 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 PRINT 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 WORKSHEET FILE FORMAT FROM LOTUS SUMMARY OF RECORD TYPES Copyright(c) 1984, Lotus Development Corporation 161 First Street Cambridge, Massachusetts 02142 (617) 492-7171 Electronic Edition, December, 1984 All Rights Reserved BOF 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 EOF 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. CALCMODE 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 CALCORDER Recrod 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 SPLIT 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 SYNC 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 RANGE 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 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. WINDOW1 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) COLW1 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 WINTWO 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) COLW2 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 NAME 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 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). BLANK 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). INTEGER 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). NUMBER 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) LABEL 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. FORMULA 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 FORMULA 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 coor- dinate 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 paren- theses in original formula. It is ignored during recal- culation. 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. FORMULA 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) FORMULA Table 1-b (continued) Formula Compiler Opcode Table, Operations Dec Hex Operation Description 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.) FORMULA 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. FORMULA 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). TABLE 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 QRANGE 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 PRANGE 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 SRANGE 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 FRANGE 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 KRANGE 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 HRANGE 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 KRANGE2 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 PROTEC 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 FOOTER 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-241 NULL terminated ASCII string HEADER 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-241 NULL terminated ASCII string SETUP 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-39 NULL terminated ASCII string MARGINS 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 LABELFMT 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 TITLES 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 GRAPH Record Type Code Body length GRAPH 45(2Dh) 437 bytes Record Description Current graph settings Used in 1-2-3 only. Byte Number Byte Description -- see Table 2 Graph Record Structure -- GRAPH 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; starting 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 GRAPH Table 2 (continued) Graph Record Structure Byte Number Byte Description 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 0 = center, 1 = right, alignment; 2 = below, 3 = left, 4 = above 114 B Range data label 0 = center, 1 = right alignment; 2 = below, 3 = left 4 = above GRAPH Table 2 (continued) Graph Record Structure Byte Number Byte Description 115 C Range data label 0 = center, 1 = right alignment; 2 = below, 3 = left 4 = above 116 D Range data label 0 = center, 1 = right alignment; 2 = below, 3 = left 4 = above 117 E Range data label 0 = center, 1 = right alignment; 2 = below, 3 = left 4 = above 118 F Range data label 0 = center, 1 = right alignment; 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 GRAPH Table 2 (continued) Graph Record Structure Byte Number Byte Description 393-412 E legend 413-432 F legend 433 X format 434 Y format 435-436 Skip factor NGRAPH Record Type Code Body length NGRAPH 46 (2EH) 453 bytes Record Description Named current graph settings Used in 1-2-3 only. Bytes Number Byte Description -- see Table 3 Ngraph Record Structure -- NGRAPH 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 NGRAPH Table 3 (continued) NGraph Record Structure Byte Number Byte Description 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, 6 = 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 NGRAPH Table 3 (continued) NGraph Record Structure Byte Number Byte Description 129 A Range data label 0 = center, 1 = right alignment 2 = below, 3 = left, 4 = above 130 B Range data label 0 = center, 1 = right alignment 2 = below, 3 = left 4 = above 131 C Range data label 0 = center, 1 = right alignment 2 = below, 3 = left 4 = above 132 D Range data label 0 = center, 1 = right alignment 2 = below, 3 = left 4 = above 133 E Range data label 0 = center, 1 = right alignment 2 = below, 3 = left 4 = above 134 F Range data label 0 = center, 1 = right alignment 2 = below, 3 = left 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 169-208 First title 209-248 Second title 249-288 X title 289-328 Y title NGRAPH Table 3 (continued) NGraph Record Structure Byte Number Byte Description 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 CALCCOUNT 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 UNFORMATTED 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 CURSORW12 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 WINDOW Record Type Code Body length WINDOW 50(32h) 144 bytes Record Description Window record structure Used in Symphony only. Byte Number Byte Description -- see Table 4 Window Record Structure -- WINDOW 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 WINDOW Table 4 (continued) Window Record Structure Byte Number Byte Description 79 Window type; 0 = SHEET 1 = DOC 2 = GRAPH 3 = COMM 4 = FORM 5 = APPLICATION 80 Automatic display mode "a" = automatic (ASCII flag; lower case "a") 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 "1" = left (ASCII lower case "1") "r" = right (ASCII lower case "r") "c" = center (ASCII lower case "c" "e" = even (ASCII lower case "e" 103-104 Right Margin 0 = FOh characters = right margin FF = no user-defined right margin; use default value 105-106 Left Margin 0-FOh characters = left margin 107-108 Tab interval 109 CR display; 0 = soft non-zero = hard 110 Auto-justify on copy/ 0 = no move; non-zero = yes 111-126 Associated application name 127-143 Reserved Application Area STRING Record Type Code Body length STRING 51(33h) variable Record Description Value of string formula Used in Symphony only. Byte Number Byte Description 0 format (see Appendix A, Cell Format Encoding) 1-2 column 3-4 row 5+ NULL terminated ASCII string PASSWORD Record Type Code Body length PASSWORD 55(37h) 4 byte Record Description File lockout (CHKSUM) This is proprietary information. Used in Symphony only. Byte Number Byte Description -- not available -- LOCKED Record Type Code Body length LOCKED 56(38h) 1 byte Record Description Lock Flag Used in Symphony only. Byte Number Byte Description 0 0 = OFF 1 = ON QUERY Record Type Code Body length QUERY 60(3Ch) 127 bytes Record Description Query settings Used in Symphony only. Byte Number Byte Description -- see Table 5 Query Record Structure -- QUERY Table 5 Query Record Structure Byte Number Byte Description 0-15 Name; NULL termination 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 QUERY Table 5 (continued) Query Record Structure Byte Number Byte Description 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 passes; 0 = single pass FF = multiple passes 125 Number of records; 0 = single FF = multiple 126 Display underscores in 0 = no entry form? FF = yes QUERYNAME Record Type Code Body length QUERYNAME 61(3Dh) 16 bytes Record Description Current Query Name Used in Symphony only. Byte Number Byte Description 0-15 NULL terminated ASCII string PRINT Record Type Code Body length PRINT 62(3Eh) 679 bytes Record Description Print record Used in Symphony only. Byte Number Byte Description -- see Table 6 Print Record Structure -- PRINT Table 6 Print Record Structure 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-100 Setup string; NULL terminated ASCII string 101-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 PRINT Table 6 Print Record Structure Byte Number Byte Description 604-605 Starting page 606-607 Ending page 608-677 Destination filename; NULL terminated ASCII string 678 Wait; 0 = no non-zero = yes PRINTNAME Record Type Code Body length PRINTNAME 63(3Fh) 16 bytes Record Description Current Print Record Name Used in Symphony only. Byte Number Byte Description 0-15 NULL terminated ASCII string GRAPH2 Record Type Code Body length GRAPH2 64(40h) 499 bytes Record Description Graph record Used in Symphony only. Byte Number Byte Description -- see Table 7 Symphony Graph Record Structure -- GRAPH2 Table 7 Symphony Graph Record Structure Byte Number Byte Description 0-15 Name; NULL terminated ASCII string 16-17 XRange; 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; starting 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 GRAPH2 Table 7 (continued) Symphony Graph Record Structure Byte Number Byte Description 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, 7=hi/low/open/close 121 Grid; 0 = none, 1 = horizontal, 2 = vertical, 3 = both 122 Color; 0 = black-white, FF = color 123 A Range line format; 0 = none, 1 = 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 GRAPH2 Table 7 (continued) Symphony Graph Record Structure Byte Number Byte Description 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 169-208 First title 209-248 Second title 249-288 X title 289-328 Y title GRAPH2 Table 7 (continued) Symphony Graph Record Structure Byte Number Byte Description 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 486 D hue code 487 E hue code 488 F hue code 489-490 Y width 491-498 Aspect (float) GRAPHNAME Record Type Code Body length GRAPHNAME 65 (41h) 16 bytes Record Description Current Graph Record Name Used in Symphony only. Byte Number Byte Description 0-15 NULL terminated ASCII string ZOOM Record Type Code Body length ZOOM 66 (42h) 9 bytes Record Description Original coordinates expanded window Used in Symphony only. 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 SYMSPLIT Record Type Code Body length SYMSPLIT 67 (43h) 2 bytes Record Description Number of split windows Used in Symphony only. Byte Number Byte Description 0-1 number of split windows NSROWS Record Type Code Body length NSROWS Code Body length NSROWS 68 (44h) 2 bytes Record Description Number of screen rows Used in Symphony only. Byte Number Byte Description 0-1 number of screen rows NSCOLS Record Type Code Body length NSCOLS 69 (45h) 2 bytes Record Description Number of screen columns Used in Symphony only. Byte Number Byte Description 0-1 Number of screen columns RULER Record Type Code Body length RULER 70 (46h) 25 bytes Record Description Name ruler range Used in Symphony only. 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 NNAME Record Type Code Body length NNAME 71 (47h) 25 bytes Record Description Named sheet range Used in Symphony only. 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 ACCOM Record Type Code Body length ACOMM 72 (48h) 65 bytes Record Description Autoload communications file Used in Symphony only. Byte Number Byte Description 0-64 Path name to Autoload file; NULL terminated ASCII string AMACRO Record Type Code Body length AMACRO 73 (49h) 8 bytes Record Description Autoexecute macro address Used in Symphony only. Byte Number Byte Description 0-1 starting column 2-3 starting row 4-5 ending column 6-7 ending row PARSE Record Type Code Body length PARSE 74 (4Ah) 16 bytes Record Description Query parse information Used in Symphony only. 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 WORKSHEET FILE FORMAT FROM LOTUS APPENDIX A - CELL FORMAT ENCODING Copyright(c) 1984, Lotus Development Corporation 161 First Street Cambridge, Massachusetts 02142 (617) 492-7171 Electronic Edition, December, 1984 All Rights Reserved APPENDIX A: Cell Format Encoding 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 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. WORKSHEET FILE FORMAT FROM LOTUS APPENDIX B - THE FORMULA COMPILER Copyright(c) 1984, Lotus Development Corporation 161 First Street Cambridge, Massachusetts 02142 (617) 492-7171 Electronic Edition, December, 1984 All Rights Reserved APPENDIX B: The Formula Compiler 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: a) Push the number code on the syntax stack b) Push the number code on the syntax stack 3) If the token is a range reference: a) Compile code to push the range reference b) Push the range code on the syntax stack 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: Stack Before After Action ) number ( number none The Rules 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 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 Table 9 Operator Precedence Table 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 Example: Using the above rules, we can now see how a particular formula is compiled. Let us consider the following formula: 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 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: push 3 push 5 push 6 * + return 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. 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 (CONTINUED) 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 The worksheet shown below was created in 1-2-3 and saved to disk. 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) 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. 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