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:
| Record | Identifier | The cell | Written as |
|---|---|---|---|
| NUMBER (§5.71) | 0003 | a 64-bit IEEE 754 number | its shortest decimal that reads back as the same double, such as 0.005 |
| INTEGER (§5.60) | 0002 | an unsigned 16-bit integer | its digits |
| LABEL (§5.63) | 0004 | text, 8-bit, its length in one byte | its text |
| BOOLERR (§5.10) | 0005 | a truth value, or an error such as #DIV/0! | TRUE or FALSE; an error is a gap |
| BLANK (§5.7) | 0001 | a cell formatted but empty | nothing |
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, asworkbook::readreads an.xlsxworkbook’s. - recognizes
- Whether
bytesopen 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).sheetsreads only a worksheet’s. - sheets
- The sheet of a bare worksheet stream as text;
sheets_withinwithMAX_TEXT_BYTES. - sheets_
within - The sheet of a bare worksheet stream as text, keeping at most
max_text_bytesof it: one sheet, as the stream holds one.