use super::xls_formula::{self, Base, Book, BookKind, Context};
use crate::error::{Error, Result};
use crate::model::DefinedName;
use crate::model::{
CellValue, ColumnRun, Pane, PanePosition, PaneState, Selection, Spreadsheet, Worksheet,
};
use crate::shared::codepage;
use crate::shared::date::Epoch;
use crate::shared::palette;
use crate::style::{
Alignment, Border, BorderStyle, Borders, Color, DiagonalDirection, Fill, Font, HorizontalAlign,
NumberFormat, Pattern, Protection, ProtectionState, Script, Style, StyleId, StyleTable,
Underline, VerticalAlign,
};
use crate::{CellRef, Col, Range, Row};
use std::collections::HashMap;
mod record {
pub const BOF: u16 = 0x0809;
pub const EOF: u16 = 0x000A;
pub const CONTINUE: u16 = 0x003C;
pub const BOUNDSHEET: u16 = 0x0085;
pub const SST: u16 = 0x00FC;
pub const FORMAT: u16 = 0x041E;
pub const XF: u16 = 0x00E0;
pub const FONT: u16 = 0x0031;
pub const PALETTE: u16 = 0x0092;
pub const DATEMODE: u16 = 0x0022;
pub const FILEPASS: u16 = 0x002F;
pub const DIMENSION: u16 = 0x0200;
pub const ROW: u16 = 0x0208;
pub const COLINFO: u16 = 0x007D;
pub const WSBOOL: u16 = 0x0081;
pub const MERGEDCELLS: u16 = 0x00E5;
pub const BLANK: u16 = 0x0201;
pub const MULBLANK: u16 = 0x00BE;
pub const NUMBER: u16 = 0x0203;
pub const RK: u16 = 0x027E;
pub const MULRK: u16 = 0x00BD;
pub const LABEL: u16 = 0x0204;
pub const RSTRING: u16 = 0x00D6;
pub const CODEPAGE: u16 = 0x0042;
pub const LABELSST: u16 = 0x00FD;
pub const BOOLERR: u16 = 0x0205;
pub const FORMULA: u16 = 0x0006;
pub const STRING: u16 = 0x0207;
pub const SHRFMLA: u16 = 0x04BC;
pub const ARRAY: u16 = 0x0221;
pub const TABLE: u16 = 0x0236;
pub const NAME: u16 = 0x0018;
pub const SUPBOOK: u16 = 0x01AE;
pub const EXTERNNAME: u16 = 0x0023;
pub const EXTERNSHEET: u16 = 0x0017;
pub const WINDOW2: u16 = 0x023E;
pub const PANE: u16 = 0x0041;
pub const SELECTION: u16 = 0x001D;
}
pub fn read_xls(path: impl AsRef<std::path::Path>) -> Result<Spreadsheet> {
let bytes = std::fs::read(path).map_err(|e| Error::Xls(e.to_string()))?;
read_xls_from(&bytes)
}
pub fn read_xls_in(path: impl AsRef<std::path::Path>, page: u16) -> Result<Spreadsheet> {
let bytes = std::fs::read(path).map_err(|e| Error::Xls(e.to_string()))?;
read_xls_from_in(&bytes, page)
}
pub fn read_xls_from(bytes: &[u8]) -> Result<Spreadsheet> {
let ole = super::ole::Ole::new(bytes).map_err(Error::Xls)?;
let stream = workbook_stream(&ole)?;
Reader::new(&stream).read()
}
pub fn read_xls_from_in(bytes: &[u8], page: u16) -> Result<Spreadsheet> {
let ole = super::ole::Ole::new(bytes).map_err(Error::Xls)?;
let stream = workbook_stream(&ole)?;
let mut reader = Reader::new(&stream);
reader.codepage = page;
reader.forced_codepage = Some(page);
reader.read()
}
fn workbook_stream(ole: &super::ole::Ole<'_>) -> Result<Vec<u8>> {
["Workbook", "Book"]
.into_iter()
.find_map(|name| ole.stream(name))
.ok_or_else(|| {
Error::Xls(format!(
"no workbook stream; the file holds {}",
ole.names().collect::<Vec<_>>().join(", ")
))
})
}
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
enum Biff {
V5,
V8,
}
struct Record<'a> {
id: u16,
data: &'a [u8],
next: usize,
}
fn record_at(stream: &[u8], at: usize) -> Option<Record<'_>> {
let id = u16::from_le_bytes(stream.get(at..at + 2)?.try_into().ok()?);
let length = usize::from(u16::from_le_bytes(
stream.get(at + 2..at + 4)?.try_into().ok()?,
));
let start = at + 4;
Some(Record {
id,
data: stream.get(start..start + length)?,
next: start + length,
})
}
fn u16_at(data: &[u8], at: usize) -> u16 {
data.get(at..at + 2)
.and_then(|b| b.try_into().ok())
.map_or(0, u16::from_le_bytes)
}
fn u32_at(data: &[u8], at: usize) -> u32 {
data.get(at..at + 4)
.and_then(|b| b.try_into().ok())
.map_or(0, u32::from_le_bytes)
}
fn f64_at(data: &[u8], at: usize) -> f64 {
data.get(at..at + 8)
.and_then(|b| b.try_into().ok())
.map_or(0.0, f64::from_le_bytes)
}
struct Reader<'a> {
stream: &'a [u8],
biff: Biff,
codepage: u16,
forced_codepage: Option<u16>,
book: Spreadsheet,
styles: StyleTable,
strings: Vec<CellValue>,
formats: HashMap<u16, String>,
cell_formats: Vec<Xf>,
fonts: Vec<FontRecord>,
palette: [u32; 56],
style_ids: HashMap<u16, StyleId>,
sheets: Vec<(String, usize)>,
context: Context,
name_records: Vec<Vec<u8>>,
shared: HashMap<(u16, u16), Group>,
array: Option<Range>,
}
type Group = (Vec<u8>, Vec<u8>, bool);
impl<'a> Reader<'a> {
fn new(stream: &'a [u8]) -> Self {
Self {
stream,
biff: Biff::V8,
codepage: codepage::WINDOWS_1252,
forced_codepage: None,
book: Spreadsheet::empty(),
styles: StyleTable::default(),
strings: Vec::new(),
formats: HashMap::new(),
cell_formats: Vec::new(),
fonts: Vec::new(),
palette: palette::DEFAULT,
style_ids: HashMap::new(),
sheets: Vec::new(),
context: Context::default(),
name_records: Vec::new(),
shared: HashMap::new(),
array: None,
}
}
fn read(mut self) -> Result<Spreadsheet> {
self.globals()?;
let normal = self.cell_formats.get(15).or(self.cell_formats.first());
if let Some(xf) = normal.copied() {
self.styles = StyleTable::from_styles(vec![self.style(&xf)]);
}
self.context.sheets = self.sheets.iter().map(|(name, _)| name.clone()).collect();
self.defined_names();
let sheets = std::mem::take(&mut self.sheets);
for (name, at) in sheets {
self.shared.clear();
let sheet = self.sheet(&name, at)?;
self.book.add_sheet(sheet)?;
}
if self.book.sheets().is_empty() {
return Err(Error::Xls("the workbook has no sheets".to_owned()));
}
self.book.styles = self.styles;
Ok(self.book)
}
fn globals(&mut self) -> Result<()> {
let mut at = 0;
let Some(bof) = record_at(self.stream, at) else {
return Err(Error::Xls("the workbook stream is empty".to_owned()));
};
if bof.id != record::BOF {
return Err(Error::Xls(
"the workbook stream does not start with a BOF".to_owned(),
));
}
let version = u16_at(bof.data, 0);
self.biff = match version {
0x0600 => Biff::V8,
0x0500 => Biff::V5,
_ => {
return Err(Error::Xls(format!(
"BIFF version {version:#06x} is not supported; only BIFF5 (Excel 5 and 95) \
and BIFF8 (Excel 97 and later) are"
)));
}
};
if self.biff == Biff::V5 {
self.context.dialect = xls_formula::Dialect::Biff5;
self.context.books.push(Book {
kind: BookKind::Internal,
names: Vec::new(),
});
}
at = bof.next;
while let Some(record) = record_at(self.stream, at) {
at = record.next;
match record.id {
record::EOF => break,
record::FILEPASS => {
return Err(Error::Xls("the workbook is encrypted".to_owned()));
}
record::CODEPAGE => {
if self.forced_codepage.is_none() && self.biff == Biff::V5 {
let page = u16_at(record.data, 0);
if page != 0 && page != codepage::UTF16 {
self.codepage = page;
}
}
}
record::DATEMODE => {
if u16_at(record.data, 0) == 1 {
self.book.epoch = Epoch::Mac1904;
}
}
record::BOUNDSHEET => {
let position = u32_at(record.data, 0) as usize;
let name = short_string(record.data, 6, self.biff, self.codepage);
self.sheets.push((name, position));
}
record::FORMAT => {
let index = u16_at(record.data, 0);
let code = match self.biff {
Biff::V5 => short_string(record.data, 2, Biff::V5, self.codepage),
Biff::V8 => unicode_string(record.data, 2, Biff::V8, self.codepage).0,
};
self.formats.insert(index, code);
}
record::XF => self.cell_formats.push(Xf::parse(record.data, self.biff)),
record::FONT => {
let font = FontRecord::parse(record.data, self.biff, self.codepage);
self.fonts.push(font);
}
record::PALETTE => {
let count = usize::from(u16_at(record.data, 0)).min(56);
for slot in 0..count {
let bytes = record.data.get(2 + slot * 4..5 + slot * 4);
if let Some(&[r, g, b]) = bytes {
self.palette[slot] =
(u32::from(r) << 16) | (u32::from(g) << 8) | u32::from(b);
}
}
}
record::NAME => self.name_records.push(record.data.to_vec()),
record::SUPBOOK => self.context.books.push(supbook(record.data)),
record::EXTERNNAME => {
if let Some(book) = self.context.books.last_mut() {
book.names
.push(short_string(record.data, 6, self.biff, self.codepage));
}
}
record::EXTERNSHEET => self.extern_sheet(record.data),
record::SST => {
let (strings, next) = self.shared_strings(&record, at);
self.strings = strings;
at = next;
}
_ => {}
}
}
Ok(())
}
fn extern_sheet(&mut self, data: &[u8]) {
if self.biff == Biff::V5 {
let count = usize::from(data.first().copied().unwrap_or(0));
let name = bytes_string(data, 2, count, self.codepage);
let sheet = self
.sheets
.iter()
.position(|(known, _)| known == &name)
.and_then(|i| u16::try_from(i).ok())
.unwrap_or(u16::MAX);
self.context.externs.push((0, sheet, sheet));
return;
}
let count = usize::from(u16_at(data, 0));
for i in 0..count {
let at = 2 + i * 6;
if data.len() < at + 6 {
break;
}
self.context.externs.push((
u16_at(data, at),
u16_at(data, at + 2),
u16_at(data, at + 4),
));
}
}
fn shared_strings(&self, sst: &Record<'_>, mut at: usize) -> (Vec<CellValue>, usize) {
let unique = u32_at(sst.data, 4) as usize;
let mut data = sst.data[8.min(sst.data.len())..].to_vec();
let mut breaks = Vec::new();
while let Some(record) = record_at(self.stream, at) {
if record.id != record::CONTINUE {
break;
}
breaks.push(data.len());
data.extend_from_slice(record.data);
at = record.next;
}
let mut strings = Vec::with_capacity(unique.min(1 << 20));
let mut pos = 0;
for _ in 0..unique {
if pos >= data.len() {
break;
}
let Some((text, next)) = sst_string(&data, pos, &breaks) else {
break;
};
strings.push(CellValue::text(text));
pos = next;
}
(strings, at)
}
fn sheet(&mut self, name: &str, start: usize) -> Result<Worksheet> {
let mut sheet = Worksheet::new(name)?;
let mut at = start;
if let Some(bof) = record_at(self.stream, at)
&& bof.id == record::BOF
{
at = bof.next;
}
let mut window = 0u16;
while let Some(record) = record_at(self.stream, at) {
at = record.next;
match record.id {
record::EOF => break,
record::DIMENSION | record::BOF => {}
record::WINDOW2 => {
window = u16_at(record.data, 0);
let view = &mut sheet.view;
view.show_grid_lines = window & 0x0002 != 0;
view.show_row_col_headers = window & 0x0004 != 0;
view.show_zeros = window & 0x0010 != 0;
view.right_to_left = window & 0x0040 != 0;
let (top, left) = (u16_at(record.data, 2), u16_at(record.data, 4));
if (top, left) != (0, 0) {
view.top_left_cell = cell_ref(left, top).ok();
}
}
record::PANE => sheet.view.pane = Some(pane(record.data, window)),
record::SELECTION => {
let selection = selection(record.data, sheet.view.pane.is_some());
if !is_corner(&selection) {
sheet.view.selections.push(selection);
}
}
record::WSBOOL => {
let flags = u16_at(record.data, 0);
sheet.properties.summary_below = flags & 0x40 != 0;
sheet.properties.summary_right = flags & 0x80 != 0;
}
record::MERGEDCELLS => {
let count = usize::from(u16_at(record.data, 0));
for i in 0..count {
let at = 2 + i * 8;
if let (Ok(first), Ok(last)) = (
cell_ref(u16_at(record.data, at + 4), u16_at(record.data, at)),
cell_ref(u16_at(record.data, at + 6), u16_at(record.data, at + 2)),
) {
sheet.merges.push(Range::new(first, last));
}
}
}
record::COLINFO => {
let (first, last) = (u16_at(record.data, 0), u16_at(record.data, 2));
let width = f64::from(u16_at(record.data, 4)) / 256.0;
let options = u16_at(record.data, 8);
if let (Ok(first), Ok(last)) = (column(first), column(last)) {
let mut run = ColumnRun::new(first, last);
run.width = Some(width);
run.custom_width = true;
run.hidden = options & 1 != 0;
run.outline_level = u8::try_from((options >> 8) & 0x07).unwrap_or_default();
run.collapsed = options & 0x1000 != 0;
sheet.columns.push(run);
}
}
record::ROW => {
let flags = u32_at(record.data, 12);
let height = u16_at(record.data, 6);
if let Ok(row) = row(u16_at(record.data, 0)) {
let properties = sheet.rows.entry(row).or_default();
if height & 0x8000 == 0 {
properties.height = Some(f64::from(height) / 20.0);
properties.custom_height = true;
}
properties.hidden = flags & 0x20 != 0;
properties.outline_level = u8::try_from(flags & 0x07).unwrap_or_default();
properties.collapsed = flags & 0x10 != 0;
}
}
_ => self.cell_record(&mut sheet, &record, &mut at),
}
}
Ok(sheet)
}
fn cell_record(&mut self, sheet: &mut Worksheet, record: &Record<'_>, at: &mut usize) {
let data = record.data;
let (r, c) = (u16_at(data, 0), u16_at(data, 2));
match record.id {
record::BLANK => self.put(sheet, r, c, u16_at(data, 4), CellValue::Empty),
record::MULBLANK => {
let count = data.len().saturating_sub(6) / 2;
for i in 0..count {
let xf = u16_at(data, 4 + i * 2);
let Some(col) = u16::try_from(i).ok().and_then(|i| c.checked_add(i)) else {
break;
};
self.put(sheet, r, col, xf, CellValue::Empty);
}
}
record::NUMBER => self.put(
sheet,
r,
c,
u16_at(data, 4),
CellValue::Number(f64_at(data, 6)),
),
record::RK => self.put(
sheet,
r,
c,
u16_at(data, 4),
CellValue::Number(rk(u32_at(data, 6))),
),
record::MULRK => {
let count = data.len().saturating_sub(6) / 6;
for i in 0..count {
let at = 4 + i * 6;
let Some(col) = u16::try_from(i).ok().and_then(|i| c.checked_add(i)) else {
break;
};
self.put(
sheet,
r,
col,
u16_at(data, at),
CellValue::Number(rk(u32_at(data, at + 2))),
);
}
}
record::LABEL | record::RSTRING => {
let (text, _) = unicode_string(data, 6, self.biff, self.codepage);
self.put(sheet, r, c, u16_at(data, 4), CellValue::text(text));
}
record::LABELSST => {
let index = u32_at(data, 6) as usize;
let text = self.strings.get(index).cloned();
let text = text.unwrap_or_else(|| CellValue::text(""));
self.put(sheet, r, c, u16_at(data, 4), text);
}
record::BOOLERR => {
let value = data.get(6).copied().unwrap_or(0);
let is_error = data.get(7).copied().unwrap_or(0) == 1;
let value = if is_error {
CellValue::Error(xls_formula::error(value))
} else {
CellValue::Bool(value != 0)
};
self.put(sheet, r, c, u16_at(data, 4), value);
}
record::FORMULA => {
let formula = self.formula(data, r, c, at);
if let Some(area) = self.array.take() {
sheet.array_formulas.push(area);
}
let cached = self.formula_result(data, at);
let value = match formula {
Some(formula) => CellValue::Formula {
formula,
cached: Some(Box::new(cached)),
},
None => cached,
};
self.put(sheet, r, c, u16_at(data, 4), value);
}
_ => {}
}
}
fn formula(&mut self, data: &[u8], row: u16, col: u16, at: &mut usize) -> Option<String> {
if let Some(next) = record_at(self.stream, *at) {
let group = match next.id {
record::SHRFMLA => Some((10, true)),
record::ARRAY => Some((14, false)),
record::TABLE => Some((0, false)),
_ => None,
};
if let Some((start, relative)) = group {
*at = next.next;
let first = (u16_at(next.data, 0), u16::from(next.data.get(4).copied()?));
if next.id == record::ARRAY {
let corner = |r: u16, c: u8| {
Some(CellRef::new(column(c.into()).ok()?, self::row(r).ok()?))
};
self.array = corner(first.0, next.data.get(4).copied()?)
.zip(corner(u16_at(next.data, 2), next.data.get(5).copied()?))
.map(|(a, b)| Range::new(a, b));
}
if next.id != record::TABLE {
let length = usize::from(u16_at(next.data, start - 2));
let tokens = next.data.get(start..start + length)?.to_vec();
let extra = next.data.get(start + length..).unwrap_or(&[]).to_vec();
self.shared.insert(first, (tokens, extra, relative));
}
}
}
let length = usize::from(u16_at(data, 20));
let tokens = data.get(22..22 + length)?;
let extra = data.get(22 + length..).unwrap_or(&[]);
let base = Base {
row: row.into(),
col: col.into(),
};
if tokens.first() == Some(&0x01) {
let first = (u16_at(tokens, 1), u16_at(tokens, 3));
let (tokens, extra, relative) = self.shared.get(&first)?;
if !relative && (row, col) != first {
return None;
}
return xls_formula::decompile(tokens, extra, base, &self.context);
}
xls_formula::decompile(tokens, extra, base, &self.context)
}
fn defined_names(&mut self) {
let (biff, page) = (self.biff, self.codepage);
self.context.names = self
.name_records
.iter()
.map(|data| name_text(data, biff, page))
.collect();
for (data, name) in self.name_records.iter().zip(&self.context.names) {
let flags = u16_at(data, 0);
let length = usize::from(u16_at(data, 4));
if flags & 0x02 != 0 || name.starts_with("_xlfn.") || length == 0 {
continue;
}
let count = usize::from(data.get(3).copied().unwrap_or(0));
let start = match biff {
Biff::V5 => 14 + count,
Biff::V8 => {
let wide = data.get(14).copied().unwrap_or(0) & 1 != 0;
15 + count * if wide { 2 } else { 1 }
}
};
let Some(tokens) = data.get(start..start + length) else {
continue;
};
let extra = data.get(start + length..).unwrap_or(&[]);
let Some(formula) =
xls_formula::decompile(tokens, extra, Base::default(), &self.context)
else {
continue;
};
let sheet = u16_at(data, 8);
self.book.defined_names.push(DefinedName {
name: name.clone(),
sheet: sheet.checked_sub(1).map(usize::from),
formula,
hidden: flags & 0x01 != 0,
});
}
}
fn formula_result(&self, data: &[u8], at: &mut usize) -> CellValue {
if u16_at(data, 12) != 0xFFFF {
return CellValue::Number(f64_at(data, 6));
}
match data.get(6).copied().unwrap_or(3) {
0 => {
let Some(next) = record_at(self.stream, *at) else {
return CellValue::text("");
};
if next.id != record::STRING {
return CellValue::text("");
}
*at = next.next;
let (text, _) = unicode_string(next.data, 0, self.biff, self.codepage);
CellValue::text(text)
}
1 => CellValue::Bool(data.get(8).copied().unwrap_or(0) != 0),
2 => CellValue::Error(xls_formula::error(data.get(8).copied().unwrap_or(0))),
_ => CellValue::Empty,
}
}
fn put(&mut self, sheet: &mut Worksheet, row: u16, col: u16, xf: u16, value: CellValue) {
let (Ok(row), Ok(col)) = (self::row(row), column(col)) else {
return;
};
let style = self.style_of(xf);
let cell = sheet.entry(CellRef::new(col, row));
cell.value = value;
cell.style = style;
}
fn style_of(&mut self, xf: u16) -> StyleId {
if let Some(&id) = self.style_ids.get(&xf) {
return id;
}
let Some(record) = self.cell_formats.get(usize::from(xf)).copied() else {
return StyleId::default();
};
let style = self.style(&record);
let id = self.styles.intern(style);
self.style_ids.insert(xf, id);
id
}
fn style(&self, xf: &Xf) -> Style {
let number_format = match self.formats.get(&xf.format) {
Some(code) if code == "General" => NumberFormat::General,
Some(code) => NumberFormat::Custom(code.clone()),
None if xf.format == 0 => NumberFormat::General,
None => NumberFormat::Builtin(xf.format),
};
let font_record = match xf.font {
0..4 => self.fonts.get(usize::from(xf.font)),
4 => None,
_ => self.fonts.get(usize::from(xf.font) - 1),
};
let font = font_record.map_or_else(Font::default, |f| f.font(&self.palette));
let color = |index| palette::resolve(&self.palette, index);
let border = |style, index| Border {
style: border_style(style),
color: if style == 0 {
Color::Auto
} else {
color(index)
},
};
let diagonal_direction = match (xf.diagonal_down, xf.diagonal_up) {
(false, false) => DiagonalDirection::None,
(true, false) => DiagonalDirection::Down,
(false, true) => DiagonalDirection::Up,
(true, true) => DiagonalDirection::Both,
};
let pattern = fill_pattern(xf.pattern);
Style {
number_format,
font,
fill: Fill {
pattern,
foreground: if pattern == Pattern::None {
Color::Auto
} else {
color(xf.pattern_foreground)
},
background: if pattern == Pattern::None {
Color::Auto
} else {
color(xf.pattern_background)
},
},
borders: Borders {
left: border(xf.left, xf.left_color),
right: border(xf.right, xf.right_color),
top: border(xf.top, xf.top_color),
bottom: border(xf.bottom, xf.bottom_color),
diagonal: if diagonal_direction == DiagonalDirection::None {
Border::default()
} else {
border(xf.diagonal, xf.diagonal_color)
},
diagonal_direction,
},
alignment: Alignment {
horizontal: match xf.horizontal {
1 => HorizontalAlign::Left,
2 => HorizontalAlign::Center,
3 => HorizontalAlign::Right,
4 => HorizontalAlign::Fill,
5 => HorizontalAlign::Justify,
6 => HorizontalAlign::CenterContinuous,
7 => HorizontalAlign::Distributed,
_ => HorizontalAlign::General,
},
vertical: match xf.vertical {
0 => VerticalAlign::Top,
1 => VerticalAlign::Center,
3 => VerticalAlign::Justify,
4 => VerticalAlign::Distributed,
_ => VerticalAlign::Bottom,
},
wrap_text: xf.wrap,
shrink_to_fit: xf.shrink,
indent: xf.indent,
text_rotation: u32::from(xf.rotation),
reading_order: xf.reading_order,
},
protection: Protection {
locked: if xf.locked {
ProtectionState::Inherit
} else {
ProtectionState::Off
},
hidden: if xf.hidden {
ProtectionState::On
} else {
ProtectionState::Inherit
},
},
}
}
}
#[derive(Debug, Clone, Copy, Default)]
#[expect(
clippy::struct_excessive_bools,
reason = "each bool is one bit of the record, named after it"
)]
struct Xf {
font: u16,
format: u16,
locked: bool,
hidden: bool,
horizontal: u8,
wrap: bool,
vertical: u8,
rotation: u8,
indent: u32,
shrink: bool,
reading_order: u32,
left: u8,
right: u8,
top: u8,
bottom: u8,
diagonal: u8,
left_color: u16,
right_color: u16,
top_color: u16,
bottom_color: u16,
diagonal_color: u16,
diagonal_down: bool,
diagonal_up: bool,
pattern: u8,
pattern_foreground: u16,
pattern_background: u16,
}
impl Xf {
fn parse(data: &[u8], biff: Biff) -> Self {
if biff == Biff::V5 {
return Self::parse_biff5(data);
}
let byte = |at: usize| data.get(at).copied().unwrap_or(0);
let protection = u16_at(data, 4);
let align = byte(6);
let options = byte(8);
let sides = u32_at(data, 10);
let more = u32_at(data, 14);
let colors = u16_at(data, 18);
#[expect(
clippy::cast_possible_truncation,
reason = "every field is masked to its width first"
)]
let bits = |value: u32, shift: u32, mask: u32| ((value >> shift) & mask) as u16;
let nibble = |value: u32, shift: u32| ((value >> shift) & 0x0F) as u8;
Self {
font: u16_at(data, 0),
format: u16_at(data, 2),
locked: protection & 0x01 != 0,
hidden: protection & 0x02 != 0,
horizontal: align & 0x07,
wrap: align & 0x08 != 0,
vertical: (align >> 4) & 0x07,
rotation: byte(7),
indent: u32::from(options & 0x0F),
shrink: options & 0x10 != 0,
reading_order: u32::from(options >> 6),
left: nibble(sides, 0),
right: nibble(sides, 4),
top: nibble(sides, 8),
bottom: nibble(sides, 12),
left_color: bits(sides, 16, 0x7F),
right_color: bits(sides, 23, 0x7F),
diagonal_down: sides & (1 << 30) != 0,
diagonal_up: sides & (1 << 31) != 0,
top_color: bits(more, 0, 0x7F),
bottom_color: bits(more, 7, 0x7F),
diagonal_color: bits(more, 14, 0x7F),
diagonal: nibble(more, 21),
pattern: ((more >> 26) & 0x3F) as u8,
pattern_foreground: colors & 0x7F,
pattern_background: (colors >> 7) & 0x7F,
}
}
fn parse_biff5(data: &[u8]) -> Self {
let byte = |at: usize| data.get(at).copied().unwrap_or(0);
let protection = u16_at(data, 4);
let align = byte(6);
let fill = u32_at(data, 8);
let sides = u32_at(data, 12);
#[expect(
clippy::cast_possible_truncation,
reason = "every field is masked to its width first"
)]
let bits = |value: u32, shift: u32, mask: u32| ((value >> shift) & mask) as u16;
#[expect(
clippy::cast_possible_truncation,
reason = "every field is masked to its width first"
)]
let small = |value: u32, shift: u32, mask: u32| ((value >> shift) & mask) as u8;
Self {
font: u16_at(data, 0),
format: u16_at(data, 2),
locked: protection & 0x01 != 0,
hidden: protection & 0x02 != 0,
horizontal: align & 0x07,
wrap: align & 0x08 != 0,
vertical: (align >> 4) & 0x07,
rotation: match byte(7) & 0x03 {
1 => 255,
2 => 90,
3 => 180,
_ => 0,
},
indent: 0,
shrink: false,
reading_order: 0,
left: small(sides, 3, 0x07),
right: small(sides, 6, 0x07),
top: small(sides, 0, 0x07),
bottom: small(fill, 22, 0x07),
diagonal: 0,
left_color: bits(sides, 16, 0x7F),
right_color: bits(sides, 23, 0x7F),
top_color: bits(sides, 9, 0x7F),
bottom_color: bits(fill, 25, 0x7F),
diagonal_color: 0,
diagonal_down: false,
diagonal_up: false,
pattern: small(fill, 16, 0x3F),
pattern_foreground: bits(fill, 0, 0x7F),
pattern_background: bits(fill, 7, 0x7F),
}
}
}
#[derive(Debug, Clone, Default)]
struct FontRecord {
height: u16,
italic: bool,
strike: bool,
color: u16,
weight: u16,
escapement: u16,
underline: u8,
family: u8,
charset: u8,
name: String,
}
impl FontRecord {
fn parse(data: &[u8], biff: Biff, page: u16) -> Self {
let flags = u16_at(data, 2);
Self {
height: u16_at(data, 0),
italic: flags & 0x02 != 0,
strike: flags & 0x08 != 0,
color: u16_at(data, 4),
weight: u16_at(data, 6),
escapement: u16_at(data, 8),
underline: data.get(10).copied().unwrap_or(0),
family: data.get(11).copied().unwrap_or(0),
charset: data.get(12).copied().unwrap_or(0),
name: short_string(data, 14, biff, page),
}
}
fn font(&self, palette: &[u32; 56]) -> Font {
Font {
name: self.name.clone(),
size: u32::from(self.height) * 5,
bold: self.weight >= 600,
italic: self.italic,
underline: match self.underline {
0x01 => Underline::Single,
0x02 => Underline::Double,
0x21 => Underline::SingleAccounting,
0x22 => Underline::DoubleAccounting,
_ => Underline::None,
},
strike: self.strike,
color: palette::resolve(palette, self.color),
script: match self.escapement {
1 => Script::Superscript,
2 => Script::Subscript,
_ => Script::Baseline,
},
family: (self.family != 0).then_some(u32::from(self.family)),
charset: (self.charset != 0).then_some(u32::from(self.charset)),
scheme: None,
}
}
}
fn border_style(value: u8) -> BorderStyle {
BorderStyle::parse(match value {
1 => "thin",
2 => "medium",
3 => "dashed",
4 => "dotted",
5 => "thick",
6 => "double",
7 => "hair",
8 => "mediumDashed",
9 => "dashDot",
10 | 13 => "mediumDashDot",
11 => "dashDotDot",
12 => "mediumDashDotDot",
_ => "none",
})
}
fn fill_pattern(value: u8) -> Pattern {
Pattern::parse(match value {
1 => "solid",
2 => "mediumGray",
3 => "darkGray",
4 => "lightGray",
5 => "darkHorizontal",
6 => "darkVertical",
7 => "darkDown",
8 => "darkUp",
9 => "darkGrid",
10 => "darkTrellis",
11 => "lightHorizontal",
12 => "lightVertical",
13 => "lightDown",
14 => "lightUp",
15 => "lightGrid",
16 => "lightTrellis",
17 => "gray125",
18 => "gray0625",
_ => "none",
})
}
fn cell_ref(col: u16, row: u16) -> Result<CellRef> {
Ok(CellRef::new(column(col)?, self::row(row)?))
}
fn pane(data: &[u8], window: u16) -> Pane {
Pane {
x_split: u32::from(u16_at(data, 0)),
y_split: u32::from(u16_at(data, 2)),
top_left_cell: cell_ref(u16_at(data, 6), u16_at(data, 4)).ok(),
active_pane: pane_position(data.get(8).copied()),
state: match (window & 0x0008 != 0, window & 0x0100 != 0) {
(false, _) => PaneState::Split,
(true, false) => PaneState::FrozenSplit,
(true, true) => PaneState::Frozen,
},
}
}
fn pane_position(value: Option<u8>) -> PanePosition {
match value {
Some(1) => PanePosition::TopRight,
Some(2) => PanePosition::BottomLeft,
Some(3) => PanePosition::TopLeft,
_ => PanePosition::BottomRight,
}
}
fn selection(data: &[u8], split: bool) -> Selection {
let count = usize::from(u16_at(data, 7));
let sqref = (0..count)
.map_while(|i| {
let at = 9 + i * 6;
let (first, last) = (data.get(at + 4)?, data.get(at + 5)?);
Some(Range::new(
cell_ref(u16::from(*first), u16_at(data, at)).ok()?,
cell_ref(u16::from(*last), u16_at(data, at + 2)).ok()?,
))
})
.collect();
Selection {
pane: split.then(|| pane_position(data.first().copied())),
active_cell: cell_ref(u16_at(data, 3), u16_at(data, 1)).ok(),
sqref,
}
}
fn is_corner(selection: &Selection) -> bool {
let Ok(corner) = cell_ref(0, 0) else {
return false;
};
selection.active_cell == Some(corner)
&& selection
.sqref
.iter()
.all(|range| range.start == corner && range.end == corner)
}
fn row(value: u16) -> Result<Row> {
Row::from_one_based(u64::from(value) + 1)
}
fn column(value: u16) -> Result<Col> {
Col::from_one_based(u64::from(value) + 1)
}
fn name_text(data: &[u8], biff: Biff, page: u16) -> String {
let count = usize::from(data.get(3).copied().unwrap_or(0));
let text = match biff {
Biff::V5 => bytes_string(data, 14, count, page),
Biff::V8 => {
let wide = data.get(14).copied().unwrap_or(0) & 1 != 0;
read_chars(data, 15, count, wide).0
}
};
if u16_at(data, 0) & 0x20 == 0 {
return text;
}
let builtin = match text.chars().next().map_or(0xFF, u32::from) {
0x00 => "Consolidate_Area",
0x01 => "Auto_Open",
0x02 => "Auto_Close",
0x03 => "Extract",
0x04 => "Database",
0x05 => "Criteria",
0x06 => "Print_Area",
0x07 => "Print_Titles",
0x08 => "Recorder",
0x09 => "Data_Form",
0x0A => "Auto_Activate",
0x0B => "Auto_Deactivate",
0x0C => "Sheet_Title",
0x0D => "_FilterDatabase",
_ => return text,
};
format!("_xlnm.{builtin}")
}
fn supbook(data: &[u8]) -> Book {
let sheets = usize::from(u16_at(data, 0));
let kind = match u16_at(data, 2) {
0x0401 => BookKind::Internal,
0x3A01 => BookKind::AddIn,
_ => {
let (path, mut at) = unicode_string(data, 2, Biff::V8, codepage::UTF16);
let mut names = Vec::with_capacity(sheets.min(1024));
for _ in 0..sheets {
let (name, next) = unicode_string(data, at, Biff::V8, codepage::UTF16);
names.push(name);
at = next;
}
BookKind::External {
path: decode_path(&path),
sheets: names,
}
}
};
Book {
kind,
names: Vec::new(),
}
}
fn decode_path(path: &str) -> String {
path.rsplit(|c: char| c.is_control() || c == '\\' || c == '/')
.next()
.unwrap_or(path)
.to_owned()
}
fn rk(value: u32) -> f64 {
let number = if value & 0x02 != 0 {
#[expect(clippy::cast_possible_wrap)]
f64::from((value as i32) >> 2)
} else {
f64::from_bits(u64::from(value & 0xFFFF_FFFC) << 32)
};
if value & 0x01 != 0 {
number / 100.0
} else {
number
}
}
fn short_string(data: &[u8], at: usize, biff: Biff, page: u16) -> String {
let count = usize::from(data.get(at).copied().unwrap_or(0));
match biff {
Biff::V5 => bytes_string(data, at + 1, count, page),
Biff::V8 => {
let wide = data.get(at + 1).copied().unwrap_or(0) & 1 != 0;
read_chars(data, at + 2, count, wide).0
}
}
}
fn unicode_string(data: &[u8], at: usize, biff: Biff, page: u16) -> (String, usize) {
let count = usize::from(u16_at(data, at));
match biff {
Biff::V5 => (bytes_string(data, at + 2, count, page), at + 2 + count),
Biff::V8 => {
let wide = data.get(at + 2).copied().unwrap_or(0) & 1 != 0;
read_chars(data, at + 3, count, wide)
}
}
}
fn bytes_string(data: &[u8], at: usize, count: usize, page: u16) -> String {
let end = at.saturating_add(count).min(data.len());
codepage::decode(page, data.get(at..end).unwrap_or(&[]))
}
fn read_chars(data: &[u8], at: usize, count: usize, wide: bool) -> (String, usize) {
let mut units = Vec::with_capacity(count.min(1 << 16));
let mut pos = at;
for _ in 0..count {
if wide {
units.push(u16_at(data, pos));
pos += 2;
} else {
match data.get(pos) {
Some(&b) => units.push(u16::from(b)),
None => break,
}
pos += 1;
}
if pos > data.len() {
break;
}
}
let text = char::decode_utf16(units)
.map(|c| c.unwrap_or(char::REPLACEMENT_CHARACTER))
.collect();
(text, pos)
}
fn sst_string(data: &[u8], at: usize, breaks: &[usize]) -> Option<(String, usize)> {
let count = usize::from(u16_at(data, at));
let flags = data.get(at + 2).copied()?;
let mut pos = at + 3;
let rich_runs = if flags & 0x08 != 0 {
let runs = usize::from(u16_at(data, pos));
pos += 2;
runs
} else {
0
};
let extended = if flags & 0x04 != 0 {
let length = u32_at(data, pos) as usize;
pos += 4;
length
} else {
0
};
let mut wide = flags & 0x01 != 0;
let mut units = Vec::with_capacity(count.min(1 << 16));
let mut next = breaks.partition_point(|&b| b < pos);
for _ in 0..count {
skip_break(data, &mut pos, breaks, &mut next, &mut wide);
let here = wide;
let low = take_byte(data, &mut pos, breaks, &mut next, &mut wide)?;
let high = if here {
take_byte(data, &mut pos, breaks, &mut next, &mut wide)?
} else {
0
};
units.push(u16::from_le_bytes([low, high]));
}
pos = pos.saturating_add(rich_runs * 4).saturating_add(extended);
let text = char::decode_utf16(units)
.map(|c| c.unwrap_or(char::REPLACEMENT_CHARACTER))
.collect();
Some((text, pos))
}
fn skip_break(data: &[u8], pos: &mut usize, breaks: &[usize], next: &mut usize, wide: &mut bool) {
while breaks.get(*next).is_some_and(|&b| b < *pos) {
*next += 1;
}
if breaks.get(*next) == Some(pos) {
*wide = data.get(*pos).copied().unwrap_or(0) & 1 != 0;
*pos += 1;
*next += 1;
}
}
fn take_byte(
data: &[u8],
pos: &mut usize,
breaks: &[usize],
next: &mut usize,
wide: &mut bool,
) -> Option<u8> {
skip_break(data, pos, breaks, next, wide);
let byte = data.get(*pos).copied()?;
*pos += 1;
Some(byte)
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn a_run_of_cells_past_the_last_column_stops_at_the_edge() {
let mut globals = Vec::new();
push(&mut globals, record::BOF, &[0x00, 0x06, 0x05, 0x00]);
let boundsheet_at = globals.len() + 4;
push(
&mut globals,
record::BOUNDSHEET,
&[0, 0, 0, 0, 0, 0, 1, 0, b'S'],
);
push(&mut globals, record::EOF, &[]);
let start = u32::try_from(globals.len()).unwrap();
globals[boundsheet_at..boundsheet_at + 4].copy_from_slice(&start.to_le_bytes());
push(&mut globals, record::BOF, &[0x00, 0x06, 0x10, 0x00]);
let mut mulrk = vec![0, 0, 0xFE, 0xFF];
for _ in 0..3 {
mulrk.extend_from_slice(&[0, 0, 0x02, 0x01, 0, 0]);
}
mulrk.extend_from_slice(&[0x00, 0x01]);
push(&mut globals, record::MULRK, &mulrk);
let mut mulblank = vec![1, 0, 0xFE, 0xFF, 0, 0, 0, 0, 0, 0];
mulblank.extend_from_slice(&[0x00, 0x01]);
push(&mut globals, record::MULBLANK, &mulblank);
push(&mut globals, record::EOF, &[]);
assert!(Reader::new(&globals).read().is_ok());
}
#[test]
fn rk_numbers_decode_all_four_ways() {
assert!(
(rk(0x3FF0_0000) - 1.0).abs() < f64::EPSILON,
"a double's top half"
);
assert!(
(rk(0x3FF0_0001) - 0.01).abs() < f64::EPSILON,
"the same, hundredths"
);
assert!((rk(0x0000_0102) - 64.0).abs() < f64::EPSILON, "an integer");
assert!(
(rk(0x0000_0103) - 0.64).abs() < f64::EPSILON,
"an integer in hundredths"
);
assert!(
(rk(0xFFFF_FFFE) - -1.0).abs() < f64::EPSILON,
"a negative integer"
);
}
#[test]
fn a_shared_string_survives_a_break_inside_a_character() {
let mut data = vec![3, 0, 0x01, 0x30, 0x04, 0x31, 0x04, 0x32];
let boundary = data.len();
data.extend_from_slice(&[0x01, 0x04]);
let (text, end) = sst_string(&data, 0, &[boundary]).expect("the string parses");
assert_eq!(text, "абв");
assert_eq!(end, data.len());
}
#[test]
fn a_shared_string_can_change_width_at_a_break() {
let mut data = vec![4, 0, 0x01, 0x30, 0x04, 0x31, 0x04, 0x32, 0x04];
let boundary = data.len();
data.extend_from_slice(&[0x00, b'x']);
let (text, _) = sst_string(&data, 0, &[boundary]).expect("the string parses");
assert_eq!(text, "абвx");
}
#[test]
fn a_shared_string_crosses_an_empty_continuation_and_another_break() {
let mut data = vec![4, 0, 0x00, b'a', b'b'];
let first = data.len();
data.extend_from_slice(&[0x01, 0x32, 0x04]);
let second = data.len();
data.extend_from_slice(&[0x00, b'c']);
let breaks = [first, first, second];
let (text, end) = sst_string(&data, 0, &breaks).expect("the string parses");
assert_eq!(text, "abвc");
assert_eq!(end, data.len());
}
fn push(out: &mut Vec<u8>, id: u16, data: &[u8]) {
out.extend_from_slice(&id.to_le_bytes());
out.extend_from_slice(&u16::try_from(data.len()).unwrap().to_le_bytes());
out.extend_from_slice(data);
}
fn formula(row: u8, col: u8, result: f64, tokens: &[u8]) -> Vec<u8> {
let mut data = vec![row, 0, col, 0, 0, 0];
data.extend_from_slice(&result.to_le_bytes());
data.extend_from_slice(&[0x08, 0, 0, 0, 0, 0]);
data.extend_from_slice(&u16::try_from(tokens.len()).unwrap().to_le_bytes());
data.extend_from_slice(tokens);
data
}
#[test]
fn shared_and_array_formulas_and_names_read_back_as_text() {
let mut globals = Vec::new();
push(&mut globals, record::BOF, &[0x00, 0x06, 0x05, 0x00]);
push(&mut globals, record::SUPBOOK, &[1, 0, 0x01, 0x04]);
push(&mut globals, record::EXTERNSHEET, &[1, 0, 0, 0, 0, 0, 0, 0]);
let mut name = vec![0, 0, 0, 4, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0];
name.extend_from_slice(b"Rate");
name.extend_from_slice(&[0x3A, 0, 0, 0, 0, 0, 0]);
push(&mut globals, record::NAME, &name);
let boundsheet_at = globals.len() + 4;
push(
&mut globals,
record::BOUNDSHEET,
&[0, 0, 0, 0, 0, 0, 4, 0, b'M', b'a', b'i', b'n'],
);
push(&mut globals, record::EOF, &[]);
let start = u32::try_from(globals.len()).unwrap();
globals[boundsheet_at..boundsheet_at + 4].copy_from_slice(&start.to_le_bytes());
let mut sheet = Vec::new();
push(&mut sheet, record::BOF, &[0x00, 0x06, 0x10, 0x00]);
let exp = [0x01, 1, 0, 0, 0];
push(&mut sheet, record::FORMULA, &formula(1, 0, 2.0, &exp));
let mut shared = vec![1, 0, 2, 0, 0, 0, 0, 2, 9, 0];
shared.extend_from_slice(&[0x4C, 0xFF, 0xFF, 0x00, 0xC0, 0x1E, 2, 0, 0x05]);
push(&mut sheet, record::SHRFMLA, &shared);
push(&mut sheet, record::FORMULA, &formula(2, 0, 4.0, &exp));
let array_exp = [0x01, 0, 0, 1, 0];
push(&mut sheet, record::FORMULA, &formula(0, 1, 1.0, &array_exp));
let mut array = vec![0, 0, 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 5, 0];
array.extend_from_slice(&[0x43, 1, 0, 0, 0]);
push(&mut sheet, record::ARRAY, &array);
push(&mut sheet, record::FORMULA, &formula(1, 1, 1.0, &array_exp));
push(&mut sheet, record::EOF, &[]);
globals.extend_from_slice(&sheet);
let book = Reader::new(&globals).read().unwrap();
let text = |at: &str| match &book.sheets()[0].get(CellRef::parse(at).unwrap())?.value {
CellValue::Formula { formula, .. } => Some(formula.clone()),
_ => None,
};
assert_eq!(text("A2").as_deref(), Some("A1*2"));
assert_eq!(text("A3").as_deref(), Some("A2*2"));
assert_eq!(text("B1").as_deref(), Some("Rate"));
assert_eq!(text("B2"), None, "the rest of an array keeps its value");
assert_eq!(
book.sheets()[0].array_formulas,
vec![Range::parse("B1:B2").unwrap()]
);
assert_eq!(
book.sheets()[0]
.get(CellRef::parse("B2").unwrap())
.map(|c| c.value.clone()),
Some(CellValue::Number(1.0))
);
let rate = &book.defined_names[0];
assert_eq!(
(rate.name.as_str(), rate.formula.as_str()),
("Rate", "Main!$A$1")
);
}
#[test]
fn error_codes_are_the_ones_biff_numbers() {
use crate::CellError;
assert_eq!(xls_formula::error(0x07), CellError::Div0);
assert_eq!(xls_formula::error(0x17), CellError::Ref);
assert_eq!(xls_formula::error(0x2A), CellError::Na);
}
#[cfg(feature = "write")]
fn biff5_with(page: Option<u16>, byte: u8) -> Vec<u8> {
let mut globals = Vec::new();
push(&mut globals, record::BOF, &[0x00, 0x05, 0x05, 0x00]);
if let Some(page) = page {
push(&mut globals, record::CODEPAGE, &page.to_le_bytes());
}
let boundsheet_at = globals.len() + 4;
push(
&mut globals,
record::BOUNDSHEET,
&[0, 0, 0, 0, 0, 0, 1, b'S'],
);
push(&mut globals, record::EOF, &[]);
let start = u32::try_from(globals.len()).unwrap();
globals[boundsheet_at..boundsheet_at + 4].copy_from_slice(&start.to_le_bytes());
push(&mut globals, record::BOF, &[0x00, 0x05, 0x10, 0x00]);
push(&mut globals, record::LABEL, &[0, 0, 0, 0, 0, 0, 1, 0, byte]);
push(&mut globals, record::EOF, &[]);
crate::writer::ole::container("Workbook", &globals)
}
#[cfg(feature = "write")]
#[test]
fn biff5_text_is_read_in_the_workbook_code_page() {
let at = CellRef::parse("A1").unwrap();
let text = |bytes: &[u8], page: Option<u16>| {
let book = match page {
Some(page) => read_xls_from_in(bytes, page),
None => read_xls_from(bytes),
}
.unwrap();
match &book.sheets()[0].get(at).unwrap().value {
CellValue::Text(text) => text.to_string(),
other => panic!("A1 is {other:?}"),
}
};
assert_eq!(
text(&biff5_with(Some(codepage::WINDOWS_1252), 0xC0), None),
"À"
);
assert_eq!(
text(&biff5_with(Some(codepage::WINDOWS_1251), 0xC0), None),
"А"
);
assert_eq!(text(&biff5_with(None, 0xC0), None), "À");
assert_eq!(
text(&biff5_with(None, 0xC0), Some(codepage::WINDOWS_1251)),
"А"
);
assert_eq!(
text(
&biff5_with(Some(codepage::WINDOWS_1252), 0xC0),
Some(codepage::WINDOWS_1251)
),
"А"
);
}
}