Task 808: .WK1 File Format
Task 808: .WK1 File Format
1. List of Properties of the .WK1 File Format Intrinsic to Its File System
The .WK1 file format is a binary spreadsheet format used by Lotus 1-2-3 version 2.x. "Properties intrinsic to its file system" seems to refer to the core structural and metadata properties that define the format's layout and content organization, such as header, record structure, and key metadata fields. Based on the specifications, the key properties are:
- Magic Number (Header Signature): The file starts with the bytes 00 00 02 00 06 04 (hex), which serves as the beginning of the BOF (Beginning of File) record for .WK1 files. This is used to identify the file type and version.
- Record-Based Structure: The file is a sequence of records. Each record has a fixed structure:
- 2 bytes: Opcode (record type, little-endian).
- 2 bytes: Length of the data (little-endian).
- Length bytes: Record data.
- BOF (Beginning of File) Record: Opcode 0 (00 00), length 2 (02 00), data 06 04 (indicating Lotus 1-2-3 version 2 revision).
- EOF (End of File) Record: Opcode 1 (01 00), length 0 (00 00), no data.
- Sheet Dimensions: Single sheet only (property of .WK1 format; multiple sheets came in later formats like .WK3). Maximum 256 columns (A to IV) and 8192 rows (1 to 8192).
- Cell Data Properties: Cells can contain labels (text), numbers (double-precision floating point), formulas, or empty. Each cell record includes column (1 byte), row (2 bytes), format, and value.
- Calc Mode: A property indicating calculation mode (automatic or manual), stored in record opcode 3.
- Calc Order: Property for calculation order (natural, row-wise, column-wise), stored in record opcode 4.
- Window Properties: Split and sync properties for window display, stored in record opcode 5.
- Column Widths: Default and per-column widths, stored in record opcode 7.
- Cell Records: Specific types include:
- Integer cell (opcode 12, length 7, data: column, row, format, 2-byte signed int).
- Floating point cell (opcode 13, length 11, data: column, row, format, 8-byte double).
- Label cell (opcode 15, length variable, data: column, row, format, string length + string).
- Formula cell (opcode 14, length variable, data: column, row, format, current value, formula expression).
- Endianness: Little-endian for all multi-byte values.
- No Compression or Encryption: The format is uncompressed and unencrypted by default.
- File Size Constraints: No strict limit, but practical limit based on 8192 rows x 256 columns.
These properties are derived from the format's binary structure and are "intrinsic" as they define how the file is organized and parsed, similar to file system metadata.
2. Two Direct Download Links for .WK1 Files
- https://pubs.usgs.gov/of/1999/ofr-99-0342/wrksh/WATANI.WK1 (sample spreadsheet data from USGS water quality report)
- https://pubs.usgs.gov/of/1999/ofr-99-0342/wrksh/WATHG.WK1 (another sample spreadsheet data from USGS water quality report)
3. Ghost Blog Embedded HTML JavaScript for Drag and Drop .WK1 File Dump
This is an embeddable HTML snippet with JavaScript that allows dragging and dropping a .WK1 file. It parses the file, extracts the properties from the list above, and dumps them to the screen. It uses FileReader and DataView for binary parsing.
Drag and Drop .WK1 File Here
4. Python Class for .WK1 File
This Python class opens, decodes, reads, writes, and prints the properties to console.
import struct
class WK1File:
def __init__(self, filename=None):
self.filename = filename
self.data = None
self.properties = {}
if filename:
self.read(filename)
def read(self, filename):
with open(filename, 'rb') as f:
self.data = f.read()
self.decode()
def decode(self):
if not self.data:
raise ValueError("No data to decode")
magic = self.data[0:6]
if magic != b'\x00\x00\x02\x00\x06\x04':
raise ValueError("Invalid WK1 magic number")
self.properties['magic'] = ' '.join(f'{b:02x}' for b in magic)
self.properties['records'] = []
offset = 0
while offset < len(self.data):
opcode = struct.unpack_from('<H', self.data, offset)[0]
offset += 2
length = struct.unpack_from('<H', self.data, offset)[0]
offset += 2
data = self.data[offset:offset + length]
self.properties['records'].append({'opcode': opcode, 'length': length, 'data': data})
offset += length
if opcode == 0: # BOF
self.properties['version'] = data[1]
self.properties['revision'] = data[0]
if opcode == 1: # EOF
break
if opcode == 6: # DIMENSIONS
col_start, col_end, row_start, row_end = struct.unpack('<HHHH', data[0:8])
self.properties['columns'] = col_end - col_start + 1
self.properties['rows'] = row_end - row_start + 1
if opcode == 3: # CALCMODE
self.properties['calc_mode'] = 'Manual' if data[0] == 0 else 'Automatic'
self.properties['sheet_dimensions'] = 'Single sheet, max 256 columns, 8192 rows'
self.properties['endianness'] = 'Little-endian'
def print_properties(self):
for key, value in self.properties.items():
if key != 'records': # Skip detailed records for brevity
print(f"{key}: {value}")
def write(self, filename):
if not self.data:
# Create a minimal WK1 file if no data
self.data = b'\x00\x00\x02\x00\x06\x04' + b'\x01\x00\x00\x00' # BOF + EOF
with open(filename, 'wb') as f:
f.write(self.data)
# Example usage
# wk1 = WK1File('sample.wk1')
# wk1.print_properties()
# wk1.write('output.wk1')
5. Java Class for .WK1 File
This Java class opens, decodes, reads, writes, and prints the properties to console.
import java.io.*;
import java.nio.*;
import java.nio.channels.FileChannel;
public class WK1File {
private byte[] data;
private final java.util.Map<String, Object> properties = new java.util.HashMap<>();
public WK1File(String filename) throws IOException {
if (filename != null) {
read(filename);
}
}
public void read(String filename) throws IOException {
FileInputStream fis = new FileInputStream(filename);
FileChannel channel = fis.getChannel();
ByteBuffer buffer = ByteBuffer.allocate((int) channel.size());
channel.read(buffer);
data = buffer.array();
fis.close();
decode();
}
private void decode() {
ByteBuffer bb = ByteBuffer.wrap(data).order(ByteOrder.LITTLE_ENDIAN);
byte[] magic = new byte[6];
bb.get(magic);
if (!java.util.Arrays.equals(magic, new byte[]{0, 0, 2, 0, 6, 4})) {
throw new IllegalArgumentException("Invalid WK1 magic number");
}
properties.put("magic", bytesToHex(magic));
java.util.List<java.util.Map<String, Object>> records = new java.util.ArrayList<>();
properties.put("records", records);
int offset = 0;
while (offset < data.length) {
int opcode = bb.getShort(offset) & 0xFFFF;
offset += 2;
int length = bb.getShort(offset) & 0xFFFF;
offset += 2;
byte[] recData = new byte[length];
bb.position(offset);
bb.get(recData);
java.util.Map<String, Object> rec = new java.util.HashMap<>();
rec.put("opcode", opcode);
rec.put("length", length);
rec.put("data", recData);
records.add(rec);
offset += length;
if (opcode == 0) { // BOF
properties.put("version", (int) recData[1] & 0xFF);
properties.put("revision", (int) recData[0] & 0xFF);
}
if (opcode == 1) break;
if (opcode == 6) { // DIMENSIONS
ByteBuffer recBb = ByteBuffer.wrap(recData).order(ByteOrder.LITTLE_ENDIAN);
properties.put("columns", recBb.getShort(2) - recBb.getShort(0) + 1);
properties.put("rows", recBb.getShort(6) - recBb.getShort(4) + 1);
}
if (opcode == 3) { // CALCMODE
properties.put("calc_mode", recData[0] == 0 ? "Manual" : "Automatic");
}
}
properties.put("sheet_dimensions", "Single sheet, max 256 columns, 8192 rows");
properties.put("endianness", "Little-endian");
}
public void printProperties() {
properties.forEach((key, value) -> {
if (!"records".equals(key)) {
System.out.println(key + ": " + value);
}
});
}
public void write(String filename) throws IOException {
if (data == null) {
// Minimal WK1
data = new byte[]{0, 0, 2, 0, 6, 4, 1, 0, 0, 0};
}
try (FileOutputStream fos = new FileOutputStream(filename)) {
fos.write(data);
}
}
private String bytesToHex(byte[] bytes) {
StringBuilder sb = new StringBuilder();
for (byte b : bytes) {
sb.append(String.format("%02x ", b));
}
return sb.toString().trim();
}
// Example usage
// public static void main(String[] args) throws IOException {
// WK1File wk1 = new WK1File("sample.wk1");
// wk1.printProperties();
// wk1.write("output.wk1");
// }
}
6. JavaScript Class for .WK1 File
This JavaScript class opens (using Node.js fs), decodes, reads, writes, and prints the properties to console.
const fs = require('fs');
class WK1File {
constructor(filename = null) {
this.data = null;
this.properties = {};
if (filename) this.read(filename);
}
read(filename) {
this.data = fs.readFileSync(filename);
this.decode();
}
decode() {
if (!this.data) throw new Error('No data to decode');
const magic = this.data.slice(0, 6);
if (magic.toString('hex') !== '000002000604') throw new Error('Invalid WK1 magic number');
this.properties.magic = magic.toString('hex').match(/.{1,2}/g).join(' ');
this.properties.records = [];
let offset = 0;
while (offset < this.data.length) {
const opcode = this.data.readUInt16LE(offset);
offset += 2;
const length = this.data.readUInt16LE(offset);
offset += 2;
const recData = this.data.slice(offset, offset + length);
this.properties.records.push({ opcode, length, data: recData });
offset += length;
if (opcode === 0) { // BOF
this.properties.version = recData[1];
this.properties.revision = recData[0];
}
if (opcode === 1) break;
if (opcode === 6) { // DIMENSIONS
this.properties.columns = recData.readUInt16LE(2) - recData.readUInt16LE(0) + 1;
this.properties.rows = recData.readUInt16LE(6) - recData.readUInt16LE(4) + 1;
}
if (opcode === 3) this.properties.calcMode = recData[0] === 0 ? 'Manual' : 'Automatic';
}
this.properties.sheetDimensions = 'Single sheet, max 256 columns, 8192 rows';
this.properties.endianness = 'Little-endian';
}
printProperties() {
for (const [key, value] of Object.entries(this.properties)) {
if (key !== 'records') console.log(`${key}: ${value}`);
}
}
write(filename) {
if (!this.data) {
// Minimal WK1
this.data = Buffer.from([0, 0, 2, 0, 6, 4, 1, 0, 0, 0]);
}
fs.writeFileSync(filename, this.data);
}
}
// Example usage
// const wk1 = new WK1File('sample.wk1');
// wk1.printProperties();
// wk1.write('output.wk1');
7. C Class for .WK1 File
This C struct (as a class-like) opens, decodes, reads, writes, and prints the properties to console.
#include <stdio.h>
#include <stdlib.h>
#include <stdint.h>
#include <string.h>
#include <endian.h> // For le16toh if needed
typedef struct {
uint8_t *data;
size_t size;
struct {
char magic[18]; // "00 00 02 00 06 04"
int version;
int revision;
int columns;
int rows;
char calc_mode[10];
char sheet_dimensions[50];
char endianness[15];
} properties;
} WK1File;
void wk1_read(WK1File *wk1, const char *filename) {
FILE *f = fopen(filename, "rb");
if (!f) {
perror("fopen");
exit(1);
}
fseek(f, 0, SEEK_END);
wk1->size = ftell(f);
fseek(f, 0, SEEK_SET);
wk1->data = malloc(wk1->size);
fread(wk1->data, 1, wk1->size, f);
fclose(f);
}
void wk1_decode(WK1File *wk1) {
uint8_t magic[6];
memcpy(magic, wk1->data, 6);
if (memcmp(magic, (uint8_t[]){0,0,2,0,6,4}, 6) != 0) {
fprintf(stderr, "Invalid WK1 magic number\n");
exit(1);
}
sprintf(wk1->properties.magic, "%02x %02x %02x %02x %02x %02x", magic[0], magic[1], magic[2], magic[3], magic[4], magic[5]);
size_t offset = 0;
while (offset < wk1->size) {
uint16_t opcode = *(uint16_t*)(wk1->data + offset);
offset += 2;
uint16_t length = *(uint16_t*)(wk1->data + offset);
offset += 2;
uint8_t *rec_data = wk1->data + offset;
offset += length;
if (opcode == 0) { // BOF
wk1->properties.version = rec_data[1];
wk1->properties.revision = rec_data[0];
}
if (opcode == 1) break;
if (opcode == 6) { // DIMENSIONS
uint16_t col_start = *(uint16_t*)rec_data;
uint16_t col_end = *(uint16_t*)(rec_data + 2);
uint16_t row_start = *(uint16_t*)(rec_data + 4);
uint16_t row_end = *(uint16_t*)(rec_data + 6);
wk1->properties.columns = col_end - col_start + 1;
wk1->properties.rows = row_end - row_start + 1;
}
if (opcode == 3) {
strcpy(wk1->properties.calc_mode, rec_data[0] == 0 ? "Manual" : "Automatic");
}
}
strcpy(wk1->properties.sheet_dimensions, "Single sheet, max 256 columns, 8192 rows");
strcpy(wk1->properties.endianness, "Little-endian");
}
void wk1_print_properties(const WK1File *wk1) {
printf("magic: %s\n", wk1->properties.magic);
printf("version: %d\n", wk1->properties.version);
printf("revision: %d\n", wk1->properties.revision);
printf("columns: %d\n", wk1->properties.columns);
printf("rows: %d\n", wk1->properties.rows);
printf("calc_mode: %s\n", wk1->properties.calc_mode);
printf("sheet_dimensions: %s\n", wk1->properties.sheet_dimensions);
printf("endianness: %s\n", wk1->properties.endianness);
}
void wk1_write(const WK1File *wk1, const char *filename) {
uint8_t minimal[] = {0, 0, 2, 0, 6, 4, 1, 0, 0, 0};
FILE *f = fopen(filename, "wb");
if (!f) {
perror("fopen");
exit(1);
}
if (wk1->data) {
fwrite(wk1->data, 1, wk1->size, f);
} else {
fwrite(minimal, 1, sizeof(minimal), f);
}
fclose(f);
}
void wk1_free(WK1File *wk1) {
free(wk1->data);
}
// Example usage
// int main() {
// WK1File wk1 = {0};
// wk1_read(&wk1, "sample.wk1");
// wk1_decode(&wk1);
// wk1_print_properties(&wk1);
// wk1_write(&wk1, "output.wk1");
// wk1_free(&wk1);
// return 0;
// }