Skip to main content

Module xls

Module xls 

Source
Expand description

Excel’s older binary format, .xls, as a bare worksheet stream: the records of one sheet, written on their own rather than inside the compound file Excel 97 and later save, as some loggers’ programs write their exports.

Like an .xlsx workbook (workbook), the file isn’t a layout of its own. sheets writes the sheet’s cells out as comma-separated text, one line a row, and read gives the text to import::read, which recognizes it as it would the file the sheet came from.

The records. A BIFF stream is a run of records, each a 16-bit identifier, a 16-bit length and that many bytes, all little-endian. A worksheet’s stream opens with a BOF record and closes with an EOF record (OpenOffice.org’s Documentation of the Microsoft Excel File Format, Daniel Rentz, revision 1.42, 2008, §2.1, §4.1.1, §5.8 and §5.37; Microsoft’s [MS-XLS] describes only BIFF8). The reader takes the stream when its first record is a BOF of any version (identifier 0009, 0209, 0409 or 0809) for a worksheet (type 0010), and reads these cell records, BIFF2’s:

RecordIdentifierThe cellWritten as
NUMBER (§5.71)0003a 64-bit IEEE 754 numberits shortest decimal that reads back as the same double, such as 0.005
INTEGER (§5.60)0002an unsigned 16-bit integerits digits
LABEL (§5.63)0004text, 8-bit, its length in one byteits text
BOOLERR (§5.10)0005a truth value, or an error such as #DIV/0!TRUE or FALSE; an error is a gap
BLANK (§5.7)0001a cell formatted but emptynothing

Every other record, formats, fonts, column widths and the sheet’s settings, is passed over. A record of a later version that holds a value (NUMBER 0203, LABEL 0204, RK, MULRK, LABELSST, RSTRING, BOOLERR 0205) or a formula is refused by name rather than passed over, so no value is left out unsaid.

What a cell becomes. A cell is written as the sheet stores it. Its format isn’t read: each BIFF2 cell names one in its attributes (§2.5.13), and a number under one other than the first, General, is written as the number stored, with a note counting them, as such a number may be a date the sheet would show as one. Text is taken only in plain ASCII: a byte beyond it is in a code page the reader doesn’t read, and the sheet is refused. A cell’s text that holds a comma or a double quote is quoted, its quotes doubled; a line break inside it becomes a space, as an .xlsx sheet’s does.

Rows and columns. Cells are placed by their row and column. Every row is written as wide as the sheet’s last column that holds a value; a row with no value is left out. A sheet with no value gives no text, and read says it has no data. A worksheet’s stream names no sheet, so the sheet’s name is empty.

Bounds. The cells must come in order, row by row and, within a row, column by column, as Excel writes them, so that a cell can’t be written twice; a column past IV, the 256th and last of a BIFF2 to BIFF5 sheet, is refused, as is a record longer than 8,224 bytes (BIFF8’s limit, §2.1.2), one that runs past the end, a stream with no EOF, or bytes after it, where a second stream would be passed over. The text written is held to MAX_TEXT_BYTES, each line counted before it is kept. A workbook of several sheets, as Excel 5 and later write inside a compound file, isn’t read yet.

use hpr_flightdata::xls;

// An invented worksheet stream: BOF, a header of two labels, two rows of numbers, EOF.
fn record(id: u16, data: &[u8]) -> Vec<u8> {
    let mut bytes = id.to_le_bytes().to_vec();
    bytes.extend((data.len() as u16).to_le_bytes());
    bytes.extend(data);
    bytes
}
fn cell(row: u16, column: u16) -> Vec<u8> {
    let mut bytes = row.to_le_bytes().to_vec();
    bytes.extend(column.to_le_bytes());
    bytes.extend([0, 0, 0]); // the cell's attributes: format 0, General
    bytes
}
let mut stream = record(0x0009, &[0, 0, 0x10, 0]);
for (column, name) in ["Time (s)", "Altitude (m)"].iter().enumerate() {
    let mut data = cell(0, column as u16);
    data.push(name.len() as u8);
    data.extend(name.as_bytes());
    stream.extend(record(0x0004, &data));
}
for (row, values) in [[0.0f64, 0.0], [0.05, 1.5]].iter().enumerate() {
    for (column, value) in values.iter().enumerate() {
        let mut data = cell(row as u16 + 1, column as u16);
        data.extend(value.to_le_bytes());
        stream.extend(record(0x0003, &data));
    }
}
stream.extend(record(0x000A, &[]));

let sheets = xls::sheets(&stream)?;
assert_eq!(sheets[0].text, "Time (s),Altitude (m)\n0,0\n0.05,1.5\n");
let log = xls::read(&stream)?.remove(0).log?;
// A table read by its header: each column a channel in its stated unit.
assert!(log.channels.iter().any(|c| c.column == "Altitude (m)" && c.values == [0.0, 1.5]));

Constants§

MAX_TEXT_BYTES
The most text taken from one stream, 256 MiB: workbook’s bound.

Functions§

read
The sheet of a bare worksheet stream, read as a log by import::read, as workbook::read reads an .xlsx workbook’s.
recognizes
Whether bytes open as a worksheet’s BIFF stream: a BOF record of any version, whole, whose type is a sheet, a chart or a macro sheet, or a workbook’s globals (§5.8). sheets reads only a worksheet’s.
sheets
The sheet of a bare worksheet stream as text; sheets_within with MAX_TEXT_BYTES.
sheets_within
The sheet of a bare worksheet stream as text, keeping at most max_text_bytes of it: one sheet, as the stream holds one.