calamine 0.36.0

// SPDX-License-Identifier: MIT
//
// Copyright 2016-2026, Johann Tuffe.

#![warn(missing_docs)]

mod cells_reader;

use std::collections::HashMap;
#[cfg(feature = "picture")]
use std::collections::HashSet;
use std::io::BufReader;
use std::io::{Read, Seek};
use std::str::FromStr;

use log::warn;
use quick_xml::events::{BytesStart, Event};
use quick_xml::name::QName;
use quick_xml::Decoder;
use quick_xml::Reader as XmlReader;
use zip::read::{ZipArchive, ZipFile};
use zip::result::ZipError;

use crate::attrs::{decode_attr, local_name_matches, RawAttributes};
use crate::datatype::DataRef;
use crate::formats::{builtin_format_by_id, detect_custom_number_format, CellFormat};
use crate::utils::{
    build_zip_path_cache, cached_zip_path, unescape_entity_to_buffer, unescape_xml,
};
use crate::vba::VbaProject;
#[cfg(feature = "picture")]
use crate::Picture;
use crate::{
    Cell, CellErrorType, Data, Dimensions, HeaderRow, Metadata, Range, Reader, ReaderRef, Sheet,
    SheetType, SheetVisible, Table,
};
pub use cells_reader::{
    XlsxCellFormula, XlsxCellFormulaMetadataRecord, XlsxCellReader, XlsxFormulaMetadata,
};

pub(crate) type XlReader<'a, RS> = XmlReader<BufReader<ZipFile<'a, RS>>>;

/// Maximum number of rows allowed in an XLSX file.
pub const MAX_ROWS: u32 = 1_048_576;

/// Maximum number of columns allowed in an XLSX file.
pub const MAX_COLUMNS: u32 = 16_384;

/// An enum for Xlsx specific errors.
#[derive(Debug)]
pub enum XlsxError {
    /// A wrapper for a variety of [`std::io::Error`] errors such as file
    /// permissions when reading an XLSX file. This can be caused by a
    /// non-existent file or parent directory or, commonly on Windows, if the
    /// file is already open in Excel.
    Io(std::io::Error),

    /// A wrapper for a variety of [`zip::result::ZipError`] errors from
    /// [`zip::ZipWriter`]. These relate to errors arising from reading the XLSX
    /// file zip container.
    Zip(zip::result::ZipError),

    /// A general error when reading a VBA project from an XLSX file.
    Vba(crate::vba::VbaError),

    /// A wrapper for a variety of [`quick_xml::Error`] XML parsing errors, but
    /// most commonly for missing data in the target file.
    Xml(quick_xml::Error),

    /// A wrapper for a variety of [`quick_xml::events::attributes::AttrError`]
    /// errors related to attributes in XML elements.
    XmlAttr(quick_xml::events::attributes::AttrError),

    /// A wrapper for a variety of [`std::string::ParseError`] errors when
    /// parsing strings.
    Parse(std::string::ParseError),

    /// A wrapper for a variety of [`std::num::ParseFloatError`] errors when
    /// parsing floats.
    ParseFloat(std::num::ParseFloatError),

    /// A wrapper for a variety of [`std::num::ParseIntError`] errors when
    /// parsing integers.
    ParseInt(std::num::ParseIntError),

    /// Unexpected end of XML file, usually when an end tag is missing.
    XmlEof(&'static str),

    /// Unexpected node in XML.
    UnexpectedNode(&'static str),

    /// XML file not found in XLSX container.
    FileNotFound(String),

    /// Relationship file not found in XLSX container.
    RelationshipNotFound,

    /// Non alphanumeric character found when parsing `A1` style range string.
    Alphanumeric(u8),

    /// Error when parsing the column name in a `A1` style range string.
    NumericColumn(u8),

    /// Missing column name when parsing an `A1` style range string.
    RangeWithoutColumnComponent,

    /// Missing row number when parsing an `A1` style range string.
    RangeWithoutRowComponent,

    /// Column number exceeds maximum allowed columns.
    ColumnNumberOverflow,

    /// Row number exceeds maximum allowed rows.
    RowNumberOverflow,

    /// Error when parsing dimensions of a worksheet.
    DimensionCount(usize),

    /// Unknown cell type (`t`) attribute.
    CellTAttribute(String),

    /// Unexpected XML element or attribute error.
    Unexpected(&'static str),

    /// Unrecognized worksheet type or state.
    Unrecognized {
        /// The data type.
        typ: &'static str,

        /// The value found.
        val: String,
    },

    /// Unrecognized cell error type.
    CellError(String),

    /// Workbook is password protected.
    Password,

    /// Specified worksheet was not found.
    WorksheetNotFound(String),

    /// Specified worksheet Table was not found.
    TableNotFound(String),

    /// The specified sheet is not a worksheet.
    NotAWorksheet(String),

    /// A wrapper for a variety of [`quick_xml::encoding::EncodingError`]
    /// encoding errors.
    Encoding(quick_xml::encoding::EncodingError),

    /// Specified Pivot Table was not found on worksheet.
    PivotTableNotFound(String),
}

from_err!(std::io::Error, XlsxError, Io);
from_err!(zip::result::ZipError, XlsxError, Zip);
from_err!(crate::vba::VbaError, XlsxError, Vba);
from_err!(quick_xml::Error, XlsxError, Xml);
from_err!(std::num::ParseFloatError, XlsxError, ParseFloat);
from_err!(std::num::ParseIntError, XlsxError, ParseInt);
from_err!(quick_xml::encoding::EncodingError, XlsxError, Encoding);
from_err!(quick_xml::events::attributes::AttrError, XlsxError, XmlAttr);

impl std::fmt::Display for XlsxError {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        match self {
            XlsxError::Io(e) => write!(f, "I/O error: {e}"),
            XlsxError::Zip(e) => write!(f, "Zip error: {e}"),
            XlsxError::Xml(e) => write!(f, "Xml error: {e}"),
            XlsxError::XmlAttr(e) => write!(f, "Xml attribute error: {e}"),
            XlsxError::Vba(e) => write!(f, "Vba error: {e}"),
            XlsxError::Parse(e) => write!(f, "Parse string error: {e}"),
            XlsxError::ParseInt(e) => write!(f, "Parse integer error: {e}"),
            XlsxError::ParseFloat(e) => write!(f, "Parse float error: {e}"),

            XlsxError::XmlEof(e) => write!(f, "Unexpected end of xml, expecting '</{e}>'"),
            XlsxError::UnexpectedNode(e) => write!(f, "Expecting '{e}' node"),
            XlsxError::FileNotFound(e) => write!(f, "File not found '{e}'"),
            XlsxError::RelationshipNotFound => write!(f, "Relationship not found"),
            XlsxError::Alphanumeric(e) => {
                write!(f, "Expecting alphanumeric character, got {e:X}")
            }
            XlsxError::NumericColumn(e) => write!(
                f,
                "Numeric character is not allowed for column name, got {e}",
            ),
            XlsxError::DimensionCount(e) => {
                write!(f, "Range dimension must be lower than 2. Got {e}")
            }
            XlsxError::CellTAttribute(e) => write!(f, "Unknown cell 't' attribute: {e:?}"),
            XlsxError::RangeWithoutColumnComponent => {
                write!(f, "Range is missing the expected column component.")
            }
            XlsxError::RangeWithoutRowComponent => {
                write!(f, "Range is missing the expected row component.")
            }
            XlsxError::ColumnNumberOverflow => write!(f, "column number overflow"),
            XlsxError::RowNumberOverflow => write!(f, "row number overflow"),
            XlsxError::Unexpected(e) => write!(f, "{e}"),
            XlsxError::Unrecognized { typ, val } => write!(f, "Unrecognized {typ}: {val}"),
            XlsxError::CellError(e) => write!(f, "Unsupported cell error value '{e}'"),
            XlsxError::WorksheetNotFound(n) => write!(f, "Worksheet '{n}' not found"),
            XlsxError::Password => write!(f, "Workbook is password protected"),
            XlsxError::TableNotFound(n) => write!(f, "Table '{n}' not found"),
            XlsxError::NotAWorksheet(typ) => write!(f, "Expecting a worksheet, got {typ}"),
            XlsxError::Encoding(e) => write!(f, "XML encoding error: {e}"),
            XlsxError::PivotTableNotFound(pt) => {
                write!(f, "Pivot Table '{pt}' was not found on worksheet")
            }
        }
    }
}

impl std::error::Error for XlsxError {
    fn source(&self) -> Option<&(dyn std::error::Error + 'static)> {
        match self {
            XlsxError::Io(e) => Some(e),
            XlsxError::Zip(e) => Some(e),
            XlsxError::Xml(e) => Some(e),
            XlsxError::Vba(e) => Some(e),
            XlsxError::Parse(e) => Some(e),
            XlsxError::ParseInt(e) => Some(e),
            XlsxError::ParseFloat(e) => Some(e),
            XlsxError::Encoding(e) => Some(e),
            _ => None,
        }
    }
}

impl FromStr for CellErrorType {
    type Err = XlsxError;
    fn from_str(s: &str) -> Result<Self, Self::Err> {
        match s {
            "#DIV/0!" => Ok(CellErrorType::Div0),
            "#N/A" => Ok(CellErrorType::NA),
            "#NAME?" => Ok(CellErrorType::Name),
            "#NULL!" => Ok(CellErrorType::Null),
            "#NUM!" => Ok(CellErrorType::Num),
            "#REF!" => Ok(CellErrorType::Ref),
            "#VALUE!" => Ok(CellErrorType::Value),
            _ => Err(XlsxError::CellError(s.into())),
        }
    }
}

type Tables = Option<Vec<(String, String, Vec<String>, Dimensions)>>;

/// A struct representing xml zipped excel file
/// Xlsx, Xlsm, Xlam
pub struct Xlsx<RS> {
    zip: ZipArchive<RS>,
    /// Path to directory containing workbook.xml within zip archive (commonly "xl/")
    xl_path: String,
    /// Shared strings
    strings: Vec<String>,
    /// Sheets paths
    sheets: Vec<(String, String)>,
    /// Tables: Name, Sheet, Columns, Data dimensions
    tables: Tables,
    /// Cell (number) formats
    formats: Vec<CellFormat>,
    /// 1904 datetime system
    is_1904: bool,
    /// Metadata
    metadata: Metadata,
    /// Pictures
    #[cfg(feature = "picture")]
    pictures: Option<Vec<Picture>>,
    /// Merged Regions: Name, Sheet, Merged Dimensions
    merged_regions: Option<Vec<(String, String, Dimensions)>>,
    /// Reader options
    options: XlsxOptions,
    /// Cached ZIP path lookups (lowercased normalized -> original).
    zip_path_cache: HashMap<String, String>,
}

/// Xlsx reader options
#[derive(Debug, Default)]
#[non_exhaustive]
struct XlsxOptions {
    pub header_row: HeaderRow,
}

impl<RS: Read + Seek> Xlsx<RS> {
    fn read_package_relationships(&mut self) -> Result<(), XlsxError> {
        let mut xml = match xml_reader(&mut self.zip, "_rels/.rels", &self.zip_path_cache) {
            None => return Err(XlsxError::FileNotFound("_rels/.rels".to_string())),
            Some(x) => x?,
        };

        let mut buf = Vec::with_capacity(1024);
        loop {
            buf.clear();
            match xml.read_event_into(&mut buf) {
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"Relationships" => break,
                Ok(Event::Eof) => return Err(XlsxError::XmlEof("Relationships")),
                Err(e) => return Err(XlsxError::Xml(e)),
                _ => (),
            }
        }

        let mut document_target = None;
        loop {
            buf.clear();
            match xml.read_event_into(&mut buf) {
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"Relationship" => {
                    let (rel_type, target) =
                        get_attrs!(e, b"Type" => rel_type, b"Target" => target)?;
                    if rel_type == Some(b"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument".as_slice()) {
                        if let Some(target) = target {
                            document_target = Some(decode_attr(&xml.decoder(), target)?);
                        }
                    }
                }
                Ok(Event::End(e)) if e.local_name().as_ref() == b"Relationships" => break,
                Ok(Event::Eof) => return Err(XlsxError::XmlEof("Relationships")),
                Err(e) => return Err(XlsxError::Xml(e)),
                _ => (),
            }
        }

        if let Some(target) = document_target {
            self.xl_path = target
                .rfind('/')
                .map(|i| &target[..=i])
                .map(|p| p.strip_prefix('/').unwrap_or(p))
                .unwrap_or("")
                .to_string();
            Ok(())
        } else {
            Err(XlsxError::RelationshipNotFound)
        }
    }

    fn read_shared_strings(&mut self) -> Result<(), XlsxError> {
        let path = format!("{}sharedStrings.xml", self.xl_path);
        let mut xml = match xml_reader(&mut self.zip, path.as_ref(), &self.zip_path_cache) {
            None => return Ok(()),
            Some(x) => x?,
        };

        let mut buf = Vec::with_capacity(1024);
        loop {
            buf.clear();
            match xml.read_event_into(&mut buf) {
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"sst" => {
                    if let Some(count) = e.raw_attr(b"uniqueCount")? {
                        if let Ok(n) = atoi_simd::parse::<usize, true, false>(count) {
                            self.strings.reserve(n);
                        }
                    }
                    break;
                }
                Ok(Event::Eof) => return Err(XlsxError::XmlEof("sst")),
                Err(e) => return Err(XlsxError::Xml(e)),
                _ => (),
            }
        }

        let mut str_buf = Vec::with_capacity(1024);
        let mut str_val_buf = Vec::with_capacity(1024);
        loop {
            buf.clear();
            match xml.read_event_into(&mut buf) {
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"si" => {
                    if let Some(s) =
                        read_string_with_bufs(&mut xml, e.name(), &mut str_buf, &mut str_val_buf)?
                    {
                        self.strings.push(s);
                    }
                }
                Ok(Event::End(e)) if e.local_name().as_ref() == b"sst" => break,
                Ok(Event::Eof) => return Err(XlsxError::XmlEof("sst")),
                Err(e) => return Err(XlsxError::Xml(e)),
                _ => (),
            }
        }
        Ok(())
    }

    fn read_styles(&mut self) -> Result<(), XlsxError> {
        let path = format!("{}styles.xml", self.xl_path);
        let mut xml = match xml_reader(&mut self.zip, path.as_ref(), &self.zip_path_cache) {
            None => return Ok(()),
            Some(x) => x?,
        };

        let mut number_formats = HashMap::new();

        let mut buf = Vec::with_capacity(1024);
        let mut inner_buf = Vec::with_capacity(1024);
        loop {
            buf.clear();
            match xml.read_event_into(&mut buf) {
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"numFmts" => loop {
                    inner_buf.clear();
                    match xml.read_event_into(&mut inner_buf) {
                        Ok(Event::Start(e)) if e.local_name().as_ref() == b"numFmt" => {
                            let (id, format_code) =
                                get_attrs!(e, b"numFmtId" => id, b"formatCode" => fmt)?;
                            if let (Some(id), Some(fc)) = (id, format_code) {
                                let format = decode_attr(&xml.decoder(), fc)?;
                                number_formats.insert(id.to_vec(), format);
                            }
                        }
                        Ok(Event::End(e)) if e.local_name().as_ref() == b"numFmts" => break,
                        Ok(Event::Eof) => return Err(XlsxError::XmlEof("numFmts")),
                        Err(e) => return Err(XlsxError::Xml(e)),
                        _ => (),
                    }
                },
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"cellXfs" => loop {
                    inner_buf.clear();
                    match xml.read_event_into(&mut inner_buf) {
                        Ok(Event::Start(e)) if e.local_name().as_ref() == b"xf" => {
                            self.formats.push(e.raw_attr(b"numFmtId")?.map_or(
                                CellFormat::Other,
                                |val| match number_formats.get(val) {
                                    Some(fmt) => detect_custom_number_format(fmt),
                                    None => builtin_format_by_id(val),
                                },
                            ));
                        }
                        Ok(Event::End(e)) if e.local_name().as_ref() == b"cellXfs" => break,
                        Ok(Event::Eof) => return Err(XlsxError::XmlEof("cellXfs")),
                        Err(e) => return Err(XlsxError::Xml(e)),
                        _ => (),
                    }
                },
                Ok(Event::End(e)) if e.local_name().as_ref() == b"styleSheet" => break,
                Ok(Event::Eof) => return Err(XlsxError::XmlEof("styleSheet")),
                Err(e) => return Err(XlsxError::Xml(e)),
                _ => (),
            }
        }
        Ok(())
    }

    fn read_workbook(
        &mut self,
        relationships: &HashMap<Vec<u8>, (String, String)>,
    ) -> Result<(), XlsxError> {
        let path = format!("{}workbook.xml", self.xl_path);
        let mut xml = match xml_reader(&mut self.zip, path.as_ref(), &self.zip_path_cache) {
            None => return Ok(()),
            Some(x) => x?,
        };
        let mut defined_names = Vec::new();
        let mut buf = Vec::with_capacity(1024);
        let mut val_buf = Vec::with_capacity(1024);
        loop {
            buf.clear();
            match xml.read_event_into(&mut buf) {
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"sheet" => {
                    let mut name = String::new();
                    let mut rel_id = Vec::new();
                    let mut visible = SheetVisible::Visible;
                    for attr in e.iter_raw_attrs() {
                        let (key, val) = attr?;
                        match key {
                            b"name" => {
                                name = decode_attr(&xml.decoder(), val)?;
                            }
                            b"state" => {
                                visible = match val {
                                    b"visible" => SheetVisible::Visible,
                                    b"hidden" => SheetVisible::Hidden,
                                    b"veryHidden" => SheetVisible::VeryHidden,
                                    v => {
                                        let v = xml.decoder().decode(v)?;
                                        return Err(XlsxError::Unrecognized {
                                            typ: "sheet:state",
                                            val: v.into_owned(),
                                        });
                                    }
                                }
                            }
                            // Ignore the "r:id" attribute namespace and match on the "id" name.
                            key if local_name_matches(key, b"id") => {
                                rel_id = val.to_vec();
                            }
                            _ => {}
                        }
                    }
                    let (r, rel_type) = relationships
                        .get(&rel_id)
                        .ok_or(XlsxError::RelationshipNotFound)?;
                    // target may be absolute or relative path;
                    let path = if let Some(abs_path) = r.strip_prefix("/") {
                        abs_path.to_string()
                    } else {
                        format!("{}{r}", self.xl_path)
                    };
                    let typ = match rel_type.split('/').next_back() {
                        Some("worksheet") => SheetType::WorkSheet,
                        Some("chartsheet") => SheetType::ChartSheet,
                        Some("dialogsheet") => SheetType::DialogSheet,
                        _ => {
                            return Err(XlsxError::Unrecognized {
                                typ: "sheet:type",
                                val: path.to_string(),
                            })
                        }
                    };
                    self.metadata.sheets.push(Sheet {
                        name: name.to_string(),
                        typ,
                        visible,
                    });
                    self.sheets.push((name, path));
                }
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"workbookPr" => {
                    self.is_1904 = match e.raw_attr(b"date1904")? {
                        Some(v) => v == b"1" || v == b"true",
                        None => false,
                    };
                }
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"definedName" => {
                    if let Some(val) = e.raw_attr(b"name")? {
                        let name = decode_attr(&xml.decoder(), val)?;
                        val_buf.clear();
                        let mut value = String::new();
                        loop {
                            match xml.read_event_into(&mut val_buf)? {
                                Event::Text(t) => value.push_str(&t.xml10_content()?),
                                Event::GeneralRef(e) => unescape_entity_to_buffer(&e, &mut value)?,
                                Event::End(end) if end.name() == e.name() => break,
                                Event::Eof => return Err(XlsxError::XmlEof("workbook")),
                                _ => (),
                            }
                        }
                        defined_names.push((name, value));
                    }
                }
                Ok(Event::End(e)) if e.local_name().as_ref() == b"workbook" => break,
                Ok(Event::Eof) => return Err(XlsxError::XmlEof("workbook")),
                Err(e) => return Err(XlsxError::Xml(e)),
                _ => (),
            }
        }
        self.metadata.names = defined_names;
        Ok(())
    }

    fn read_relationships(&mut self) -> Result<HashMap<Vec<u8>, (String, String)>, XlsxError> {
        let rels_path = format!("{}_rels/workbook.xml.rels", self.xl_path);
        let mut xml = match xml_reader(&mut self.zip, rels_path.as_ref(), &self.zip_path_cache) {
            None => {
                return Err(XlsxError::FileNotFound(rels_path));
            }
            Some(x) => x?,
        };
        let mut relationships = HashMap::new();
        let mut buf = Vec::with_capacity(64);
        loop {
            buf.clear();
            match xml.read_event_into(&mut buf) {
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"Relationship" => {
                    let (id, rel_type, target) =
                        get_attrs!(e, b"Id" => id, b"Type" => rel_type, b"Target" => target)?;
                    if let Some(id) = id {
                        let decoder = xml.decoder();
                        let rel_type = rel_type
                            .map(|v| decoder.decode(v).map(|s| s.into_owned()))
                            .transpose()?
                            .unwrap_or_default();
                        let target = target
                            .map(|v| decoder.decode(v).map(|s| s.into_owned()))
                            .transpose()?
                            .unwrap_or_default();
                        relationships.insert(id.to_vec(), (target, rel_type));
                    }
                }
                Ok(Event::End(e)) if e.local_name().as_ref() == b"Relationships" => break,
                Ok(Event::Eof) => return Err(XlsxError::XmlEof("Relationships")),
                Err(e) => return Err(XlsxError::Xml(e)),
                _ => (),
            }
        }
        Ok(relationships)
    }

    // sheets must be added before this is called!!
    fn read_table_metadata(&mut self) -> Result<(), XlsxError> {
        let mut new_tables = Vec::new();
        for (sheet_name, sheet_path) in &self.sheets {
            let last_folder_index = sheet_path.rfind('/').expect("should be in a folder");
            let (base_folder, file_name) = sheet_path.split_at(last_folder_index);
            let rel_path = format!("{base_folder}/_rels{file_name}.rels");

            let mut table_locations = Vec::new();
            let mut buf = Vec::with_capacity(64);
            // we need another mutable borrow of self.zip later so we enclose this borrow within braces
            {
                let mut xml = match xml_reader(&mut self.zip, &rel_path, &self.zip_path_cache) {
                    None => continue,
                    Some(x) => x?,
                };
                loop {
                    buf.clear();
                    match xml.read_event_into(&mut buf) {
                        Ok(Event::Start(e)) if e.local_name().as_ref() == b"Relationship" => {
                            let (_, target, typ) =
                                get_attrs!(e, b"Id" => id, b"Target" => target, b"Type" => typ)?;
                            let table_type = typ == Some(b"http://schemas.openxmlformats.org/officeDocument/2006/relationships/table" as &[u8]);
                            if table_type {
                                let target = match target {
                                    Some(t) => decode_attr(&xml.decoder(), t)?,
                                    None => String::new(),
                                };
                                if target.starts_with("../") {
                                    // Relative path.
                                    let new_index =
                                        base_folder.rfind('/').expect("Must be a parent folder");
                                    let full_path =
                                        format!("{}{}", &base_folder[..new_index], &target[2..]);
                                    table_locations.push(full_path);
                                } else if let Some(stripped) = target.strip_prefix('/') {
                                    // Absolute path.
                                    table_locations.push(stripped.to_string());
                                } else if !target.is_empty() {
                                    // Assume absolute path without leading slash.
                                    table_locations.push(target);
                                }
                            }
                        }
                        Ok(Event::End(e)) if e.local_name().as_ref() == b"Relationships" => break,
                        Ok(Event::Eof) => return Err(XlsxError::XmlEof("Relationships")),
                        Err(e) => return Err(XlsxError::Xml(e)),
                        _ => (),
                    }
                }
            }
            for table_file in table_locations {
                let mut xml = match xml_reader(&mut self.zip, &table_file, &self.zip_path_cache) {
                    None => continue,
                    Some(x) => x?,
                };
                let mut column_names = Vec::new();
                let mut table_meta = InnerTableMetadata::new();
                loop {
                    buf.clear();
                    match xml.read_event_into(&mut buf) {
                        Ok(Event::Start(e)) if e.local_name().as_ref() == b"table" => {
                            for attr in e.iter_raw_attrs() {
                                let (key, val) = attr?;
                                match key {
                                    b"displayName" => {
                                        table_meta.display_name = decode_attr(&xml.decoder(), val)?;
                                    }
                                    b"ref" => {
                                        table_meta.ref_cells =
                                            xml.decoder().decode(val)?.into_owned();
                                    }
                                    b"headerRowCount" => {
                                        table_meta.header_row_count =
                                            xml.decoder().decode(val)?.parse()?;
                                    }
                                    b"totalsRowCount" => {
                                        table_meta.totals_row_count =
                                            xml.decoder().decode(val)?.parse()?;
                                    }
                                    _ => {}
                                }
                            }
                        }
                        Ok(Event::Start(e)) if e.local_name().as_ref() == b"tableColumn" => {
                            if let Some(val) = e.raw_attr(b"name")? {
                                column_names.push(decode_attr(&xml.decoder(), val)?);
                            }
                        }
                        Ok(Event::End(e)) if e.local_name().as_ref() == b"table" => break,
                        Ok(Event::Eof) => return Err(XlsxError::XmlEof("Table")),
                        Err(e) => return Err(XlsxError::Xml(e)),
                        _ => (),
                    }
                }
                let mut dims = get_dimension(table_meta.ref_cells.as_bytes())?;
                if table_meta.header_row_count != 0 {
                    dims.start.0 += table_meta.header_row_count;
                }
                if table_meta.totals_row_count != 0 {
                    dims.end.0 -= table_meta.header_row_count;
                }

                new_tables.push((
                    table_meta.display_name,
                    sheet_name.clone(),
                    column_names,
                    dims,
                ));
            }
        }
        self.tables = Some(new_tables);
        Ok(())
    }

    // Read pictures and their DrawingML/rich-data anchor positions.
    #[cfg(feature = "picture")]
    fn read_pictures(&mut self) -> Result<(), XlsxError> {
        let mut media: HashMap<String, (String, Vec<u8>)> = HashMap::new();
        let media_path = format!("{}media", self.xl_path);
        for i in 0..self.zip.len() {
            let mut zfile = self.zip.by_index(i)?;
            let zname = zfile.name();
            if zname.starts_with(&media_path) {
                if let Some(ext) = zname.split('.').next_back() {
                    if [
                        "emf", "wmf", "pict", "jpeg", "jpg", "png", "dib", "gif", "tiff", "eps",
                        "bmp", "wpg",
                    ]
                    .contains(&ext)
                    {
                        let norm = zname.to_ascii_lowercase();
                        let ext = ext.to_string();
                        let mut buf: Vec<u8> = Vec::new();
                        zfile.read_to_end(&mut buf)?;
                        media.insert(norm, (ext, buf));
                    }
                }
            }
        }

        if media.is_empty() {
            return Ok(());
        }

        let mut pics: Vec<Picture> = Vec::new();

        // Track which media paths we've already assigned to an anchor.
        let mut seen: HashSet<String> = HashSet::new();

        // Parse DrawingML anchors for each sheet.
        for sheet_idx in 0..self.sheets.len() {
            let sheet_name = self.sheets[sheet_idx].0.clone();
            let sheet_path = self.sheets[sheet_idx].1.clone();

            let last_slash = sheet_path.rfind('/').unwrap_or(0);
            let base_folder = &sheet_path[..last_slash];
            let file_name = &sheet_path[last_slash..];
            let rel_path = format!("{base_folder}/_rels{file_name}.rels");

            // Collect drawing paths referenced from this sheet's rels.
            let mut drawing_paths: Vec<String> = Vec::new();
            {
                let mut xml = match xml_reader(&mut self.zip, &rel_path, &self.zip_path_cache) {
                    None => continue,
                    Some(x) => x?,
                };
                let mut buf = Vec::with_capacity(64);
                loop {
                    buf.clear();
                    match xml.read_event_into(&mut buf) {
                        Ok(Event::Start(e)) if e.local_name().as_ref() == b"Relationship" => {
                            let (rel_type, target) =
                                get_attrs!(e, b"Type" => typ, b"Target" => target)?;
                            let is_drawing = rel_type.is_some_and(|t| t.ends_with(b"/drawing"));
                            if is_drawing {
                                if let Some(target) = target {
                                    let target = decode_attr(&xml.decoder(), target)?;
                                    if !target.is_empty() {
                                        drawing_paths.push(resolve_path(base_folder, &target));
                                    }
                                }
                            }
                        }
                        Ok(Event::End(e)) if e.local_name().as_ref() == b"Relationships" => break,
                        Ok(Event::Eof) | Err(_) => break,
                        _ => (),
                    }
                }
            }

            for drawing_path in drawing_paths {
                // Map rId -> normalized media path from the drawing's own rels.
                let draw_last_slash = drawing_path.rfind('/').unwrap_or(0);
                let draw_base = &drawing_path[..draw_last_slash];
                let draw_file = &drawing_path[draw_last_slash..];
                let draw_rel_path = format!("{draw_base}/_rels{draw_file}.rels");

                let mut rid_to_media: HashMap<String, String> = HashMap::new();
                {
                    let mut xml =
                        match xml_reader(&mut self.zip, &draw_rel_path, &self.zip_path_cache) {
                            None => continue,
                            Some(x) => x?,
                        };
                    let mut buf = Vec::with_capacity(64);
                    loop {
                        buf.clear();
                        match xml.read_event_into(&mut buf) {
                            Ok(Event::Start(e)) if e.local_name().as_ref() == b"Relationship" => {
                                let (id, target, rel_type) = get_attrs!(e, b"Id" => id, b"Target" => target, b"Type" => typ)?;
                                let is_image = rel_type.is_some_and(|t| t.ends_with(b"/image"));
                                if is_image {
                                    if let Some(id) = id {
                                        let id = decode_attr(&xml.decoder(), id)?;
                                        if !id.is_empty() {
                                            let target = match target {
                                                Some(t) => decode_attr(&xml.decoder(), t)?,
                                                None => String::new(),
                                            };
                                            let norm = resolve_path(draw_base, &target)
                                                .to_ascii_lowercase();
                                            rid_to_media.insert(id, norm);
                                        }
                                    }
                                }
                            }
                            Ok(Event::End(e)) if e.local_name().as_ref() == b"Relationships" => {
                                break
                            }
                            Ok(Event::Eof) | Err(_) => break,
                            _ => (),
                        }
                    }
                }

                // Parse drawing XML with a single-pass per-anchor state machine.
                {
                    let mut xml =
                        match xml_reader(&mut self.zip, &drawing_path, &self.zip_path_cache) {
                            None => continue,
                            Some(x) => x?,
                        };
                    let mut buf = Vec::with_capacity(1024);
                    let mut in_anchor = false;
                    let mut in_from = false;
                    let mut in_col = false;
                    let mut in_row = false;
                    let mut anchor_col: u32 = 0;
                    let mut anchor_row: u32 = 0;
                    let mut current_name = String::new();
                    let mut current_rid = String::new();

                    loop {
                        buf.clear();
                        match xml.read_event_into(&mut buf) {
                            Ok(Event::Start(e)) => {
                                match e.local_name().as_ref() {
                                    b"twoCellAnchor" | b"oneCellAnchor" | b"absoluteAnchor" => {
                                        // absoluteAnchor uses <pos> in EMUs
                                        // rather than <from>, so its position
                                        // is reported as (0, 0).
                                        in_anchor = true;
                                        anchor_col = 0;
                                        anchor_row = 0;
                                        current_name.clear();
                                        current_rid.clear();
                                    }
                                    b"from" if in_anchor => in_from = true,
                                    b"col" if in_from => in_col = true,
                                    b"row" if in_from => in_row = true,
                                    b"blip" if in_anchor => {
                                        // The embed rId is namespaced (`r:embed`).
                                        if let Some(val) = e.raw_attr_local(b"embed")? {
                                            current_rid = decode_attr(&xml.decoder(), val)?;
                                        }
                                    }
                                    b"cNvPr" if in_anchor => {
                                        if let Some(val) = e.raw_attr(b"name")? {
                                            current_name = decode_attr(&xml.decoder(), val)?;
                                        }
                                    }
                                    _ => (),
                                }
                            }
                            Ok(Event::End(e)) => match e.local_name().as_ref() {
                                b"twoCellAnchor" | b"oneCellAnchor" | b"absoluteAnchor" => {
                                    if !current_rid.is_empty() {
                                        if let Some(mp) = rid_to_media.get(&current_rid) {
                                            if let Some((ext, data)) = media.get(mp) {
                                                seen.insert(mp.clone());
                                                pics.push(Picture {
                                                    extension: ext.clone(),
                                                    data: data.clone(),
                                                    sheet_name: sheet_name.clone(),
                                                    row: anchor_row,
                                                    col: anchor_col,
                                                    name: current_name.clone(),
                                                });
                                            }
                                        }
                                    }
                                    in_anchor = false;
                                }
                                b"from" => in_from = false,
                                b"col" => in_col = false,
                                b"row" => in_row = false,
                                _ => (),
                            },
                            Ok(Event::Text(e)) => {
                                if in_col {
                                    let s = e.xml10_content()?;
                                    anchor_col = s.trim().parse().unwrap_or(0);
                                } else if in_row {
                                    let s = e.xml10_content()?;
                                    anchor_row = s.trim().parse().unwrap_or(0);
                                }
                            }
                            Ok(Event::Eof) => break,
                            Err(e) => return Err(XlsxError::Xml(e)),
                            _ => (),
                        }
                    }
                }
            }
        }

        // Parse new-style embedded rich-data images (xl/richData/).
        self.read_rich_data_pictures(&media, &mut seen, &mut pics)?;

        // Parse any media not matched to an anchor and add with an empty position.
        for (path, (ext, data)) in &media {
            if !seen.contains(path) {
                pics.push(Picture {
                    extension: ext.clone(),
                    data: data.clone(),
                    sheet_name: String::new(),
                    row: 0,
                    col: 0,
                    name: String::new(),
                });
            }
        }

        if !pics.is_empty() {
            self.pictures = Some(pics);
        }
        Ok(())
    }

    // Parse xl/richData/ to find images embedded via Excel 365 rich data.
    #[cfg(feature = "picture")]
    fn read_rich_data_pictures(
        &mut self,
        media: &HashMap<String, (String, Vec<u8>)>,
        seen: &mut HashSet<String>,
        pics: &mut Vec<Picture>,
    ) -> Result<(), XlsxError> {
        // Get rId -> normalized media path from richValueRel.xml.rels.
        let mut rid_to_media: HashMap<String, String> = HashMap::new();
        {
            let path = "xl/richData/_rels/richValueRel.xml.rels";
            let mut xml = match xml_reader(&mut self.zip, path, &self.zip_path_cache) {
                None => return Ok(()),
                Some(x) => x?,
            };
            let mut buf = Vec::with_capacity(64);
            loop {
                buf.clear();
                match xml.read_event_into(&mut buf) {
                    Ok(Event::Start(e)) if e.local_name().as_ref() == b"Relationship" => {
                        let (id, target, rel_type) =
                            get_attrs!(e, b"Id" => id, b"Target" => target, b"Type" => typ)?;
                        let is_image = rel_type.is_some_and(|t| t.ends_with(b"/image"));
                        if is_image {
                            if let Some(id) = id {
                                let id = decode_attr(&xml.decoder(), id)?;
                                if !id.is_empty() {
                                    let target = match target {
                                        Some(t) => decode_attr(&xml.decoder(), t)?,
                                        None => String::new(),
                                    };
                                    let norm =
                                        resolve_path("xl/richData", &target).to_ascii_lowercase();
                                    rid_to_media.insert(id, norm);
                                }
                            }
                        }
                    }
                    Ok(Event::End(e)) if e.local_name().as_ref() == b"Relationships" => break,
                    Ok(Event::Eof) | Err(_) => break,
                    _ => (),
                }
            }
        }

        if rid_to_media.is_empty() {
            return Ok(());
        }

        // Get ordered list of rIds from richValueRel.xml, index -> media path.
        let mut idx_to_media: Vec<String> = Vec::new();
        {
            let path = "xl/richData/richValueRel.xml";
            let mut xml = match xml_reader(&mut self.zip, path, &self.zip_path_cache) {
                None => return Ok(()),
                Some(x) => x?,
            };
            let mut buf = Vec::with_capacity(64);
            loop {
                buf.clear();
                match xml.read_event_into(&mut buf) {
                    Ok(Event::Start(e)) if e.local_name().as_ref() == b"rel" => {
                        // The rId is namespaced (`r:id`).
                        let rid = match e.raw_attr_local(b"id")? {
                            Some(val) => decode_attr(&xml.decoder(), val)?,
                            None => String::new(),
                        };
                        let media_path = rid_to_media.get(&rid).cloned().unwrap_or_default();
                        idx_to_media.push(media_path);
                    }
                    Ok(Event::Eof) | Err(_) => break,
                    _ => (),
                }
            }
        }

        if idx_to_media.is_empty() {
            return Ok(());
        }

        // Convert rich-value index to image index (first <v>) from rdrichvalue.xml.
        let mut rv_to_img_idx: HashMap<usize, usize> = HashMap::new();
        {
            let path = "xl/richData/rdrichvalue.xml";
            let mut xml = match xml_reader(&mut self.zip, path, &self.zip_path_cache) {
                None => return Ok(()),
                Some(x) => x?,
            };
            let mut buf = Vec::with_capacity(64);
            let mut rv_idx: usize = 0;
            let mut in_rv = false;
            let mut in_v = false;
            let mut v_count: usize = 0;
            let mut img_idx: usize = 0;

            loop {
                buf.clear();
                match xml.read_event_into(&mut buf) {
                    Ok(Event::Start(e)) if e.local_name().as_ref() == b"rv" => {
                        in_rv = true;
                        v_count = 0;
                        img_idx = 0;
                    }
                    Ok(Event::Start(e)) if in_rv && e.local_name().as_ref() == b"v" => {
                        in_v = true;
                    }
                    Ok(Event::Text(e)) if in_v && v_count == 0 => {
                        img_idx = e.xml10_content()?.trim().parse().unwrap_or(0);
                    }
                    Ok(Event::End(e)) if e.local_name().as_ref() == b"v" => {
                        in_v = false;
                        v_count += 1;
                    }
                    Ok(Event::End(e)) if e.local_name().as_ref() == b"rv" => {
                        rv_to_img_idx.insert(rv_idx, img_idx);
                        rv_idx += 1;
                        in_rv = false;
                    }
                    Ok(Event::Eof) | Err(_) => break,
                    _ => (),
                }
            }
        }

        if rv_to_img_idx.is_empty() {
            return Ok(());
        }

        // Scan each worksheet for cells carrying a vm (rich-value) attribute.
        for sheet_idx in 0..self.sheets.len() {
            let sheet_name = self.sheets[sheet_idx].0.clone();
            let sheet_path = self.sheets[sheet_idx].1.clone();

            let mut xml = match xml_reader(&mut self.zip, &sheet_path, &self.zip_path_cache) {
                None => continue,
                Some(x) => x?,
            };

            let mut buf = Vec::with_capacity(1024);
            let mut current_row: u32 = 0;

            loop {
                buf.clear();
                match xml.read_event_into(&mut buf) {
                    Ok(Event::Start(e)) if e.local_name().as_ref() == b"row" => {
                        if let Some(r) = e.raw_attr(b"r")? {
                            current_row = atoi_simd::parse::<u32, true, false>(r).unwrap_or(1) - 1;
                        }
                    }
                    Ok(Event::Start(e)) if e.local_name().as_ref() == b"c" => {
                        let (vm, r) = get_attrs!(e, b"vm" => vm, b"r" => r)?;
                        let vm = vm.and_then(|v| atoi_simd::parse::<usize, true, false>(v).ok());
                        if let Some(vm_val) = vm {
                            let rv_idx = vm_val.saturating_sub(1);
                            if let Some(&img_idx) = rv_to_img_idx.get(&rv_idx) {
                                if let Some(media_path) = idx_to_media.get(img_idx) {
                                    if !media_path.is_empty() {
                                        if let Some((ext, data)) = media.get(media_path) {
                                            let col = r.map_or(0, col_from_cell_ref);
                                            seen.insert(media_path.clone());
                                            pics.push(Picture {
                                                extension: ext.clone(),
                                                data: data.clone(),
                                                sheet_name: sheet_name.clone(),
                                                row: current_row,
                                                col,
                                                name: String::new(),
                                            });
                                        }
                                    }
                                }
                            }
                        }
                    }
                    Ok(Event::Eof) => break,
                    Err(e) => return Err(XlsxError::Xml(e)),
                    _ => (),
                }
            }
        }

        Ok(())
    }

    /// Check if the workbook uses the 1904 date system.
    ///
    /// Returns `true` if the workbook uses the 1904 date epoch (used by some older
    /// Mac versions of Excel), or `false` if it uses the standard 1900 date epoch.
    ///
    /// # Examples
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let workbook: Xlsx<_> = open_workbook("tests/date_1904.xlsx")?;
    ///     let is_1904 = workbook.has_1904_epoch();
    ///     println!("Uses 1904 date system: {}", is_1904);
    ///     Ok(())
    /// }
    /// ```
    pub fn has_1904_epoch(&self) -> bool {
        self.is_1904
    }

    /// Get all Pivot Tables in a workbook.
    ///
    /// # Note
    ///
    /// This function is required before working with Pivot Table Data due to reliance on metadata in `PivotTableRef`.
    pub fn pivot_tables(&mut self) -> Result<PivotTables, XlsxError>
    where
        RS: Read + Seek,
    {
        let mut pivot_tables = PivotTables::new();
        for (sheet_name, sheet_path) in self.sheets.iter() {
            for pivot_path in
                find_pivot_table_paths_from_sheet(&mut self.zip, sheet_path, &self.zip_path_cache)?
                    .iter()
            {
                let name = find_pivot_name_from_pivot_path(
                    &mut self.zip,
                    pivot_path,
                    &self.zip_path_cache,
                )?;
                let definition_cache_path = find_pivot_cache_definitions_from_pivot(
                    &mut self.zip,
                    pivot_path,
                    &self.zip_path_cache,
                )?;
                let record_cache_path = find_pivot_cache_records_from_definitions(
                    &mut self.zip,
                    &definition_cache_path,
                    &self.zip_path_cache,
                    &format!("{}pivotCache", self.xl_path),
                )?;

                pivot_tables.push(PivotTableRef::new(
                    name,
                    sheet_name.to_string(),
                    record_cache_path,
                    definition_cache_path,
                ));
            }
        }
        Ok(pivot_tables)
    }

    /// Get an iterator over a pivot table's cached data.
    ///
    /// Invalid Pivot Table names will return None.
    ///
    /// # Examples
    ///
    /// An example of retrieving pivot data for a Pivot Table named PivotTable1 on sheet PivotSheet1.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///
    ///     let path = "tests/pivots.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Must retrieve necessary metadata before reading Pivot Table data.
    ///     let pivot_tables = workbook.pivot_tables()?;
    ///
    ///    // Get the Pivot Table data by referencing the pivot table name and the worksheet it resides.
    ///     for row in workbook.pivot_table_data(&pivot_tables, "PivotSheet1", "PivotTable1")? {
    ///             // Do something.
    ///     }
    ///
    ///     Ok(())
    /// }
    /// ```
    ///
    pub fn pivot_table_data(
        &'_ mut self,
        pivot_tables: &PivotTables,
        sheet_name: &str,
        pivot_table_name: &str,
    ) -> Result<PivotCacheIter<'_, RS>, XlsxError> {
        match pivot_tables.0.iter().find(|pivot_table| {
            pivot_table.name() == pivot_table_name && pivot_table.sheet() == sheet_name
        }) {
            Some(pt_ref) => get_pivot_cache_iter(self, pt_ref),
            None => Err(XlsxError::PivotTableNotFound(pivot_table_name.to_string())),
        }
    }

    // sheets must be added before this is called!!
    fn read_merged_regions(&mut self) -> Result<(), XlsxError> {
        let mut regions = Vec::new();
        for (sheet_name, sheet_path) in &self.sheets {
            // we need another mutable borrow of self.zip later so we enclose this borrow within braces
            {
                let mut xml = match xml_reader(&mut self.zip, sheet_path, &self.zip_path_cache) {
                    None => continue,
                    Some(x) => x?,
                };
                let mut buf = Vec::new();
                loop {
                    buf.clear();
                    match xml.read_event_into(&mut buf) {
                        Ok(Event::Start(e)) if e.local_name() == QName(b"mergeCell").into() => {
                            if let Some(val) = e.raw_attr(b"ref")? {
                                let dimension = get_dimension(val)?;
                                regions.push((
                                    sheet_name.to_string(),
                                    sheet_path.to_string(),
                                    dimension,
                                ));
                            }
                        }
                        Ok(Event::Eof) => break,
                        Err(e) => return Err(XlsxError::Xml(e)),
                        _ => (),
                    }
                }
            }
        }
        self.merged_regions = Some(regions);
        Ok(())
    }

    #[inline]
    fn get_table_meta(&self, table_name: &str) -> Result<TableMetadata, XlsxError> {
        let match_table_meta = self
            .tables
            .as_ref()
            .expect("Tables must be loaded before they are referenced")
            .iter()
            .find(|(table, ..)| table == table_name)
            .ok_or_else(|| XlsxError::TableNotFound(table_name.into()))?;

        let name = match_table_meta.0.to_owned();
        let sheet_name = match_table_meta.1.clone();
        let columns = match_table_meta.2.clone();
        let dimensions = Dimensions {
            start: match_table_meta.3.start,
            end: match_table_meta.3.end,
        };

        Ok(TableMetadata {
            name,
            sheet_name,
            columns,
            dimensions,
        })
    }

    /// Load the merged regions in the workbook.
    ///
    /// A merged region in Excel is a range of cells that have been merged to
    /// act as a single cell. It is often used to create headers or titles that
    /// span multiple columns or rows.
    ///
    /// This method must be called before accessing the merged regions using the
    /// methods:
    ///
    /// - [`Xlsx::merged_regions()`].
    /// - [`Xlsx::merged_regions_by_sheet()`].
    ///
    /// These methods are explained below.
    ///
    /// # Errors
    ///
    /// - [`XlsxError::Xml`].
    ///
    #[doc(hidden)]
    #[deprecated(
        since = "0.36.0",
        note = "use `merge_cells_by_sheet_name()` or `merge_cells_by_sheet_id()` instead"
    )]
    pub fn load_merged_regions(&mut self) -> Result<(), XlsxError> {
        if self.merged_regions.is_none() {
            self.read_merged_regions()
        } else {
            Ok(())
        }
    }

    /// Get the merged regions for all the worksheets in a workbook.
    ///
    /// The function returns a ref to a vector of tuples containing the sheet
    /// name, the sheet path, and the [`Dimensions`] of the merged region. The
    /// middle element of the tuple can generally be ignored.
    ///
    /// The [`Xlsx::load_merged_regions()`] method must be called before calling
    /// this method.
    ///
    /// # Examples
    ///
    /// An example of getting all the merged regions in an Excel workbook.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/merged_range.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Load the merged regions in the workbook.
    ///     workbook.load_merged_regions()?;
    ///
    ///     // Get all the merged regions in the workbook.
    ///     let merged_regions = workbook.merged_regions();
    ///
    ///     // Print the sheet name and dimensions of each merged region.
    ///     for (sheet_name, _, dimensions) in merged_regions {
    ///         println!("{sheet_name}: {dimensions:?}");
    ///     }
    ///
    ///     Ok(())
    /// }
    ///
    /// ```
    ///
    /// Output:
    ///
    /// ```text
    /// Sheet1: Dimensions { start: (0, 7), end: (1, 7) }
    /// Sheet1: Dimensions { start: (0, 0), end: (1, 0) }
    /// Sheet1: Dimensions { start: (0, 1), end: (1, 1) }
    /// Sheet1: Dimensions { start: (0, 2), end: (1, 3) }
    /// Sheet1: Dimensions { start: (2, 2), end: (2, 3) }
    /// Sheet1: Dimensions { start: (3, 2), end: (3, 3) }
    /// Sheet1: Dimensions { start: (0, 4), end: (1, 4) }
    /// Sheet1: Dimensions { start: (0, 5), end: (1, 5) }
    /// Sheet1: Dimensions { start: (0, 6), end: (1, 6) }
    /// Sheet2: Dimensions { start: (0, 0), end: (3, 0) }
    /// Sheet2: Dimensions { start: (2, 2), end: (3, 3) }
    /// Sheet2: Dimensions { start: (0, 5), end: (3, 7) }
    /// Sheet2: Dimensions { start: (0, 1), end: (1, 1) }
    /// Sheet2: Dimensions { start: (0, 2), end: (1, 3) }
    /// Sheet2: Dimensions { start: (0, 4), end: (1, 4) }
    /// ```
    ///
    #[doc(hidden)]
    #[deprecated(
        since = "0.36.0",
        note = "use `merge_cells_by_sheet_name()` or `merge_cells_by_sheet_id()` instead"
    )]
    pub fn merged_regions(&self) -> &Vec<(String, String, Dimensions)> {
        self.merged_regions
            .as_ref()
            .expect("Merged Regions must be loaded before the are referenced")
    }

    /// Get the merged regions in a workbook by the sheet name.
    ///
    /// The function returns a vector of tuples containing the sheet name, the
    /// sheet path, and the [`Dimensions`] of the merged region. The first two
    /// elements of the tuple can generally be ignored.
    ///
    /// The [`Xlsx::load_merged_regions()`] method must be called before calling
    /// this method.
    ///
    /// # Parameters
    ///
    /// - `sheet_name`: The name of the worksheet to get the merged regions from.
    ///
    /// # Examples
    ///
    /// An example of getting the merged regions in an Excel workbook, by individual
    /// worksheet.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Reader, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/merged_range.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Get the names of all the sheets in the workbook.
    ///     let sheet_names = workbook.sheet_names();
    ///
    ///     // Load the merged regions in the workbook.
    ///     workbook.load_merged_regions()?;
    ///
    ///     for sheet_name in &sheet_names {
    ///         println!("{sheet_name}: ");
    ///
    ///         // Get the merged regions in the current sheet.
    ///         let merged_regions = workbook.merged_regions_by_sheet(sheet_name);
    ///
    ///         for (_, _, dimensions) in &merged_regions {
    ///             // Print the dimensions of each merged region.
    ///             println!("    {dimensions:?}");
    ///         }
    ///     }
    ///
    ///     Ok(())
    /// }
    ///
    /// ```
    ///
    /// Output:
    ///
    ///
    /// ```text
    /// Sheet1:
    ///     Dimensions { start: (0, 7), end: (1, 7) }
    ///     Dimensions { start: (0, 0), end: (1, 0) }
    ///     Dimensions { start: (0, 1), end: (1, 1) }
    ///     Dimensions { start: (0, 2), end: (1, 3) }
    ///     Dimensions { start: (2, 2), end: (2, 3) }
    ///     Dimensions { start: (3, 2), end: (3, 3) }
    ///     Dimensions { start: (0, 4), end: (1, 4) }
    ///     Dimensions { start: (0, 5), end: (1, 5) }
    ///     Dimensions { start: (0, 6), end: (1, 6) }
    /// Sheet2:
    ///     Dimensions { start: (0, 0), end: (3, 0) }
    ///     Dimensions { start: (2, 2), end: (3, 3) }
    ///     Dimensions { start: (0, 5), end: (3, 7) }
    ///     Dimensions { start: (0, 1), end: (1, 1) }
    ///     Dimensions { start: (0, 2), end: (1, 3) }
    ///     Dimensions { start: (0, 4), end: (1, 4) }
    /// ```
    ///
    #[doc(hidden)]
    #[deprecated(
        since = "0.36.0",
        note = "use `merge_cells_by_sheet_name()` or `merge_cells_by_sheet_id()` instead"
    )]
    pub fn merged_regions_by_sheet(&self, name: &str) -> Vec<(&String, &String, &Dimensions)> {
        #[allow(deprecated)]
        self.merged_regions()
            .iter()
            .filter(|s| s.0 == name)
            .map(|(name, sheet, region)| (name, sheet, region))
            .collect()
    }

    /// Load the worksheet tables from the XLSX file.
    ///
    /// Tables in Excel are a way of grouping a range of cells into a single
    /// entity that has common formatting or that can be referenced in formulas.
    /// In `calamine`, tables can be read as a [`Table`] object and converted to
    /// a data [`Range`] for further processing.
    ///
    /// Calamine does not automatically load table data from a workbook to avoid
    /// unnecessary overhead. Instead you must explicitly load the table data
    /// using the `Xlsx::load_tables()` method. Once the tables have been loaded
    /// the following methods can be used to extract and work with individual
    /// tables:
    ///
    /// - [`Xlsx::table_by_name()`].
    /// - [`Xlsx::table_by_name_ref()`].
    /// - [`Xlsx::table_names()`].
    /// - [`Xlsx::table_names_in_sheet()`].
    ///
    /// These methods are explained below. See also the [`Table`] struct for
    /// additional methods that can be used when working with tables.
    ///
    /// # Errors
    ///
    /// - [`XlsxError::XmlAttr`].
    /// - [`XlsxError::XmlEof`].
    /// - [`XlsxError::Xml`].
    ///
    ///
    pub fn load_tables(&mut self) -> Result<(), XlsxError> {
        if self.tables.is_none() {
            self.read_table_metadata()
        } else {
            Ok(())
        }
    }

    /// Get the names of all the tables in the workbook.
    ///
    /// Read all the table names in the workbook. This can be used in
    /// conjunction with [`Xlsx::table_by_name()`] to iterate over the tables in
    /// the workbook.
    ///
    /// # Panics
    ///
    /// Panics if tables have not been loaded via [`Xlsx::load_tables()`].
    ///
    /// # Examples
    ///
    /// An example of getting the names of all the tables in an Excel workbook.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/table-multiple.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Load the tables in the workbook.
    ///     workbook.load_tables()?;
    ///
    ///     // Get all the table names in the workbook.
    ///     let table_names = workbook.table_names();
    ///
    ///     // Check the table names.
    ///     assert_eq!(
    ///         table_names,
    ///         vec!["Inventory", "Pricing", "Sales_Bob", "Sales_Alice"]
    ///     );
    ///
    ///     Ok(())
    /// }
    /// ```
    ///
    pub fn table_names(&self) -> Vec<&String> {
        self.tables
            .as_ref()
            .expect("Tables must be loaded before they are referenced")
            .iter()
            .map(|(name, ..)| name)
            .collect()
    }

    /// Get the names of all the tables in a worksheet.
    ///
    /// Read all the table names in a worksheet. This can be used in conjunction
    /// with [`Xlsx::table_by_name()`] to iterate over the tables in the
    /// worksheet.
    ///
    /// # Parameters
    ///
    /// - `sheet_name`: The name of the worksheet to get the table names from.
    ///
    /// # Panics
    ///
    /// Panics if tables have not been loaded via [`Xlsx::load_tables()`].
    ///
    /// # Examples
    ///
    /// An example of getting the names of all the tables in an Excel workbook,
    /// sheet by sheet.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Reader, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/table-multiple.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Get the names of all the sheets in the workbook.
    ///     let sheet_names = workbook.sheet_names();
    ///
    ///     // Load the tables in the workbook.
    ///     workbook.load_tables()?;
    ///
    ///     for sheet_name in &sheet_names {
    ///         // Get the table names in the current sheet.
    ///         let table_names = workbook.table_names_in_sheet(sheet_name);
    ///
    ///         // Print the associated table names.
    ///         println!("{sheet_name} contains tables: {table_names:?}");
    ///     }
    ///
    ///     Ok(())
    /// }
    /// ```
    ///
    /// Output:
    ///
    /// ```text
    /// Sheet1 contains tables: ["Inventory"]
    /// Sheet2 contains tables: ["Pricing"]
    /// Sheet3 contains tables: ["Sales_Bob", "Sales_Alice"]
    /// ```
    ///
    pub fn table_names_in_sheet(&self, sheet_name: &str) -> Vec<&String> {
        self.tables
            .as_ref()
            .expect("Tables must be loaded before they are referenced")
            .iter()
            .filter(|(_, sheet, ..)| sheet == sheet_name)
            .map(|(name, ..)| name)
            .collect()
    }

    /// Get a worksheet table by name.
    ///
    /// This method retrieves a [`Table`] from the workbook by its name. The
    /// table will contain an owned copy of the worksheet data in the table
    /// range.
    ///
    /// # Parameters
    ///
    /// - `table_name`: The name of the table to retrieve.
    ///
    /// # Errors
    ///
    /// - [`XlsxError::TableNotFound`].
    /// - [`XlsxError::NotAWorksheet`].
    ///
    /// # Panics
    ///
    /// Panics if tables have not been loaded via [`Xlsx::load_tables()`].
    ///
    /// # Examples
    ///
    /// An example of getting an Excel worksheet table by its name. The file in
    /// this example contains 4 tables spread across 3 worksheets. This example
    /// gets an owned copy of the worksheet data in the table area.
    ///
    /// ```
    /// use calamine::{open_workbook, Data, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/table-multiple.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Load the tables in the workbook.
    ///     workbook.load_tables()?;
    ///
    ///     // Get the table by name.
    ///     let table = workbook.table_by_name("Inventory")?;
    ///
    ///     // Get the data range of the table. The data type is `&Range<Data>`.
    ///     let data_range = table.data();
    ///
    ///     // Do something with the data using the `Range` APIs. In this case
    ///     // we will just check for a cell value.
    ///     assert_eq!(
    ///         data_range.get((0, 1)),
    ///         Some(&Data::String("Apple".to_string()))
    ///     );
    ///
    ///     Ok(())
    /// }
    /// ```
    ///
    pub fn table_by_name(&mut self, table_name: &str) -> Result<Table<Data>, XlsxError> {
        let TableMetadata {
            name,
            sheet_name,
            columns,
            dimensions,
        } = self.get_table_meta(table_name)?;
        let Dimensions { start, end } = dimensions;
        let range = self.worksheet_range(&sheet_name)?;
        let tbl_rng = range.range(start, end);

        Ok(Table {
            name,
            sheet_name,
            columns,
            data: tbl_rng,
        })
    }

    /// Get a worksheet table by name, with referenced data.
    ///
    /// This method retrieves a [`Table`] from the workbook by its name. The
    /// table will contain an borrowed/referenced copy of the worksheet data in
    /// the table range. This is more efficient than [`Xlsx::table_by_name()`]
    /// for large tables.
    ///
    /// # Parameters
    ///
    /// - `table_name`: The name of the table to retrieve.
    ///
    /// # Errors
    ///
    /// - [`XlsxError::TableNotFound`].
    /// - [`XlsxError::NotAWorksheet`].
    ///
    /// # Panics
    ///
    /// Panics if tables have not been loaded via [`Xlsx::load_tables()`].
    ///
    /// # Examples
    ///
    /// An example of getting an Excel worksheet table by its name. The file in
    /// this example contains 4 tables spread across 3 worksheets. This example
    /// gets a borrowed/referenced copy of the worksheet data in the table area.
    ///
    /// ```
    /// use calamine::{open_workbook, DataRef, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/table-multiple.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Load the tables in the workbook.
    ///     workbook.load_tables()?;
    ///
    ///     // Get the table by name.
    ///     let table = workbook.table_by_name_ref("Inventory")?;
    ///
    ///     // Get the data range of the table. The data type is `&Range<DataRef<'_>>`.
    ///     let data_range = table.data();
    ///
    ///     // Do something with the data using the `Range` APIs. In this case
    ///     // we will just check for a cell value.
    ///     assert_eq!(
    ///         data_range.get((0, 1)),
    ///         Some(&DataRef::SharedString("Apple"))
    ///     );
    ///
    ///     Ok(())
    /// }
    /// ```
    ///
    pub fn table_by_name_ref(&mut self, table_name: &str) -> Result<Table<DataRef<'_>>, XlsxError> {
        let TableMetadata {
            name,
            sheet_name,
            columns,
            dimensions,
        } = self.get_table_meta(table_name)?;
        let Dimensions { start, end } = dimensions;
        let range = self.worksheet_range_ref(&sheet_name)?;
        let tbl_rng = range.range(start, end);

        Ok(Table {
            name,
            sheet_name,
            columns,
            data: tbl_rng,
        })
    }

    /// Get the merged cells/regions in a workbook by the sheet name.
    ///
    /// Merged cells in Excel are a range of cells that have been merged to act
    /// as a single cell. It is often used to create headers or titles that span
    /// multiple columns or rows.
    ///
    /// The function returns a vector of [`Dimensions`] of the merged region.
    /// This is wrapped in a [`Result`] and an [`Option`].
    ///
    /// # Parameters
    ///
    /// - `sheet_name`: The name of the worksheet to get the merged regions
    ///   from.
    ///
    /// # Errors
    ///
    /// - [`XlsxError::Xml`].
    ///
    /// # Examples
    ///
    /// An example of getting the merged regions/cells in an Excel workbook, by
    /// individual worksheet.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Reader, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/merged_range.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Get the names of all the sheets in the workbook.
    ///     let sheet_names = workbook.sheet_names();
    ///
    ///     for sheet_name in &sheet_names {
    ///         println!("{sheet_name}: ");
    ///
    ///         // Get the merged cells in the current sheet.
    ///         let merge_cells = workbook.worksheet_merge_cells(sheet_name);
    ///
    ///         if let Some(dimensions) = merge_cells {
    ///             let dimensions = dimensions?;
    ///
    ///             // Print the dimensions of each merged region.
    ///             for dimension in &dimensions {
    ///                 println!("    {dimension:?}");
    ///             }
    ///         }
    ///     }
    ///
    ///     Ok(())
    /// }
    ///
    /// ```
    ///
    /// Output:
    ///
    /// ```text
    /// Sheet1:
    ///     Dimensions { start: (0, 7), end: (1, 7) }
    ///     Dimensions { start: (0, 0), end: (1, 0) }
    ///     Dimensions { start: (0, 1), end: (1, 1) }
    ///     Dimensions { start: (0, 2), end: (1, 3) }
    ///     Dimensions { start: (2, 2), end: (2, 3) }
    ///     Dimensions { start: (3, 2), end: (3, 3) }
    ///     Dimensions { start: (0, 4), end: (1, 4) }
    ///     Dimensions { start: (0, 5), end: (1, 5) }
    ///     Dimensions { start: (0, 6), end: (1, 6) }
    /// Sheet2:
    ///     Dimensions { start: (0, 0), end: (3, 0) }
    ///     Dimensions { start: (2, 2), end: (3, 3) }
    ///     Dimensions { start: (0, 5), end: (3, 7) }
    ///     Dimensions { start: (0, 1), end: (1, 1) }
    ///     Dimensions { start: (0, 2), end: (1, 3) }
    ///     Dimensions { start: (0, 4), end: (1, 4) }
    /// ```
    ///
    #[doc(hidden)]
    #[deprecated(since = "0.36.0", note = "use `merge_cells_by_sheet_name()` instead")]
    pub fn worksheet_merge_cells(
        &mut self,
        name: &str,
    ) -> Option<Result<Vec<Dimensions>, XlsxError>> {
        let (_, path) = self.sheets.iter().find(|(n, _)| n == name)?;
        let xml = xml_reader(&mut self.zip, path, &self.zip_path_cache);

        xml.map(|xml| {
            let mut xml = xml?;
            let mut merge_cells = Vec::new();
            let mut buffer = Vec::new();

            loop {
                buffer.clear();

                match xml.read_event_into(&mut buffer) {
                    Ok(Event::Start(event)) if event.local_name().as_ref() == b"mergeCells" => {
                        if let Ok(cells) = read_merge_cells(&mut xml) {
                            merge_cells = cells;
                        }

                        break;
                    }
                    Ok(Event::Eof) => break,
                    Err(e) => return Err(XlsxError::Xml(e)),
                    _ => (),
                }
            }

            Ok(merge_cells)
        })
    }

    /// Get the merged cells/regions in a workbook by the sheet index.
    ///
    /// Merged cells in Excel are a range of cells that have been merged to act
    /// as a single cell. It is often used to create headers or titles that span
    /// multiple columns or rows.
    ///
    /// The function returns a vector of [`Dimensions`] of the merged region.
    /// This is wrapped in a [`Result`] and an [`Option`].
    ///
    /// # Parameters
    ///
    /// - `sheet_index`: The zero index of the worksheet to get the merged
    ///   regions from.
    ///
    /// # Errors
    ///
    /// - [`XlsxError::Xml`].
    ///
    /// # Examples
    ///
    /// An example of getting the merged regions/cells in an Excel workbook, by
    /// worksheet index.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/merged_range.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Get the merged cells in the first worksheet.
    ///     let merge_cells = workbook.worksheet_merge_cells_at(0);
    ///
    ///     if let Some(dimensions) = merge_cells {
    ///         let dimensions = dimensions?;
    ///
    ///         // Print the dimensions of each merged region.
    ///         for dimension in &dimensions {
    ///             println!("{dimension:?}");
    ///         }
    ///     }
    ///
    ///     Ok(())
    /// }
    ///
    /// ```
    ///
    /// Output:
    ///
    /// ```text
    /// Dimensions { start: (0, 7), end: (1, 7) }
    /// Dimensions { start: (0, 0), end: (1, 0) }
    /// Dimensions { start: (0, 1), end: (1, 1) }
    /// Dimensions { start: (0, 2), end: (1, 3) }
    /// Dimensions { start: (2, 2), end: (2, 3) }
    /// Dimensions { start: (3, 2), end: (3, 3) }
    /// Dimensions { start: (0, 4), end: (1, 4) }
    /// Dimensions { start: (0, 5), end: (1, 5) }
    /// Dimensions { start: (0, 6), end: (1, 6) }
    /// ```
    ///
    #[doc(hidden)]
    #[deprecated(since = "0.36.0", note = "use `merge_cells_by_sheet_id()` instead")]
    pub fn worksheet_merge_cells_at(
        &mut self,
        sheet_index: usize,
    ) -> Option<Result<Vec<Dimensions>, XlsxError>> {
        let name = self
            .metadata()
            .sheets
            .get(sheet_index)
            .map(|sheet| sheet.name.clone())?;

        #[allow(deprecated)]
        self.worksheet_merge_cells(&name)
    }

    /// Get the merged cells/regions in a worksheet by sheet name.
    ///
    /// Merged cells in Excel are a range of cells that have been merged to act
    /// as a single cell. It is often used to create headers or titles that span
    /// multiple columns or rows.
    ///
    /// The function returns a vector of [`Dimensions`] for the merged cells in
    /// the named worksheet.
    ///
    /// # Parameters
    ///
    /// - `name`: The name of the worksheet.
    ///
    /// # Errors
    ///
    /// - [`XlsxError::WorksheetNotFound`] if no worksheet with the given name
    ///   exists.
    /// - [`XlsxError::Xml`] if the worksheet XML cannot be parsed.
    ///
    /// # Examples
    ///
    /// An example of getting the merged cells in an Excel workbook by sheet
    /// name.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Reader, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/merged_range.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Get the names of all the sheets in the workbook.
    ///     let sheet_names = workbook.sheet_names();
    ///
    ///     for sheet_name in &sheet_names {
    ///         println!("{sheet_name}:");
    ///
    ///         // Get the merged cells in the current sheet.
    ///         let merge_cells = workbook.merge_cells_by_sheet_name(sheet_name)?;
    ///
    ///         // Print the dimensions of each merged region.
    ///         for dimension in &merge_cells {
    ///             println!("    {dimension:?}");
    ///         }
    ///     }
    ///
    ///     Ok(())
    /// }
    /// ```
    ///
    /// Output:
    ///
    /// ```text
    /// Sheet1:
    ///     Dimensions { start: (0, 7), end: (1, 7) }
    ///     Dimensions { start: (0, 0), end: (1, 0) }
    ///     Dimensions { start: (0, 1), end: (1, 1) }
    ///     Dimensions { start: (0, 2), end: (1, 3) }
    ///     Dimensions { start: (2, 2), end: (2, 3) }
    ///     Dimensions { start: (3, 2), end: (3, 3) }
    ///     Dimensions { start: (0, 4), end: (1, 4) }
    ///     Dimensions { start: (0, 5), end: (1, 5) }
    ///     Dimensions { start: (0, 6), end: (1, 6) }
    /// Sheet2:
    ///     Dimensions { start: (0, 0), end: (3, 0) }
    ///     Dimensions { start: (2, 2), end: (3, 3) }
    ///     Dimensions { start: (0, 5), end: (3, 7) }
    ///     Dimensions { start: (0, 1), end: (1, 1) }
    ///     Dimensions { start: (0, 2), end: (1, 3) }
    ///     Dimensions { start: (0, 4), end: (1, 4) }
    /// ```
    ///
    pub fn merge_cells_by_sheet_name(&mut self, name: &str) -> Result<Vec<Dimensions>, XlsxError> {
        let (_, path) = self
            .sheets
            .iter()
            .find(|(n, _)| n == name)
            .ok_or_else(|| XlsxError::WorksheetNotFound(name.into()))?;

        let xml = xml_reader(&mut self.zip, path, &self.zip_path_cache)
            .ok_or_else(|| XlsxError::WorksheetNotFound(name.into()))?;

        let mut xml = xml?;
        let mut merge_cells = Vec::new();
        let mut buffer = Vec::new();

        loop {
            buffer.clear();

            match xml.read_event_into(&mut buffer) {
                Ok(Event::Start(event)) if event.local_name().as_ref() == b"mergeCells" => {
                    merge_cells = read_merge_cells(&mut xml)?;
                    break;
                }
                Ok(Event::Eof) => break,
                Err(e) => return Err(XlsxError::Xml(e)),
                _ => (),
            }
        }

        Ok(merge_cells)
    }

    /// Get the merged cells/regions in a worksheet by sheet index.
    ///
    /// Merged cells in Excel are a range of cells that have been merged to act
    /// as a single cell. It is often used to create headers or titles that span
    /// multiple columns or rows.
    ///
    /// The function returns a vector of [`Dimensions`] for the merged cells in
    /// the worksheet at the given index.
    ///
    /// # Parameters
    ///
    /// - `id`: The zero-based index of the worksheet.
    ///
    /// # Errors
    ///
    /// - [`XlsxError::WorksheetNotFound`] if `id` is out of range.
    /// - [`XlsxError::Xml`] if the worksheet XML cannot be parsed.
    ///
    /// # Examples
    ///
    /// An example of getting the merged cells in an Excel workbook by sheet
    /// index.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/merged_range.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Get the merged cells in the first worksheet.
    ///     let merge_cells = workbook.merge_cells_by_sheet_id(0)?;
    ///
    ///     // Print the dimensions of each merged region.
    ///     for dimension in &merge_cells {
    ///         println!("{dimension:?}");
    ///     }
    ///
    ///     Ok(())
    /// }
    /// ```
    ///
    /// Output:
    ///
    /// ```text
    /// Dimensions { start: (0, 7), end: (1, 7) }
    /// Dimensions { start: (0, 0), end: (1, 0) }
    /// Dimensions { start: (0, 1), end: (1, 1) }
    /// Dimensions { start: (0, 2), end: (1, 3) }
    /// Dimensions { start: (2, 2), end: (2, 3) }
    /// Dimensions { start: (3, 2), end: (3, 3) }
    /// Dimensions { start: (0, 4), end: (1, 4) }
    /// Dimensions { start: (0, 5), end: (1, 5) }
    /// Dimensions { start: (0, 6), end: (1, 6) }
    /// ```
    ///
    pub fn merge_cells_by_sheet_id(&mut self, id: usize) -> Result<Vec<Dimensions>, XlsxError> {
        let name = self
            .metadata()
            .sheets
            .get(id)
            .map(|sheet| sheet.name.clone())
            .ok_or_else(|| {
                XlsxError::WorksheetNotFound(format!("Sheet index {id} out of range"))
            })?;

        self.merge_cells_by_sheet_name(&name)
    }

    /// Get the hyperlinks defined on a worksheet by sheet name.
    ///
    /// Hyperlinks in Excel can point to external URLs, email addresses, files,
    /// or to internal locations such as another cell or named range. Each
    /// hyperlink is anchored to a cell or cell range on the worksheet and may
    /// have an optional displayed text string and tooltip.
    ///
    /// The function returns a vector of [`Hyperlink`] values for the named
    /// worksheet. An empty vector is returned for worksheets that do not
    /// declare any hyperlinks.
    ///
    /// External hyperlink targets are resolved from the worksheet's
    /// relationships file (`xl/worksheets/_rels/sheetN.xml.rels`). Purely
    /// internal hyperlinks (those declared with only a `location` attribute)
    /// have [`None`] for [`Hyperlink::target`].
    ///
    /// # Parameters
    ///
    /// - `name`: The name of the worksheet to get the hyperlinks from.
    ///
    /// # Errors
    ///
    /// - [`XlsxError::WorksheetNotFound`] if no worksheet with the given name
    ///   exists.
    /// - [`XlsxError::Xml`] if the worksheet XML cannot be parsed.
    ///
    /// # Examples
    ///
    /// An example of reading the hyperlinks from a worksheet.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/hyperlinks.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Get the hyperlinks defined on the first sheet.
    ///     let hyperlinks = workbook.hyperlinks_by_sheet_name("Links")?;
    ///     for hyperlink in &hyperlinks {
    ///         println!("{:?} -> {:?}", hyperlink.range, hyperlink.target);
    ///     }
    ///
    ///     Ok(())
    /// }
    /// ```
    pub fn hyperlinks_by_sheet_name(&mut self, name: &str) -> Result<Vec<Hyperlink>, XlsxError> {
        let (_, path) = self
            .sheets
            .iter()
            .find(|(n, _)| n == name)
            .ok_or_else(|| XlsxError::WorksheetNotFound(name.into()))?;
        let path = path.clone();

        let rels = read_sheet_hyperlink_rels(&mut self.zip, &path, &self.zip_path_cache)?;

        let xml = xml_reader(&mut self.zip, &path, &self.zip_path_cache)
            .ok_or_else(|| XlsxError::WorksheetNotFound(name.into()))?;

        let mut xml = xml?;
        let mut hyperlinks = Vec::new();
        let mut buffer = Vec::new();

        loop {
            buffer.clear();

            match xml.read_event_into(&mut buffer) {
                Ok(Event::Start(event)) if event.local_name().as_ref() == b"hyperlinks" => {
                    hyperlinks = read_hyperlinks(&mut xml, &rels)?;
                    break;
                }
                Ok(Event::Eof) => break,
                Err(e) => return Err(XlsxError::Xml(e)),
                _ => (),
            }
        }

        Ok(hyperlinks)
    }

    /// Get the hyperlinks defined on a worksheet by sheet index.
    ///
    /// See [`Xlsx::hyperlinks_by_sheet_name()`] for the per-sheet variant keyed
    /// by sheet name. This method is a convenience that resolves the sheet name
    /// from the zero-based `id` and forwards to it.
    ///
    /// # Parameters
    ///
    /// - `id`: The zero-based index of the worksheet to get the hyperlinks
    ///   from.
    ///
    /// # Errors
    ///
    /// - [`XlsxError::WorksheetNotFound`] if no worksheet with the given index
    ///   exists.
    /// - [`XlsxError::Xml`] if the worksheet XML cannot be parsed.
    ///
    /// # Examples
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let path = "tests/hyperlinks.xlsx";
    ///
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     let hyperlinks = workbook.hyperlinks_by_sheet_id(0)?;
    ///     for hyperlink in &hyperlinks {
    ///         println!("{:?} -> {:?}", hyperlink.range, hyperlink.target);
    ///     }
    ///
    ///     Ok(())
    /// }
    /// ```
    pub fn hyperlinks_by_sheet_id(&mut self, id: usize) -> Result<Vec<Hyperlink>, XlsxError> {
        let name = self
            .metadata()
            .sheets
            .get(id)
            .map(|sheet| sheet.name.clone())
            .ok_or_else(|| {
                XlsxError::WorksheetNotFound(format!("Sheet index {id} out of range"))
            })?;

        self.hyperlinks_by_sheet_name(&name)
    }
}

/// A hyperlink declared on an XLSX worksheet.
///
/// Hyperlinks are anchored to a cell or cell range and point to one of the
/// link types supported by Excel:
///
/// - A web-based URI such as `http://`, `https://`, `ftp://`, `ftps://`,
///   `mailto:` or a custom `protocol://domain` link — stored in
///   [`Hyperlink::target`].
/// - A local file link using the `file://` URI (e.g.
///   `file:///C:\Temp\Book1.xlsx` or `file:///Book2.xlsx#'Sales Data'!A1:G5`)
///   — stored in [`Hyperlink::target`].
/// - An internal link to a cell or range in the same workbook (e.g.
///   `'Sheet2'!B5`) — stored in [`Hyperlink::location`], with
///   [`Hyperlink::target`] left as [`None`].
///
/// A single hyperlink may combine an external target with an internal
/// location, in which case the location acts as a sub-anchor within the
/// target file.
#[derive(Debug, Clone, PartialEq, Eq)]
pub struct Hyperlink {
    /// Cell or range the hyperlink is anchored to.
    pub range: Dimensions,

    /// External link target.
    ///
    /// This holds the subset of Excel link types that resolve to an external
    /// resource: web URIs (e.g. `https://example.com`), email addresses (e.g.
    /// `mailto:foo@example.com`) and local file links (e.g.
    /// `file:///C:\Temp\Book1.xlsx`).
    ///
    /// It is [`None`] for purely internal hyperlinks, where only
    /// [`Hyperlink::location`] is set.
    pub target: Option<String>,

    /// In-file location the hyperlink points to, set only for a subset of
    /// link types.
    ///
    /// For a purely internal link this is the cell, range or named range it
    /// targets (e.g. `'Sheet2'!B5`). For an external file link it holds the
    /// sub-anchor — the part after `#`, e.g. the `Sheet1!A1` in
    /// `file:///Book2.xlsx#Sheet1!A1` — while [`Hyperlink::target`] holds the
    /// file itself. [`None`] when the hyperlink has no in-file location.
    pub location: Option<String>,

    /// Optional text displayed in the cell instead of the target.
    ///
    /// For example a link to `https://www.rust-lang.org/` might be displayed
    /// as `Rust Programming Language`. [`None`] when Excel shows the target
    /// itself.
    pub displayed_text: Option<String>,

    /// Optional tooltip shown when hovering the hyperlink in Excel.
    pub tooltip: Option<String>,
}

impl Hyperlink {
    /// Returns `true` if this hyperlink's anchor range contains the cell at
    /// the given zero-based `(row, column)`.
    ///
    /// This is a convenience for looking up the hyperlink applied to a
    /// specific cell from the vector returned by
    /// [`Xlsx::hyperlinks_by_sheet_name()`]:
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///     let mut workbook: Xlsx<_> = open_workbook("tests/hyperlinks.xlsx")?;
    ///     let hyperlinks = workbook.hyperlinks_by_sheet_name("Links")?;
    ///
    ///     let link = hyperlinks.iter().find(|h| h.contains(0, 0));
    ///     assert!(link.is_some());
    ///
    ///     Ok(())
    /// }
    /// ```
    pub fn contains(&self, row: u32, column: u32) -> bool {
        self.range.contains(row, column)
    }
}

/// Read the hyperlink relationships for a sheet from its `.rels` file.
///
/// Returns a map from relationship id (`rId...`) to the relationship target
/// (URL, file, etc.). Only relationships whose `Type` matches the OOXML
/// hyperlink relationship are included. Sheets without a `.rels` file return
/// an empty map.
fn read_sheet_hyperlink_rels<RS>(
    zip: &mut ZipArchive<RS>,
    sheet_path: &str,
    cache: &HashMap<String, String>,
) -> Result<HashMap<String, String>, XlsxError>
where
    RS: Read + Seek,
{
    let mut rels = HashMap::new();

    let last_folder_index = match sheet_path.rfind('/') {
        Some(i) => i,
        None => return Ok(rels),
    };
    let (base_folder, file_name) = sheet_path.split_at(last_folder_index);
    let rel_path = format!("{base_folder}/_rels{file_name}.rels");

    let mut xml = match xml_reader(zip, &rel_path, cache) {
        // A sheet without hyperlinks won't have a .rels file. That's not an error.
        None => return Ok(rels),
        Some(x) => x?,
    };

    let mut buf = Vec::with_capacity(256);
    loop {
        buf.clear();
        match xml.read_event_into(&mut buf) {
            Ok(Event::Start(e)) | Ok(Event::Empty(e))
                if e.local_name().as_ref() == b"Relationship" =>
            {
                let (id, target, typ) =
                    get_attrs!(e, b"Id" => id, b"Target" => target, b"Type" => typ)?;
                let is_hyperlink = typ.is_some_and(|t| t.ends_with(b"/relationships/hyperlink"));
                if is_hyperlink {
                    if let Some(id) = id.filter(|id| !id.is_empty()) {
                        let id = xml.decoder().decode(id)?.into_owned();
                        let target = target
                            .map(|t| {
                                xml.decoder()
                                    .decode(t)
                                    .map(|t| unescape_xml(&t).into_owned())
                            })
                            .transpose()?
                            .unwrap_or_default();
                        rels.insert(id, target);
                    }
                }
            }
            Ok(Event::End(e)) if e.local_name().as_ref() == b"Relationships" => break,
            Ok(Event::Eof) => break,
            Err(e) => return Err(XlsxError::Xml(e)),
            _ => (),
        }
    }

    Ok(rels)
}

/// Parse a `<hyperlinks>` block from a worksheet XML stream.
///
/// Caller has already consumed the opening `<hyperlinks>` event; this reads
/// child `<hyperlink>` elements until the matching close tag.
fn read_hyperlinks<RS>(
    xml: &mut XlReader<'_, RS>,
    rels: &HashMap<String, String>,
) -> Result<Vec<Hyperlink>, XlsxError>
where
    RS: Read + Seek,
{
    let mut hyperlinks = Vec::new();
    let mut buffer = Vec::new();

    loop {
        buffer.clear();

        match xml.read_event_into(&mut buffer) {
            Ok(Event::Start(event)) | Ok(Event::Empty(event))
                if event.local_name().as_ref() == b"hyperlink" =>
            {
                let mut range: Option<Dimensions> = None;
                let mut rid: Option<String> = None;
                let mut location: Option<String> = None;
                let mut displayed_text: Option<String> = None;
                let mut tooltip: Option<String> = None;

                for attr in event.iter_raw_attrs() {
                    let (key, val) = attr?;
                    match key {
                        b"ref" => {
                            range = Some(get_dimension(val)?);
                        }
                        b"location" => {
                            location = Some(unescape_xml(&xml.decoder().decode(val)?).into_owned());
                        }
                        b"display" => {
                            displayed_text =
                                Some(unescape_xml(&xml.decoder().decode(val)?).into_owned());
                        }
                        b"tooltip" => {
                            tooltip = Some(unescape_xml(&xml.decoder().decode(val)?).into_owned());
                        }
                        // Match on the local name; the namespace prefix
                        // for the relationships namespace is conventionally
                        // "r" but OOXML allows any prefix bound to the
                        // relationships namespace URI.
                        key if local_name_matches(key, b"id") => {
                            rid = Some(xml.decoder().decode(val)?.into_owned());
                        }
                        _ => (),
                    }
                }

                let Some(range) = range else { continue };
                let target = rid.as_deref().and_then(|id| rels.get(id).cloned());

                hyperlinks.push(Hyperlink {
                    range,
                    target,
                    location,
                    displayed_text,
                    tooltip,
                });
            }
            Ok(Event::End(event)) if event.local_name().as_ref() == b"hyperlinks" => break,
            Ok(Event::Eof) => return Err(XlsxError::XmlEof("hyperlinks")),
            Err(e) => return Err(XlsxError::Xml(e)),
            _ => (),
        }
    }

    Ok(hyperlinks)
}

struct TableMetadata {
    name: String,
    sheet_name: String,
    columns: Vec<String>,
    dimensions: Dimensions,
}

struct InnerTableMetadata {
    display_name: String,
    ref_cells: String,
    header_row_count: u32,
    totals_row_count: u32,
}

impl InnerTableMetadata {
    fn new() -> Self {
        Self {
            display_name: String::new(),
            ref_cells: String::new(),
            header_row_count: 1,
            totals_row_count: 0,
        }
    }
}

impl<RS: Read + Seek> Xlsx<RS> {
    /// Get a reader over all used cells in the given worksheet cell reader
    pub fn worksheet_cells_reader<'a>(
        &'a mut self,
        name: &str,
    ) -> Result<XlsxCellReader<'a, RS>, XlsxError> {
        let (_, path) = self
            .sheets
            .iter()
            .find(|&(n, _)| n == name)
            .ok_or_else(|| XlsxError::WorksheetNotFound(name.into()))?;
        let xml = xml_reader(&mut self.zip, path, &self.zip_path_cache)
            .ok_or_else(|| XlsxError::WorksheetNotFound(name.into()))??;
        let is_1904 = self.is_1904;
        let strings = &self.strings;
        let formats = &self.formats;
        XlsxCellReader::new(xml, strings, formats, is_1904)
    }
}

impl<RS: Read + Seek> Reader<RS> for Xlsx<RS> {
    type Error = XlsxError;

    fn new(mut reader: RS) -> Result<Self, XlsxError> {
        check_for_password_protected(&mut reader)?;

        let zip = ZipArchive::new(reader)?;
        let zip_path_cache = build_zip_path_cache(&zip);
        let mut xlsx = Xlsx {
            zip,
            xl_path: "xl/".to_string(),
            strings: Vec::new(),
            formats: Vec::new(),
            is_1904: false,
            sheets: Vec::new(),
            tables: None,
            metadata: Metadata::default(),
            #[cfg(feature = "picture")]
            pictures: None,
            merged_regions: None,
            options: XlsxOptions::default(),
            zip_path_cache,
        };
        xlsx.read_package_relationships()?;
        xlsx.read_shared_strings()?;
        xlsx.read_styles()?;
        let relationships = xlsx.read_relationships()?;
        xlsx.read_workbook(&relationships)?;
        #[cfg(feature = "picture")]
        xlsx.read_pictures()?;

        Ok(xlsx)
    }

    fn with_header_row(&mut self, header_row: HeaderRow) -> &mut Self {
        self.options.header_row = header_row;
        self
    }

    fn vba_project(&mut self) -> Result<Option<VbaProject>, XlsxError> {
        let path = format!("{}vbaProject.bin", self.xl_path);
        let Some(mut f) = self.zip.by_name(path.as_ref()).ok() else {
            return Ok(None);
        };
        let len = f.size() as usize;
        let vba = VbaProject::new(&mut f, len)?;
        Ok(Some(vba))
    }

    fn metadata(&self) -> &Metadata {
        &self.metadata
    }

    fn worksheet_range(&mut self, name: &str) -> Result<Range<Data>, XlsxError> {
        let rge = self.worksheet_range_ref(name)?;
        let inner = rge.inner.into_iter().map(|v| v.into()).collect();
        Ok(Range {
            start: rge.start,
            end: rge.end,
            inner,
        })
    }

    fn worksheet_formula(&mut self, name: &str) -> Result<Range<String>, XlsxError> {
        let mut cell_reader = match self.worksheet_cells_reader(name) {
            Ok(reader) => reader,
            Err(XlsxError::NotAWorksheet(typ)) => {
                warn!("'{typ}' not a worksheet");
                return Ok(Range::default());
            }
            Err(e) => return Err(e),
        };
        let len = cell_reader.dimensions().len();
        let mut cells = Vec::new();
        if len < 100_000 {
            cells.reserve(len as usize);
        }
        while let Some(cell) = cell_reader.next_formula()? {
            if !cell.val.is_empty() {
                cells.push(cell);
            }
        }
        Ok(Range::from_sparse(cells))
    }

    fn worksheets(&mut self) -> Vec<(String, Range<Data>)> {
        let names = self
            .sheets
            .iter()
            .map(|(n, _)| n.clone())
            .collect::<Vec<_>>();
        names
            .into_iter()
            .filter_map(|n| {
                let rge = self.worksheet_range(&n).ok()?;
                Some((n, rge))
            })
            .collect()
    }

    #[cfg(feature = "picture")]
    fn pictures(&self) -> Option<Vec<(String, Vec<u8>)>> {
        self.pictures.as_ref().map(|pics| {
            pics.iter()
                .map(|p| (p.extension.clone(), p.data.clone()))
                .collect()
        })
    }

    #[cfg(feature = "picture")]
    fn pictures_with_metadata(&self) -> Vec<Picture> {
        self.pictures.as_deref().unwrap_or(&[]).to_vec()
    }
}

impl<RS: Read + Seek> ReaderRef<RS> for Xlsx<RS> {
    fn worksheet_range_ref<'a>(&'a mut self, name: &str) -> Result<Range<DataRef<'a>>, XlsxError> {
        let header_row = self.options.header_row;
        let mut cell_reader = match self.worksheet_cells_reader(name) {
            Ok(reader) => reader,
            Err(XlsxError::NotAWorksheet(typ)) => {
                log::warn!("'{typ}' not a valid worksheet");
                return Ok(Range::default());
            }
            Err(e) => return Err(e),
        };
        let len = cell_reader.dimensions().len();
        let mut cells = Vec::new();
        if len < 100_000 {
            cells.reserve(len as usize);
        }

        match header_row {
            HeaderRow::FirstNonEmptyRow => {
                // the header row is the row of the first non-empty cell
                loop {
                    match cell_reader.next_cell() {
                        Ok(Some(Cell {
                            val: DataRef::Empty,
                            ..
                        })) => (),
                        Ok(Some(cell)) => cells.push(cell),
                        Ok(None) => break,
                        Err(e) => return Err(e),
                    }
                }
            }
            HeaderRow::Row(header_row_idx) => {
                // If `header_row` is a row index, we only add non-empty cells after this index.
                loop {
                    match cell_reader.next_cell() {
                        Ok(Some(Cell {
                            val: DataRef::Empty,
                            ..
                        })) => (),
                        Ok(Some(cell)) => {
                            if cell.pos.0 >= header_row_idx {
                                cells.push(cell);
                            }
                        }
                        Ok(None) => break,
                        Err(e) => return Err(e),
                    }
                }

                // If `header_row` is set and the first non-empty cell is not at the `header_row`, we add
                // an empty cell at the beginning with row `header_row` and same column as the first non-empty cell.
                if cells.first().is_some_and(|c| c.pos.0 != header_row_idx) {
                    cells.insert(
                        0,
                        Cell {
                            pos: (
                                header_row_idx,
                                cells.first().expect("cells should not be empty").pos.1,
                            ),
                            val: DataRef::Empty,
                        },
                    );
                }
            }
        }

        Ok(Range::from_sparse(cells))
    }
}

// Resolve a relative path `target` against a `base` folder path.
// E.g. base="xl/worksheets", target="../drawings/drawing1.xml" -> "xl/drawings/drawing1.xml".
// Only handles one level of ".."; standard Excel files don't use deeper relative paths.
#[cfg(feature = "picture")]
fn resolve_path(base: &str, target: &str) -> String {
    if let Some(stripped) = target.strip_prefix('/') {
        stripped.to_string()
    } else if let Some(rel) = target.strip_prefix("../") {
        let parent = base.rfind('/').map_or("", |i| &base[..i]);
        format!("{parent}/{rel}")
    } else {
        format!("{base}/{target}")
    }
}

// Parse the 0-based column index from the raw bytes of an A1-style cell
// reference like `B2`.
#[cfg(feature = "picture")]
fn col_from_cell_ref(cell_ref: &[u8]) -> u32 {
    let mut col: u32 = 0;
    for &b in cell_ref {
        if b.is_ascii_alphabetic() {
            col = col * 26 + (b.to_ascii_uppercase() - b'A') as u32 + 1;
        } else {
            break;
        }
    }
    col.saturating_sub(1)
}

fn xml_reader<'a, RS: Read + Seek>(
    zip: &'a mut ZipArchive<RS>,
    path: &str,
    cache: &HashMap<String, String>,
) -> Option<Result<XlReader<'a, RS>, XlsxError>> {
    let zip_path = cached_zip_path(cache, path);

    match zip.by_name(zip_path) {
        Ok(f) => {
            let mut r = XmlReader::from_reader(BufReader::new(f));
            let config = r.config_mut();
            config.check_end_names = false;
            config.trim_text(false);
            config.check_comments = false;
            config.expand_empty_elements = true;
            Some(Ok(r))
        }
        Err(ZipError::FileNotFound) => None,
        Err(e) => Some(Err(e.into())),
    }
}

/// converts a text representation (e.g. "A6:G67") of a dimension into integers
/// - top left (row, column),
/// - bottom right (row, column)
pub(crate) fn get_dimension(dimension: &[u8]) -> Result<Dimensions, XlsxError> {
    let parts: Vec<_> = dimension
        .split(|c| *c == b':')
        .map(get_row_column)
        .collect::<Result<Vec<_>, XlsxError>>()?;

    match parts.len() {
        0 => Err(XlsxError::DimensionCount(0)),
        1 => Ok(Dimensions {
            start: parts[0],
            end: parts[0],
        }),
        2 => {
            let rows = parts[1].0 - parts[0].0;
            let columns = parts[1].1 - parts[0].1;
            if rows > MAX_ROWS {
                warn!("xlsx has more than maximum number of rows ({rows} > {MAX_ROWS})");
            }
            if columns > MAX_COLUMNS {
                warn!("xlsx has more than maximum number of columns ({columns} > {MAX_COLUMNS})");
            }
            Ok(Dimensions {
                start: parts[0],
                end: parts[1],
            })
        }
        len => Err(XlsxError::DimensionCount(len)),
    }
}

/// Converts a text range name into its position (row, column) (0 based index).
/// If the row or column component in the range is missing, an Error is returned.
pub(crate) fn get_row_column(range: &[u8]) -> Result<(u32, u32), XlsxError> {
    let (row, col) = get_row_and_optional_column(range)?;
    let col = col.ok_or(XlsxError::RangeWithoutColumnComponent)?;
    Ok((row, col))
}

/// Converts a text row name into its position (0 based index).
/// If the row component in the range is missing, an Error is returned.
/// If the text row name also contains a column component, it is ignored.
pub(crate) fn get_row(range: &[u8]) -> Result<u32, XlsxError> {
    get_row_and_optional_column(range).map(|(row, _)| row)
}

/// Converts a text-based range name into its `(row, column)` position (0-based index).
/// If the column component of the range is missing, a None is returned (for the column).
/// If the row component of the range is missing, an Error is returned.
fn get_row_and_optional_column(range: &[u8]) -> Result<(u32, Option<u32>), XlsxError> {
    let len = range.len();
    let mut i = 0;

    // Column: accumulate base-26 from letters
    // (eg: A=1, B=2, ..., Z=26, AA=27, ..., AZ=52, ..., etc)
    let mut col: u32 = 0;
    let mut row: u32 = 0;
    while i < len {
        match range[i] {
            c @ b'A'..=b'Z' => col = col * 26 + (c - b'A') as u32 + 1,
            c @ b'a'..=b'z' => col = col * 26 + (c - b'a') as u32 + 1,
            c @ b'0'..=b'9' => {
                // on first digit, capture it and transition to the row loop
                row = (c - b'0') as u32;
                i += 1;
                break;
            }
            c => return Err(XlsxError::Alphanumeric(c)),
        }
        i += 1;
    }

    // Row: accumulate base-10 from remaining digits (1-based in source)
    while i < len {
        match range[i] {
            c @ b'0'..=b'9' => row = row * 10 + (c - b'0') as u32,
            c => return Err(XlsxError::Alphanumeric(c)),
        }
        i += 1;
    }

    // Convert from 1-based to 0-based (col=0 means no column found)
    let row = row
        .checked_sub(1)
        .ok_or(XlsxError::RangeWithoutRowComponent)?;
    Ok((row, col.checked_sub(1)))
}

/// Attempts to read either a simple or richtext string, reusing caller-provided
/// buffers to avoid per-call allocations.
pub(crate) fn read_string_with_bufs<RS>(
    xml: &mut XlReader<'_, RS>,
    closing: QName,
    xml_buf: &mut Vec<u8>,
    text_buf: &mut Vec<u8>,
) -> Result<Option<String>, XlsxError>
where
    RS: Read + Seek,
{
    let mut rich_buffer: Option<String> = None;
    let mut is_phonetic_text = false;
    loop {
        xml_buf.clear();
        match xml.read_event_into(xml_buf) {
            Ok(Event::Start(e)) if e.local_name().as_ref() == b"r" && rich_buffer.is_none() => {
                // use a buffer since richtext has multiples <r> and <t> for the same cell
                rich_buffer = Some(String::new());
            }
            Ok(Event::Start(e)) if e.local_name().as_ref() == b"rPh" => {
                is_phonetic_text = true;
            }
            Ok(Event::End(e)) if e.name() == closing => {
                if rich_buffer.is_none() {
                    // An empty <s></s> element, without <t> or other
                    // subelements, is treated as a valid empty string in Excel.
                    rich_buffer = Some(String::new());
                }
                return Ok(rich_buffer);
            }
            Ok(Event::End(e)) if e.local_name().as_ref() == b"rPh" => {
                is_phonetic_text = false;
            }
            Ok(Event::Start(e)) if e.local_name().as_ref() == b"t" && !is_phonetic_text => {
                let preserve_space =
                    matches!(e.raw_attr(b"xml:space")?, Some(v) if v == b"preserve");
                text_buf.clear();
                let mut value = String::new();
                loop {
                    match xml.read_event_into(text_buf)? {
                        Event::Text(t) => value.push_str(&t.xml10_content()?),
                        Event::CData(t) => value.push_str(&t.xml10_content()?),
                        Event::GeneralRef(e) => unescape_entity_to_buffer(&e, &mut value)?,
                        Event::End(end) if end.name() == e.name() => break,
                        Event::Eof => return Err(XlsxError::XmlEof("t")),
                        _ => (),
                    }
                }
                let value = if preserve_space {
                    unescape_xml(&value).into_owned()
                } else {
                    // Trim ASCII whitespace (space/tab/CR/LF). Unicode spaces
                    // such as U+00A0 (NBSP) are preserved.
                    unescape_xml(value.trim_matches([' ', '\t', '\r', '\n'])).into_owned()
                };
                if let Some(s) = &mut rich_buffer {
                    s.push_str(&value);
                } else {
                    rich_buffer = Some(value);
                }
            }
            Ok(Event::Eof) => return Err(XlsxError::XmlEof("")),
            Err(e) => return Err(XlsxError::Xml(e)),
            _ => (),
        }
    }
}

fn check_for_password_protected<RS: Read + Seek>(reader: &mut RS) -> Result<(), XlsxError> {
    let offset_end = reader.seek(std::io::SeekFrom::End(0))? as usize;
    reader.seek(std::io::SeekFrom::Start(0))?;

    if let Ok(cfb) = crate::cfb::Cfb::new(reader, offset_end) {
        if cfb.has_directory("EncryptedPackage") {
            return Err(XlsxError::Password);
        }
    }

    Ok(())
}

fn read_merge_cells<RS>(xml: &mut XlReader<'_, RS>) -> Result<Vec<Dimensions>, XlsxError>
where
    RS: Read + Seek,
{
    let mut merge_cells = Vec::new();

    loop {
        let mut buffer = Vec::new();

        match xml.read_event_into(&mut buffer) {
            Ok(Event::Start(event)) if event.local_name().as_ref() == b"mergeCell" => {
                if let Some(val) = event.raw_attr(b"ref")? {
                    merge_cells.push(get_dimension(val)?);
                }
            }
            Ok(Event::End(event)) if event.local_name().as_ref() == b"mergeCells" => {
                break;
            }
            Ok(Event::Eof) => return Err(XlsxError::XmlEof("")),
            Err(e) => return Err(XlsxError::Xml(e)),
            _ => (),
        }
    }

    Ok(merge_cells)
}

#[derive(Debug, Copy, Clone)]
enum Reference {
    Cell {
        row: u32,
        col: u32,
        absolute_row: bool,
        absolute_col: bool,
    },
    Row {
        row: u32,
        absolute: bool,
    },
    Column {
        col: u32,
        absolute: bool,
    },
}

impl Reference {
    // Create a cell reference with validation.
    fn cell(row: u32, col: u32, absolute_row: bool, absolute_col: bool) -> Result<Self, XlsxError> {
        let reference = Reference::Cell {
            row,
            col,
            absolute_row,
            absolute_col,
        };
        reference.validate()?;
        Ok(reference)
    }

    // Create a column reference with validation.
    fn column(col: u32, absolute: bool) -> Result<Self, XlsxError> {
        let reference = Reference::Column { col, absolute };
        reference.validate()?;
        Ok(reference)
    }

    // Create a row reference with validation.
    fn row(row: u32, absolute: bool) -> Result<Self, XlsxError> {
        let reference = Reference::Row { row, absolute };
        reference.validate()?;
        Ok(reference)
    }

    // Parse a reference (e.g., "A1", "$A1", "A$1", "$A$1", "E", "$E", "5", "$5").
    fn parse(name: &[u8]) -> Result<Self, XlsxError> {
        let mut iter = name.iter().peekable();
        let mut col: u32 = 0;
        let mut row: u32 = 0;
        let mut absolute_col = false;
        let mut absolute_row = false;

        while let Some(&c) = iter.next() {
            match (c, iter.peek()) {
                (b'$', Some(b'A'..=b'Z' | b'a'..=b'z')) => {
                    if row > 0 || col > 0 {
                        return Err(XlsxError::Alphanumeric(c));
                    }
                    absolute_col = true;
                }
                (b'$', Some(b'0'..=b'9')) => {
                    if row > 0 {
                        return Err(XlsxError::Alphanumeric(c));
                    }
                    absolute_row = true;
                }
                (b'$', _) => return Err(XlsxError::Alphanumeric(c)),
                (c @ (b'A'..=b'Z' | b'a'..=b'z'), _) => {
                    if row > 0 {
                        return Err(XlsxError::Alphanumeric(c));
                    }
                    col = col
                        .wrapping_mul(26)
                        .wrapping_add((c.to_ascii_uppercase() - b'A') as u32 + 1);
                }
                (c @ b'0'..=b'9', _) => {
                    row = row.wrapping_mul(10).wrapping_add((c - b'0') as u32);
                }
                _ => return Err(XlsxError::Alphanumeric(c)),
            }
        }

        match (col.checked_sub(1), row.checked_sub(1)) {
            (Some(col), Some(row)) => Reference::cell(row, col, absolute_row, absolute_col),
            (Some(col), None) => Reference::column(col, absolute_col),
            (None, Some(row)) => Reference::row(row, absolute_row),
            (None, None) => Err(XlsxError::Unexpected("Empty reference")),
        }
    }

    // Apply offset to create a new reference with validation.
    fn offset(self, offset: (i64, i64)) -> Result<Self, XlsxError> {
        let result = match self {
            Reference::Cell {
                row,
                col,
                absolute_row,
                absolute_col,
            } => {
                let new_col = if absolute_col {
                    col
                } else {
                    (col as i64 + offset.1) as u32
                };
                let new_row = if absolute_row {
                    row
                } else {
                    (row as i64 + offset.0) as u32
                };

                Reference::Cell {
                    row: new_row,
                    col: new_col,
                    absolute_row,
                    absolute_col,
                }
            }
            Reference::Column { col, absolute } => {
                let new_col = if absolute {
                    col
                } else {
                    (col as i64 + offset.1) as u32
                };

                Reference::Column {
                    col: new_col,
                    absolute,
                }
            }
            Reference::Row { row, absolute } => {
                let new_row = if absolute {
                    row
                } else {
                    (row as i64 + offset.0) as u32
                };

                Reference::Row {
                    row: new_row,
                    absolute,
                }
            }
        };

        result.validate()?;
        Ok(result)
    }

    // Validate that row/column values are in bounds.
    fn validate(&self) -> Result<(), XlsxError> {
        match self {
            Reference::Cell { row, col, .. } => {
                if *col >= MAX_COLUMNS {
                    return Err(XlsxError::ColumnNumberOverflow);
                }
                if *row >= MAX_ROWS {
                    return Err(XlsxError::RowNumberOverflow);
                }
                Ok(())
            }
            Reference::Column { col, .. } => {
                if *col >= MAX_COLUMNS {
                    return Err(XlsxError::ColumnNumberOverflow);
                }
                Ok(())
            }
            Reference::Row { row, .. } => {
                if *row >= MAX_ROWS {
                    return Err(XlsxError::RowNumberOverflow);
                }
                Ok(())
            }
        }
    }

    // Format a reference to bytes.
    fn format(&self, buf: &mut Vec<u8>) -> Result<(), XlsxError> {
        match self {
            Reference::Cell {
                row,
                col,
                absolute_row,
                absolute_col,
            } => {
                if *absolute_col {
                    buf.push(b'$');
                }
                column_number_to_name(*col, buf)?;
                if *absolute_row {
                    buf.push(b'$');
                }
                buf.extend((row + 1).to_string().into_bytes());
                Ok(())
            }
            Reference::Column { col, absolute } => {
                if *absolute {
                    buf.push(b'$');
                }
                column_number_to_name(*col, buf)
            }
            Reference::Row { row, absolute } => {
                if *absolute {
                    buf.push(b'$');
                }
                buf.extend((row + 1).to_string().into_bytes());
                Ok(())
            }
        }
    }
}

// Advance a reference by the offset (e.g., "A1", "E:F", "5:6", "A1:B5").
fn offset_range(range: &[u8], offset: (i64, i64), buf: &mut Vec<u8>) -> Result<(), XlsxError> {
    let colon_pos = range.iter().position(|&b| b == b':');

    match colon_pos {
        None => {
            let reference = Reference::parse(range)?;
            if !matches!(reference, Reference::Cell { .. }) {
                return Err(XlsxError::Unexpected("Single reference type must be cell"));
            }
            let offset_ref = reference.offset(offset)?;
            offset_ref.format(buf)
        }
        Some(idx) => {
            let start = &range[..idx];
            let end = &range[idx + 1..];

            let start_ref = Reference::parse(start)?;
            let end_ref = Reference::parse(end)?;

            if std::mem::discriminant(&start_ref) != std::mem::discriminant(&end_ref) {
                return Err(XlsxError::Unexpected("Range type mismatch"));
            }

            let start_offset = start_ref.offset(offset)?;
            let end_offset = end_ref.offset(offset)?;

            start_offset.format(buf)?;
            buf.push(b':');
            end_offset.format(buf)
        }
    }
}

/// Expand a shared-formula template from its starting cell to another cell.
///
/// `start` and `end` are zero-based `(row, column)` positions. Relative
/// references in `formula` are shifted by `end - start`; absolute references
/// remain fixed. This is the same expansion Calamine uses for XLSX shared
/// formulas in [`Reader::worksheet_formula`].
///
/// This helper is intentionally workbook-state agnostic. XLSX shared-formula
/// indices are worksheet-local, so callers are responsible for pairing the
/// right template and starting cell with each derived cell.
pub fn expand_shared_formula(
    formula: &str,
    start: (u32, u32),
    end: (u32, u32),
) -> Result<String, XlsxError> {
    let mut out = String::with_capacity(formula.len());
    expand_shared_formula_into(formula, start, end, &mut out)?;
    Ok(out)
}

/// Expand a shared-formula template into a caller-provided buffer.
///
/// This is the allocation-reuse variant of [`expand_shared_formula`]. `out` is
/// cleared before writing.
pub fn expand_shared_formula_into(
    formula: &str,
    start: (u32, u32),
    end: (u32, u32),
    out: &mut String,
) -> Result<(), XlsxError> {
    let offset = (end.0 as i64 - start.0 as i64, end.1 as i64 - start.1 as i64);
    expand_shared_formula_with_offset_into(formula, offset, out)
}

#[cfg(test)]
fn expand_shared_formula_with_offset(
    formula: &str,
    offset: (i64, i64),
) -> Result<String, XlsxError> {
    let mut out = String::with_capacity(formula.len());
    expand_shared_formula_with_offset_into(formula, offset, &mut out)?;
    Ok(out)
}

// Apply a row/column offset to each valid cell or range reference in a formula.
// Function-like tokens (immediately followed by `(`), quoted strings, structured
// references, and references that would move out of XLSX bounds are preserved.
fn expand_shared_formula_with_offset_into(
    formula: &str,
    offset: (i64, i64),
    out: &mut String,
) -> Result<(), XlsxError> {
    let bytes = formula.as_bytes();
    let mut res = std::mem::take(out).into_bytes();
    res.clear();
    let mut token_buf = Vec::with_capacity(32);
    let mut in_quote = false;

    let mut token_start = 0;
    let mut token_end = 0;

    for (i, &c) in bytes.iter().enumerate() {
        if !in_quote
            && (c.is_ascii_alphanumeric()
                || c == b'\\'
                || c == b'.'
                || c == b'_'
                || c == b'$'
                || c == b':')
        {
            token_end = i + 1;
        } else {
            if token_start < token_end {
                let next_is_paren = c == b'(';
                token_buf.clear();
                if !next_is_paren
                    && offset_range(&bytes[token_start..token_end], offset, &mut token_buf).is_ok()
                {
                    res.extend(&token_buf);
                } else {
                    res.extend(&bytes[token_start..token_end]);
                }
            }
            res.push(c);
            token_start = i + 1;
            token_end = i + 1;

            if c == b'"' {
                in_quote = !in_quote;
            }
        }
    }

    if token_start < token_end {
        token_buf.clear();
        if offset_range(&bytes[token_start..token_end], offset, &mut token_buf).is_ok() {
            res.extend(&token_buf);
        } else {
            res.extend(&bytes[token_start..token_end]);
        }
    }

    match String::from_utf8(res) {
        Ok(translated) => {
            *out = translated;
            Ok(())
        }
        Err(_) => Err(XlsxError::Unexpected("fail to convert cell name")),
    }
}

// Backward-compatible internal name used by existing tests.
#[cfg(test)]
fn replace_cell_names(s: &str, offset: (i64, i64)) -> Result<String, XlsxError> {
    expand_shared_formula_with_offset(s, offset)
}

/// Convert the integer to Excelsheet column title.
/// If the column number not in 1~16384, an Error is returned.
pub(crate) fn column_number_to_name(num: u32, buf: &mut Vec<u8>) -> Result<(), XlsxError> {
    if num >= MAX_COLUMNS {
        return Err(XlsxError::ColumnNumberOverflow);
    }
    let start = buf.len();
    let mut num = num + 1;
    while num > 0 {
        let integer = ((num - 1) % 26 + 65) as u8;
        buf.push(integer);
        num = (num - 1) / 26;
    }
    buf[start..].reverse();
    Ok(())
}

// Data type of the record's value.
enum Tag {
    // String
    S,
    // Number (Float or Int)
    N,
    // Missing
    M,
    // Error
    E,
    // Bool
    B,
    // Date
    D,
}

type Value = Option<Box<[u8]>>;

/// Check if tag is an item within a PivotCache Record, which does not require a Definitions lookup.
fn item_tag(e: &BytesStart) -> Option<Tag> {
    match e.local_name().as_ref() {
        b"s" => Some(Tag::S),
        b"n" => Some(Tag::N),
        b"m" => Some(Tag::M),
        b"e" => Some(Tag::E),
        b"b" => Some(Tag::B),
        b"d" => Some(Tag::D),
        _ => None,
    }
}

fn item_value(e: &BytesStart) -> Result<Value, quick_xml::events::attributes::AttrError> {
    Ok(e.raw_attr(b"v")?.map(Box::from))
}

// Get the target location of the pivot table's pivot cache definitions.
fn find_pivot_cache_definitions_from_pivot<RS>(
    zip: &mut zip::ZipArchive<RS>,
    path: &str,
    cache: &HashMap<String, String>,
) -> Result<String, XlsxError>
where
    RS: Read + Seek,
{
    let (base_folder, file_name) = path.rsplit_once('/').expect("should be in a folder");
    let rel_path = format!("{base_folder}/_rels/{file_name}.rels");
    let mut xml = match xml_reader(zip, &rel_path, cache) {
        None => return Err(XlsxError::FileNotFound(rel_path.to_owned())),
        Some(x) => x?,
    };
    let mut definitions_path = None;
    let mut buf = Vec::with_capacity(64);
    loop {
        buf.clear();
        match xml.read_event_into(&mut buf) {
            Ok(Event::Start(e)) if e.local_name().as_ref() == b"Relationship" => {
                let (target, typ) = get_attrs!(e, b"Target" => target, b"Type" => typ)?;
                let is_pivot_cache_definitions_type = typ == Some(b"http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition" as &[u8]);
                let target = match target {
                    Some(t) => decode_attr(&xml.decoder(), t)?,
                    None => String::new(),
                };
                match (is_pivot_cache_definitions_type, definitions_path.is_some()) {
                    (true, false) => {
                        if let Some(target) = target.strip_prefix("../") {
                            // this is an incomplete implementation, but should be good enough for excel
                            let (parent, _) = base_folder
                                .rsplit_once('/')
                                .expect("Must be a parent folder");
                            definitions_path.replace(format!("{parent}/{target}"));
                        } else if !target.is_empty() {
                            definitions_path.replace(target);
                        }
                    }
                    (true, true) => return Err(XlsxError::Unexpected(
                        "multiple pivot cache definition relationships found for one pivot table",
                    )),
                    _ => {}
                }
            }
            Ok(Event::End(e)) if e.local_name().as_ref() == b"Relationships" => break,
            Ok(Event::Eof) => return Err(XlsxError::XmlEof("Relationships")),
            Err(e) => return Err(XlsxError::Xml(e)),
            _ => (),
        }
    }
    match definitions_path {
        Some(path) => Ok(path),
        None => Err(XlsxError::Unexpected(
            "no pivot cache definition found for pivot table",
        )),
    }
}

// Get the target location of the pivot cache record file.
fn find_pivot_cache_records_from_definitions<RS>(
    zip: &mut zip::ZipArchive<RS>,
    path: &str,
    cache: &HashMap<String, String>,
    cache_path: &str,
) -> Result<String, XlsxError>
where
    RS: Read + Seek,
{
    let (base_folder, file_name) = path.rsplit_once('/').expect("should be in a folder");
    let rel_path = format!("{base_folder}/_rels/{file_name}.rels");
    let mut xml = match xml_reader(zip, rel_path.as_ref(), cache) {
        None => return Err(XlsxError::FileNotFound(rel_path.to_owned())),
        Some(x) => x?,
    };
    let mut record_path = None;
    let mut buf = Vec::with_capacity(64);
    loop {
        buf.clear();
        match xml.read_event_into(&mut buf) {
            Ok(Event::Start(e)) if e.local_name().as_ref() == b"Relationship" => {
                let (target, typ) = get_attrs!(e, b"Target" => target, b"Type" => typ)?;
                let is_pivot_cache_record_type = typ == Some(b"http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheRecords" as &[u8]);
                let target = match target {
                    Some(t) => decode_attr(&xml.decoder(), t)?,
                    None => String::new(),
                };
                match (is_pivot_cache_record_type, record_path.is_some()) {
                    (true, false) => {
                        if target.starts_with(cache_path) {
                            record_path.replace(target);
                        } else if !target.is_empty() {
                            record_path.replace(format!("{cache_path}/{target}"));
                        }
                    }
                    (true, true) => {
                        return Err(XlsxError::Unexpected(
                            "multiple pivot cache record relationships found for one pivot table",
                        ))
                    }
                    _ => {}
                }
            }
            Ok(Event::End(e)) if e.local_name().as_ref() == b"Relationships" => break,
            Ok(Event::Eof) => return Err(XlsxError::XmlEof("Relationships")),
            Err(e) => return Err(XlsxError::Xml(e)),
            _ => (),
        }
    }
    match record_path {
        Some(path) => Ok(path),
        None => Err(XlsxError::Unexpected(
            "no pivot cache records found for pivot table",
        )),
    }
}

// Return a vec of pivot table paths (ie xl/pivotTables/pivot1.xml) for a given sheet name.
fn find_pivot_table_paths_from_sheet<RS>(
    zip: &mut zip::ZipArchive<RS>,
    sheet_path: &str,
    cache: &HashMap<String, String>,
) -> Result<Vec<String>, XlsxError>
where
    RS: Read + Seek,
{
    let mut pivots_on_sheet = vec![];
    let mut buf = Vec::with_capacity(64);

    let last_folder_index = sheet_path.rfind('/').expect("should be in a folder");
    let (base_folder, file_name) = sheet_path.split_at(last_folder_index);
    let rel_path = format!("{base_folder}/_rels{file_name}.rels");

    let mut xml = match xml_reader(zip, &rel_path, cache) {
        // Some sheets may not have relationships - okay for path to not exist.
        None => return Ok(vec![]),
        Some(x) => x?,
    };
    loop {
        buf.clear();
        match xml.read_event_into(&mut buf) {
            Ok(Event::Start(e)) if e.local_name().as_ref() == b"Relationship" => {
                let (target, typ) = get_attrs!(e, b"Target" => target, b"Type" => typ)?;
                let is_pivot_table_type = typ == Some(b"http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable" as &[u8]);
                if is_pivot_table_type {
                    let target = match target {
                        Some(t) => decode_attr(&xml.decoder(), t)?,
                        None => String::new(),
                    };
                    if let Some(target) = target.strip_prefix("../") {
                        // this is an incomplete implementation, but should be good enough for excel
                        let (parent, _) = base_folder
                            .rsplit_once('/')
                            .expect("Must be a parent folder");
                        pivots_on_sheet.push(format!("{parent}/{target}"));
                    } else if !target.is_empty() {
                        pivots_on_sheet.push(target);
                    }
                }
            }
            Ok(Event::End(e)) if e.local_name().as_ref() == b"Relationships" => break,
            Ok(Event::Eof) => return Err(XlsxError::XmlEof("Relationships")),
            Err(e) => return Err(XlsxError::Xml(e)),
            _ => (),
        }
    }

    Ok(pivots_on_sheet)
}

// Takes a pivot table path (ie xl/pivotTables/pivot1.xml) and returns the name.
fn find_pivot_name_from_pivot_path<RS>(
    zip: &mut zip::ZipArchive<RS>,
    pivot_path: &str,
    cache: &HashMap<String, String>,
) -> Result<String, XlsxError>
where
    RS: Read + Seek,
{
    let mut xml = match xml_reader(zip, pivot_path, cache) {
        None => return Err(XlsxError::FileNotFound(pivot_path.to_string())),
        Some(x) => x?,
    };
    let mut buf = Vec::with_capacity(64);
    let mut name = None;
    loop {
        buf.clear();
        match xml.read_event_into(&mut buf) {
            Ok(Event::Start(e)) if e.local_name().as_ref() == b"pivotTableDefinition" => {
                if let Some(val) = e.raw_attr(b"name")? {
                    if name.is_some() {
                        return Err(XlsxError::Unexpected(
                            "multiple name entries for one pivot table path",
                        ));
                    }
                    name.replace(decode_attr(&xml.decoder(), val)?);
                }
            }
            Ok(Event::End(e)) if e.local_name().as_ref() == b"pivotTableDefinition" => break,
            Ok(Event::Eof) => return Err(XlsxError::XmlEof("pivotTableDefinition")),
            Err(e) => return Err(XlsxError::Xml(e)),
            _ => (),
        }
    }
    match name {
        Some(name) => Ok(name),
        None => Err(XlsxError::Unexpected("no name for pivot table")),
    }
}

/// Parse an item within a PivotCache Record into its appropriate [`Data`] type.
fn parse_item(item: &(Tag, Value), decoder: &Decoder) -> Data {
    let Some(val) = item.1.as_deref() else {
        return Data::Empty;
    };
    match item.0 {
        Tag::M => Data::Empty,
        Tag::S => {
            if let Ok(val) = decoder.decode(val.as_ref()) {
                Data::String(val.to_string())
            } else {
                Data::Error(CellErrorType::GettingData)
            }
        }
        Tag::N => {
            if val.contains(&b'.') {
                match bytes_to_f64(val, decoder) {
                    Some(val) => Data::Float(val),
                    None => Data::Error(CellErrorType::GettingData),
                }
            } else {
                match bytes_to_i64(val, decoder) {
                    Some(val) => Data::Int(val),
                    None => Data::Error(CellErrorType::GettingData),
                }
            }
        }
        Tag::D => {
            if let Ok(val) = decoder.decode(val) {
                Data::DateTimeIso(val.into())
            } else {
                Data::Error(CellErrorType::GettingData)
            }
        }
        Tag::B => {
            {
                // boolean tags only support W3C XML Schema
                match val {
                    b"0" | b"false" => Data::Bool(false),
                    b"1" | b"true" => Data::Bool(true),
                    _ => Data::Error(CellErrorType::GettingData),
                }
            }
        }
        Tag::E => Data::Error(CellErrorType::Ref),
    }
}

// Parse failures are handled with None and left to `Self::parse_item` to address.
fn bytes_to_i64(val: &[u8], decoder: &Decoder) -> Option<i64> {
    if let Ok(val) = decoder.decode(val) {
        atoi_simd::parse::<i64, true, false>(val.as_bytes()).ok()
    } else {
        None
    }
}

// Parse failures are handled with None and left to `parse_item` to address.
fn bytes_to_f64(val: &[u8], decoder: &Decoder) -> Option<f64> {
    if let Ok(val) = decoder.decode(val) {
        fast_float2::parse(val.as_bytes()).ok()
    } else {
        None
    }
}

#[derive(Default)]
pub struct PivotTables(Vec<PivotTableRef>);

impl PivotTables {
    fn new() -> Self {
        Self(vec![])
    }

    fn push(&mut self, pivot_table: PivotTableRef) {
        self.0.push(pivot_table);
    }

    /// Helper function to identify pivot tables by name and worksheet.
    ///
    /// # Returns
    ///
    /// ```text
    /// Vec<(&str, &str)>
    ///        │     │
    ///        │     └─── Pivot Table name
    ///        │
    ///        └──── Worksheet name
    /// ```
    ///
    /// # Note
    ///
    /// Pivot table names are unique per worksheet, not per workbook.
    ///
    /// # Examples
    ///
    /// An example of retrieving pivot cache data for a Pivot Table named "PivotTable1"
    /// on worksheet "PivotSheet1".
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook("tests/pivots.xlsx")?;
    ///     // Must retrieve necessary metadata before reading Pivot Table data.
    ///     let pivot_tables = workbook.pivot_tables()?;
    ///
    ///     // "PivotTable1" is found on both sheets: "PivotSheet1" & "PivotSheet3" so
    ///     // we must include the sheet name in our filter ~ see note on uniqueness.
    ///     let names_and_sheets = pivot_tables.get_pivot_tables_by_name_and_sheet()
    ///         .into_iter()
    ///         .filter_map(|pt| {
    ///             if pt.0.eq("PivotSheet1") && pt.1.eq("PivotTable1") {
    ///                 Some(pt)
    ///             } else {
    ///                 None
    ///             }
    ///         })
    ///         .collect::<Vec<_>>();
    ///
    ///     assert_eq!(names_and_sheets.len(), 1);
    ///
    ///     Ok(())
    ///
    /// }
    /// ```
    ///
    pub fn get_pivot_tables_by_name_and_sheet(&self) -> Vec<(&str, &str)> {
        self.0.iter().map(|pt| (pt.sheet(), pt.name())).collect()
    }

    /// Get the names of all pivot tables for a given worksheet.
    ///
    /// Worksheets that do not contain any pivot tables will return None. Worksheet names
    ///
    /// # Examples
    ///
    /// An example of getting all pivot tables for a provided sheet.
    ///
    /// ```
    /// use calamine::{open_workbook, Error, Xlsx};
    ///
    /// fn main() -> Result<(), Error> {
    ///
    ///     let path = "tests/pivots.xlsx";
    ///
    ///     // Open the workbook.
    ///     let mut workbook: Xlsx<_> = open_workbook(path)?;
    ///
    ///     // Must retrieve necessary metadata before reading Pivot Table data.
    ///     let pivot_tables = workbook.pivot_tables()?;
    ///
    ///     // Get the pivot table names in the workbook for a given sheet.
    ///     let pivot_table_names = pivot_tables.pivot_tables_by_sheet("PivotSheet1");
    ///
    ///     // Check the pivot table names (ordering not guaranteed).
    ///     assert_eq!(pivot_table_names, vec!["PivotTable1"]);
    ///
    ///     Ok(())
    /// }
    /// ```
    ///
    pub fn pivot_tables_by_sheet(&self, sheet_name: &str) -> Vec<&str> {
        self.0
            .iter()
            .filter_map(|val| {
                if val.sheet() == sheet_name {
                    Some(val.name())
                } else {
                    None
                }
            })
            .collect::<Vec<&str>>()
    }
}

struct PivotTableRef {
    name: String,
    sheet: String,
    records: String,
    definitions: String,
}

impl PivotTableRef {
    fn new(name: String, sheet: String, records: String, definitions: String) -> Self {
        Self {
            name,
            sheet,
            records,
            definitions,
        }
    }
    fn name(&self) -> &str {
        self.name.as_ref()
    }
    fn sheet(&self) -> &str {
        self.sheet.as_ref()
    }
    fn records(&self) -> &str {
        self.records.as_ref()
    }
    fn definitions(&self) -> &str {
        self.definitions.as_ref()
    }
}

pub struct PivotCacheIter<'a, RS: Read + Seek + 'a> {
    definitions: HashMap<String, Vec<(Tag, Value)>>,
    field_names: Vec<String>,
    reader: XlReader<'a, RS>,
}
fn get_pivot_cache_iter<'a, RS: Read + Seek + 'a>(
    xl: &'a mut crate::Xlsx<RS>,
    pivot_table: &PivotTableRef,
) -> Result<PivotCacheIter<'a, RS>, XlsxError> {
    let definitions = pivot_table.definitions();
    let records = pivot_table.records();

    let mut fields: Vec<Vec<(Tag, Value)>> = vec![];
    let mut definition_map = HashMap::new();
    let mut field_names = vec![];

    // Converting into an iterator requires first reading a pivotCacheDefinitions.xml file
    // to get lookup values used in pivotCacheRecords.xml file.
    {
        let mut xml = match xml_reader(&mut xl.zip, definitions, &xl.zip_path_cache) {
            None => {
                return Err(XlsxError::FileNotFound(format!(
                    "File not found: {}",
                    definitions
                )))
            }
            Some(x) => x?,
        };

        let mut buf = Vec::with_capacity(64);
        // building list of field names and definitions from some pivotCacheDefinitions.xml file
        loop {
            buf.clear();

            match xml.read_event_into(&mut buf) {
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"cacheField" => {
                    let (name, formula) = get_attrs!(e, b"name" => name, b"formula" => formula)?;
                    if let Some(name) = name {
                        field_names.push(decode_attr(&xml.decoder(), name)?);
                        fields.push(vec![]);
                    }
                    if formula.is_some() {
                        field_names.pop();
                        fields.pop();
                    }
                }
                // Exclude grouped fields from results.
                // This does not represent the underlying data and should be removed.
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"groupItems" => {
                    field_names.pop();
                    fields.pop();
                }
                Ok(Event::Eof) => break,
                Err(e) => {
                    panic!("{e}")
                }
                Ok(Event::Start(e)) => {
                    if let Some(tag) = item_tag(&e) {
                        if let Some(field) = fields.last_mut() {
                            field.push((tag, item_value(&e)?));
                        }
                    }
                }
                Ok(_) => {}
            }
        }

        // add the definitions to the definition map with a key on field name
        for (field, name) in fields.into_iter().zip(field_names.iter()) {
            definition_map.insert(name.to_string(), field);
        }
    }

    xml_reader(&mut xl.zip, records, &xl.zip_path_cache).map_or_else(
        || {
            Err(XlsxError::FileNotFound(format!(
                "File not found: {records}"
            )))
        },
        |record_reader| {
            Ok(PivotCacheIter::new(
                definition_map,
                field_names,
                record_reader?,
            ))
        },
    )
}

impl<'a, RS: Read + Seek + 'a> PivotCacheIter<'a, RS> {
    fn new(
        definitions: HashMap<String, Vec<(Tag, Value)>>,
        field_names: Vec<String>,
        reader: XlReader<'a, RS>,
    ) -> Self {
        Self {
            definitions,
            field_names,
            reader,
        }
    }
}

// Iterates over <r>, the tag for a row, found in the PivotCacheRecords.xml file.
// PivotCacheIter must also hold some lookup values / metadata to support the content within <r>.
//
// https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.pivotcacherecord?view=openxml-3.0.1
impl<'a, RS: Read + Seek + 'a> Iterator for PivotCacheIter<'a, RS> {
    type Item = Result<Vec<Data>, XlsxError>;

    fn next(&mut self) -> Option<Self::Item> {
        let mut row = vec![];
        let mut col_number = 0;
        let mut buf = Vec::with_capacity(64);
        loop {
            buf.clear();
            match self.reader.read_event_into(&mut buf) {
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"x" => {
                    let v = match e.raw_attr(b"v") {
                        Ok(v) => v,
                        Err(e) => return Some(Err(e.into())),
                    };
                    if let Some(val) = v {
                        let value_position = match atoi_simd::parse::<usize, true, false>(val) {
                            Ok(val) => val,
                            Err(_) => {
                                return Some(Err(XlsxError::Unexpected(
                                    "pivot cache x:v attribute must be a number",
                                )));
                            }
                        };

                        let column_name = &self.field_names[col_number];
                        row.push(parse_item(
                            &self.definitions[column_name][value_position],
                            &self.reader.decoder(),
                        ));
                    }

                    col_number += 1;
                }
                Ok(Event::End(e)) if e.local_name().as_ref() == b"r" => return Some(Ok(row)),
                Ok(Event::Start(e)) if e.local_name().as_ref() == b"pivotCacheRecords" => {
                    return Some(Ok(self
                        .field_names
                        .iter()
                        .map(|fields| Data::String(fields.to_string()))
                        .collect()))
                }
                Ok(Event::Eof) => return None,
                Err(e) => return Some(Err(XlsxError::Xml(e))),
                Ok(Event::Start(e)) => {
                    if let Some(tag) = item_tag(&e) {
                        let value = match item_value(&e) {
                            Ok(value) => value,
                            Err(e) => return Some(Err(e.into())),
                        };
                        row.push(parse_item(&(tag, value), &self.reader.decoder()));
                        col_number += 1;
                    }
                }
                Ok(_) => {}
            }
        }
    }
}

// -----------------------------------------------------------------------
// Unit tests for Xlsx.
// -----------------------------------------------------------------------
#[cfg(test)]
mod tests {
    use super::*;
    use std::io::Write;
    use zip::write::SimpleFileOptions;
    use zip::ZipWriter;

    #[test]
    fn test_dimensions() {
        assert_eq!(get_row_column(b"A1").unwrap(), (0, 0));
        assert_eq!(get_row_column(b"C107").unwrap(), (106, 2));
        assert_eq!(
            get_dimension(b"C2:D35").unwrap(),
            Dimensions {
                start: (1, 2),
                end: (34, 3)
            }
        );
        assert_eq!(
            get_dimension(b"A1:XFD1048576").unwrap(),
            Dimensions {
                start: (0, 0),
                end: (1_048_575, 16_383),
            }
        );
    }

    #[test]
    fn test_dimension_length() {
        assert_eq!(get_dimension(b"A1:Z99").unwrap().len(), 2_574);
        assert_eq!(
            get_dimension(b"A1:XFD1048576").unwrap().len(),
            17_179_869_184
        );
    }

    #[test]
    fn test_parse_error() {
        assert_eq!(
            CellErrorType::from_str("#DIV/0!").unwrap(),
            CellErrorType::Div0
        );
        assert_eq!(CellErrorType::from_str("#N/A").unwrap(), CellErrorType::NA);
        assert_eq!(
            CellErrorType::from_str("#NAME?").unwrap(),
            CellErrorType::Name
        );
        assert_eq!(
            CellErrorType::from_str("#NULL!").unwrap(),
            CellErrorType::Null
        );
        assert_eq!(
            CellErrorType::from_str("#NUM!").unwrap(),
            CellErrorType::Num
        );
        assert_eq!(
            CellErrorType::from_str("#REF!").unwrap(),
            CellErrorType::Ref
        );
        assert_eq!(
            CellErrorType::from_str("#VALUE!").unwrap(),
            CellErrorType::Value
        );
    }

    #[test]
    fn test_column_number_to_name() {
        let check = |num, expected: &[u8]| {
            let mut buf = Vec::new();
            column_number_to_name(num, &mut buf).unwrap();
            assert_eq!(buf, expected);
        };

        check(0, b"A");
        check(25, b"Z");
        check(26, b"AA");
        check(27, b"AB");
        check(MAX_COLUMNS - 1, b"XFD");
    }

    #[test]
    fn test_parse_reference() {
        let check_cell =
            |input: &[u8], row, col, abs_row, abs_col| match Reference::parse(input).unwrap() {
                Reference::Cell {
                    row: r,
                    col: c,
                    absolute_row: ar,
                    absolute_col: ac,
                } => {
                    assert_eq!((r, c, ar, ac), (row, col, abs_row, abs_col));
                }
                _ => panic!("Expected Cell reference"),
            };

        let check_column = |input: &[u8], col, abs| match Reference::parse(input).unwrap() {
            Reference::Column {
                col: c,
                absolute: a,
            } => {
                assert_eq!((c, a), (col, abs));
            }
            _ => panic!("Expected Column reference"),
        };

        let check_row = |input: &[u8], row, abs| match Reference::parse(input).unwrap() {
            Reference::Row {
                row: r,
                absolute: a,
            } => {
                assert_eq!((r, a), (row, abs));
            }
            _ => panic!("Expected Row reference"),
        };

        // Cell references
        check_cell(b"A1", 0, 0, false, false);
        check_cell(b"$A1", 0, 0, false, true);
        check_cell(b"A$1", 0, 0, true, false);
        check_cell(b"$A$1", 0, 0, true, true);
        check_cell(b"XFD1048576", MAX_ROWS - 1, MAX_COLUMNS - 1, false, false);

        // Column references
        check_column(b"A", 0, false);
        check_column(b"$A", 0, true);
        check_column(b"XFD", MAX_COLUMNS - 1, false);

        // Row references
        check_row(b"1", 0, false);
        check_row(b"$1", 0, true);
        check_row(b"1048576", MAX_ROWS - 1, false);
    }

    #[test]
    fn test_format_reference() {
        let check_cell = |row, col, abs_row, abs_col, expected: &[u8]| {
            let mut buf = Vec::new();
            Reference::Cell {
                row,
                col,
                absolute_row: abs_row,
                absolute_col: abs_col,
            }
            .format(&mut buf)
            .unwrap();
            assert_eq!(buf, expected);
        };

        let check_column = |col, absolute, expected: &[u8]| {
            let mut buf = Vec::new();
            Reference::Column { col, absolute }
                .format(&mut buf)
                .unwrap();
            assert_eq!(buf, expected);
        };

        let check_row = |row, absolute, expected: &[u8]| {
            let mut buf = Vec::new();
            Reference::Row { row, absolute }.format(&mut buf).unwrap();
            assert_eq!(buf, expected);
        };

        // Cell references
        check_cell(0, 0, false, false, b"A1");
        check_cell(0, 0, false, true, b"$A1");
        check_cell(0, 0, true, false, b"A$1");
        check_cell(0, 0, true, true, b"$A$1");
        check_cell(MAX_ROWS - 1, MAX_COLUMNS - 1, false, false, b"XFD1048576");

        // Column references
        check_column(0, false, b"A");
        check_column(0, true, b"$A");
        check_column(MAX_COLUMNS - 1, false, b"XFD");

        // Row references
        check_row(0, false, b"1");
        check_row(0, true, b"$1");
        check_row(MAX_ROWS - 1, false, b"1048576");
    }

    #[test]
    fn test_format_reference_overflow() {
        let check_err = |reference: Reference, offset| {
            let result = reference.offset(offset);
            assert!(
                matches!(
                    result,
                    Err(XlsxError::ColumnNumberOverflow) | Err(XlsxError::RowNumberOverflow)
                ),
                "expected overflow error, got {:?}",
                result
            );
        };

        // Cell reference offset pushes column out of bounds
        check_err(
            Reference::Cell {
                row: 0,
                col: MAX_COLUMNS - 1,
                absolute_row: false,
                absolute_col: false,
            },
            (0, 1),
        );

        // Cell reference offset pushes row out of bounds
        check_err(
            Reference::Cell {
                row: MAX_ROWS - 1,
                col: 0,
                absolute_row: false,
                absolute_col: false,
            },
            (1, 0),
        );

        // Column reference offset pushes out of bounds
        check_err(
            Reference::Column {
                col: MAX_COLUMNS - 1,
                absolute: false,
            },
            (0, 1),
        );

        // Row reference offset pushes out of bounds
        check_err(
            Reference::Row {
                row: MAX_ROWS - 1,
                absolute: false,
            },
            (1, 0),
        );
    }

    #[test]
    fn test_offset_range() {
        let check = |input: &[u8], offset, expected: &[u8]| {
            let mut buf = Vec::new();
            offset_range(input, offset, &mut buf).unwrap();
            assert_eq!(buf, expected);
        };

        let check_err = |input: &[u8], offset| {
            let mut buf = Vec::new();
            let res = offset_range(input, offset, &mut buf);
            assert!(res.is_err());
            assert_eq!(buf.len(), 0)
        };

        // Cell references
        check(b"A1", (1, 1), b"B2");
        check(b"$A1", (1, 1), b"$A2");
        check(b"A$1", (1, 1), b"B$1");
        check(b"$A$1", (1, 1), b"$A$1");

        // Column references
        check_err(b"E", (0, 1));
        check_err(b"$E", (0, 1));

        // Row references
        check_err(b"5", (1, 0));
        check_err(b"$5", (1, 0));

        // Cell ranges
        check(b"A1:B2", (1, 1), b"B2:C3");
        check(b"$A$1:$B$2", (1, 1), b"$A$1:$B$2");

        // Column ranges
        check(b"E:F", (0, 1), b"F:G");
        check(b"$E:$F", (0, 1), b"$E:$F");
        check(b"E:F", (1, 0), b"E:F");

        // Row ranges
        check(b"5:6", (1, 0), b"6:7");
        check(b"$5:$6", (1, 0), b"$5:$6");
        check(b"5:6", (0, 1), b"5:6");
    }

    #[test]
    fn test_parse_reference_overflow() {
        let check_col_err = |input: &[u8]| {
            assert!(matches!(
                Reference::parse(input),
                Err(XlsxError::ColumnNumberOverflow)
            ));
        };
        let check_row_err = |input: &[u8]| {
            assert!(matches!(
                Reference::parse(input),
                Err(XlsxError::RowNumberOverflow)
            ));
        };
        let check_syntax_err = |input: &[u8]| {
            assert!(matches!(
                Reference::parse(input),
                Err(XlsxError::Alphanumeric(_))
            ));
        };

        // Invalid syntax
        check_syntax_err(b"A$A1");
        check_syntax_err(b"A1$2");
        check_syntax_err(b"$$A1");
        check_syntax_err(b"$A$$1");
        check_syntax_err(b"A$$1");
        check_syntax_err(b"1A");
        check_syntax_err(b"1A1");
        check_syntax_err(b"A1B2");

        // Cell references
        check_col_err(b"XFE1");
        check_col_err(b"AAAA1");
        check_row_err(b"A1048577");
        check_row_err(b"A99999999999999999999");
        check_col_err(b"$XFE$1");

        // Column references
        check_col_err(b"XFE");
        check_col_err(b"$XFE");

        // Row references
        check_row_err(b"1048577");
        check_row_err(b"$1048577");
    }

    #[test]
    fn test_offset_range_overflow() {
        let check_col_err = |input: &[u8], offset| {
            let mut buf = Vec::new();
            assert!(matches!(
                offset_range(input, offset, &mut buf),
                Err(XlsxError::ColumnNumberOverflow)
            ));
        };
        let check_row_err = |input: &[u8], offset| {
            let mut buf = Vec::new();
            assert!(matches!(
                offset_range(input, offset, &mut buf),
                Err(XlsxError::RowNumberOverflow)
            ));
        };

        // Original reference is out of bounds
        check_col_err(b"XFE1", (0, 0));
        check_col_err(b"$XFE$1", (0, 0));
        check_row_err(b"A1048577", (0, 0));
        check_row_err(b"$A$1048577", (0, 0));
        check_col_err(b"XFE:XFE", (0, 0));
        check_row_err(b"1048577:1048577", (0, 0));

        // Offset pushes valid cell out of bounds
        check_col_err(b"XFD1", (0, 1));
        check_row_err(b"A1048576", (1, 0));
        check_row_err(b"XFD1048576", (1, 0));
        check_col_err(b"XFD1048576", (0, 1));

        // Offset pushes valid range out of bounds
        check_col_err(b"XFD:XFD", (0, 1));
        check_row_err(b"1048576:1048576", (1, 0));
    }

    #[test]
    fn test_expand_shared_formula_helpers() {
        assert_eq!(
            expand_shared_formula("A1+$B1+C$1+$D$1", (0, 0), (2, 3)).unwrap(),
            "D3+$B3+F$1+$D$1"
        );
        assert_eq!(
            expand_shared_formula("SUM(Sheet1!A1,'My Sheet'!$B2)", (0, 0), (1, 1)).unwrap(),
            "SUM(Sheet1!B2,'My Sheet'!$B3)"
        );
        assert_eq!(
            expand_shared_formula("CONCATENATE(A1, \"A1\", Table1[Column1])", (0, 0), (1, 0))
                .unwrap(),
            "CONCATENATE(A2, \"A1\", Table1[Column1])"
        );
        assert_eq!(
            expand_shared_formula("SUM(A1:XFE1)", (0, 0), (1, 1)).unwrap(),
            "SUM(A1:XFE1)"
        );

        let mut out = String::with_capacity(128);
        expand_shared_formula_into("A1", (0, 0), (4, 4), &mut out).unwrap();
        assert_eq!(out, "E5");
        let cap = out.capacity();
        expand_shared_formula_into("B2", (1, 1), (2, 2), &mut out).unwrap();
        assert_eq!(out, "C3");
        assert!(out.capacity() >= cap);
    }

    #[test]
    fn test_replace_cell_names() {
        assert_eq!(replace_cell_names("A1", (1, 0)).unwrap(), "A2".to_owned());
        assert_eq!(
            replace_cell_names("CONCATENATE(A1, \"a\")", (1, 0)).unwrap(),
            "CONCATENATE(A2, \"a\")".to_owned()
        );
        assert_eq!(
            replace_cell_names(
                "A1 is a cell, B1 is another, also C107, but XFE123 is not and \"A3\" in quote wont change.",
                (1, 0)
            )
            .unwrap(),
            "A2 is a cell, B2 is another, also C108, but XFE123 is not and \"A3\" in quote wont change.".to_owned()
        );
        assert_eq!(
            replace_cell_names("한글 A1 テスト", (0, 1)).unwrap(),
            "한글 B1 テスト".to_owned()
        );

        assert_eq!(
            replace_cell_names("ABS(AND(Q1934;AA1_SOMETHING))", (0, 1)).unwrap(),
            "ABS(AND(R1934;AA1_SOMETHING))".to_owned()
        );

        assert_eq!(
            replace_cell_names("ABS(AND(Q1934;AA1_SOMETHING))", (1, 0)).unwrap(),
            "ABS(AND(Q1935;AA1_SOMETHING))".to_owned()
        );

        assert_eq!(
            replace_cell_names("Z1+1", (0, 1)).unwrap(),
            "AA1+1".to_owned()
        );

        assert_eq!(
            replace_cell_names("Z9+3", (1, 0)).unwrap(),
            "Z10+3".to_owned()
        );

        assert_eq!(
            replace_cell_names("Z1+A3B", (0, 1)).unwrap(),
            "AA1+A3B".to_owned()
        );

        assert_eq!(
            replace_cell_names("Z1+A3B", (1, 0)).unwrap(),
            "Z2+A3B".to_owned()
        );

        // Named range after a cell ref.
        assert_eq!(
            replace_cell_names("A6+NO1_VARIABLE", (0, 1)).unwrap(),
            "B6+NO1_VARIABLE"
        );
        assert_eq!(
            replace_cell_names("A6+NO1_VARIABLE", (1, 0)).unwrap(),
            "A7+NO1_VARIABLE"
        );

        assert_eq!(
            replace_cell_names("A6+NO1.VARIABLE", (0, 1)).unwrap(),
            "B6+NO1.VARIABLE"
        );
        assert_eq!(
            replace_cell_names("A6+NO1.VARIABLE", (1, 0)).unwrap(),
            "A7+NO1.VARIABLE"
        );

        assert_eq!(replace_cell_names("A6+\\NO1", (0, 1)).unwrap(), "B6+\\NO1");
        assert_eq!(replace_cell_names("A6+\\NO1", (1, 0)).unwrap(), "A7+\\NO1");

        // Named range before a cell ref.
        assert_eq!(
            replace_cell_names("NO1_VARIABLE+Q5", (0, 1)).unwrap(),
            "NO1_VARIABLE+R5"
        );

        assert_eq!(
            replace_cell_names("NO1.VARIABLE+Q5", (0, 1)).unwrap(),
            "NO1.VARIABLE+R5"
        );

        assert_eq!(replace_cell_names("\\NO1+Q5", (0, 1)).unwrap(), "\\NO1+R5");

        // Leading underscore named range — should pass through.
        assert_eq!(
            replace_cell_names("_PRIVATE+A1", (0, 1)).unwrap(),
            "_PRIVATE+B1"
        );

        // Named range with multiple underscores.
        assert_eq!(
            replace_cell_names("NO1__DOUBLE+A1", (0, 1)).unwrap(),
            "NO1__DOUBLE+B1"
        );

        // Named range with multiple period signs.
        assert_eq!(
            replace_cell_names("NO1..VARIABLE+A1", (0, 1)).unwrap(),
            "NO1..VARIABLE+B1"
        );

        // Named range with multiple back slashes.
        assert_eq!(
            replace_cell_names("\\\\NO1+A1", (0, 1)).unwrap(),
            "\\\\NO1+B1"
        );

        assert_eq!(
            replace_cell_names("ABC\"asd\"123", (1, 0)).unwrap(),
            "ABC\"asd\"123".to_owned()
        );

        // Column ranges
        assert_eq!(
            replace_cell_names("SUM(E:F)", (0, 1)).unwrap(),
            "SUM(F:G)".to_owned()
        );
        assert_eq!(
            replace_cell_names("SUM($E:$F)", (0, 1)).unwrap(),
            "SUM($E:$F)".to_owned()
        );
        assert_eq!(
            replace_cell_names("SUM($E:F)", (0, 1)).unwrap(),
            "SUM($E:G)".to_owned()
        );

        // Row ranges
        assert_eq!(
            replace_cell_names("SUM(5:6)", (1, 0)).unwrap(),
            "SUM(6:7)".to_owned()
        );
        assert_eq!(
            replace_cell_names("SUM($5:$6)", (1, 0)).unwrap(),
            "SUM($5:$6)".to_owned()
        );
        assert_eq!(
            replace_cell_names("SUM($5:6)", (1, 0)).unwrap(),
            "SUM($5:7)".to_owned()
        );

        // Mixed with cell references
        assert_eq!(
            replace_cell_names("SUM(A1:A5,E:F)", (0, 1)).unwrap(),
            "SUM(B1:B5,F:G)".to_owned()
        );

        // Invalid syntax
        assert_eq!(
            replace_cell_names(
                "Valid: A1 Invalid: A1B1 A1$ $$A1 $A$$1 A$$1 A:1 1:A 1 A A1:1 A1:B A$A1 A1$2 $1 $A Valid: C1:D1",
                (1, 1)
            )
            .unwrap(),
            "Valid: B2 Invalid: A1B1 A1$ $$A1 $A$$1 A$$1 A:1 1:A 1 A A1:1 A1:B A$A1 A1$2 $1 $A Valid: D2:E2"
                .to_owned()
        );
    }

    #[test]
    fn test_read_shared_strings_with_namespaced_si_name() {
        let shared_strings_data = br#"<?xml version="1.0" encoding="utf-8"?>
<x:sst count="1187" uniqueCount="1187" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <x:si>
        <x:t>String 1</x:t>
    </x:si>
    <x:si>
        <x:r>
            <x:rPr>
                <x:sz val="11"/>
            </x:rPr>
            <x:t>String 2</x:t>
        </x:r>
    </x:si>
    <x:si>
        <x:r>
            <x:t>String 3</x:t>
        </x:r>
    </x:si>
</x:sst>"#;

        let mut buf = [0; 1000];
        let mut zip_writer = ZipWriter::new(std::io::Cursor::new(&mut buf[..]));
        let options =
            SimpleFileOptions::default().compression_method(zip::CompressionMethod::Stored);
        zip_writer
            .start_file("xl/sharedStrings.xml", options)
            .unwrap();
        zip_writer.write_all(shared_strings_data).unwrap();
        let zip_size = zip_writer.finish().unwrap().position() as usize;

        let zip = ZipArchive::new(std::io::Cursor::new(&buf[..zip_size])).unwrap();
        let zip_path_cache = build_zip_path_cache(&zip);
        let mut xlsx = Xlsx {
            zip,
            xl_path: "xl/".to_string(),
            strings: vec![],
            sheets: vec![],
            tables: None,
            formats: vec![],
            is_1904: false,
            metadata: Metadata::default(),
            #[cfg(feature = "picture")]
            pictures: None,
            merged_regions: None,
            options: XlsxOptions::default(),
            zip_path_cache,
        };

        assert!(xlsx.read_shared_strings().is_ok());
        assert_eq!(3, xlsx.strings.len());
        assert_eq!("String 1", &xlsx.strings[0]);
        assert_eq!("String 2", &xlsx.strings[1]);
        assert_eq!("String 3", &xlsx.strings[2]);
    }
}