//! `.xlsb` (BIFF12 / Excel binary workbook) reading.
//!
//! A `.xlsb` is a ZIP of **binary** parts — `xl/workbook.bin`, `xl/sharedStrings.bin`,
//! `xl/styles.bin`, `xl/worksheets/sheetN.bin` — the same package shape as `.xlsx`
//! but with BIFF12 record streams instead of XML. A record is
//! `[recordType: var-uint][recordSize: var-uint][payload]`. This module decodes the
//! shared strings, number formats (for date detection), merged ranges, and the
//! common cell records into the shared [`Cell`] model, reusing the `.xlsx` ZIP
//! plumbing and the [`crate::format`] classifier. Panic-free / bounds-checked.
//!
//! Reference: [MS-XLSB]. Formula token decompilation is best-effort (cached values
//! are still read); cell `.bin` records beyond the common value kinds are skipped.
use std::collections::{BTreeMap, BTreeSet, HashMap, HashSet};
use std::io::Read;
use crate::error::{Error, Result};
use crate::{
format, rk_to_f64, Cell, CellEntry, Color, Comment, DataValidation, DvKind, DvOp, PageSetup,
ProtectionOptions, Sheet, SheetType, Table, Workbook,
};
// BIFF12 record type ids ([MS-XLSB] 2.4).
const BRT_ROW_HDR: u32 = 0;
const BRT_CELL_RK: u32 = 2;
const BRT_CELL_ERROR: u32 = 3;
const BRT_CELL_BOOL: u32 = 4;
const BRT_CELL_REAL: u32 = 5;
const BRT_CELL_ST: u32 = 6;
const BRT_CELL_ISST: u32 = 7;
const BRT_FMLA_STRING: u32 = 8;
const BRT_FMLA_NUM: u32 = 9;
const BRT_FMLA_BOOL: u32 = 10;
const BRT_FMLA_ERROR: u32 = 11;
const BRT_SST_ITEM: u32 = 19;
const BRT_NAME: u32 = 39;
const BRT_FMT: u32 = 44;
const BRT_XF: u32 = 47;
const BRT_COL_INFO: u32 = 60;
const BRT_DVAL: u32 = 64;
const BRT_BEGIN_WS_VIEW: u32 = 137;
const BRT_END_WS_VIEW: u32 = 138;
const BRT_WS_PROP: u32 = 147;
const BRT_PANE: u32 = 151;
const BRT_BEGIN_AFILTER: u32 = 161;
const BRT_BUNDLE_SH: u32 = 156;
const BRT_BOOK_VIEW: u32 = 158;
const BRT_WB_PROP: u32 = 153; // 0x99 — workbook properties, carries the 1904 flag
const BRT_MERGE_CELL: u32 = 176;
const BRT_BEGIN_LIST: u32 = 288;
const BRT_BEGIN_LIST_COL: u32 = 291;
const BRT_MARGINS: u32 = 475;
const BRT_PRINT_OPTIONS: u32 = 476;
const BRT_PAGE_SETUP: u32 = 477;
const BRT_BEGIN_HEADER_FOOTER: u32 = 478;
const BRT_HLINK: u32 = 0x01EE;
const BRT_BOOK_PROTECTION: u32 = 534;
const BRT_SHEET_PROTECTION: u32 = 535;
const BRT_LIST_PART: u32 = 550;
const BRT_COMMENT_AUTHOR: u32 = 632;
const BRT_BEGIN_COMMENT: u32 = 635;
const BRT_END_COMMENT: u32 = 636;
const BRT_COMMENT_TEXT: u32 = 637;
const BRT_TABLE_STYLE_CLIENT: u32 = 649;
const BRT_DVAL_LIST: u32 = 681;
const BRT_BEGIN_CELL_XFS: u32 = 0x0269;
const BRT_END_CELL_XFS: u32 = 0x026A;
const MAX_DVAL_RANGES: usize = 8192;
const MAX_TABLE_COLUMNS: usize = 16_384;
const MAX_XLSB_COL_INDEX: u32 = 16_383;
const MAX_XLSB_ROW_INDEX: u32 = 1_048_575;
/// A cursor over one BIFF12 record stream that yields `(record_type, payload)`,
/// bounded by the buffer — a hostile size never reads past the end.
struct RecReader<'a> {
b: &'a [u8],
pos: usize,
}
impl<'a> RecReader<'a> {
fn new(b: &'a [u8]) -> Self {
RecReader { b, pos: 0 }
}
/// Read a variable-width unsigned int of up to `max_bytes` (7 bits each, high
/// bit = continue).
fn var(&mut self, max_bytes: usize) -> Option<u32> {
let mut val: u32 = 0;
for i in 0..max_bytes {
let byte = *self.b.get(self.pos)?;
self.pos += 1;
val |= u32::from(byte & 0x7F) << (7 * i);
if byte & 0x80 == 0 {
break;
}
}
Some(val)
}
/// Next record as `(type, payload)`, or `None` at end / on truncation.
fn next(&mut self) -> Option<(u32, &'a [u8])> {
if self.pos >= self.b.len() {
return None;
}
let rt = self.var(2)?;
let sz = self.var(4)? as usize;
let start = self.pos;
let end = start.checked_add(sz)?;
if end > self.b.len() {
return None;
}
self.pos = end;
Some((rt, &self.b[start..end]))
}
}
fn u16le(b: &[u8], o: usize) -> Option<u16> {
b.get(o..o + 2).map(|s| u16::from_le_bytes([s[0], s[1]]))
}
fn u32le(b: &[u8], o: usize) -> Option<u32> {
b.get(o..o + 4)
.map(|s| u32::from_le_bytes([s[0], s[1], s[2], s[3]]))
}
fn i32le(b: &[u8], o: usize) -> Option<i32> {
b.get(o..o + 4)
.map(|s| i32::from_le_bytes([s[0], s[1], s[2], s[3]]))
}
fn f64le(b: &[u8], o: usize) -> Option<f64> {
b.get(o..o + 8)
.and_then(|s| s.try_into().ok())
.map(f64::from_le_bytes)
}
/// An `XLWideString`: `cch: u32` then `cch` UTF-16LE code units. Returns the
/// string and the byte length consumed.
fn wide_string(b: &[u8], o: usize) -> Option<(String, usize)> {
let cch = u32le(b, o)? as usize;
let bytes = cch.checked_mul(2)?;
let chars = b.get(o + 4..o + 4 + bytes)?;
let units: Vec<u16> = chars
.chunks_exact(2)
.map(|c| u16::from_le_bytes([c[0], c[1]]))
.collect();
Some((String::from_utf16_lossy(&units), 4 + bytes))
}
/// Detect `.xlsb` by the presence of `xl/workbook.bin` in the ZIP.
pub(crate) fn is_xlsb(bytes: &[u8]) -> bool {
zip::ZipArchive::new(std::io::Cursor::new(bytes))
.map(|mut z| part_index(&mut z, "xl/workbook.bin").is_some())
.unwrap_or(false)
}
fn part(zip: &mut zip::ZipArchive<std::io::Cursor<&[u8]>>, name: &str) -> Option<Vec<u8>> {
const MAX_PART: u64 = 256 << 20;
let idx = part_index(zip, name)?;
let f = zip.by_index(idx).ok()?;
let mut v = Vec::new();
f.take(MAX_PART).read_to_end(&mut v).ok()?;
Some(v)
}
fn part_index(zip: &mut zip::ZipArchive<std::io::Cursor<&[u8]>>, name: &str) -> Option<usize> {
if let Some(idx) = zip.index_for_name(name) {
return Some(idx);
}
let wanted = canonical_part_name(name);
for idx in 0..zip.len() {
let Ok(file) = zip.by_index(idx) else {
continue;
};
if canonical_part_name(file.name()).eq_ignore_ascii_case(&wanted) {
return Some(idx);
}
}
None
}
fn canonical_part_name(name: &str) -> String {
name.replace('\\', "/").trim_start_matches('/').to_string()
}
pub(crate) fn open(bytes: &[u8]) -> Result<Workbook> {
let mut zip = zip::ZipArchive::new(std::io::Cursor::new(bytes))
.map_err(|_| Error::Zip("not a valid .xlsb ZIP container"))?;
let shared = part(&mut zip, "xl/sharedStrings.bin")
.map(|b| parse_shared_strings(&b))
.unwrap_or_default();
let styles = part(&mut zip, "xl/styles.bin")
.map(|b| parse_styles(&b))
.unwrap_or_default();
let properties = crate::xlsx::parse_doc_properties(
crate::xlsx::part_str(&mut zip, "docProps/core.xml").as_deref(),
crate::xlsx::part_str(&mut zip, "docProps/app.xml").as_deref(),
);
let workbook_rels_xml =
crate::xlsx::part_str(&mut zip, "xl/_rels/workbook.bin.rels").unwrap_or_default();
let rels = crate::xlsx::parse_rels(&workbook_rels_xml);
let rel_types = crate::xlsx::parse_rel_types(&workbook_rels_xml);
let (names, date1904, active_sheet, defined_names, protect_structure, sheet_builtin_names) =
match part(&mut zip, "xl/workbook.bin") {
Some(b) => parse_workbook(&b),
None => return Err(Error::MissingWorkbook),
};
let mut budget = crate::MAX_TEXT_BYTES;
let mut sheets = Vec::with_capacity(names.len().min(1 << 16));
let mut selected_sheet_fallback = None;
for (sheet_index, (name, rid, hs_state)) in names.into_iter().enumerate() {
let sheet_type = xlsb_sheet_type(&rid, hs_state, rel_types.get(&rid).map(String::as_str));
let is_worksheet = sheet_type == SheetType::WorkSheet;
let target = rels.get(&rid).cloned().unwrap_or_default();
let path = normalize_target(&target);
let sheet_rels_xml = if is_worksheet {
crate::xlsx::part_str(&mut zip, &sheet_rels_path(&path)).unwrap_or_default()
} else {
String::new()
};
let sheet_rels = if is_worksheet && !sheet_rels_xml.is_empty() {
crate::xlsx::parse_rels(&sheet_rels_xml)
} else {
HashMap::new()
};
let comments = if is_worksheet {
parse_sheet_comments(&mut zip, &path, &sheet_rels, &sheet_rels_xml)
} else {
Vec::new()
};
let (cells, merges, read_hyperlinks, metadata) = if is_worksheet {
part(&mut zip, &path)
.map(|b| parse_sheet(&b, &shared, &styles, date1904, &sheet_rels, &mut budget))
.unwrap_or_default()
} else {
(
Vec::new(),
Vec::new(),
Vec::new(),
SheetReadMetadata::default(),
)
};
if is_worksheet && metadata.selected && selected_sheet_fallback.is_none() {
selected_sheet_fallback = Some(sheet_index);
}
let tables = if is_worksheet {
parse_sheet_tables(
&mut zip,
&path,
&sheet_rels,
&sheet_rels_xml,
&metadata.table_rel_ids,
)
} else {
Vec::new()
};
sheets.push(Sheet {
name,
is_worksheet,
sheet_type: Some(sheet_type),
cells,
read_merges: merges,
read_hyperlinks,
comments,
tables,
freeze: metadata.freeze,
autofilter: metadata.autofilter,
data_validations: metadata.data_validations,
page_setup: metadata.page_setup,
tab_color: metadata.tab_color,
print_gridlines: metadata.print_gridlines,
print_headings: metadata.print_headings,
hide_gridlines: metadata.hide_gridlines,
zoom: metadata.zoom,
show_headers: metadata.show_headers,
right_to_left: metadata.right_to_left,
protect: metadata.protect,
protect_options: metadata.protect_options,
row_outline: metadata.row_outline,
col_outline: metadata.col_outline,
outline_summary_below: metadata.outline_summary_below.unwrap_or(true),
outline_summary_right: metadata.outline_summary_right.unwrap_or(true),
collapsed_rows: metadata.collapsed_rows,
// hsState: 0 = visible, 1 = hidden, 2 = veryHidden ([MS-XLSB] 2.4.301).
hidden: hs_state == 1,
very_hidden: hs_state == 2,
..Default::default()
});
}
apply_xlsb_sheet_builtin_names(&mut sheets, sheet_builtin_names);
Ok(Workbook {
sheets,
properties,
defined_names,
date1904,
active_sheet: active_sheet.or(selected_sheet_fallback).unwrap_or_default(),
text_truncated: budget == 0,
protect_structure,
..Default::default()
})
}
fn xlsb_sheet_type(rid: &str, hs_state: u32, rel_type: Option<&str>) -> SheetType {
if rid.is_empty() && hs_state == 2 {
return SheetType::Vba;
}
let kind = rel_type
.and_then(|ty| ty.rsplit('/').next())
.unwrap_or("worksheet");
match kind.to_ascii_lowercase().as_str() {
"chartsheet" => SheetType::ChartSheet,
"dialogsheet" => SheetType::DialogSheet,
"macrosheet" | "xlmacrosheet" | "xlintlmacrosheet" => SheetType::MacroSheet,
_ => SheetType::WorkSheet,
}
}
fn normalize_target(target: &str) -> String {
let t = target.trim_start_matches('/');
if t.starts_with("xl/") {
t.to_string()
} else {
format!("xl/{t}")
}
}
fn sheet_rels_path(path: &str) -> String {
match path.rsplit_once('/') {
Some((dir, file)) => format!("{dir}/_rels/{file}.rels"),
None => format!("_rels/{path}.rels"),
}
}
fn normalize_part_target(base: &str, target: &str) -> String {
if let Some(abs) = target.strip_prefix('/') {
return abs.to_string();
}
let mut dir: Vec<&str> = match base.rfind('/') {
Some(i) => base[..i].split('/').collect(),
None => Vec::new(),
};
for seg in target.split('/') {
match seg {
"" | "." => {}
".." => {
dir.pop();
}
other => dir.push(other),
}
}
dir.join("/")
}
/// Number formats from `styles.bin` (for date detection), shared with `.xlsx`.
#[derive(Default)]
struct Styles {
xf_numfmt: Vec<u16>,
custom: HashMap<u16, String>,
}
impl Styles {
fn kind(&self, style_idx: usize) -> format::Kind {
let id = self.xf_numfmt.get(style_idx).copied().unwrap_or(0);
format::classify(id, self.custom.get(&id).map(String::as_str))
}
}
fn parse_styles(b: &[u8]) -> Styles {
let mut s = Styles::default();
let mut r = RecReader::new(b);
let mut in_cell_xfs = false;
while let Some((rt, p)) = r.next() {
match rt {
BRT_FMT => {
// ifmt:u16, stFmtCode: XLWideString.
if let (Some(ifmt), Some((code, _))) = (u16le(p, 0), wide_string(p, 2)) {
s.custom.insert(ifmt, code);
}
}
BRT_BEGIN_CELL_XFS => {
in_cell_xfs = true;
}
BRT_END_CELL_XFS => {
in_cell_xfs = false;
}
BRT_XF if in_cell_xfs => {
// BrtXF: ixfeParent:u16, iFmt:u16, ...
if let Some(ifmt) = u16le(p, 2) {
s.xf_numfmt.push(ifmt);
}
}
_ => {}
}
}
s
}
fn parse_shared_strings(b: &[u8]) -> Vec<String> {
let mut out = Vec::new();
let mut r = RecReader::new(b);
while let Some((rt, p)) = r.next() {
if rt == BRT_SST_ITEM {
// flags:u8, then XLWideString (rich-run/phonetic data after is ignored).
if let Some((s, _)) = wide_string(p, 1) {
out.push(s);
} else {
out.push(String::new());
}
}
}
out
}
/// Returns the `(sheet name, rel id, hsState)` triples, whether the workbook
/// uses the 1904 date system (`BrtWbProp` bit 0, matching calamine and
/// `[MS-XLSB]`), the active sheet index from `BrtBookView.itabCur`, workbook
/// defined names, workbook structure protection from `BrtBookProtection`, and
/// selected sheet-local built-in names used by sheet metadata facades.
/// `hsState` is the sheet visibility (0 = visible, 1 = hidden, 2 = veryHidden).
fn parse_workbook(
b: &[u8],
) -> (
WorkbookSheets,
bool,
Option<usize>,
DefinedNames,
bool,
Vec<SheetBuiltinName>,
) {
let mut out = Vec::new();
let mut defined_names = Vec::new();
let mut sheet_builtin_names = Vec::new();
let mut date1904 = false;
let mut active_sheet = None;
let mut protect_structure = false;
let mut r = RecReader::new(b);
while let Some((rt, p)) = r.next() {
if rt == BRT_BUNDLE_SH {
// hsState:u32, iTabID:u32, strRelID: XLNullableWideString, strName: XLWideString.
let hs_state = u32le(p, 0).unwrap_or(0);
let Some((rid, used)) = nullable_wide(p, 8) else {
continue;
};
if let Some((name, _)) = wide_string(p, 8 + used) {
out.push((name, rid, hs_state));
}
} else if rt == BRT_WB_PROP {
date1904 = p.first().is_some_and(|byte| byte & 0x1 != 0);
} else if rt == BRT_BOOK_VIEW && active_sheet.is_none() {
active_sheet = u32le(p, 24).and_then(|index| usize::try_from(index).ok());
} else if rt == BRT_NAME {
match parse_brt_name(p) {
Some(ParsedBrtName::GlobalUser(name)) => defined_names.push(name),
Some(ParsedBrtName::SheetBuiltin(name)) => sheet_builtin_names.push(name),
None => {}
}
} else if rt == BRT_BOOK_PROTECTION {
protect_structure |= u16le(p, 4).is_some_and(|flags| flags & 0x0001 != 0);
}
}
(
out,
date1904,
active_sheet,
defined_names,
protect_structure,
sheet_builtin_names,
)
}
/// Parse a `BrtName` record. Workbook-global user names are surfaced through
/// `Workbook::defined_names`; selected sheet-local built-ins become existing
/// sheet metadata facades.
fn parse_brt_name(p: &[u8]) -> Option<ParsedBrtName> {
let flags = u32le(p, 0)?;
let built_in = flags & 0x20 != 0;
let itab = u32le(p, 5)?;
let (name, used) = wide_string(p, 9)?;
let formula_start = 9usize.checked_add(used)?;
let cce = u32le(p, formula_start)? as usize;
let rgce_start = formula_start + 4;
let rgce = p.get(rgce_start..rgce_start.checked_add(cce)?)?;
if built_in {
if itab == 0xFFFF_FFFF {
return None;
}
let kind = xlsb_builtin_name(&name)?;
let sheet_index = usize::try_from(itab).ok()?;
let ranges = parse_brt_name_ranges(rgce)?;
Some(ParsedBrtName::SheetBuiltin(SheetBuiltinName {
sheet_index,
kind,
ranges,
}))
} else if itab == 0xFFFF_FFFF && !name.is_empty() {
Some(ParsedBrtName::GlobalUser((
name,
crate::ptg::decompile(rgce, true),
)))
} else {
None
}
}
enum ParsedBrtName {
GlobalUser((String, String)),
SheetBuiltin(SheetBuiltinName),
}
#[derive(Clone, Copy)]
enum SheetBuiltinKind {
PrintArea,
PrintTitles,
FilterDatabase,
}
struct SheetBuiltinName {
sheet_index: usize,
kind: SheetBuiltinKind,
ranges: Vec<SheetRange>,
}
fn xlsb_builtin_name(name: &str) -> Option<SheetBuiltinKind> {
let lower = name.to_ascii_lowercase();
let name = lower.strip_prefix("_xlnm.").unwrap_or(&lower);
match name {
"_filterdatabase" => Some(SheetBuiltinKind::FilterDatabase),
"print_area" => Some(SheetBuiltinKind::PrintArea),
"print_titles" => Some(SheetBuiltinKind::PrintTitles),
_ => None,
}
}
fn apply_xlsb_sheet_builtin_names(sheets: &mut [Sheet], names: Vec<SheetBuiltinName>) {
for name in names {
let Some(sheet) = sheets.get_mut(name.sheet_index) else {
continue;
};
match name.kind {
SheetBuiltinKind::PrintArea => {
if let Some(range) = name.ranges.into_iter().next() {
sheet
.page_setup
.get_or_insert_with(PageSetup::default)
.print_area = Some(range);
}
}
SheetBuiltinKind::PrintTitles => {
let setup = sheet.page_setup.get_or_insert_with(PageSetup::default);
for range in name.ranges {
apply_xlsb_print_title_range(setup, range);
}
}
SheetBuiltinKind::FilterDatabase => {
if let Some(range) = name.ranges.into_iter().next() {
sheet.autofilter = Some(range);
}
}
}
}
}
fn apply_xlsb_print_title_range(setup: &mut PageSetup, range: SheetRange) {
let (r0, c0, r1, c1) = range;
if c0 == 0 && u32::from(c1) >= MAX_XLSB_COL_INDEX {
setup.repeat_rows = Some((r0, r1));
}
if r0 == 0 && r1 >= MAX_XLSB_ROW_INDEX {
setup.repeat_cols = Some((c0, c1));
}
}
fn parse_brt_name_ranges(rgce: &[u8]) -> Option<Vec<SheetRange>> {
let mut ranges = Vec::new();
let mut offset = 0usize;
while offset < rgce.len() {
let token = rgce[offset];
match token {
0x24 | 0x44 | 0x64 => {
let (row, col) = parse_brt_name_ref(rgce, offset + 1)?;
ranges.push((row, col, row, col));
offset += 7;
}
0x1A | 0x3A | 0x5A | 0x7A => {
let (row, col) = parse_brt_name_ref(rgce, offset + 3)?;
ranges.push((row, col, row, col));
offset += 9;
}
0x25 | 0x45 | 0x65 => {
ranges.push(parse_brt_name_area(rgce, offset + 1)?);
offset += 13;
}
0x1B | 0x3B | 0x5B | 0x7B => {
ranges.push(parse_brt_name_area(rgce, offset + 3)?);
offset += 15;
}
0x10 => offset += 1, // PtgUnion
_ => return None,
}
}
(!ranges.is_empty()).then_some(ranges)
}
fn parse_brt_name_ref(rgce: &[u8], offset: usize) -> Option<(u32, u16)> {
let row = u32le(rgce, offset)?;
let col = u16le(rgce, offset + 4)? & 0x3FFF;
Some((row, col))
}
fn parse_brt_name_area(rgce: &[u8], offset: usize) -> Option<SheetRange> {
let r0 = u32le(rgce, offset)?;
let r1 = u32le(rgce, offset + 4)?;
let c0 = u16le(rgce, offset + 8)? & 0x3FFF;
let c1 = u16le(rgce, offset + 10)? & 0x3FFF;
Some((r0.min(r1), c0.min(c1), r0.max(r1), c0.max(c1)))
}
/// An `XLNullableWideString`: `cch == 0xFFFFFFFF` means null (empty), else a
/// normal `XLWideString`. Returns `(string, bytes_consumed)`.
fn nullable_wide(b: &[u8], o: usize) -> Option<(String, usize)> {
let cch = u32le(b, o)?;
if cch == 0xFFFF_FFFF {
return Some((String::new(), 4));
}
wide_string(b, o)
}
type WorkbookSheets = Vec<(String, String, u32)>;
type DefinedNames = Vec<(String, String)>;
type SheetRange = (u32, u16, u32, u16);
type SheetRanges = Vec<SheetRange>;
type Merges = SheetRanges;
type Hyperlinks = Vec<(u32, u16, String)>;
type AutoFilter = Option<SheetRange>;
#[derive(Default)]
struct SheetReadMetadata {
freeze: Option<(u32, u16)>,
autofilter: AutoFilter,
data_validations: Vec<DataValidation>,
page_setup: Option<PageSetup>,
tab_color: Option<Color>,
table_rel_ids: Vec<String>,
print_gridlines: bool,
print_headings: bool,
hide_gridlines: bool,
zoom: Option<u16>,
show_headers: Option<bool>,
right_to_left: bool,
selected: bool,
protect: bool,
protect_options: Option<ProtectionOptions>,
row_outline: BTreeMap<u32, u8>,
col_outline: BTreeMap<u16, u8>,
outline_summary_below: Option<bool>,
outline_summary_right: Option<bool>,
collapsed_rows: BTreeSet<u32>,
}
fn parse_sheet_comments(
zip: &mut zip::ZipArchive<std::io::Cursor<&[u8]>>,
sheet_path: &str,
sheet_rels: &HashMap<String, String>,
sheet_rels_xml: &str,
) -> Vec<Comment> {
if sheet_rels_xml.is_empty() {
return Vec::new();
}
let rel_types = crate::xlsx::parse_rel_types(sheet_rels_xml);
let Some(target) = rel_types
.iter()
.find(|(_, ty)| ty.rsplit('/').next() == Some("comments"))
.and_then(|(id, _)| sheet_rels.get(id))
else {
return Vec::new();
};
let path = normalize_part_target(sheet_path, target);
part(zip, &path)
.map(|b| parse_comments(&b))
.unwrap_or_default()
}
fn parse_comments(b: &[u8]) -> Vec<Comment> {
struct PendingComment {
row: u32,
col: u16,
text: String,
author: Option<String>,
}
let mut authors = Vec::new();
let mut out = Vec::new();
let mut pending: Option<PendingComment> = None;
let mut r = RecReader::new(b);
while let Some((rt, p)) = r.next() {
match rt {
BRT_COMMENT_AUTHOR => {
if let Some((author, _)) = wide_string(p, 0) {
authors.push(author);
}
}
BRT_BEGIN_COMMENT => {
let (Some(author_id), Some(row), Some(col)) =
(u32le(p, 0), u32le(p, 4), u32le(p, 12))
else {
pending = None;
continue;
};
let author = authors.get(author_id as usize).cloned();
pending = Some(PendingComment {
row,
col: col.min(u32::from(u16::MAX)) as u16,
text: String::new(),
author,
});
}
BRT_COMMENT_TEXT => {
if let (Some(comment), Some(text)) = (pending.as_mut(), rich_string_text(p)) {
comment.text.push_str(&text);
}
}
BRT_END_COMMENT => {
if let Some(comment) = pending.take() {
out.push(Comment {
row: comment.row,
col: comment.col,
text: comment.text,
author: comment.author,
});
}
}
_ => {}
}
}
out
}
fn rich_string_text(b: &[u8]) -> Option<String> {
// RichStr starts with one flag byte followed by the plain XLWideString; the
// trailing rich-format runs are not needed for the public comment text.
wide_string(b, 1).map(|(s, _)| s)
}
fn parse_sheet(
b: &[u8],
shared: &[String],
styles: &Styles,
date1904: bool,
sheet_rels: &HashMap<String, String>,
budget: &mut usize,
) -> (Vec<CellEntry>, Merges, Hyperlinks, SheetReadMetadata) {
let mut cells = Vec::new();
let mut merges = Vec::new();
let mut hyperlinks = Vec::new();
let mut metadata = SheetReadMetadata::default();
let mut selected_view_rank = 0u8;
let mut in_selected_view = false;
let mut pending_dval_list: Option<String> = None;
let mut row: u32 = 0;
let mut r = RecReader::new(b);
while let Some((rt, p)) = r.next() {
match rt {
BRT_DVAL_LIST => {
pending_dval_list = wide_string(p, 0).map(|(formula, _)| formula);
}
BRT_DVAL => {
metadata
.data_validations
.extend(parse_dval(p, pending_dval_list.take()));
}
BRT_SHEET_PROTECTION => {
apply_sheet_protection(p, &mut metadata);
}
BRT_WS_PROP => {
apply_ws_prop_metadata(p, &mut metadata);
}
BRT_MARGINS => {
if let Some(margins) = parse_page_margins(p) {
page_setup_mut(&mut metadata).margins = Some(margins);
}
}
BRT_PRINT_OPTIONS => {
parse_print_options(p, &mut metadata);
}
BRT_PAGE_SETUP => {
parse_page_setup(p, &mut metadata);
}
BRT_BEGIN_HEADER_FOOTER => {
parse_header_footer(p, &mut metadata);
}
BRT_BEGIN_WS_VIEW => {
let Some(rank) = parse_sheet_view(p, &mut metadata, selected_view_rank) else {
in_selected_view = false;
continue;
};
selected_view_rank = rank;
in_selected_view = true;
}
BRT_END_WS_VIEW => {
in_selected_view = false;
}
BRT_PANE if in_selected_view => {
if let Some(freeze) = parse_pane_freeze(p) {
metadata.freeze = Some(freeze);
}
}
BRT_BEGIN_AFILTER => {
metadata.autofilter = parse_unchecked_rfx(p);
}
BRT_COL_INFO => {
apply_col_outline(p, &mut metadata);
}
BRT_ROW_HDR => {
if let Some(rr) = u32le(p, 0) {
row = rr;
}
apply_row_outline(p, &mut metadata);
}
BRT_MERGE_CELL => {
// UncheckedRfX: rwFirst:u32, rwLast:u32, colFirst:u32, colLast:u32.
if let (Some(rf), Some(rl), Some(cf), Some(cl)) =
(u32le(p, 0), u32le(p, 4), u32le(p, 8), u32le(p, 12))
{
merges.push((
rf,
cf.min(u32::from(u16::MAX)) as u16,
rl,
cl.min(u32::from(u16::MAX)) as u16,
));
}
}
BRT_HLINK => {
hyperlinks.extend(parse_hlink(p, sheet_rels));
}
BRT_LIST_PART => {
if let Some(rel_id) = parse_list_part_rel_id(p) {
metadata.table_rel_ids.push(rel_id);
}
}
BRT_CELL_RK | BRT_CELL_REAL | BRT_CELL_ISST | BRT_CELL_ST | BRT_CELL_BOOL
| BRT_CELL_ERROR | BRT_FMLA_NUM | BRT_FMLA_STRING | BRT_FMLA_BOOL | BRT_FMLA_ERROR => {
if *budget == 0 {
continue;
}
// Cell: col:u32 (0..4), iStyleRef:u24 + flags (4..8). Value at 8.
let Some(col_u) = u32le(p, 0) else { continue };
let col = col_u.min(u32::from(u16::MAX)) as u16;
let style_idx = (u32::from(*p.get(4).unwrap_or(&0))
| u32::from(*p.get(5).unwrap_or(&0)) << 8
| u32::from(*p.get(6).unwrap_or(&0)) << 16)
as usize;
decode_cell(
rt, p, col, style_idx, row, shared, styles, date1904, &mut cells, budget,
);
}
_ => {}
}
}
(cells, merges, hyperlinks, metadata)
}
fn parse_list_part_rel_id(p: &[u8]) -> Option<String> {
let (rel_id, _) = wide_string(p, 0)?;
(!rel_id.is_empty()).then_some(rel_id)
}
fn parse_ws_prop_tab_color(p: &[u8]) -> Option<Color> {
// BrtWsProp starts with 2 bytes of worksheet property flags plus one byte of
// filter/conditional-format flags, followed by the 8-byte BrtColor tab color.
parse_brt_color(p.get(3..11)?)
}
fn apply_sheet_protection(p: &[u8], metadata: &mut SheetReadMetadata) {
let Some(locked) = u32le(p, 2) else {
return;
};
metadata.protect = locked != 0;
if !metadata.protect {
metadata.protect_options = None;
return;
}
let options = ProtectionOptions {
format_cells: u32le(p, 14).unwrap_or(0) != 0,
format_columns: u32le(p, 18).unwrap_or(0) != 0,
format_rows: u32le(p, 22).unwrap_or(0) != 0,
insert_columns: u32le(p, 26).unwrap_or(0) != 0,
insert_rows: u32le(p, 30).unwrap_or(0) != 0,
insert_hyperlinks: u32le(p, 34).unwrap_or(0) != 0,
delete_columns: u32le(p, 38).unwrap_or(0) != 0,
delete_rows: u32le(p, 42).unwrap_or(0) != 0,
sort: u32le(p, 50).unwrap_or(0) != 0,
auto_filter: u32le(p, 54).unwrap_or(0) != 0,
pivot_tables: u32le(p, 58).unwrap_or(0) != 0,
};
metadata.protect_options = (options != ProtectionOptions::default()).then_some(options);
}
fn apply_ws_prop_metadata(p: &[u8], metadata: &mut SheetReadMetadata) {
metadata.tab_color = parse_ws_prop_tab_color(p);
if let Some(flags) = u16le(p, 0) {
metadata.outline_summary_below = Some(flags & 0x0040 != 0);
metadata.outline_summary_right = Some(flags & 0x0080 != 0);
}
}
fn apply_row_outline(p: &[u8], metadata: &mut SheetReadMetadata) {
let (Some(row), Some(flags)) = (u32le(p, 0), u16le(p, 10)) else {
return;
};
let level = ((flags >> 8) & 0x07) as u8;
if level > 0 {
metadata.row_outline.insert(row, level);
}
if flags & (1 << 11) != 0 {
metadata.collapsed_rows.insert(row);
}
}
fn apply_col_outline(p: &[u8], metadata: &mut SheetReadMetadata) {
let (Some(first), Some(last), Some(flags)) = (u32le(p, 0), u32le(p, 4), u16le(p, 16)) else {
return;
};
let level = ((flags >> 8) & 0x07) as u8;
if level == 0 || first > last || first > MAX_XLSB_COL_INDEX {
return;
}
for col in first..=last.min(MAX_XLSB_COL_INDEX) {
metadata.col_outline.insert(col as u16, level);
}
}
fn parse_brt_color(p: &[u8]) -> Option<Color> {
let flags = *p.first()?;
let valid_rgb = flags & 0x01 != 0;
let color_type = flags >> 1;
if !valid_rgb || color_type != 0x02 {
return None;
}
Some(Color::rgb(*p.get(4)?, *p.get(5)?, *p.get(6)?))
}
fn parse_sheet_tables(
zip: &mut zip::ZipArchive<std::io::Cursor<&[u8]>>,
sheet_path: &str,
sheet_rels: &HashMap<String, String>,
sheet_rels_xml: &str,
table_rel_ids: &[String],
) -> Vec<Table> {
if sheet_rels_xml.is_empty() {
return Vec::new();
}
let rel_types = crate::xlsx::parse_rel_types(sheet_rels_xml);
let mut rel_ids: Vec<String> = table_rel_ids
.iter()
.filter(|id| {
rel_types
.get(*id)
.is_some_and(|ty| ty.rsplit('/').next() == Some("table"))
})
.cloned()
.collect();
if rel_ids.is_empty() {
rel_ids.extend(
rel_types
.iter()
.filter(|(_, ty)| ty.rsplit('/').next() == Some("table"))
.map(|(id, _)| id.clone()),
);
}
let mut seen = HashSet::new();
rel_ids
.into_iter()
.filter(|id| seen.insert(id.clone()))
.filter_map(|id| sheet_rels.get(&id).cloned())
.map(|target| normalize_part_target(sheet_path, &target))
.filter_map(|path| part(zip, &path))
.filter_map(|bytes| parse_table(&bytes))
.collect()
}
fn parse_table(b: &[u8]) -> Option<Table> {
let mut range: Option<SheetRange> = None;
let mut name: Option<String> = None;
let mut display_name: Option<String> = None;
let mut columns = Vec::new();
let mut style: Option<String> = None;
let mut r = RecReader::new(b);
while let Some((rt, p)) = r.next() {
match rt {
BRT_BEGIN_LIST => {
if let Some((parsed_range, parsed_name, parsed_display_name)) =
parse_table_begin_list(p)
{
range = Some(parsed_range);
name = parsed_name;
display_name = parsed_display_name;
}
}
BRT_BEGIN_LIST_COL if columns.len() < MAX_TABLE_COLUMNS => {
if let Some(column) = parse_table_column(p) {
columns.push(column);
}
}
BRT_TABLE_STYLE_CLIENT => {
style = parse_table_style_client(p);
}
_ => {}
}
}
Some(Table {
range: range?,
name: display_name.or(name).unwrap_or_default(),
columns,
style,
})
}
type XlsbTableHeader = (SheetRange, Option<String>, Option<String>);
fn parse_table_begin_list(p: &[u8]) -> Option<XlsbTableHeader> {
let range = parse_unchecked_rfx(p.get(0..16)?)?;
let mut offset = 64usize;
let (name, used) = nullable_wide_opt(p, offset)?;
offset = offset.checked_add(used)?;
let (display_name, _) = nullable_wide_opt(p, offset)?;
Some((
range,
name.filter(|s| !s.is_empty()),
display_name.filter(|s| !s.is_empty()),
))
}
fn parse_table_column(p: &[u8]) -> Option<String> {
let mut offset = 24usize;
let (name, used) = nullable_wide_opt(p, offset)?;
offset = offset.checked_add(used)?;
let (caption, _) = nullable_wide_opt(p, offset)?;
caption.or(name).and_then(|s| (!s.is_empty()).then_some(s))
}
fn parse_table_style_client(p: &[u8]) -> Option<String> {
let (style, _) = wide_string(p, 2)?;
(!style.is_empty()).then_some(style)
}
fn page_setup_mut(metadata: &mut SheetReadMetadata) -> &mut PageSetup {
metadata.page_setup.get_or_insert_with(PageSetup::default)
}
fn parse_page_margins(p: &[u8]) -> Option<(f64, f64, f64, f64, f64, f64)> {
Some((
xlsb_margin_at(p, 0)?,
xlsb_margin_at(p, 8)?,
xlsb_margin_at(p, 16)?,
xlsb_margin_at(p, 24)?,
xlsb_margin_at(p, 32)?,
xlsb_margin_at(p, 40)?,
))
}
fn xlsb_margin_at(p: &[u8], offset: usize) -> Option<f64> {
f64le(p, offset).filter(|value| value.is_finite() && *value >= 0.0 && *value < 49.0)
}
fn parse_print_options(p: &[u8], metadata: &mut SheetReadMetadata) {
let Some(flags) = u16le(p, 0) else { return };
if flags & 0x0001 != 0 {
page_setup_mut(metadata).center_horizontally = true;
}
if flags & 0x0002 != 0 {
page_setup_mut(metadata).center_vertically = true;
}
metadata.print_headings = flags & 0x0004 != 0;
metadata.print_gridlines = flags & 0x0008 != 0;
}
fn parse_page_setup(p: &[u8], metadata: &mut SheetReadMetadata) {
let (Some(page_start), Some(flags)) = (i32le(p, 20), u16le(p, 32)) else {
return;
};
let ps = page_setup_mut(metadata);
ps.paper_size = nonzero_u32_as_u16(p, 0);
ps.scale = nonzero_u32_as_u16(p, 4);
ps.fit_to_width = nonzero_u32_as_u16(p, 24);
ps.fit_to_height = nonzero_u32_as_u16(p, 28);
if flags & 0x0040 == 0 {
ps.landscape = flags & 0x0002 != 0;
}
if flags & 0x0080 != 0 && page_start > 0 {
ps.first_page_number = u16::try_from(page_start).ok();
}
}
fn nonzero_u32_as_u16(p: &[u8], offset: usize) -> Option<u16> {
let value = u32le(p, offset)?;
if value == 0 {
return None;
}
u16::try_from(value).ok()
}
fn parse_header_footer(p: &[u8], metadata: &mut SheetReadMetadata) {
let Some((header, footer)) = parse_header_footer_pair(p) else {
return;
};
if !header.is_empty() {
page_setup_mut(metadata).header = Some(header);
}
if !footer.is_empty() {
page_setup_mut(metadata).footer = Some(footer);
}
}
fn parse_header_footer_pair(p: &[u8]) -> Option<(String, String)> {
let mut offset = 2usize;
let (header, used) = nullable_wide(p, offset)?;
offset = offset.checked_add(used)?;
let (footer, _) = nullable_wide(p, offset)?;
Some((header, footer))
}
fn parse_dval(p: &[u8], list_formula: Option<String>) -> Vec<DataValidation> {
let Some(flags) = u32le(p, 0) else {
return Vec::new();
};
let Some(kind) = parse_dval_kind(flags & 0x0F) else {
return Vec::new();
};
let operator = parse_dval_op((flags >> 20) & 0x0F).unwrap_or(DvOp::Between);
let allow_blank = flags & (1 << 8) != 0;
let show_input_message = flags & (1 << 18) != 0;
let show_error_message = flags & (1 << 19) != 0;
let Some((ranges, ranges_len)) = parse_unchecked_sq_rfx(p, 4) else {
return Vec::new();
};
let strings_offset = 4 + ranges_len;
let Some((error, prompt, strings_len)) = parse_dval_strings(p, strings_offset) else {
return Vec::new();
};
let formula1_offset = strings_offset + strings_len;
let Some((parsed_formula1, formula1_len)) = parse_dv_formula(p, formula1_offset) else {
return Vec::new();
};
let formula2_offset = formula1_offset + formula1_len;
let Some((parsed_formula2, _formula2_len)) = parse_dv_formula(p, formula2_offset) else {
return Vec::new();
};
let formula1 = list_formula.unwrap_or(parsed_formula1);
if formula1.is_empty() {
return Vec::new();
}
let formula2 = (!parsed_formula2.is_empty()).then_some(parsed_formula2);
let Some((&sqref, rest)) = ranges.split_first() else {
return Vec::new();
};
let base = DataValidation {
sqref,
kind,
operator,
formula1,
formula2,
allow_blank,
show_input_message,
show_error_message,
prompt,
error,
};
let mut out = Vec::with_capacity(ranges.len().min(MAX_DVAL_RANGES));
out.push(base.clone());
for sqref in rest.iter().take(MAX_DVAL_RANGES - 1) {
let mut clone = base.clone();
clone.sqref = *sqref;
out.push(clone);
}
out
}
fn parse_dval_kind(value: u32) -> Option<DvKind> {
match value {
1 => Some(DvKind::Whole),
2 => Some(DvKind::Decimal),
3 => Some(DvKind::List),
4 => Some(DvKind::Date),
5 => Some(DvKind::Time),
6 => Some(DvKind::TextLength),
7 => Some(DvKind::Custom),
_ => None,
}
}
fn parse_dval_op(value: u32) -> Option<DvOp> {
match value {
0 => Some(DvOp::Between),
1 => Some(DvOp::NotBetween),
2 => Some(DvOp::Equal),
3 => Some(DvOp::NotEqual),
4 => Some(DvOp::GreaterThan),
5 => Some(DvOp::LessThan),
6 => Some(DvOp::GreaterThanOrEqual),
7 => Some(DvOp::LessThanOrEqual),
_ => None,
}
}
fn parse_unchecked_sq_rfx(p: &[u8], offset: usize) -> Option<(SheetRanges, usize)> {
let crfx = i32::from_le_bytes(p.get(offset..offset + 4)?.try_into().ok()?);
if crfx <= 0 {
return None;
}
let count = usize::try_from(crfx).ok()?;
let start = offset.checked_add(4)?;
let ranges_len = count.checked_mul(16)?;
let end = start.checked_add(ranges_len)?;
p.get(start..end)?;
let retained_count = count.min(MAX_DVAL_RANGES);
let mut ranges = Vec::with_capacity(retained_count);
for i in 0..retained_count {
let pos = start + i * 16;
let range = parse_unchecked_rfx(p.get(pos..pos + 16)?)?;
ranges.push(range);
}
Some((ranges, end - offset))
}
type DvalStrings = (Option<(String, String)>, Option<(String, String)>, usize);
fn parse_dval_strings(p: &[u8], offset: usize) -> Option<DvalStrings> {
let (error_title, used1) = nullable_wide_opt(p, offset)?;
let (error_message, used2) = nullable_wide_opt(p, offset + used1)?;
let (prompt_title, used3) = nullable_wide_opt(p, offset + used1 + used2)?;
let (prompt_message, used4) = nullable_wide_opt(p, offset + used1 + used2 + used3)?;
let error = match (error_title, error_message) {
(None, None) => None,
(title, message) => Some((title.unwrap_or_default(), message.unwrap_or_default())),
};
let prompt = match (prompt_title, prompt_message) {
(None, None) => None,
(title, message) => Some((title.unwrap_or_default(), message.unwrap_or_default())),
};
Some((error, prompt, used1 + used2 + used3 + used4))
}
fn nullable_wide_opt(b: &[u8], o: usize) -> Option<(Option<String>, usize)> {
let cch = u32le(b, o)?;
if cch == 0xFFFF_FFFF {
return Some((None, 4));
}
wide_string(b, o).map(|(s, used)| (Some(s), used))
}
fn parse_dv_formula(p: &[u8], offset: usize) -> Option<(String, usize)> {
let cce = u32le(p, offset)? as usize;
let rgce_start = offset + 4;
let rgce_end = rgce_start.checked_add(cce)?;
let rgce = p.get(rgce_start..rgce_end)?;
let cb = u32le(p, rgce_end)? as usize;
let end = rgce_end.checked_add(4)?.checked_add(cb)?;
p.get(rgce_end + 4..end)?;
Some((crate::ptg::decompile(rgce, true), end - offset))
}
fn parse_sheet_view(p: &[u8], metadata: &mut SheetReadMetadata, current_rank: u8) -> Option<u8> {
let flags = u16le(p, 0)?;
let i_wbk_view = u32le(p, 26).unwrap_or(0);
let rank = if i_wbk_view == 0 { 2 } else { 1 };
if rank <= current_rank {
return None;
}
metadata.freeze = None;
metadata.hide_gridlines = flags & (1 << 2) == 0;
metadata.show_headers = Some(flags & (1 << 3) != 0);
metadata.right_to_left = flags & (1 << 5) != 0;
metadata.selected = flags & (1 << 6) != 0;
metadata.zoom = u16le(p, 18).filter(|&zoom| zoom != 0);
Some(rank)
}
fn parse_pane_freeze(p: &[u8]) -> Option<(u32, u16)> {
let flags = *p.get(28)?;
if flags & 0x03 == 0 {
return None;
}
let rows = f64le(p, 0)?.max(0.0).floor() as u32;
let cols_u = f64le(p, 8)?.max(0.0).floor() as u32;
let cols = cols_u.min(u32::from(u16::MAX)) as u16;
if rows == 0 && cols == 0 {
None
} else {
Some((rows, cols))
}
}
fn parse_unchecked_rfx(p: &[u8]) -> AutoFilter {
let (Some(rf), Some(rl), Some(cf), Some(cl)) =
(u32le(p, 0), u32le(p, 4), u32le(p, 8), u32le(p, 12))
else {
return None;
};
if rf > rl || cf > cl {
return None;
}
Some((
rf,
cf.min(u32::from(u16::MAX)) as u16,
rl,
cl.min(u32::from(u16::MAX)) as u16,
))
}
fn parse_hlink(p: &[u8], sheet_rels: &HashMap<String, String>) -> Hyperlinks {
const MAX_HYPERLINK_CELLS: usize = 1 << 16;
let (Some(rf), Some(rl), Some(cf), Some(cl)) =
(u32le(p, 0), u32le(p, 4), u32le(p, 8), u32le(p, 12))
else {
return Vec::new();
};
let Some((rel_id, rel_len)) = nullable_wide(p, 16) else {
return Vec::new();
};
let location_offset = 16 + rel_len;
let Some((location, _location_len)) = wide_string(p, location_offset) else {
return Vec::new();
};
let target = match (sheet_rels.get(&rel_id), location.is_empty()) {
(Some(url), true) => url.clone(),
(Some(url), false) => format!("{url}#{location}"),
(None, false) if rel_id.is_empty() => format!("#{location}"),
_ => return Vec::new(),
};
let c0 = cf.min(u32::from(u16::MAX)) as u16;
let c1 = cl.min(u32::from(u16::MAX)) as u16;
if rf > rl || c0 > c1 {
return Vec::new();
}
let mut hyperlinks = Vec::new();
'links: for row in rf..=rl {
for col in c0..=c1 {
if hyperlinks.len() >= MAX_HYPERLINK_CELLS {
break 'links;
}
hyperlinks.push((row, col, target.clone()));
}
}
hyperlinks
}
#[allow(clippy::too_many_arguments)]
fn decode_cell(
rt: u32,
p: &[u8],
col: u16,
style_idx: usize,
row: u32,
shared: &[String],
styles: &Styles,
date1904: bool,
cells: &mut Vec<CellEntry>,
budget: &mut usize,
) {
let push = |cells: &mut Vec<CellEntry>, budget: &mut usize, value: Cell, text: String| {
if text.len() > *budget {
*budget = 0;
return;
}
*budget -= text.len();
cells.push(CellEntry {
row,
col,
value,
text,
style: None,
hyperlink: None,
});
};
let number = |f: f64, cells: &mut Vec<CellEntry>, budget: &mut usize| {
let kind = styles.kind(style_idx);
let display = format::render_value(f, kind, date1904);
let cell = if kind.is_datetime() {
Cell::Date(f)
} else {
Cell::Number(f)
};
push(cells, budget, cell, display);
};
match rt {
BRT_CELL_REAL => {
if let Some(s) = p.get(8..16) {
number(
f64::from_le_bytes(s.try_into().unwrap_or([0; 8])),
cells,
budget,
);
}
}
BRT_FMLA_NUM => {
if let Some(s) = p.get(8..16) {
let f = f64::from_le_bytes(s.try_into().unwrap_or([0; 8]));
let kind = styles.kind(style_idx);
let display = format::render_value(f, kind, date1904);
let cached = if kind.is_datetime() {
Cell::Date(f)
} else {
Cell::Number(f)
};
push(cells, budget, wrap_fmla(p, 16, cached), display);
}
}
BRT_CELL_RK => {
if let Some(rk) = u32le(p, 8) {
number(rk_to_f64(rk), cells, budget);
}
}
BRT_CELL_ISST => {
if let Some(isst) = u32le(p, 8) {
if let Some(s) = shared.get(isst as usize) {
push(cells, budget, Cell::Text(s.clone()), s.clone());
}
}
}
BRT_CELL_ST => {
if let Some((s, _)) = wide_string(p, 8) {
push(cells, budget, Cell::Text(s.clone()), s);
}
}
BRT_FMLA_STRING => {
if let Some((s, used)) = wide_string(p, 8) {
push(
cells,
budget,
wrap_fmla(p, 8 + used, Cell::Text(s.clone())),
s,
);
}
}
BRT_CELL_BOOL => {
let b = p.get(8).copied().unwrap_or(0) != 0;
push(
cells,
budget,
Cell::Bool(b),
if b { "TRUE" } else { "FALSE" }.to_string(),
);
}
BRT_FMLA_BOOL => {
let b = p.get(8).copied().unwrap_or(0) != 0;
let text = if b { "TRUE" } else { "FALSE" }.to_string();
push(cells, budget, wrap_fmla(p, 9, Cell::Bool(b)), text);
}
BRT_CELL_ERROR => {
let code = crate::error_code(p.get(8).copied().unwrap_or(0)).to_string();
push(cells, budget, Cell::Error(code.clone()), code);
}
BRT_FMLA_ERROR => {
let code = crate::error_code(p.get(8).copied().unwrap_or(0)).to_string();
push(
cells,
budget,
wrap_fmla(p, 9, Cell::Error(code.clone())),
code,
);
}
_ => {}
}
}
/// Wrap a cached value as `Cell::Formula` by decoding the `BrtFmla*` formula that
/// follows it. Layout after the cached value: `grbitFlags:u16`, then a
/// `CellParsedFormula` (`cce:u32`, `rgce[cce]`, …). `value_end` is the byte offset
/// just past the cached value. Falls back to the bare cached value if the rgce is
/// absent or decompiles to nothing.
fn wrap_fmla(p: &[u8], value_end: usize, cached: Cell) -> Cell {
let Some(cce) = u32le(p, value_end + 2) else {
return cached;
};
let start = value_end + 6;
let Some(rgce) = p.get(start..start.saturating_add(cce as usize)) else {
return cached;
};
let f = crate::ptg::decompile(rgce, true);
if f.is_empty() {
cached
} else {
Cell::Formula {
formula: f,
cached: Box::new(cached),
}
}
}
#[cfg(test)]
mod tests {
use super::*;
use crate::{Color, SheetMetadata, SheetType, SheetVisible};
use std::io::Write;
use zip::write::SimpleFileOptions;
/// `XLWideString`: `cch:u32` + UTF-16LE chars.
fn wstr(s: &str) -> Vec<u8> {
let units: Vec<u16> = s.encode_utf16().collect();
let mut v = (units.len() as u32).to_le_bytes().to_vec();
for u in units {
v.extend_from_slice(&u.to_le_bytes());
}
v
}
fn null_wstr() -> Vec<u8> {
0xFFFF_FFFFu32.to_le_bytes().to_vec()
}
/// Frame a BIFF12 record: var-uint type, var-uint size, payload.
fn rec(rt: u32, payload: &[u8]) -> Vec<u8> {
let mut v = Vec::new();
if rt < 0x80 {
v.push(rt as u8);
} else {
v.push((rt & 0x7F) as u8 | 0x80);
v.push((rt >> 7) as u8 & 0x7F);
}
let mut sz = payload.len();
loop {
let mut b = (sz & 0x7F) as u8;
sz >>= 7;
if sz > 0 {
b |= 0x80;
}
v.push(b);
if sz == 0 {
break;
}
}
v.extend_from_slice(payload);
v
}
fn xf(numfmt: u16) -> Vec<u8> {
let mut v = vec![0u8; 16];
v[2..4].copy_from_slice(&numfmt.to_le_bytes());
v
}
#[test]
fn record_framing_var_uints() {
// A 2-byte record type (156 = BrtBundleSh) and small size round-trip.
let r = rec(BRT_BUNDLE_SH, &[1, 2, 3]);
let mut rr = RecReader::new(&r);
let (rt, p) = rr.next().unwrap();
assert_eq!(rt, BRT_BUNDLE_SH);
assert_eq!(p, &[1, 2, 3]);
assert!(rr.next().is_none());
}
#[test]
fn reads_a_synthetic_xlsb() {
// workbook.bin: one BrtBundleSh(rId1, "시트1").
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1")); // strRelID (non-null)
wb_bin.extend_from_slice(&wstr("시트1")); // strName
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
// sharedStrings.bin: BrtSSTItem("품목").
let mut item = vec![0u8]; // flags
item.extend_from_slice(&wstr("품목"));
let sst = rec(BRT_SST_ITEM, &item);
// sheet1.bin: RowHdr(0), CellIsst(0,0 → isst 0), CellReal(0,1 → 42.0).
let mut sh = rec(BRT_ROW_HDR, &[0, 0, 0, 0]);
let mut isst = vec![0u8; 8]; // col=0, styleRef/flags
isst.extend_from_slice(&0u32.to_le_bytes()); // isst = 0
sh.extend_from_slice(&rec(BRT_CELL_ISST, &isst));
let mut real = vec![1, 0, 0, 0, 0, 0, 0, 0]; // col=1, styleRef
real.extend_from_slice(&42.0f64.to_le_bytes());
sh.extend_from_slice(&rec(BRT_CELL_REAL, &real));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (name, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/sharedStrings.bin", sst.as_slice()),
("xl/worksheets/sheet1.bin", sh.as_slice()),
] {
zw.start_file(name, opt).unwrap();
zw.write_all(body).unwrap();
}
let bytes = zw.finish().unwrap().into_inner();
let wb = Workbook::open(&bytes).unwrap();
assert_eq!(wb.sheets.len(), 1);
assert_eq!(wb.sheets[0].name, "시트1");
assert_eq!(
wb.sheets[0].cell(0, 0),
Some(&Cell::Text("품목".to_string()))
);
assert_eq!(wb.sheets[0].cell(0, 1), Some(&Cell::Number(42.0)));
}
#[test]
fn xlsb_book_view_active_tab_surfaces_workbook_metadata() {
let mut wb_bin = Vec::new();
for (rid, name) in [("rId1", "Data"), ("rId2", "Summary")] {
let mut bundle = vec![0u8; 8]; // hsState + iTabID
bundle.extend_from_slice(&wstr(rid));
bundle.extend_from_slice(&wstr(name));
wb_bin.extend_from_slice(&rec(BRT_BUNDLE_SH, &bundle));
}
let mut book_view = Vec::new();
book_view.extend_from_slice(&0i32.to_le_bytes()); // xWn
book_view.extend_from_slice(&0i32.to_le_bytes()); // yWn
book_view.extend_from_slice(&0u32.to_le_bytes()); // dxWn
book_view.extend_from_slice(&0u32.to_le_bytes()); // dyWn
book_view.extend_from_slice(&600u32.to_le_bytes()); // iTabRatio
book_view.extend_from_slice(&0u32.to_le_bytes()); // itabFirst
book_view.extend_from_slice(&1u32.to_le_bytes()); // itabCur
wb_bin.extend_from_slice(&rec(158, &book_view)); // BrtBookView
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/><Relationship Id="rId2" Target="worksheets/sheet2.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", [].as_slice()),
("xl/worksheets/sheet2.bin", [].as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
let metadata = wb.metadata();
assert_eq!(wb.active_sheet_index(), Some(1));
assert_eq!(wb.active_sheet_name(), Some("Summary"));
assert_eq!(metadata.active_sheet, Some(1));
assert_eq!(metadata.active_sheet_name, Some("Summary"));
assert_eq!(
<Workbook as crate::Reader>::metadata(&wb).active_sheet_name,
Some("Summary")
);
}
#[test]
fn xlsb_selected_sheet_view_falls_back_to_active_sheet_metadata() {
const BRT_BEGIN_WS_VIEWS: u32 = 133;
const BRT_END_WS_VIEWS: u32 = 134;
let mut wb_bin = Vec::new();
for (rid, name) in [("rId1", "Data"), ("rId2", "Summary")] {
let mut bundle = vec![0u8; 8]; // hsState + iTabID
bundle.extend_from_slice(&wstr(rid));
bundle.extend_from_slice(&wstr(name));
wb_bin.extend_from_slice(&rec(BRT_BUNDLE_SH, &bundle));
}
let mut ws_view = Vec::new();
ws_view.extend_from_slice(&(1u16 << 6).to_le_bytes()); // fSelected
ws_view.extend_from_slice(&0u32.to_le_bytes()); // xlView normal
ws_view.extend_from_slice(&0u32.to_le_bytes()); // rwTop
ws_view.extend_from_slice(&0u32.to_le_bytes()); // colLeft
ws_view.push(0x40); // icvHdr
ws_view.push(0); // reserved2
ws_view.extend_from_slice(&0u16.to_le_bytes()); // reserved3
ws_view.extend_from_slice(&100u16.to_le_bytes()); // wScale
ws_view.extend_from_slice(&0u16.to_le_bytes()); // wScaleNormal
ws_view.extend_from_slice(&0u16.to_le_bytes()); // wScaleSLV
ws_view.extend_from_slice(&0u16.to_le_bytes()); // wScalePLV
ws_view.extend_from_slice(&0u32.to_le_bytes()); // iWbkView
let mut selected_sheet = rec(BRT_BEGIN_WS_VIEWS, &[]);
selected_sheet.extend_from_slice(&rec(BRT_BEGIN_WS_VIEW, &ws_view));
selected_sheet.extend_from_slice(&rec(BRT_END_WS_VIEW, &[]));
selected_sheet.extend_from_slice(&rec(BRT_END_WS_VIEWS, &[]));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/><Relationship Id="rId2" Target="worksheets/sheet2.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", [].as_slice()),
("xl/worksheets/sheet2.bin", selected_sheet.as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
let metadata = wb.metadata();
assert_eq!(wb.active_sheet_index(), Some(1));
assert_eq!(wb.active_sheet_name(), Some("Summary"));
assert_eq!(metadata.active_sheet, Some(1));
assert_eq!(metadata.active_sheet_name, Some("Summary"));
assert_eq!(
<Workbook as crate::Reader>::metadata(&wb).active_sheet_name,
Some("Summary")
);
}
#[test]
fn xlsb_shared_strings_part_lookup_is_case_insensitive() {
// calamine/tests/issue_419.xlsb stores the shared-string part as
// `xl/SharedStrings.bin`; the cell stream still references it through
// BrtCellIsst.
let mut wb_bin = vec![0u8; 8];
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("Sheet1"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let mut item = vec![0u8];
item.extend_from_slice(&wstr("Hello"));
let sst = rec(BRT_SST_ITEM, &item);
let mut sh = rec(BRT_ROW_HDR, &[0, 0, 0, 0]);
let mut isst = vec![0u8; 8];
isst.extend_from_slice(&0u32.to_le_bytes());
sh.extend_from_slice(&rec(BRT_CELL_ISST, &isst));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (name, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/SharedStrings.bin", sst.as_slice()),
("xl/worksheets/sheet1.bin", sh.as_slice()),
] {
zw.start_file(name, opt).unwrap();
zw.write_all(body).unwrap();
}
let bytes = zw.finish().unwrap().into_inner();
let wb = Workbook::open(&bytes).unwrap();
assert_eq!(
wb.sheets[0].cell(0, 0),
Some(&Cell::Text("Hello".to_string()))
);
}
#[test]
fn xlsb_styles_use_only_cell_xfs_for_cell_style_indexes() {
// Real styles.bin parts can contain a non-cell XF group before
// BrtBeginCellXFs. Cell records index only the XF records inside
// BrtBeginCellXFs; collecting earlier BrtXF records shifts style
// indexes and can turn plain numeric cells into dates.
let mut wb_bin = vec![0u8; 8];
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("Sheet1"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let mut fmt = 164u16.to_le_bytes().to_vec();
fmt.extend_from_slice(&wstr("yyyy-mm-dd"));
let mut styles = rec(BRT_FMT, &fmt);
styles.extend_from_slice(&rec(0x0272, &1u32.to_le_bytes()));
styles.extend_from_slice(&rec(BRT_XF, &xf(0)));
styles.extend_from_slice(&rec(0x0273, &[]));
styles.extend_from_slice(&rec(BRT_BEGIN_CELL_XFS, &2u32.to_le_bytes()));
styles.extend_from_slice(&rec(BRT_XF, &xf(164)));
styles.extend_from_slice(&rec(BRT_XF, &xf(0)));
styles.extend_from_slice(&rec(BRT_END_CELL_XFS, &[]));
let mut sh = rec(BRT_ROW_HDR, &[0, 0, 0, 0]);
let mut date = vec![0u8; 8];
date.extend_from_slice(&44_197.0f64.to_le_bytes());
sh.extend_from_slice(&rec(BRT_CELL_REAL, &date));
let mut number = vec![1, 0, 0, 0, 1, 0, 0, 0];
number.extend_from_slice(&15.0f64.to_le_bytes());
sh.extend_from_slice(&rec(BRT_CELL_REAL, &number));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (name, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/styles.bin", styles.as_slice()),
("xl/worksheets/sheet1.bin", sh.as_slice()),
] {
zw.start_file(name, opt).unwrap();
zw.write_all(body).unwrap();
}
let bytes = zw.finish().unwrap().into_inner();
let wb = Workbook::open(&bytes).unwrap();
assert_eq!(wb.sheets[0].cell(0, 0), Some(&Cell::Date(44_197.0)));
assert_eq!(wb.sheets[0].cell(0, 1), Some(&Cell::Number(15.0)));
}
#[test]
fn xlsb_hyperlinks_surface_public_metadata() {
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("Links"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let url = "https://example.com/xlsb";
let mut hlink = Vec::new();
for value in [1u32, 2, 1, 1] {
hlink.extend_from_slice(&value.to_le_bytes());
}
hlink.extend_from_slice(&wstr("rId2"));
hlink.extend_from_slice(&wstr(""));
hlink.extend_from_slice(&wstr("Open bid"));
hlink.extend_from_slice(&wstr(""));
let sheet = rec(0x01EE, &hlink);
let wb_rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.bin"/></Relationships>"#;
let sheet_rels = format!(
r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink" Target="{url}" TargetMode="External"/></Relationships>"#
);
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", wb_rels.as_bytes()),
("xl/worksheets/sheet1.bin", sheet.as_slice()),
("xl/worksheets/_rels/sheet1.bin.rels", sheet_rels.as_bytes()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
assert_eq!(
wb.sheets[0].hyperlinks(),
&[(1, 1, url.to_string()), (2, 1, url.to_string())]
);
}
#[test]
fn xlsb_comments_surface_public_metadata() {
const BRT_BEGIN_COMMENTS: u32 = 628;
const BRT_END_COMMENTS: u32 = 629;
const BRT_BEGIN_COMMENT_AUTHORS: u32 = 630;
const BRT_END_COMMENT_AUTHORS: u32 = 631;
const BRT_COMMENT_AUTHOR: u32 = 632;
const BRT_BEGIN_COMMENT_LIST: u32 = 633;
const BRT_END_COMMENT_LIST: u32 = 634;
const BRT_BEGIN_COMMENT: u32 = 635;
const BRT_END_COMMENT: u32 = 636;
const BRT_COMMENT_TEXT: u32 = 637;
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("Notes"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let mut comment = Vec::new();
comment.extend_from_slice(&0u32.to_le_bytes()); // iauthor
for value in [2u32, 2, 3, 3] {
comment.extend_from_slice(&value.to_le_bytes()); // UncheckedRfX: D3
}
comment.extend_from_slice(&[0u8; 16]); // guid
let mut rich_text = vec![0x01]; // RichStr flags: fRichStr=1, fExtStr=0
rich_text.extend_from_slice(&wstr("검토 필요"));
rich_text.extend_from_slice(&0u32.to_le_bytes()); // zero StrRun entries
let mut comments = rec(BRT_BEGIN_COMMENTS, &[]);
comments.extend_from_slice(&rec(BRT_BEGIN_COMMENT_AUTHORS, &[]));
comments.extend_from_slice(&rec(BRT_COMMENT_AUTHOR, &wstr("auditor")));
comments.extend_from_slice(&rec(BRT_END_COMMENT_AUTHORS, &[]));
comments.extend_from_slice(&rec(BRT_BEGIN_COMMENT_LIST, &[]));
comments.extend_from_slice(&rec(BRT_BEGIN_COMMENT, &comment));
comments.extend_from_slice(&rec(BRT_COMMENT_TEXT, &rich_text));
comments.extend_from_slice(&rec(BRT_END_COMMENT, &[]));
comments.extend_from_slice(&rec(BRT_END_COMMENT_LIST, &[]));
comments.extend_from_slice(&rec(BRT_END_COMMENTS, &[]));
let wb_rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.bin"/></Relationships>"#;
let sheet_rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments" Target="../comments1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", wb_rels.as_bytes()),
("xl/worksheets/sheet1.bin", [].as_slice()),
("xl/worksheets/_rels/sheet1.bin.rels", sheet_rels.as_bytes()),
("xl/comments1.bin", comments.as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
let comments = wb.sheets[0].comments();
assert_eq!(comments.len(), 1);
assert_eq!(comments[0].row, 2);
assert_eq!(comments[0].col, 3);
assert_eq!(comments[0].text, "검토 필요");
assert_eq!(comments[0].author.as_deref(), Some("auditor"));
}
#[test]
fn xlsb_tables_surface_public_metadata() {
const BRT_BEGIN_LIST: u32 = 288;
const BRT_BEGIN_LIST_COL: u32 = 291;
const BRT_BEGIN_LIST_COLS: u32 = 293;
const BRT_LIST_PART: u32 = 550;
const BRT_TABLE_STYLE_CLIENT: u32 = 649;
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("Tables"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let sheet = rec(BRT_LIST_PART, &wstr("rId2"));
let mut begin_list = Vec::new();
for value in [0u32, 2, 0, 2] {
begin_list.extend_from_slice(&value.to_le_bytes()); // A1:C3
}
begin_list.extend_from_slice(&0u32.to_le_bytes()); // lt = LTRANGE
begin_list.extend_from_slice(&1u32.to_le_bytes()); // idList
begin_list.extend_from_slice(&1u32.to_le_bytes()); // crwHeader
begin_list.extend_from_slice(&0u32.to_le_bytes()); // crwTotals
begin_list.extend_from_slice(&0u32.to_le_bytes()); // table flags
for _ in 0..6 {
begin_list.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // DXF ids
}
begin_list.extend_from_slice(&0u32.to_le_bytes()); // dwConnID
begin_list.extend_from_slice(&null_wstr()); // stName
begin_list.extend_from_slice(&wstr("SalesTable")); // stDisplayName
begin_list.extend_from_slice(&wstr("")); // stComment
begin_list.extend_from_slice(&null_wstr()); // stStyleHeader
begin_list.extend_from_slice(&null_wstr()); // stStyleData
begin_list.extend_from_slice(&null_wstr()); // stStyleAgg
let mut table = rec(BRT_BEGIN_LIST, &begin_list);
table.extend_from_slice(&rec(BRT_BEGIN_LIST_COLS, &3u32.to_le_bytes()));
for (idx, caption) in ["Item", "Qty", "Total"].iter().enumerate() {
let mut column = Vec::new();
column.extend_from_slice(&((idx + 1) as u32).to_le_bytes()); // idField
column.extend_from_slice(&0u32.to_le_bytes()); // ilta
column.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // nDxfHdr
column.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // nDxfInsertRow
column.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // nDxfAgg
column.extend_from_slice(&0u32.to_le_bytes()); // idqsif
column.extend_from_slice(&null_wstr()); // stName
column.extend_from_slice(&wstr(caption)); // stCaption
column.extend_from_slice(&null_wstr()); // stTotal
column.extend_from_slice(&null_wstr()); // stStyleHeader
column.extend_from_slice(&null_wstr()); // stStyleInsertRow
column.extend_from_slice(&null_wstr()); // stStyleAgg
table.extend_from_slice(&rec(BRT_BEGIN_LIST_COL, &column));
}
let mut style = 0b100u16.to_le_bytes().to_vec(); // fRowStripes
style.extend_from_slice(&wstr("TableStyleMedium9"));
table.extend_from_slice(&rec(BRT_TABLE_STYLE_CLIENT, &style));
let wb_rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.bin"/></Relationships>"#;
let sheet_rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/table" Target="../tables/table1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", wb_rels.as_bytes()),
("xl/worksheets/sheet1.bin", sheet.as_slice()),
("xl/worksheets/_rels/sheet1.bin.rels", sheet_rels.as_bytes()),
("xl/tables/table1.bin", table.as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
let tables = wb.sheets[0].tables();
assert_eq!(tables.len(), 1);
assert_eq!(tables[0].name, "SalesTable");
assert_eq!(tables[0].range, (0, 0, 2, 2));
assert_eq!(tables[0].columns, vec!["Item", "Qty", "Total"]);
assert_eq!(tables[0].style.as_deref(), Some("TableStyleMedium9"));
}
#[test]
fn xlsb_sheet_view_and_autofilter_surface_public_metadata() {
const BRT_BEGIN_WS_VIEWS: u32 = 133;
const BRT_END_WS_VIEWS: u32 = 134;
const BRT_BEGIN_WS_VIEW: u32 = 137;
const BRT_END_WS_VIEW: u32 = 138;
const BRT_BEGIN_AFILTER: u32 = 161;
const BRT_END_AFILTER: u32 = 162;
const BRT_PANE: u32 = 151;
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("View"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let mut ws_view = Vec::new();
let flags = (1u16 << 4) // fDspZeros
| (1u16 << 5) // fRightToLeft
| (1u16 << 6) // fSelected
| (1u16 << 7) // fDspRuler
| (1u16 << 8) // fDspGuts
| (1u16 << 9); // fDefaultHdr
ws_view.extend_from_slice(&flags.to_le_bytes());
ws_view.extend_from_slice(&0u32.to_le_bytes()); // xlView normal
ws_view.extend_from_slice(&0u32.to_le_bytes()); // rwTop
ws_view.extend_from_slice(&0u32.to_le_bytes()); // colLeft
ws_view.push(0x40); // icvHdr
ws_view.push(0); // reserved2
ws_view.extend_from_slice(&0u16.to_le_bytes()); // reserved3
ws_view.extend_from_slice(&125u16.to_le_bytes()); // wScale
ws_view.extend_from_slice(&0u16.to_le_bytes()); // wScaleNormal
ws_view.extend_from_slice(&0u16.to_le_bytes()); // wScaleSLV
ws_view.extend_from_slice(&0u16.to_le_bytes()); // wScalePLV
ws_view.extend_from_slice(&0u32.to_le_bytes()); // iWbkView
let mut pane = Vec::new();
pane.extend_from_slice(&1.0f64.to_le_bytes()); // frozen rows
pane.extend_from_slice(&2.0f64.to_le_bytes()); // frozen columns
pane.extend_from_slice(&1u32.to_le_bytes()); // rwTop
pane.extend_from_slice(&2u32.to_le_bytes()); // colLeft
pane.extend_from_slice(&0u32.to_le_bytes()); // pnnAct
pane.push(0x01); // fFrozen
let mut autofilter = Vec::new();
for value in [0u32, 9, 0, 3] {
autofilter.extend_from_slice(&value.to_le_bytes());
}
let mut sheet = rec(BRT_BEGIN_WS_VIEWS, &[]);
sheet.extend_from_slice(&rec(BRT_BEGIN_WS_VIEW, &ws_view));
sheet.extend_from_slice(&rec(BRT_PANE, &pane));
sheet.extend_from_slice(&rec(BRT_END_WS_VIEW, &[]));
sheet.extend_from_slice(&rec(BRT_END_WS_VIEWS, &[]));
sheet.extend_from_slice(&rec(BRT_BEGIN_AFILTER, &autofilter));
sheet.extend_from_slice(&rec(BRT_END_AFILTER, &[]));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", sheet.as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
let sheet = &wb.sheets[0];
assert_eq!(
sheet.sheet_view(),
crate::SheetView {
freeze: Some((1, 2)),
hide_gridlines: true,
zoom: Some(125),
show_headers: Some(false),
right_to_left: true,
}
);
assert_eq!(sheet.autofilter_range(), Some((0, 0, 9, 3)));
}
#[test]
fn xlsb_sheet_view_explicit_visible_headers_are_preserved() {
const BRT_BEGIN_WS_VIEWS: u32 = 133;
const BRT_END_WS_VIEWS: u32 = 134;
const BRT_BEGIN_WS_VIEW: u32 = 137;
const BRT_END_WS_VIEW: u32 = 138;
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("View"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let mut ws_view = Vec::new();
let flags = (1u16 << 2) // display gridlines
| (1u16 << 3); // display row/column headings
ws_view.extend_from_slice(&flags.to_le_bytes());
ws_view.extend_from_slice(&0u32.to_le_bytes()); // xlView normal
ws_view.extend_from_slice(&0u32.to_le_bytes()); // rwTop
ws_view.extend_from_slice(&0u32.to_le_bytes()); // colLeft
ws_view.push(0x40); // icvHdr
ws_view.push(0); // reserved2
ws_view.extend_from_slice(&0u16.to_le_bytes()); // reserved3
ws_view.extend_from_slice(&0u16.to_le_bytes()); // wScale
ws_view.extend_from_slice(&0u16.to_le_bytes()); // wScaleNormal
ws_view.extend_from_slice(&0u16.to_le_bytes()); // wScaleSLV
ws_view.extend_from_slice(&0u16.to_le_bytes()); // wScalePLV
ws_view.extend_from_slice(&0u32.to_le_bytes()); // iWbkView
let mut sheet = rec(BRT_BEGIN_WS_VIEWS, &[]);
sheet.extend_from_slice(&rec(BRT_BEGIN_WS_VIEW, &ws_view));
sheet.extend_from_slice(&rec(BRT_END_WS_VIEW, &[]));
sheet.extend_from_slice(&rec(BRT_END_WS_VIEWS, &[]));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", sheet.as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
assert_eq!(wb.sheets[0].sheet_view().show_headers, Some(true));
}
#[test]
fn xlsb_ws_prop_tab_color_surfaces_public_metadata() {
const BRT_WS_PROP: u32 = 147;
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("Color"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let mut color = Vec::new();
color.push(1 | (2 << 1)); // fValidRGB + xColorType=ARGB
color.push(0); // index, ignored for ARGB
color.extend_from_slice(&0i16.to_le_bytes()); // nTintAndShade
color.extend_from_slice(&[0x12, 0x34, 0x56, 0xFF]); // RGB + alpha
let mut ws_prop = Vec::new();
ws_prop.extend_from_slice(&0u16.to_le_bytes()); // worksheet property flags
ws_prop.push(0); // filter/conditional-format flags
ws_prop.extend_from_slice(&color);
ws_prop.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // rwSync ignored
ws_prop.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // colSync ignored
ws_prop.extend_from_slice(&wstr("")); // code name
let sheet = rec(BRT_WS_PROP, &ws_prop);
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", sheet.as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
assert_eq!(wb.sheets[0].tab_color(), Some(Color::rgb(0x12, 0x34, 0x56)));
}
#[test]
fn xlsb_sheet_protection_surfaces_public_metadata() {
fn sheet_protection() -> Vec<u8> {
let mut out = Vec::new();
out.extend_from_slice(&0u16.to_le_bytes()); // protpwd
for allowed in [
1u32, // fLocked
0, // fObjects (not modeled)
0, // fScenarios (not modeled)
1, // fFormatCells
0, // fFormatColumns
1, // fFormatRows
0, // fInsertColumns
1, // fInsertRows
1, // fInsertHyperlinks
0, // fDeleteColumns
1, // fDeleteRows
1, // fSelLockedCells (not modeled)
1, // fSort
1, // fAutoFilter
0, // fPivotTables
1, // fSelUnlockedCells (not modeled)
] {
out.extend_from_slice(&allowed.to_le_bytes());
}
out
}
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("Protected"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let sheet = rec(BRT_SHEET_PROTECTION, &sheet_protection());
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", sheet.as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
let sheet = &wb.sheets[0];
assert!(sheet.is_protected());
assert_eq!(
sheet.protection_options(),
Some(crate::ProtectionOptions {
sort: true,
auto_filter: true,
format_cells: true,
format_rows: true,
insert_rows: true,
insert_hyperlinks: true,
delete_rows: true,
..Default::default()
})
);
let metadata = sheet.metadata();
assert!(metadata.protected);
assert_eq!(metadata.protection_options, sheet.protection_options());
}
#[test]
fn xlsb_book_protection_surfaces_workbook_metadata() {
const BRT_BOOK_PROTECTION: u32 = 534;
fn book_protection(flags: u16) -> Vec<u8> {
let mut out = Vec::new();
out.extend_from_slice(&0u16.to_le_bytes()); // protpwdBook
out.extend_from_slice(&0u16.to_le_bytes()); // protpwdRev
out.extend_from_slice(&flags.to_le_bytes()); // wFlags
out
}
let mut wb_bin = rec(BRT_BOOK_PROTECTION, &book_protection(0x0001));
let mut sheet_ref = vec![0u8; 8]; // hsState + iTabID
sheet_ref.extend_from_slice(&wstr("rId1"));
sheet_ref.extend_from_slice(&wstr("LockedBook"));
wb_bin.extend_from_slice(&rec(BRT_BUNDLE_SH, &sheet_ref));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", [].as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
assert!(wb.is_structure_protected());
assert!(wb.metadata().structure_protected);
}
#[test]
fn xlsb_outline_records_surface_public_metadata() {
fn row_hdr(row: u32, level: u8, collapsed: bool) -> Vec<u8> {
let mut out = Vec::new();
out.extend_from_slice(&row.to_le_bytes());
out.extend_from_slice(&0u32.to_le_bytes()); // ixfe
out.extend_from_slice(&0u16.to_le_bytes()); // miyRw
let mut flags = u16::from(level) << 8;
if collapsed {
flags |= 1 << 11;
}
out.extend_from_slice(&flags.to_le_bytes());
out
}
fn col_info(first: u32, last: u32, level: u8) -> Vec<u8> {
let mut out = Vec::new();
out.extend_from_slice(&first.to_le_bytes());
out.extend_from_slice(&last.to_le_bytes());
out.extend_from_slice(&0x08FFu32.to_le_bytes()); // default width
out.extend_from_slice(&0u32.to_le_bytes()); // ixfe
out.extend_from_slice(&(u16::from(level) << 8).to_le_bytes());
out
}
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("Outline"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let mut ws_prop = Vec::new();
ws_prop.extend_from_slice(&0u16.to_le_bytes()); // summaries above/left
ws_prop.push(0); // filter/conditional-format flags
ws_prop.extend_from_slice(&[0u8; 8]); // no tab color
ws_prop.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // rwSync ignored
ws_prop.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // colSync ignored
ws_prop.extend_from_slice(&wstr("")); // code name
let mut sheet = rec(BRT_WS_PROP, &ws_prop);
sheet.extend_from_slice(&rec(BRT_COL_INFO, &col_info(1, 3, 3)));
sheet.extend_from_slice(&rec(BRT_ROW_HDR, &row_hdr(2, 2, true)));
sheet.extend_from_slice(&rec(BRT_ROW_HDR, &row_hdr(3, 2, false)));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", sheet.as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
let sheet = &wb.sheets[0];
assert_eq!(sheet.row_outline_levels().get(&2), Some(&2));
assert_eq!(sheet.row_outline_levels().get(&3), Some(&2));
assert!(sheet.collapsed_rows().contains(&2));
assert_eq!(sheet.col_outline_levels().get(&1), Some(&3));
assert_eq!(sheet.col_outline_levels().get(&3), Some(&3));
assert!(!sheet.outline_summary_below());
assert!(!sheet.outline_summary_right());
let metadata = sheet.metadata();
assert_eq!(metadata.row_outline_levels.get(&2), Some(&2));
assert_eq!(metadata.col_outline_levels.get(&1), Some(&3));
assert!(metadata.collapsed_rows.contains(&2));
assert!(!metadata.outline_summary_below);
assert!(!metadata.outline_summary_right);
}
#[test]
fn xlsb_page_setup_records_surface_public_metadata() {
const BRT_MARGINS: u32 = 475;
const BRT_PRINT_OPTIONS: u32 = 476;
const BRT_PAGE_SETUP: u32 = 477;
const BRT_BEGIN_HEADER_FOOTER: u32 = 478;
const BRT_END_HEADER_FOOTER: u32 = 479;
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("Print"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let mut margins = Vec::new();
for margin in [0.7, 0.8, 0.9, 1.0, 0.3, 0.4] {
margins.extend_from_slice(&f64::to_le_bytes(margin));
}
let mut page_setup = Vec::new();
page_setup.extend_from_slice(&9u32.to_le_bytes()); // iPaperSize: A4
page_setup.extend_from_slice(&80u32.to_le_bytes()); // iScale
page_setup.extend_from_slice(&600u32.to_le_bytes()); // iRes
page_setup.extend_from_slice(&600u32.to_le_bytes()); // iVRes
page_setup.extend_from_slice(&1u32.to_le_bytes()); // iCopies
page_setup.extend_from_slice(&3i32.to_le_bytes()); // iPageStart
page_setup.extend_from_slice(&2u32.to_le_bytes()); // iFitWidth
page_setup.extend_from_slice(&1u32.to_le_bytes()); // iFitHeight
page_setup.extend_from_slice(&((1u16 << 1) | (1u16 << 7)).to_le_bytes());
page_setup.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // szRelID null
let mut header_footer = Vec::new();
header_footer.extend_from_slice(&0u16.to_le_bytes());
header_footer.extend_from_slice(&wstr("&CQuarterly"));
header_footer.extend_from_slice(&wstr("&RPage &P"));
for _ in 0..4 {
header_footer.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes());
}
let mut sheet = rec(BRT_MARGINS, &margins);
sheet.extend_from_slice(&rec(BRT_PRINT_OPTIONS, &0b1111u16.to_le_bytes()));
sheet.extend_from_slice(&rec(BRT_PAGE_SETUP, &page_setup));
sheet.extend_from_slice(&rec(BRT_BEGIN_HEADER_FOOTER, &header_footer));
sheet.extend_from_slice(&rec(BRT_END_HEADER_FOOTER, &[]));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", sheet.as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
let ps = wb.sheets[0].page_setup().expect("page setup");
assert!(ps.landscape);
assert_eq!(ps.paper_size, Some(9));
assert_eq!(ps.scale, Some(80));
assert_eq!(ps.fit_to_width, Some(2));
assert_eq!(ps.fit_to_height, Some(1));
assert_eq!(ps.first_page_number, Some(3));
assert_eq!(ps.margins, Some((0.7, 0.8, 0.9, 1.0, 0.3, 0.4)));
assert!(ps.center_horizontally);
assert!(ps.center_vertically);
assert!(wb.sheets[0].print_headings());
assert!(wb.sheets[0].print_gridlines());
assert_eq!(ps.header.as_deref(), Some("&CQuarterly"));
assert_eq!(ps.footer.as_deref(), Some("&RPage &P"));
}
#[test]
fn xlsb_data_validations_surface_public_metadata() {
const BRT_DVAL: u32 = 64;
const BRT_BEGIN_DVALS: u32 = 573;
const BRT_END_DVALS: u32 = 574;
const BRT_DVAL_LIST: u32 = 681;
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("Validation"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let mut dvals = Vec::new();
dvals.extend_from_slice(&0u16.to_le_bytes()); // DVals flags
dvals.extend_from_slice(&0u32.to_le_bytes()); // xLeft
dvals.extend_from_slice(&0u32.to_le_bytes()); // yTop
dvals.extend_from_slice(&0u32.to_le_bytes()); // unused
dvals.extend_from_slice(&1u32.to_le_bytes()); // idvMac
let mut dval = Vec::new();
let flags = 3u32 // valType=list
| (1u32 << 8) // fAllowBlank
| (1u32 << 18); // fShowInputMsg
dval.extend_from_slice(&flags.to_le_bytes());
dval.extend_from_slice(&2i32.to_le_bytes()); // two UncheckedRfX ranges
for value in [0u32, 0, 0, 0, 2, 3, 0, 0] {
dval.extend_from_slice(&value.to_le_bytes()); // A1, A3:A4
}
dval.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // strErrorTitle null
dval.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // strError null
dval.extend_from_slice(&wstr("Pick")); // strPromptTitle
dval.extend_from_slice(&wstr("Choose one")); // strPrompt
dval.extend_from_slice(&0u32.to_le_bytes()); // formula1.cce
dval.extend_from_slice(&0u32.to_le_bytes()); // formula1.cb
dval.extend_from_slice(&0u32.to_le_bytes()); // formula2.cce
dval.extend_from_slice(&0u32.to_le_bytes()); // formula2.cb
let mut sheet = rec(BRT_BEGIN_DVALS, &dvals);
sheet.extend_from_slice(&rec(BRT_DVAL_LIST, &wstr("\"Yes,No\"")));
sheet.extend_from_slice(&rec(BRT_DVAL, &dval));
sheet.extend_from_slice(&rec(BRT_END_DVALS, &[]));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", sheet.as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
let validations = wb.sheets[0].data_validations();
assert_eq!(validations.len(), 2);
assert_eq!(validations[0].sqref, (0, 0, 0, 0));
assert_eq!(validations[1].sqref, (2, 0, 3, 0));
assert_eq!(validations[0].kind, crate::DvKind::List);
assert_eq!(validations[0].operator, crate::DvOp::Between);
assert_eq!(validations[0].formula1, "\"Yes,No\"");
assert!(validations[0].allow_blank);
assert!(validations[0].show_input_message);
assert!(!validations[0].show_error_message);
assert_eq!(
validations[0].prompt.as_ref(),
Some(&("Pick".to_string(), "Choose one".to_string()))
);
}
#[test]
fn xlsb_data_validations_consume_ranges_beyond_retained_cap() {
let mut dval = Vec::new();
dval.extend_from_slice(&3u32.to_le_bytes()); // valType=list
dval.extend_from_slice(&((MAX_DVAL_RANGES + 1) as i32).to_le_bytes());
for _ in 0..=MAX_DVAL_RANGES {
for value in [0u32, 0, 0, 0] {
dval.extend_from_slice(&value.to_le_bytes());
}
}
for _ in 0..4 {
dval.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes());
}
dval.extend_from_slice(&0u32.to_le_bytes()); // formula1.cce
dval.extend_from_slice(&0u32.to_le_bytes()); // formula1.cb
dval.extend_from_slice(&0u32.to_le_bytes()); // formula2.cce
dval.extend_from_slice(&0u32.to_le_bytes()); // formula2.cb
let validations = parse_dval(&dval, Some("\"A\"".to_string()));
assert_eq!(validations.len(), MAX_DVAL_RANGES);
assert_eq!(validations[0].kind, crate::DvKind::List);
assert_eq!(validations[0].formula1, "\"A\"");
}
#[test]
fn xlsb_defined_name_is_read_from_brt_name_record() {
let mut name = Vec::new();
name.extend_from_slice(&0u32.to_le_bytes()); // flags: visible, non-built-in
name.push(0); // chKey
name.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // workbook scope
name.extend_from_slice(&wstr("Answer"));
name.extend_from_slice(&3u32.to_le_bytes()); // cce
name.extend_from_slice(&[0x1E, 42, 0]); // PtgInt(42)
name.extend_from_slice(&0u32.to_le_bytes()); // formula cb
name.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // null comment
let mut wb_bin = rec(39, &name);
let mut bundle = vec![0u8; 8]; // hsState + iTabID
bundle.extend_from_slice(&wstr("rId1"));
bundle.extend_from_slice(&wstr("S1"));
wb_bin.extend_from_slice(&rec(BRT_BUNDLE_SH, &bundle));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", [].as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
assert_eq!(
wb.defined_names(),
&[("Answer".to_string(), "42".to_string())]
);
}
#[test]
fn xlsb_sheet_local_builtin_names_surface_page_setup() {
fn name_builtin(name_text: &str, sheet_index: u32, rgce: &[u8]) -> Vec<u8> {
let mut name = Vec::new();
name.extend_from_slice(&0x20u32.to_le_bytes()); // flags: built-in
name.push(0); // chKey: no macro shortcut
name.extend_from_slice(&sheet_index.to_le_bytes());
name.extend_from_slice(&wstr(name_text));
name.extend_from_slice(&(rgce.len() as u32).to_le_bytes());
name.extend_from_slice(rgce);
name.extend_from_slice(&0u32.to_le_bytes()); // formula cb
name.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // null comment
name
}
fn ptg_area(r0: u32, c0: u16, r1: u32, c1: u16) -> Vec<u8> {
let mut rgce = vec![0x25]; // PtgArea with BIFF12 row widths
rgce.extend_from_slice(&r0.to_le_bytes());
rgce.extend_from_slice(&r1.to_le_bytes());
rgce.extend_from_slice(&c0.to_le_bytes());
rgce.extend_from_slice(&c1.to_le_bytes());
rgce
}
let print_area = ptg_area(1, 1, 5, 3);
let mut print_titles = ptg_area(0, 0, 1, MAX_XLSB_COL_INDEX as u16);
print_titles.extend_from_slice(&ptg_area(0, 0, 1_048_575, 2));
print_titles.push(0x10); // PtgUnion
let mut wb_bin = rec(BRT_NAME, &name_builtin("_xlnm.Print_Area", 0, &print_area));
wb_bin.extend_from_slice(&rec(
BRT_NAME,
&name_builtin("_xlnm.Print_Titles", 0, &print_titles),
));
let mut bundle = vec![0u8; 8]; // hsState + iTabID
bundle.extend_from_slice(&wstr("rId1"));
bundle.extend_from_slice(&wstr("S1"));
wb_bin.extend_from_slice(&rec(BRT_BUNDLE_SH, &bundle));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", [].as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
assert!(wb.defined_names().is_empty());
let ps = wb.sheets[0].page_setup().expect("page setup");
assert_eq!(ps.print_area, Some((1, 1, 5, 3)));
assert_eq!(ps.repeat_rows, Some((0, 1)));
assert_eq!(ps.repeat_cols, Some((0, 2)));
}
#[test]
fn xlsb_sheet_local_filter_database_name_surfaces_autofilter() {
fn name_builtin(name_text: &str, sheet_index: u32, rgce: &[u8]) -> Vec<u8> {
let mut name = Vec::new();
name.extend_from_slice(&0x20u32.to_le_bytes()); // flags: built-in
name.push(0); // chKey: no macro shortcut
name.extend_from_slice(&sheet_index.to_le_bytes());
name.extend_from_slice(&wstr(name_text));
name.extend_from_slice(&(rgce.len() as u32).to_le_bytes());
name.extend_from_slice(rgce);
name.extend_from_slice(&0u32.to_le_bytes()); // formula cb
name.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes()); // null comment
name
}
let mut filter_area = vec![0x25]; // PtgArea with BIFF12 row widths
filter_area.extend_from_slice(&2u32.to_le_bytes());
filter_area.extend_from_slice(&9u32.to_le_bytes());
filter_area.extend_from_slice(&1u16.to_le_bytes());
filter_area.extend_from_slice(&4u16.to_le_bytes());
let mut wb_bin = rec(
BRT_NAME,
&name_builtin("_xlnm._FilterDatabase", 0, &filter_area),
);
let mut bundle = vec![0u8; 8]; // hsState + iTabID
bundle.extend_from_slice(&wstr("rId1"));
bundle.extend_from_slice(&wstr("S1"));
wb_bin.extend_from_slice(&rec(BRT_BUNDLE_SH, &bundle));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", [].as_slice()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
assert!(wb.defined_names().is_empty());
assert_eq!(wb.sheets[0].autofilter_range(), Some((2, 1, 9, 4)));
assert_eq!(wb.sheets[0].page_setup(), None);
}
#[test]
fn xlsb_doc_properties_surface_through_workbook_metadata() {
let mut wb_bin = vec![0u8; 8]; // hsState + iTabID
wb_bin.extend_from_slice(&wstr("rId1"));
wb_bin.extend_from_slice(&wstr("S1"));
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let core = r#"<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/"><dc:title>Binary Report</dc:title><dc:subject>Procurement</dc:subject><dc:creator>rxls xlsb</dc:creator><cp:keywords>bid,binary</cp:keywords><dc:description>XLSB public metadata</dc:description><cp:lastModifiedBy>reviewer</cp:lastModifiedBy><dcterms:created>2026-06-24T01:02:03Z</dcterms:created></cp:coreProperties>"#;
let app = r#"<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"><Application>Excel</Application><Company>ACME XLSB</Company></Properties>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (path, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", [].as_slice()),
("docProps/core.xml", core.as_bytes()),
("docProps/app.xml", app.as_bytes()),
] {
zw.start_file(path, opt).unwrap();
zw.write_all(body).unwrap();
}
let wb = Workbook::open(&zw.finish().unwrap().into_inner()).unwrap();
let metadata = wb.metadata();
assert_eq!(metadata.properties.title.as_deref(), Some("Binary Report"));
assert_eq!(metadata.properties.subject.as_deref(), Some("Procurement"));
assert_eq!(metadata.properties.creator.as_deref(), Some("rxls xlsb"));
assert_eq!(metadata.properties.keywords.as_deref(), Some("bid,binary"));
assert_eq!(
metadata.properties.description.as_deref(),
Some("XLSB public metadata")
);
assert_eq!(
metadata.properties.last_modified_by.as_deref(),
Some("reviewer")
);
assert_eq!(
metadata.properties.created.as_deref(),
Some("2026-06-24T01:02:03Z")
);
assert_eq!(metadata.properties.company.as_deref(), Some("ACME XLSB"));
}
#[test]
fn xlsb_formula_is_decoded() {
// BrtFmlaNum: cell(8) + xnum 30.0 + grbitFlags(2) + cce(4) + rgce. The rgce
// is BIFF12 SUM(A1:A2): PtgArea (u32 rows) + PtgFuncVar(SUM, 1 arg).
let mut p = vec![0u8; 8]; // col + style
p.extend_from_slice(&30.0f64.to_le_bytes()); // xnum
p.extend_from_slice(&[0, 0]); // grbitFlags
let rgce: Vec<u8> = vec![
0x25, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, // PtgArea A1:A2 (r0=0,r1=1,c0=0,c1=0)
0x22, 0x01, 0x04, 0x00, // PtgFuncVar SUM(1 arg)
];
p.extend_from_slice(&(rgce.len() as u32).to_le_bytes()); // cce
p.extend_from_slice(&rgce);
let mut cells = Vec::new();
let mut budget = crate::MAX_TEXT_BYTES;
decode_cell(
BRT_FMLA_NUM,
&p,
0,
0,
0,
&[],
&Styles::default(),
false,
&mut cells,
&mut budget,
);
assert_eq!(cells.len(), 1);
match &cells[0].value {
Cell::Formula { formula, cached } => {
assert_eq!(formula, "SUM(A1:A2)");
assert_eq!(**cached, Cell::Number(30.0));
}
o => panic!("expected a formula cell, got {o:?}"),
}
}
#[test]
fn xlsb_formula_string_is_decoded() {
// BrtFmlaString stores its cached string inside the formula cell record
// (`XLWideString`), unlike BIFF8 `.xls` which uses a following STRING
// record for string-result formulas.
let mut p = vec![0u8; 8]; // col + style
p.extend_from_slice(&wstr("cached"));
p.extend_from_slice(&[0, 0]); // grbitFlags
let rgce = vec![0x17, 1, 0, b'x']; // PtgStr("x")
p.extend_from_slice(&(rgce.len() as u32).to_le_bytes());
p.extend_from_slice(&rgce);
let mut cells = Vec::new();
let mut budget = crate::MAX_TEXT_BYTES;
decode_cell(
BRT_FMLA_STRING,
&p,
0,
0,
0,
&[],
&Styles::default(),
false,
&mut cells,
&mut budget,
);
assert_eq!(cells.len(), 1);
assert_eq!(cells[0].text, "cached");
match &cells[0].value {
Cell::Formula { formula, cached } => {
assert_eq!(formula, "\"x\"");
assert_eq!(**cached, Cell::Text("cached".to_string()));
}
other => panic!("expected a string-result formula cell, got {other:?}"),
}
}
#[test]
fn xlsb_formula_string_budget_exhaustion_sets_partial_signal() {
// BrtFmlaString: cell(8) + XLWideString cached value + grbitFlags(2) +
// CellParsedFormula. If the cached display text cannot fit in the shared
// workbook text budget, parsing must leave a partial-extraction signal.
let mut p = vec![0u8; 8]; // col + style
p.extend_from_slice(&wstr("toolong"));
p.extend_from_slice(&[0, 0]); // grbitFlags
let rgce = vec![0x17, 1, 0, b'x']; // PtgStr("x")
p.extend_from_slice(&(rgce.len() as u32).to_le_bytes());
p.extend_from_slice(&rgce);
let sh = rec(BRT_FMLA_STRING, &p);
let mut budget = "toolong".len() - 1;
let (cells, _merges, _hyperlinks, _metadata) = parse_sheet(
&sh,
&[],
&Styles::default(),
false,
&HashMap::new(),
&mut budget,
);
assert!(cells.is_empty());
assert_eq!(budget, 0);
}
#[test]
fn bundle_sh_hsstate_visibility() {
// BrtBundleSh: hsState:u32 (0 visible / 1 hidden / 2 veryHidden), iTabID:u32,
// strRelID, strName. Build one with hsState=1 and assert it parses as hidden.
let bundle = |hs_state: u32| {
let mut p = hs_state.to_le_bytes().to_vec(); // hsState
p.extend_from_slice(&0u32.to_le_bytes()); // iTabID
p.extend_from_slice(&wstr("rId1")); // strRelID
p.extend_from_slice(&wstr("S1")); // strName
let (names, _, _, _, _, _) = parse_workbook(&rec(BRT_BUNDLE_SH, &p));
names
};
assert_eq!(bundle(0), vec![("S1".to_string(), "rId1".to_string(), 0)]);
assert_eq!(bundle(1), vec![("S1".to_string(), "rId1".to_string(), 1)]);
assert_eq!(bundle(2), vec![("S1".to_string(), "rId1".to_string(), 2)]);
}
#[test]
fn xlsb_hidden_sheet_end_to_end() {
// workbook.bin: one BrtBundleSh with hsState=1 (hidden) for "Secret".
let mut wb_bin = 1u32.to_le_bytes().to_vec(); // hsState = 1 (hidden)
wb_bin.extend_from_slice(&0u32.to_le_bytes()); // iTabID
wb_bin.extend_from_slice(&wstr("rId1")); // strRelID
wb_bin.extend_from_slice(&wstr("Secret")); // strName
let wb_bin = rec(BRT_BUNDLE_SH, &wb_bin);
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Target="worksheets/sheet1.bin"/></Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (name, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", [].as_slice()),
] {
zw.start_file(name, opt).unwrap();
zw.write_all(body).unwrap();
}
let bytes = zw.finish().unwrap().into_inner();
let wb = Workbook::open(&bytes).unwrap();
assert_eq!(wb.sheets.len(), 1);
assert_eq!(wb.sheets[0].name, "Secret");
assert!(wb.sheets[0].is_hidden(), "hsState=1 => hidden");
assert!(!wb.sheets[0].is_very_hidden());
}
#[test]
fn xlsb_bundle_sheet_preserves_sheet_types_end_to_end() {
let bundle = |name: &str, rid: Option<&str>, hs_state: u32, tab_id: u32| {
let mut p = hs_state.to_le_bytes().to_vec();
p.extend_from_slice(&tab_id.to_le_bytes());
if let Some(rid) = rid {
p.extend_from_slice(&wstr(rid));
} else {
p.extend_from_slice(&0xFFFF_FFFFu32.to_le_bytes());
}
p.extend_from_slice(&wstr(name));
rec(BRT_BUNDLE_SH, &p)
};
let mut wb_bin = bundle("Data", Some("rId1"), 0, 1);
wb_bin.extend_from_slice(&bundle("Chart", Some("rId2"), 0, 2));
wb_bin.extend_from_slice(&bundle("Macro", Some("rId3"), 1, 3));
wb_bin.extend_from_slice(&bundle("Dialog", Some("rId4"), 2, 4));
wb_bin.extend_from_slice(&bundle("Module", None, 2, 5));
let rels = r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.bin"/>
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartsheet" Target="chartsheets/sheet1.bin"/>
<Relationship Id="rId3" Type="http://schemas.microsoft.com/office/2006/relationships/xlMacrosheet" Target="macrosheets/sheet1.bin"/>
<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/dialogsheet" Target="dialogsheets/sheet1.bin"/>
</Relationships>"#;
let mut zw = zip::ZipWriter::new(std::io::Cursor::new(Vec::new()));
let opt = SimpleFileOptions::default();
for (name, body) in [
("xl/workbook.bin", wb_bin.as_slice()),
("xl/_rels/workbook.bin.rels", rels.as_bytes()),
("xl/worksheets/sheet1.bin", [].as_slice()),
] {
zw.start_file(name, opt).unwrap();
zw.write_all(body).unwrap();
}
let bytes = zw.finish().unwrap().into_inner();
let wb = Workbook::open(&bytes).unwrap();
assert_eq!(
wb.sheets_metadata(),
vec![
SheetMetadata {
name: "Data".to_string(),
typ: SheetType::WorkSheet,
visible: SheetVisible::Visible,
},
SheetMetadata {
name: "Chart".to_string(),
typ: SheetType::ChartSheet,
visible: SheetVisible::Visible,
},
SheetMetadata {
name: "Macro".to_string(),
typ: SheetType::MacroSheet,
visible: SheetVisible::Hidden,
},
SheetMetadata {
name: "Dialog".to_string(),
typ: SheetType::DialogSheet,
visible: SheetVisible::VeryHidden,
},
SheetMetadata {
name: "Module".to_string(),
typ: SheetType::Vba,
visible: SheetVisible::VeryHidden,
},
]
);
assert_eq!(
wb.worksheets()
.into_iter()
.map(|(name, _)| name)
.collect::<Vec<_>>(),
vec!["Data".to_string()]
);
}
#[test]
fn parses_date1904_flag() {
// BrtWbProp with bit 0 set => 1904 date system (matches calamine/MS-XLSB).
let on = rec(BRT_WB_PROP, &[0x01, 0, 0, 0]);
assert!(parse_workbook(&on).1, "bit 0 set => 1904");
let off = rec(BRT_WB_PROP, &[0x00, 0, 0, 0]);
assert!(!parse_workbook(&off).1, "bit 0 clear => 1900");
}
}