Classic Computer Magazine Archive COMPUTE! ISSUE 150 / MARCH 1993 / PAGE 54

Open up your world. (using the worksheet format) (Column)
by Tom Campbell

If you've ever wondered how to open up your programs to data from the outside world, the 1-2-3 worksheet format probably came to mind. It's easy to use, especially if you stick to the basics--labels, numbers, dates, and so on--because it was made public in the product's first release.

Later formats weren't made public by Lotus, but everything that you need is on CompuServe's LOTUSA forum, library 9, under the name WSFF.ARC.

The worksheet format, elegantly simple and extensible, is made of variable-length records as follows:

Bytes 1-2: Record type Bytes 2-3: Length of record body Bytes 4-n: Record body

In the two-byte quantities (such as record type and record length), numbers are stored in the standard Intel fashion: the first byte is the remainder when the number is divided by 256; the second byte is the quotient. These two-byte values are often called words. The first byte is called the least significant byte (LSB), is the lower part of the word, and is stored first. The second byte is called the most significant byte (MSB). As we'll see later, sometimes the length word will be 0, and there will not be a record body.

A worksheet file is made up of at least two records: the BOF (Beginning Of File) and EOF (End Of File). The BOF also serves as a version indicator; it's different among WKS, WK1, and WK3 files. For our purposes, the original WKS file will be fine, as any program that imports worksheets supports it. Since information on the newer WK1 and WK3 formats wasn't made public, applications are less likely to read them.

The BOF record contains a 0 for each of the type bytes, so the BOF record type number is 0. The next two bytes represent the length word, which tells how long the rest of the record will be (it does not count the type or length words). The BOF record is only two bytes long: the 2 in the length word comes first and is stored in the LSB; the MSB contains 0. For a WKS file 04 04, the body of the BOF record contains the version bytes BOFRecord: DATA 0, 0, 2, 0, 04, 04.

The EOF doesn't need any version info; it consists solely of the type word--who has a value of 1 and is stored as a byte value of 1 followed by a 0 byte for the MSB--and a length word, whose value is 0. These two items make the minimum legal WKS file, EOFRecord: DATA 1, 0, 0, 0.

A label is sandwiched between. The WriteLabel subroutine lets you pass any text, along with row and column info. It automatically creates a label at the specified row and column. Cell A1's row value is 0 and column value is 0; B2 would be 1, 1; D4 would be 2,3; and so on.

Download WSFF.ARC for the numeric format. The listing that follows here begins with labels because they can be converted to numbers once they're in the program.