excel_ooxml/

writer.rs

//! Writing our minimal [`Workbook`] model out to a valid `.xlsx` package.

use std::collections::HashMap;
use std::io::{Seek, Write};

use opc::{Package, Part, Relationship, Relationships, TargetMode};
use xml_core::{BytesDecl, BytesEnd, BytesStart, BytesText, Event, Writer};

use crate::error::Result;
use crate::model::{
    AutoFilterColumn, Border, BorderEdge, BorderStyle, CalculationMode, CalculationProperties,
    Cell, CellAnchorPoint, CellComment, CellFormat, CellHyperlink, CellValue, CfvoPosition,
    ColumnSetting, ComparisonOperator, ConditionalFormattingCondition, ConditionalFormattingRule,
    CustomPropertyValue, DataValidationKind, DataValidationRule, DefinedName, DocumentProperties,
    DrawingObject, ExcelDateTime, ExcelTable, ExternalWorkbookLink, GradientFill,
    HorizontalAlignment, HyperlinkTarget, IconSetType, IgnoredError, NamedCellStyle,
    PageOrientation, PatternFill, PatternType, PrintSettings, ProtectedRange, RichTextRun,
    Scenario, Sheet, SheetChart, SheetDrawing, SheetPicture, SheetProtection, SheetVisibility,
    SortState, TableColumn, TableStyle, TableStyleElementType, TextComparisonOperator,
    TotalsRowFunction, VerticalAlignment, Workbook, WorkbookProtection, WriteProtection,
};

const SPREADSHEETML_NAMESPACE: &str = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
const RELATIONSHIPS_NAMESPACE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships";

const MAIN_WORKBOOK_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
const MAIN_WORKBOOK_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument";
const MAIN_WORKBOOK_PART_NAME: &str = "/xl/workbook.xml";
const MAIN_WORKBOOK_ENTRY_NAME: &str = "xl/workbook.xml";

const WORKSHEET_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml";
const WORKSHEET_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet";

// `xl/styles.xml` and `xl/sharedStrings.xml` are, unlike `word-ooxml`'s
// `word/styles.xml`/`word/numbering.xml`, not optional here: both relationships are created
// unconditionally the moment a brand-new workbook is created, before the caller adds a single cell
// — real Excel expects `styles.xml` to carry at least one font, exactly two fills (`none` then
// `gray125`, in that order — the second is a real, easy-to-miss Excel quirk with no obvious purpose
// but required nonetheless), one border and one `cellXfs`/`cellStyleXfs` entry each, or it treats
// the file as needing repair. This crate always writes both parts, matching that real-world minimum
// rather than the letter of the schema (which technically allows omitting almost everything).
const STYLES_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml";
const STYLES_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles";
const STYLES_PART_NAME: &str = "/xl/styles.xml";
const STYLES_ENTRY_NAME: &str = "styles.xml";

const SHARED_STRINGS_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml";
const SHARED_STRINGS_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings";
const SHARED_STRINGS_PART_NAME: &str = "/xl/sharedStrings.xml";
const SHARED_STRINGS_ENTRY_NAME: &str = "sharedStrings.xml";

// Package-level parts, same posture (and same fixed minimal content, for now — no
// `WorkbookProperties` model yet, matching `word-ooxml`'s own before
// `DocumentProperties`/`ExtendedProperties` existed) as `word-ooxml`'s
// `docProps/core.xml`/`docProps/app.xml`.
const CORE_PROPERTIES_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-package.core-properties+xml";
const CORE_PROPERTIES_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties";
const CORE_PROPERTIES_PART_NAME: &str = "/docProps/core.xml";
const CORE_PROPERTIES_ENTRY_NAME: &str = "docProps/core.xml";
const CORE_PROPERTIES_NAMESPACE: &str =
    "http://schemas.openxmlformats.org/package/2006/metadata/core-properties";
const DC_NAMESPACE: &str = "http://purl.org/dc/elements/1.1/";
const DCTERMS_NAMESPACE: &str = "http://purl.org/dc/terms/";
const XSI_NAMESPACE: &str = "http://www.w3.org/2001/XMLSchema-instance";

const EXTENDED_PROPERTIES_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-officedocument.extended-properties+xml";
const EXTENDED_PROPERTIES_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties";
const EXTENDED_PROPERTIES_PART_NAME: &str = "/docProps/app.xml";
const EXTENDED_PROPERTIES_ENTRY_NAME: &str = "docProps/app.xml";
const EXTENDED_PROPERTIES_NAMESPACE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/extended-properties";

// `docProps/custom.xml`. Genuinely optional (unlike `core.xml`/`app.xml` above, always written):
// only added when `Workbook.properties.custom_properties` is non-empty, mirroring `word-ooxml`'s
// own `CUSTOM_PROPERTIES_*` constants and posture exactly.
const CUSTOM_PROPERTIES_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-officedocument.custom-properties+xml";
const CUSTOM_PROPERTIES_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties";
const CUSTOM_PROPERTIES_PART_NAME: &str = "/docProps/custom.xml";
const CUSTOM_PROPERTIES_ENTRY_NAME: &str = "docProps/custom.xml";
const CUSTOM_PROPERTIES_NAMESPACE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/custom-properties";
const CUSTOM_PROPERTIES_VT_NAMESPACE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes";
/// The fixed `fmtid` every custom document property uses — the well-known GUID confirmed against
/// real `docProps/custom.xml` fixtures (e.g. the Open-XML-SDK test suite), same constant
/// `word-ooxml`'s own `CUSTOM_PROPERTY_FMTID` uses.
const CUSTOM_PROPERTY_FMTID: &str = "{D5CDD505-2E9C-101B-9397-08002B2CF9AE}";
/// The first `pid` assigned to a custom property, in declaration order — `0`/`1` are reserved by
/// the format, same convention `word-ooxml`'s own `FIRST_CUSTOM_PROPERTY_PID` uses.
const FIRST_CUSTOM_PROPERTY_PID: i32 = 2;

// Per-worksheet related parts (hyperlinks, structured tables, legacy comments + their VML
// drawing).
const HYPERLINK_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink";

const TABLE_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml";
const TABLE_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/table";

const COMMENTS_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml";
const COMMENTS_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments";

// a sheet's drawing (embedded pictures/charts).
const DRAWING_CONTENT_TYPE: &str = "application/vnd.openxmlformats-officedocument.drawing+xml";
const DRAWING_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing";
const IMAGE_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image";
const CHART_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-officedocument.drawingml.chart+xml";
const CHART_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart";
const DRAWINGML_NAMESPACE: &str = "http://schemas.openxmlformats.org/drawingml/2006/main";
const SPREADSHEET_DRAWING_NAMESPACE: &str =
    "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing";
const CHART_NAMESPACE: &str = "http://schemas.openxmlformats.org/drawingml/2006/chart";

// The legacy VML comment-shape drawing real Excel still expects alongside `xl/commentsN.xml` for
// the comment indicator/popup to render correctly in every Excel version.
const VML_DRAWING_CONTENT_TYPE: &str = "application/vnd.openxmlformats-officedocument.vmlDrawing";
const VML_DRAWING_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing";

const THEME_CONTENT_TYPE: &str = "application/vnd.openxmlformats-officedocument.theme+xml";
const THEME_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme";
const THEME_PART_NAME: &str = "/xl/theme/theme1.xml";
const THEME_ENTRY_NAME: &str = "theme/theme1.xml";

/// The workbook part's own content type once a VBA project is present — the `.xlsm` "macro-enabled"
/// variant of the ordinary `.xlsx` main workbook content type, otherwise identical in every other
/// respect (same schema, same `[Content_Types].xml` `Override` mechanism).
const MACRO_ENABLED_WORKBOOK_CONTENT_TYPE: &str =
    "application/vnd.ms-excel.sheet.macroEnabled.main+xml";
const VBA_PROJECT_CONTENT_TYPE: &str = "application/vnd.ms-office.vbaProject";
const VBA_PROJECT_RELATIONSHIP_TYPE: &str =
    "http://schemas.microsoft.com/office/2006/relationships/vbaProject";
const VBA_PROJECT_PART_NAME: &str = "/xl/vbaProject.bin";
const VBA_PROJECT_ENTRY_NAME: &str = "vbaProject.bin";

const EXTERNAL_LINK_CONTENT_TYPE: &str =
    "application/vnd.openxmlformats-officedocument.spreadsheetml.externalLink+xml";
const EXTERNAL_LINK_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLink";
const EXTERNAL_LINK_TARGET_RELATIONSHIP_TYPE: &str =
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath";

/// The first `numFmtId` this crate ever assigns to a custom number format — ids below this are
/// reserved by Excel for its ~40 built-in formats (`0` = "General", `2` = `"0.00"`..); `164` is the
/// conventional first free id, the same convention `word-ooxml`'s custom document properties follow
/// for `pid` (starting at 2, past the 2 reserved slots).
const FIRST_CUSTOM_NUM_FMT_ID: u32 = 164;

impl Workbook {
    /// Writes this workbook out as a valid `.xlsx` package.
    ///
    /// Writes `[Content_Types].xml`, `_rels/.rels`, `xl/workbook.xml`,
    /// `xl/_rels/workbook.xml.rels`, one `xl/worksheets/sheetN.xml` per sheet, `xl/styles.xml`,
    /// `xl/sharedStrings.xml`, `docProps/core.xml`, `docProps/app.xml`, and `xl/theme/theme1.xml`
    /// (always the fixed default "Office" theme —; this crate has no notion of a customizable theme
    /// model, unlike `word-ooxml`'s `Theme`). `xl/calcChain.xml` is still never written — it only
    /// matters for formula dependency ordering, which this crate leaves to Excel to recompute on
    /// open. `xl/vbaProject.bin` and `xl/externalLinks/externalLinkN.xml` are written only when
    /// [`Workbook::vba_project`]/[`Workbook::external_links`] are non-empty.
    pub fn write_to<W: Write + Seek>(&self, writer: W) -> Result<W> {
        let mut package = Package::new();

        let workbook_content_type = if self.vba_project.is_some() {
            MACRO_ENABLED_WORKBOOK_CONTENT_TYPE
        } else {
            MAIN_WORKBOOK_CONTENT_TYPE
        };

        package.add_relationship(Relationship {
            id: "rId1".to_string(),
            rel_type: MAIN_WORKBOOK_RELATIONSHIP_TYPE.to_string(),
            target: MAIN_WORKBOOK_ENTRY_NAME.to_string(),
            target_mode: TargetMode::Internal,
        });
        package.add_relationship(Relationship {
            id: "rId2".to_string(),
            rel_type: CORE_PROPERTIES_RELATIONSHIP_TYPE.to_string(),
            target: CORE_PROPERTIES_ENTRY_NAME.to_string(),
            target_mode: TargetMode::Internal,
        });
        package.add_relationship(Relationship {
            id: "rId3".to_string(),
            rel_type: EXTENDED_PROPERTIES_RELATIONSHIP_TYPE.to_string(),
            target: EXTENDED_PROPERTIES_ENTRY_NAME.to_string(),
            target_mode: TargetMode::Internal,
        });
        // `docProps/custom.xml` is a genuinely optional part (see its constants' doc comment above)
        // — its package relationship is only added when there's actually something to write, unlike
        // `rId1`/`rId2`/`rId3` above which are unconditional. The point 56, mirrors `word-ooxml`'s
        // own posture exactly.
        if !self.properties.custom_properties.is_empty() {
            package.add_relationship(Relationship {
                id: "rId4".to_string(),
                rel_type: CUSTOM_PROPERTIES_RELATIONSHIP_TYPE.to_string(),
                target: CUSTOM_PROPERTIES_ENTRY_NAME.to_string(),
                target_mode: TargetMode::Internal,
            });
        }

        let shared_strings = collect_shared_strings(self);
        let cell_formats = collect_cell_formats(self);
        let dxfs = collect_dxfs(self);

        // `xl/workbook.xml`'s own relationships: one per sheet (in order), then styles, then shared
        // strings, then theme, then (optionally) external links and a VBA project — mirrors real
        // Excel output's id ordering closely enough without trying to match it exactly (Excel
        // itself doesn't guarantee a fixed id assignment order either).
        let mut workbook_relationships = Relationships::new();
        let sheet_relationship_ids: Vec<String> = (0..self.sheets.len())
            .map(|index| format!("rId{}", index + 1))
            .collect();
        for (index, relationship_id) in sheet_relationship_ids.iter().enumerate() {
            workbook_relationships.add(Relationship {
                id: relationship_id.clone(),
                rel_type: WORKSHEET_RELATIONSHIP_TYPE.to_string(),
                target: format!("worksheets/sheet{}.xml", index + 1),
                target_mode: TargetMode::Internal,
            });
        }
        let mut next_workbook_relationship_id = self.sheets.len() + 1;

        let styles_relationship_id = format!("rId{next_workbook_relationship_id}");
        next_workbook_relationship_id += 1;
        workbook_relationships.add(Relationship {
            id: styles_relationship_id,
            rel_type: STYLES_RELATIONSHIP_TYPE.to_string(),
            target: STYLES_ENTRY_NAME.to_string(),
            target_mode: TargetMode::Internal,
        });
        let shared_strings_relationship_id = format!("rId{next_workbook_relationship_id}");
        next_workbook_relationship_id += 1;
        workbook_relationships.add(Relationship {
            id: shared_strings_relationship_id,
            rel_type: SHARED_STRINGS_RELATIONSHIP_TYPE.to_string(),
            target: SHARED_STRINGS_ENTRY_NAME.to_string(),
            target_mode: TargetMode::Internal,
        });
        let theme_relationship_id = format!("rId{next_workbook_relationship_id}");
        next_workbook_relationship_id += 1;
        workbook_relationships.add(Relationship {
            id: theme_relationship_id,
            rel_type: THEME_RELATIONSHIP_TYPE.to_string(),
            target: THEME_ENTRY_NAME.to_string(),
            target_mode: TargetMode::Internal,
        });

        let mut external_link_relationship_ids: Vec<String> = Vec::new();
        for (index, _link) in self.external_links.iter().enumerate() {
            let id = format!("rId{next_workbook_relationship_id}");
            next_workbook_relationship_id += 1;
            workbook_relationships.add(Relationship {
                id: id.clone(),
                rel_type: EXTERNAL_LINK_RELATIONSHIP_TYPE.to_string(),
                target: format!("externalLinks/externalLink{}.xml", index + 1),
                target_mode: TargetMode::Internal,
            });
            external_link_relationship_ids.push(id);
        }

        if self.vba_project.is_some() {
            let id = format!("rId{next_workbook_relationship_id}");
            next_workbook_relationship_id += 1;
            workbook_relationships.add(Relationship {
                id,
                rel_type: VBA_PROJECT_RELATIONSHIP_TYPE.to_string(),
                target: VBA_PROJECT_ENTRY_NAME.to_string(),
                target_mode: TargetMode::Internal,
            });
        }
        let _ = next_workbook_relationship_id; // last write not read further, avoids an unused-assignment warning

        // `_xlnm.Print_Area`/`_xlnm.Print_Titles` are, despite living on `Sheet.print_settings`,
        // actually workbook-level `<definedNames>` entries scoped to their sheet — synthesized here
        // and appended after the caller's own `self.defined_names`, see `PrintSettings`'s doc
        // comment.
        let mut all_defined_names = self.defined_names.clone();
        all_defined_names.extend(synthesized_print_defined_names(self));

        let workbook_xml = to_workbook_xml(
            self,
            &sheet_relationship_ids,
            &all_defined_names,
            &external_link_relationship_ids,
        )?;
        let mut workbook_part =
            Part::new(MAIN_WORKBOOK_PART_NAME, workbook_content_type, workbook_xml);
        workbook_part.relationships = workbook_relationships;
        package.add_part(workbook_part);

        package.add_part(Part::new(
            THEME_PART_NAME,
            THEME_CONTENT_TYPE,
            to_theme_xml(),
        ));

        if let Some(vba_project) = &self.vba_project {
            package.add_part(Part::new(
                VBA_PROJECT_PART_NAME,
                VBA_PROJECT_CONTENT_TYPE,
                vba_project.clone(),
            ));
        }

        for (index, link) in self.external_links.iter().enumerate() {
            let mut link_relationships = Relationships::new();
            link_relationships.add(Relationship {
                id: "rId1".to_string(),
                rel_type: EXTERNAL_LINK_TARGET_RELATIONSHIP_TYPE.to_string(),
                target: link.target.clone(),
                target_mode: TargetMode::External,
            });
            let mut link_part = Part::new(
                format!("/xl/externalLinks/externalLink{}.xml", index + 1),
                EXTERNAL_LINK_CONTENT_TYPE,
                to_external_link_xml(link)?,
            );
            link_part.relationships = link_relationships;
            package.add_part(link_part);
        }

        // `xl/media/imageN.<ext>` and `xl/charts/chartN.xml` are numbered globally across the whole
        // workbook (not per-sheet, unlike `xl/tables/tableN.xml`), matching how real Excel itself
        // shares a single `xl/media` folder and a single chart-numbering sequence across every
        // sheet's drawing.
        let mut next_media_index = 1usize;
        let mut next_chart_index = 1usize;

        for (index, sheet) in self.sheets.iter().enumerate() {
            // Each worksheet's own `.rels` (hyperlinks/table/legacy comment drawing) is built
            // alongside its XML, since the `r:id`s embedded in the XML must match exactly — see
            // `WorksheetRelationshipIds`'s doc comment.
            let mut sheet_relationships = Relationships::new();
            let mut next_sheet_relationship_id = 1usize;

            let hyperlink_ids: Vec<Option<String>> = sheet
                .hyperlinks
                .iter()
                .map(|hyperlink| match &hyperlink.target {
                    HyperlinkTarget::External(url) => {
                        let id = format!("rId{next_sheet_relationship_id}");
                        next_sheet_relationship_id += 1;
                        sheet_relationships.add(Relationship {
                            id: id.clone(),
                            rel_type: HYPERLINK_RELATIONSHIP_TYPE.to_string(),
                            target: url.clone(),
                            target_mode: TargetMode::External,
                        });
                        Some(id)
                    }
                    HyperlinkTarget::Internal(_) => None,
                })
                .collect();

            let table_id = if sheet.table.is_some() {
                let id = format!("rId{next_sheet_relationship_id}");
                next_sheet_relationship_id += 1;
                sheet_relationships.add(Relationship {
                    id: id.clone(),
                    rel_type: TABLE_RELATIONSHIP_TYPE.to_string(),
                    target: format!("../tables/table{}.xml", index + 1),
                    target_mode: TargetMode::Internal,
                });
                Some(id)
            } else {
                None
            };

            let legacy_drawing_id = if !sheet.comments.is_empty() {
                let id = format!("rId{next_sheet_relationship_id}");
                next_sheet_relationship_id += 1;
                sheet_relationships.add(Relationship {
                    id: id.clone(),
                    rel_type: VML_DRAWING_RELATIONSHIP_TYPE.to_string(),
                    target: format!("../drawings/vmlDrawing{}.vml", index + 1),
                    target_mode: TargetMode::Internal,
                });
                // `xl/commentsN.xml` is referenced from the *workbook* relationship-wise the same
                // way a worksheet is (a plain internal relationship declared on the worksheet
                // part), even though the comments themselves are conceptually "for" this sheet —
                // matches how real Excel wires the two together.
                let comments_id = format!("rId{next_sheet_relationship_id}");
                sheet_relationships.add(Relationship {
                    id: comments_id,
                    rel_type: COMMENTS_RELATIONSHIP_TYPE.to_string(),
                    target: format!("../comments{}.xml", index + 1),
                    target_mode: TargetMode::Internal,
                });
                Some(id)
            } else {
                None
            };

            // the sheet's own drawing part (`xl/drawings/drawingN.xml`), if any. Its relationship
            // id is resolved here (alongside the others above) so it can be embedded in `<drawing
            // r:id="..">`, but the drawing part itself — and the image/chart parts it in turn
            // references — is only built and added to the package further down, once this
            // worksheet's own XML/part has been created, mirroring the existing
            // table/legacy-drawing ordering.
            let drawing_id = if sheet.drawing.is_some() {
                // No `+= 1` here: this is the last id computed for this sheet's relationships in
                // this loop iteration, so the incremented value would never be read before
                // `next_sheet_relationship_id` gets reset to `1` at the top of the next iteration —
                // `cargo build` correctly flagged the previous unconditional increment as dead.
                let id = format!("rId{next_sheet_relationship_id}");
                sheet_relationships.add(Relationship {
                    id: id.clone(),
                    rel_type: DRAWING_RELATIONSHIP_TYPE.to_string(),
                    target: format!("../drawings/drawing{}.xml", index + 1),
                    target_mode: TargetMode::Internal,
                });
                Some(id)
            } else {
                None
            };

            let relationship_ids = WorksheetRelationshipIds {
                hyperlinks: hyperlink_ids,
                table: table_id,
                legacy_drawing: legacy_drawing_id,
                drawing: drawing_id,
            };

            let worksheet_xml = to_worksheet_xml(
                sheet,
                &shared_strings,
                &cell_formats,
                &dxfs,
                &relationship_ids,
            )?;
            let mut worksheet_part = Part::new(
                format!("/xl/worksheets/sheet{}.xml", index + 1),
                WORKSHEET_CONTENT_TYPE,
                worksheet_xml,
            );
            worksheet_part.relationships = sheet_relationships;
            package.add_part(worksheet_part);

            if let Some(sheet_drawing) = &sheet.drawing {
                let mut drawing_relationships = Relationships::new();
                let drawing_xml = to_drawing_xml(
                    sheet_drawing,
                    &mut drawing_relationships,
                    &mut next_media_index,
                    &mut next_chart_index,
                    &mut package,
                )?;
                let mut drawing_part = Part::new(
                    format!("/xl/drawings/drawing{}.xml", index + 1),
                    DRAWING_CONTENT_TYPE,
                    drawing_xml,
                );
                drawing_part.relationships = drawing_relationships;
                package.add_part(drawing_part);
            }

            if let Some(table) = &sheet.table {
                package.add_part(Part::new(
                    format!("/xl/tables/table{}.xml", index + 1),
                    TABLE_CONTENT_TYPE,
                    to_table_xml(table, index + 1)?,
                ));
            }

            if !sheet.comments.is_empty() {
                package.add_part(Part::new(
                    format!("/xl/comments{}.xml", index + 1),
                    COMMENTS_CONTENT_TYPE,
                    to_comments_xml(&sheet.comments)?,
                ));
                package.add_part(Part::new(
                    format!("/xl/drawings/vmlDrawing{}.vml", index + 1),
                    VML_DRAWING_CONTENT_TYPE,
                    to_comments_vml(&sheet.comments),
                ));
            }
        }

        package.add_part(Part::new(
            STYLES_PART_NAME,
            STYLES_CONTENT_TYPE,
            to_styles_xml(
                &cell_formats,
                &dxfs,
                &self.table_styles,
                &self.named_cell_styles,
            )?,
        ));
        package.add_part(Part::new(
            SHARED_STRINGS_PART_NAME,
            SHARED_STRINGS_CONTENT_TYPE,
            to_shared_strings_xml(&shared_strings)?,
        ));
        package.add_part(Part::new(
            CORE_PROPERTIES_PART_NAME,
            CORE_PROPERTIES_CONTENT_TYPE,
            to_core_properties_xml(&self.properties)?,
        ));
        package.add_part(Part::new(
            EXTENDED_PROPERTIES_PART_NAME,
            EXTENDED_PROPERTIES_CONTENT_TYPE,
            to_extended_properties_xml(&self.properties)?,
        ));
        if !self.properties.custom_properties.is_empty() {
            package.add_part(Part::new(
                CUSTOM_PROPERTIES_PART_NAME,
                CUSTOM_PROPERTIES_CONTENT_TYPE,
                to_custom_properties_xml(&self.properties.custom_properties)?,
            ));
        }

        Ok(package.write_to(writer)?)
    }
}

/// One entry of a workbook's shared strings table — either a plain text value (`CellValue::Text`)
/// or a rich-text value with per-run formatting (`CellValue::RichText`). Both share the same index
/// space (a cell's `<v>` simply indexes into `xl/sharedStrings.xml`'s flat `<si>` list regardless
/// of which form each entry takes), so this crate models them as one `Vec` of this enum rather than
/// two separate collections.
#[derive(Clone, PartialEq)]
enum SharedStringEntry {
    Plain(String),
    Rich(Vec<RichTextRun>),
}

/// The distinct text/rich-text values used anywhere in a workbook's cells (`xl/sharedStrings.xml`,
/// `CT_Sst`), collected in first-appearance order (matching real Excel output, which also assigns
/// indices in the order strings are first encountered while saving) — `plain_indices` maps each
/// distinct plain string to its assigned index for `write_cell` to look up (a `HashMap` fast path,
/// since plain text is the overwhelmingly common case); a `CellValue::RichText`'s index is instead
/// resolved by linear scan through `entries` (see `SharedStrings::index_of_rich`) — rich text is
/// rare enough in practice that this isn't worth a second `HashMap` (`Vec<RichTextRun>` isn't
/// `Hash` either, since `RichTextRun::font_size` is an `f64`). `total_count` is the total number of
/// text/rich-text cells across the whole workbook, counting repeats (`CT_Sst/@count`, distinct from
/// `@uniqueCount`, which is simply `entries.len()`).
struct SharedStrings {
    entries: Vec<SharedStringEntry>,
    plain_indices: HashMap<String, usize>,
    total_count: usize,
}

impl SharedStrings {
    fn index_of_rich(&self, runs: &[RichTextRun]) -> Option<usize> {
        self.entries.iter().position(
            |entry| matches!(entry, SharedStringEntry::Rich(existing) if existing == runs),
        )
    }
}

fn collect_shared_strings(workbook: &Workbook) -> SharedStrings {
    let mut entries = Vec::new();
    let mut plain_indices = HashMap::new();
    let mut total_count = 0usize;

    for sheet in &workbook.sheets {
        for row in &sheet.rows {
            for cell in &row.cells {
                match &cell.value {
                    CellValue::Text(text) => {
                        total_count += 1;
                        if !plain_indices.contains_key(text) {
                            plain_indices.insert(text.clone(), entries.len());
                            entries.push(SharedStringEntry::Plain(text.clone()));
                        }
                    }
                    CellValue::RichText(runs) => {
                        total_count += 1;
                        let already_present =
                            entries.iter().any(|entry| matches!(entry, SharedStringEntry::Rich(existing) if existing == runs));
                        if !already_present {
                            entries.push(SharedStringEntry::Rich(runs.clone()));
                        }
                    }
                    _ => {}
                }
            }
        }
    }

    SharedStrings {
        entries,
        plain_indices,
        total_count,
    }
}

/// The distinct [`CellFormat`]s used anywhere in a workbook's cells, collected in first-appearance
/// order — exactly like `collect_shared_strings`, but for cell styles rather than text. A cell's
/// assigned `cellXfs` index is `1 + position_in_this_vec` (index 0 is always reserved for "no
/// format", the default style) — see `write_cell`'s `style_index` lookup and `build_styles`, which
/// turns this flat list into the `numFmts`/`fonts`/`fills`/`cellXfs` collections `xl/styles.xml`
/// actually needs.
fn collect_cell_formats(workbook: &Workbook) -> Vec<CellFormat> {
    let mut formats: Vec<CellFormat> = Vec::new();

    for sheet in &workbook.sheets {
        for row in &sheet.rows {
            if let Some(format) = &row.default_format {
                if !formats.contains(format) {
                    formats.push(format.clone());
                }
            }
            for cell in &row.cells {
                if let Some(format) = &cell.format {
                    if !formats.contains(format) {
                        formats.push(format.clone());
                    }
                }
            }
        }
        // `<col style="n">` — point 49, same shared `cellXfs` index space as a cell's own `s`
        // attribute.
        for setting in &sheet.column_settings {
            if let Some(format) = &setting.style {
                if !formats.contains(format) {
                    formats.push(format.clone());
                }
            }
        }
    }

    formats
}

/// The distinct [`CellFormat`]s used as a [`ConditionalFormattingRule`]'s `format` anywhere in a
/// workbook, collected in first-appearance order — same dedup principle as `collect_cell_formats`,
/// but a separate index space: a rule's `dxfId` is its position in this `Vec` (no "index 0
/// reserved" convention here, since every conditional formatting rule always carries a format —
/// there's no "no format" case to reserve for). Also collects each [`TableStyleElement::format`]
/// from `workbook.table_styles` into this same shared index space — point 50
/// (`ExcelTable::table_style_name` referencing a [`TableStyle`], each of whose elements' `dxfId`
/// resolves against this very `<dxfs>` collection, exactly like a conditional formatting rule's own
/// `dxfId`).
fn collect_dxfs(workbook: &Workbook) -> Vec<CellFormat> {
    let mut dxfs: Vec<CellFormat> = Vec::new();

    for sheet in &workbook.sheets {
        for rule in &sheet.conditional_formatting_rules {
            if !dxfs.contains(&rule.format) {
                dxfs.push(rule.format.clone());
            }
        }
    }

    for table_style in &workbook.table_styles {
        for element in &table_style.elements {
            if !dxfs.contains(&element.format) {
                dxfs.push(element.format.clone());
            }
        }
    }

    dxfs
}

/// Serializes `xl/workbook.xml`'s `<sheets>` list — one `<sheet>` per entry in `workbook.sheets`,
/// in order, with a 1-based `sheetId` (this crate's own convention, not schema-mandated —
/// `ST_SheetId` just needs to be unique, ascending-from-1 is simplest) and the matching `r:id` from
/// `sheet_relationship_ids` (resolved against `xl/_rels/workbook.xml.rels` by the caller).
fn to_workbook_xml(
    workbook: &Workbook,
    sheet_relationship_ids: &[String],
    defined_names: &[DefinedName],
    external_link_relationship_ids: &[String],
) -> Result<Vec<u8>> {
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let mut root = BytesStart::new("workbook");
    root.push_attribute(("xmlns", SPREADSHEETML_NAMESPACE));
    root.push_attribute(("xmlns:r", RELATIONSHIPS_NAMESPACE));
    writer.write_event(Event::Start(root))?;

    // `CT_Workbook`'s sequence: `fileVersion?`, `fileSharing?`, `workbookPr?`
    // (`fileVersion`/`workbookPr` not written by this crate), `workbookProtection?`, `bookViews?`,
    // `sheets` — so write-protection, structure protection, and the active-tab view all come
    // *before* `<sheets>`, and (`fileSharing`, which comes first of the three).
    if let Some(write_protection) = &workbook.write_protection {
        write_file_sharing(&mut writer, write_protection)?;
    }

    if let Some(protection) = &workbook.protection {
        write_workbook_protection(&mut writer, protection)?;
    }

    if workbook.active_tab.is_some() {
        writer.write_event(Event::Start(BytesStart::new("bookViews")))?;
        let mut workbook_view = BytesStart::new("workbookView");
        if let Some(active_tab) = workbook.active_tab {
            let active_tab_text = active_tab.to_string();
            workbook_view.push_attribute(("activeTab", active_tab_text.as_str()));
        }
        writer.write_event(Event::Empty(workbook_view))?;
        writer.write_event(Event::End(BytesEnd::new("bookViews")))?;
    }

    writer.write_event(Event::Start(BytesStart::new("sheets")))?;
    for (index, sheet) in workbook.sheets.iter().enumerate() {
        let sheet_id = (index + 1).to_string();
        let mut sheet_start = BytesStart::new("sheet");
        sheet_start.push_attribute(("name", sheet.name.as_str()));
        sheet_start.push_attribute(("sheetId", sheet_id.as_str()));
        // `state="hidden"/"veryHidden"` — extended to the three-state form.
        match sheet.visibility {
            SheetVisibility::Visible => {}
            SheetVisibility::Hidden => sheet_start.push_attribute(("state", "hidden")),
            SheetVisibility::VeryHidden => sheet_start.push_attribute(("state", "veryHidden")),
        }
        sheet_start.push_attribute(("r:id", sheet_relationship_ids[index].as_str()));
        writer.write_event(Event::Empty(sheet_start))?;
    }
    writer.write_event(Event::End(BytesEnd::new("sheets")))?;

    // `CT_Workbook`'s sequence puts `externalReferences` right after `sheets` (and
    // `functionGroups`, not written by this crate).
    if !workbook.external_links.is_empty() {
        writer.write_event(Event::Start(BytesStart::new("externalReferences")))?;
        for relationship_id in external_link_relationship_ids {
            let mut external_reference = BytesStart::new("externalReference");
            external_reference.push_attribute(("r:id", relationship_id.as_str()));
            writer.write_event(Event::Empty(external_reference))?;
        }
        writer.write_event(Event::End(BytesEnd::new("externalReferences")))?;
    }

    // `definedNames` follows `externalReferences` and comes before `calcPr` (not written either) —
    // so in this crate's own output, `<definedNames>` is simply the last child of `<workbook>` when
    // present. `defined_names` here is the caller's own `workbook.defined_names` plus any
    // `_xlnm.Print_Area`/`_xlnm.Print_Titles` names synthesized from each sheet's `print_settings`
    // (see `synthesized_print_defined_names`) — merged together before this function is ever
    // called.
    if !defined_names.is_empty() {
        write_defined_names(&mut writer, defined_names)?;
    }

    // `calcPr` is `CT_Workbook`'s very last modeled child.
    if let Some(calculation) = &workbook.calculation {
        write_calculation_properties(&mut writer, calculation)?;
    }

    writer.write_event(Event::End(BytesEnd::new("workbook")))?;

    Ok(writer.into_inner())
}

/// Serializes `<calcPr>` (`CT_CalcPr`) from a workbook's [`CalculationProperties`].
fn write_calculation_properties<W: Write>(
    writer: &mut Writer<W>,
    calculation: &CalculationProperties,
) -> Result<()> {
    let mut start = BytesStart::new("calcPr");
    if let Some(mode) = calculation.mode {
        start.push_attribute(("calcMode", calculation_mode_str(mode)));
    }
    if calculation.iterate {
        start.push_attribute(("iterate", "1"));
    }
    writer.write_event(Event::Empty(start))?;
    Ok(())
}

fn calculation_mode_str(mode: CalculationMode) -> &'static str {
    match mode {
        CalculationMode::Automatic => "auto",
        CalculationMode::AutomaticExceptTables => "autoNoTable",
        CalculationMode::Manual => "manual",
    }
}

/// Serializes `<workbookProtection>` (`CT_WorkbookProtection`) from a [`WorkbookProtection`]. Uses
/// the same legacy password hash as `write_sheet_protection`, see `legacy_password_hash`'s doc
/// comment.
fn write_workbook_protection<W: Write>(
    writer: &mut Writer<W>,
    protection: &WorkbookProtection,
) -> Result<()> {
    let password_text = protection.password.as_deref().map(legacy_password_hash_hex);
    let mut start = BytesStart::new("workbookProtection");
    if protection.lock_structure {
        start.push_attribute(("lockStructure", "1"));
    }
    if protection.lock_windows {
        start.push_attribute(("lockWindows", "1"));
    }
    if let Some(password_text) = &password_text {
        start.push_attribute(("workbookPassword", password_text.as_str()));
    }
    writer.write_event(Event::Empty(start))?;
    Ok(())
}

/// Serializes `<fileSharing>` (`CT_FileSharing`) from a [`WriteProtection`]. Uses the same legacy
/// password hash as `write_workbook_protection`/`write_sheet_protection`, see
/// `legacy_password_hash`'s doc comment.
fn write_file_sharing<W: Write>(
    writer: &mut Writer<W>,
    write_protection: &WriteProtection,
) -> Result<()> {
    let password_text = write_protection
        .password
        .as_deref()
        .map(legacy_password_hash_hex);
    let mut start = BytesStart::new("fileSharing");
    if write_protection.read_only_recommended {
        start.push_attribute(("readOnlyRecommended", "1"));
    }
    if let Some(user_name) = &write_protection.user_name {
        start.push_attribute(("userName", user_name.as_str()));
    }
    if let Some(password_text) = &password_text {
        start.push_attribute(("reservationPassword", password_text.as_str()));
    }
    writer.write_event(Event::Empty(start))?;
    Ok(())
}

/// Synthesizes `_xlnm.Print_Area`/`_xlnm.Print_Titles` [`DefinedName`]s from every sheet's
/// [`PrintSettings`] — see `PrintSettings`'s doc comment for why these are workbook-level defined
/// names rather than a worksheet XML attribute.
fn synthesized_print_defined_names(workbook: &Workbook) -> Vec<DefinedName> {
    let mut names = Vec::new();

    for (index, sheet) in workbook.sheets.iter().enumerate() {
        if let Some(print_area) = &sheet.print_settings.print_area {
            let refers_to = qualify_sheet_reference(&sheet.name, print_area);
            names.push(
                DefinedName::new("_xlnm.Print_Area", refers_to)
                    .with_local_sheet_id(index)
                    .with_hidden(true),
            );
        }

        let repeat_rows = sheet.print_settings.repeat_rows.as_deref();
        let repeat_columns = sheet.print_settings.repeat_columns.as_deref();
        if repeat_rows.is_some() || repeat_columns.is_some() {
            let mut parts = Vec::new();
            if let Some(columns) = repeat_columns {
                parts.push(qualify_sheet_reference(&sheet.name, columns));
            }
            if let Some(rows) = repeat_rows {
                parts.push(qualify_sheet_reference(&sheet.name, rows));
            }
            names.push(
                DefinedName::new("_xlnm.Print_Titles", parts.join(","))
                    .with_local_sheet_id(index)
                    .with_hidden(true),
            );
        }
    }

    names
}

/// Prefixes a range reference with its sheet name (e.g. `"Feuil1"` + `"$A$1:$F$30"` →
/// `"Feuil1!$A$1:$F$30"`) — the shape `_xlnm.Print_Area`/ `_xlnm.Print_Titles` need, since (unlike
/// a plain `DefinedName` created through `DefinedName::new`)
/// `PrintSettings.print_area`/`repeat_rows`/ `repeat_columns` are just the bare range, not the
/// caller's responsibility to already sheet-qualify. A sheet name containing a space or one of
/// SpreadsheetML's reserved characters would need wrapping in single quotes for the result to be a
/// valid reference — not handled here (out of scope, same posture as `Sheet.name`'s own doc comment
/// about not validating Excel's naming rules).
fn qualify_sheet_reference(sheet_name: &str, range: &str) -> String {
    format!("{sheet_name}!{range}")
}

/// Serializes `<definedNames>` (`CT_DefinedNames`) from a workbook's [`DefinedName`]s. Unlike a
/// conditional formatting rule's `<formula>` or a data validation rule's `<formula1>`, a
/// `<definedName>` has no dedicated child element for its reference/formula — `CT_DefinedName` has
/// simple content (`ST_Formula`), so the reference text is written directly as the element's own
/// text.
fn write_defined_names<W: Write>(
    writer: &mut Writer<W>,
    defined_names: &[DefinedName],
) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new("definedNames")))?;

    for defined_name in defined_names {
        let mut start = BytesStart::new("definedName");
        start.push_attribute(("name", defined_name.name.as_str()));
        let local_sheet_id_text = defined_name.local_sheet_id.map(|id| id.to_string());
        if let Some(local_sheet_id_text) = &local_sheet_id_text {
            start.push_attribute(("localSheetId", local_sheet_id_text.as_str()));
        }
        if defined_name.hidden {
            start.push_attribute(("hidden", "1"));
        }
        if let Some(comment) = &defined_name.comment {
            start.push_attribute(("comment", comment.as_str()));
        }
        writer.write_event(Event::Start(start))?;
        writer.write_event(Event::Text(BytesText::new(&defined_name.refers_to)))?;
        writer.write_event(Event::End(BytesEnd::new("definedName")))?;
    }

    writer.write_event(Event::End(BytesEnd::new("definedNames")))?;

    Ok(())
}

/// Per-worksheet relationship ids resolved by `Workbook::write_to` before calling
/// `to_worksheet_xml` — hyperlinks/tables/legacy-comment-drawings each need an `r:id` embedded in
/// the worksheet's own XML that must match exactly the id used in that worksheet's own `.rels` part
/// (built alongside, see `write_to`'s per-sheet loop), so both have to be resolved together before
/// either is written.
struct WorksheetRelationshipIds {
    /// One entry per `Sheet.hyperlinks`, in order — `Some(rId)` for an external hyperlink (resolved
    /// against this worksheet's `.rels`), `None` for an internal one (no relationship needed, see
    /// `HyperlinkTarget::Internal`).
    hyperlinks: Vec<Option<String>>,
    /// The relationship id for this sheet's `xl/tables/tableN.xml`, if any.
    table: Option<String>,
    /// The relationship id for this sheet's legacy comment VML drawing, if any (`<legacyDrawing
    /// r:id="..">`).
    legacy_drawing: Option<String>,
    /// The relationship id for this sheet's DrawingML drawing part (embedded pictures/charts), if
    /// any (`<drawing r:id="..">`). Written *before* `legacy_drawing` in `to_worksheet_xml`,
    /// matching `CT_Worksheet`'s own schema sequence (`drawing?` comes before `legacyDrawing?`).
    drawing: Option<String>,
}

/// Serializes one sheet's `xl/worksheets/sheetN.xml` (`CT_Worksheet`). Only the child elements this
/// crate actually models are written, always in `CT_Worksheet`'s own schema sequence (per
/// ECMA-376/datypic.com): `sheetPr?`, `dimension`, `sheetViews?`, `cols?`, `sheetData`,
/// `sheetProtection?`, `protectedRanges?`, `autoFilter?`, `mergeCells?`, `conditionalFormatting*`,
/// `dataValidations?`, `hyperlinks?`, `pageSetup?`, `headerFooter?`, `ignoredErrors?`,
/// `legacyDrawing?`, `tableParts?` — everything else (`sheetCalcPr`, `dataConsolidate`,
/// `customSheetViews`, `phoneticPr`, `printOptions`, `pageMargins`, `drawing`..) is skipped, all
/// `minOccurs="0"` and either not modeled or not needed for a file that still opens cleanly (Excel
/// fills in its own defaults for anything omitted). `dimension` is the one exception written
/// unconditionally (even for an empty sheet, as `"A1"`) despite being schema-optional: every real
/// Excel- generated file always includes it, and its absence is what caused real Excel to reject
/// and strip a structured table on open — note `dimension` comes *after* `sheetPr`, not before (an
/// ordering bug in this fix's first version, also found and fixed during that same investigation).
/// A row's number and a cell's column letter are derived from their position (see
/// `Sheet.rows`/`Row.cells`'s own doc comments), not stored on the model.
fn to_worksheet_xml(
    sheet: &Sheet,
    shared_strings: &SharedStrings,
    cell_formats: &[CellFormat],
    dxfs: &[CellFormat],
    relationship_ids: &WorksheetRelationshipIds,
) -> Result<Vec<u8>> {
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let mut root = BytesStart::new("worksheet");
    root.push_attribute(("xmlns", SPREADSHEETML_NAMESPACE));
    root.push_attribute(("xmlns:r", RELATIONSHIPS_NAMESPACE));
    writer.write_event(Event::Start(root))?;

    // `CT_SheetPr`'s sequence is `tabColor?`, `outlinePr?`, `pageSetUpPr?` — (`pageSetUpPr`),
    // extended for `tabColor` and for `outlinePr`. `sheetPr` comes *before* `dimension` in
    // `CT_Worksheet`'s own schema sequence — writing `dimension` first is schema-invalid and is
    // what caused real Excel to reject any sheet that *also* writes a `sheetPr` (tab color and/or
    // fit-to-page), even though sheets with neither happened to still open fine (nothing came
    // before `dimension` for them, so the swap was invisible) — confirmed via bisection down to
    // sheet 38 alone (tab color only, no table) still failing after the table-specific fixes.
    let fits_to_page =
        sheet.print_settings.fit_to_width.is_some() || sheet.print_settings.fit_to_height.is_some();
    let has_outline_pr =
        sheet.outline_summary_below.is_some() || sheet.outline_summary_right.is_some();
    if fits_to_page || sheet.tab_color.is_some() || has_outline_pr {
        writer.write_event(Event::Start(BytesStart::new("sheetPr")))?;
        if let Some(tab_color) = &sheet.tab_color {
            let mut tab_color_start = BytesStart::new("tabColor");
            tab_color_start.push_attribute(("rgb", tab_color.as_str()));
            writer.write_event(Event::Empty(tab_color_start))?;
        }
        if has_outline_pr {
            let mut outline_pr = BytesStart::new("outlinePr");
            if let Some(summary_below) = sheet.outline_summary_below {
                outline_pr.push_attribute(("summaryBelow", if summary_below { "1" } else { "0" }));
            }
            if let Some(summary_right) = sheet.outline_summary_right {
                outline_pr.push_attribute(("summaryRight", if summary_right { "1" } else { "0" }));
            }
            writer.write_event(Event::Empty(outline_pr))?;
        }
        if fits_to_page {
            let mut page_setup_pr = BytesStart::new("pageSetUpPr");
            page_setup_pr.push_attribute(("fitToPage", "1"));
            writer.write_event(Event::Empty(page_setup_pr))?;
        }
        writer.write_event(Event::End(BytesEnd::new("sheetPr")))?;
    }

    let mut dimension = BytesStart::new("dimension");
    let dimension_ref = compute_dimension_ref(sheet);
    dimension.push_attribute(("ref", dimension_ref.as_str()));
    writer.write_event(Event::Empty(dimension))?;

    // (freeze panes)/29 (zoom), extended for `showGridLines`/
    // `showRowColHeaders`/`showZeros`/`rightToLeft` and `<selection>`.
    if sheet.freeze_panes.is_some()
        || sheet.zoom_scale.is_some()
        || sheet.show_grid_lines.is_some()
        || sheet.show_row_col_headers.is_some()
        || sheet.show_zeros.is_some()
        || sheet.right_to_left.is_some()
        || sheet.active_cell.is_some()
    {
        write_sheet_views(&mut writer, sheet)?;
    }

    // `CT_Worksheet`'s sequence puts `sheetFormatPr` right after `sheetViews`, before `cols`.
    // `defaultRowHeight` is written whenever `sheetFormatPr` is written at all (a fixed `15`
    // fallback when only `default_column_width` is set), matching real Excel's own always-present
    // convention for this one attribute — see `Sheet.default_row_height`'s doc comment.
    if sheet.default_column_width.is_some() || sheet.default_row_height.is_some() {
        let mut sheet_format_pr = BytesStart::new("sheetFormatPr");
        if let Some(width) = sheet.default_column_width {
            let width_text = width.to_string();
            sheet_format_pr.push_attribute(("defaultColWidth", width_text.as_str()));
        }
        let row_height_text = sheet.default_row_height.unwrap_or(15.0).to_string();
        sheet_format_pr.push_attribute(("defaultRowHeight", row_height_text.as_str()));
        writer.write_event(Event::Empty(sheet_format_pr))?;
    }

    if !sheet.column_settings.is_empty() {
        write_cols(&mut writer, &sheet.column_settings, cell_formats)?;
    }

    writer.write_event(Event::Start(BytesStart::new("sheetData")))?;
    for (row_index, row) in sheet.rows.iter().enumerate() {
        let row_number = row_index + 1;
        let row_number_text = row_number.to_string();
        let mut row_start = BytesStart::new("row");
        row_start.push_attribute(("r", row_number_text.as_str()));
        let height_text = row.height.map(|height| height.to_string());
        if let Some(height_text) = &height_text {
            row_start.push_attribute(("ht", height_text.as_str()));
            row_start.push_attribute(("customHeight", "1"));
        }
        if row.hidden {
            row_start.push_attribute(("hidden", "1"));
        }
        if row.outline_level != 0 {
            let outline_level_text = row.outline_level.to_string();
            row_start.push_attribute(("outlineLevel", outline_level_text.as_str()));
        }
        if row.collapsed {
            row_start.push_attribute(("collapsed", "1"));
        }
        // `<row s="n" customFormat="1">`, same shared `cellXfs` index space as a cell's own `s`
        // attribute. Always paired with `customFormat="1"` when written (see `Row.default_format`'s
        // doc comment).
        let row_style_text =
            style_index(&row.default_format, cell_formats).map(|index| index.to_string());
        if let Some(row_style_text) = &row_style_text {
            row_start.push_attribute(("s", row_style_text.as_str()));
            row_start.push_attribute(("customFormat", "1"));
        }
        writer.write_event(Event::Start(row_start))?;

        for (column_index, cell) in row.cells.iter().enumerate() {
            write_cell(
                &mut writer,
                column_index,
                row_number,
                cell,
                shared_strings,
                cell_formats,
            )?;
        }

        writer.write_event(Event::End(BytesEnd::new("row")))?;
    }
    writer.write_event(Event::End(BytesEnd::new("sheetData")))?;

    if let Some(protection) = &sheet.protection {
        write_sheet_protection(&mut writer, protection)?;
    }

    // `CT_Worksheet`'s sequence puts `protectedRanges` right after `sheetProtection` and before
    // `scenarios` — point 51 (confirmed via `sml.xsd`, previously not written by this crate at
    // all).
    if !sheet.protected_ranges.is_empty() {
        write_protected_ranges(&mut writer, &sheet.protected_ranges)?;
    }

    // `CT_Worksheet`'s sequence puts `scenarios` right after `sheetProtection`/`protectedRanges`
    // and before `autoFilter`.
    if !sheet.scenarios.is_empty() {
        write_scenarios(&mut writer, &sheet.scenarios)?;
    }

    if let Some(range) = &sheet.auto_filter_range {
        // `CT_AutoFilter`'s own sequence puts `sortState` right after `filterColumn*`, nested
        // *inside* `<autoFilter>` (per `sml.xsd`) — distinct from a table's own `<sortState>`,
        // which is a sibling of `<autoFilter>` (see `to_table_xml`).
        if sheet.auto_filter_columns.is_empty() && sheet.sort_state.is_none() {
            let mut auto_filter = BytesStart::new("autoFilter");
            auto_filter.push_attribute(("ref", range.as_str()));
            writer.write_event(Event::Empty(auto_filter))?;
        } else {
            let mut auto_filter = BytesStart::new("autoFilter");
            auto_filter.push_attribute(("ref", range.as_str()));
            writer.write_event(Event::Start(auto_filter))?;
            write_filter_columns(&mut writer, &sheet.auto_filter_columns)?;
            if let Some(sort_state) = &sheet.sort_state {
                write_sort_state(&mut writer, sort_state)?;
            }
            writer.write_event(Event::End(BytesEnd::new("autoFilter")))?;
        }
    }

    if !sheet.merged_ranges.is_empty() {
        let count_text = sheet.merged_ranges.len().to_string();
        let mut merge_cells_start = BytesStart::new("mergeCells");
        merge_cells_start.push_attribute(("count", count_text.as_str()));
        writer.write_event(Event::Start(merge_cells_start))?;
        for range in &sheet.merged_ranges {
            let mut merge_cell = BytesStart::new("mergeCell");
            merge_cell.push_attribute(("ref", range.as_str()));
            writer.write_event(Event::Empty(merge_cell))?;
        }
        writer.write_event(Event::End(BytesEnd::new("mergeCells")))?;
    }

    for (index, rule) in sheet.conditional_formatting_rules.iter().enumerate() {
        write_conditional_formatting(&mut writer, index, rule, dxfs)?;
    }
    if !sheet.data_validation_rules.is_empty() {
        write_data_validations(&mut writer, &sheet.data_validation_rules)?;
    }

    if !sheet.hyperlinks.is_empty() {
        write_hyperlinks(&mut writer, &sheet.hyperlinks, &relationship_ids.hyperlinks)?;
    }

    if sheet.print_settings.orientation.is_some()
        || sheet.print_settings.fit_to_width.is_some()
        || sheet.print_settings.fit_to_height.is_some()
        || sheet.print_settings.scale.is_some()
    {
        write_page_setup(&mut writer, &sheet.print_settings)?;
    }

    if sheet.print_settings.header.is_some()
        || sheet.print_settings.footer.is_some()
        || sheet.print_settings.header_first.is_some()
        || sheet.print_settings.footer_first.is_some()
        || sheet.print_settings.header_even.is_some()
        || sheet.print_settings.footer_even.is_some()
    {
        write_header_footer(&mut writer, &sheet.print_settings)?;
    }

    // `CT_Worksheet`'s sequence puts `rowBreaks`/`colBreaks` right after `headerFooter`.
    if !sheet.row_breaks.is_empty() {
        write_page_breaks(&mut writer, "rowBreaks", &sheet.row_breaks, 16383)?;
    }
    if !sheet.column_breaks.is_empty() {
        write_page_breaks(&mut writer, "colBreaks", &sheet.column_breaks, 1048575)?;
    }

    // `CT_Worksheet`'s sequence puts `ignoredErrors` right after `cellWatches` (not modeled by this
    // crate) and before `smartTags`/ `drawing`/`legacyDrawing`/`tableParts`.
    if !sheet.ignored_errors.is_empty() {
        write_ignored_errors(&mut writer, &sheet.ignored_errors)?;
    }

    // `CT_Worksheet`'s sequence puts `drawing?` immediately before `legacyDrawing?`
    if let Some(drawing_id) = &relationship_ids.drawing {
        let mut drawing_start = BytesStart::new("drawing");
        drawing_start.push_attribute(("r:id", drawing_id.as_str()));
        writer.write_event(Event::Empty(drawing_start))?;
    }

    if let Some(legacy_drawing_id) = &relationship_ids.legacy_drawing {
        let mut legacy_drawing = BytesStart::new("legacyDrawing");
        legacy_drawing.push_attribute(("r:id", legacy_drawing_id.as_str()));
        writer.write_event(Event::Empty(legacy_drawing))?;
    }

    if let (Some(_table), Some(table_id)) = (&sheet.table, &relationship_ids.table) {
        // `count="1"` — this crate only ever writes at most one table per sheet, see
        // `Sheet.table`'s doc comment.
        let mut table_parts_start = BytesStart::new("tableParts");
        table_parts_start.push_attribute(("count", "1"));
        writer.write_event(Event::Start(table_parts_start))?;
        let mut table_part = BytesStart::new("tablePart");
        table_part.push_attribute(("r:id", table_id.as_str()));
        writer.write_event(Event::Empty(table_part))?;
        writer.write_event(Event::End(BytesEnd::new("tableParts")))?;
    }

    writer.write_event(Event::End(BytesEnd::new("worksheet")))?;

    Ok(writer.into_inner())
}

/// Serializes `<sheetViews><sheetView.><pane./><selection./></sheetView></sheetViews>` from a
/// sheet's view-related settings (freeze panes), extended for `zoomScale` and for
/// `showGridLines`/`showRowColHeaders`/`showZeros`/`rightToLeft` and `<selection>`. `activePane`
/// follows real Excel's own convention: `"bottomRight"` when both rows and columns are frozen,
/// `"topRight"` for columns only, `"bottomLeft"` for rows only (confirmed via `CT_Pane`'s
/// `ST_PaneType`/`ST_Pane` semantics — the active pane is always the one below/right of the
/// freeze). `topLeftCell` is the first visible (scrollable) cell, one row/column past the frozen
/// region. `CT_SheetView`'s child sequence is `pane?`, `selection*`. — `<selection>` always comes
/// after `<pane>` when both are present.
fn write_sheet_views<W: Write>(writer: &mut Writer<W>, sheet: &Sheet) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new("sheetViews")))?;
    let mut sheet_view = BytesStart::new("sheetView");
    if let Some(show_grid_lines) = sheet.show_grid_lines {
        sheet_view.push_attribute(("showGridLines", if show_grid_lines { "1" } else { "0" }));
    }
    if let Some(show_row_col_headers) = sheet.show_row_col_headers {
        sheet_view.push_attribute((
            "showRowColHeaders",
            if show_row_col_headers { "1" } else { "0" },
        ));
    }
    if let Some(show_zeros) = sheet.show_zeros {
        sheet_view.push_attribute(("showZeros", if show_zeros { "1" } else { "0" }));
    }
    if let Some(right_to_left) = sheet.right_to_left {
        sheet_view.push_attribute(("rightToLeft", if right_to_left { "1" } else { "0" }));
    }
    let zoom_scale_text = sheet.zoom_scale.map(|value| value.to_string());
    if let Some(zoom_scale_text) = &zoom_scale_text {
        sheet_view.push_attribute(("zoomScale", zoom_scale_text.as_str()));
    }
    sheet_view.push_attribute(("workbookViewId", "0"));

    if sheet.freeze_panes.is_none() && sheet.active_cell.is_none() {
        writer.write_event(Event::Empty(sheet_view))?;
        writer.write_event(Event::End(BytesEnd::new("sheetViews")))?;
        return Ok(());
    }
    writer.write_event(Event::Start(sheet_view))?;

    if let Some(freeze_panes) = &sheet.freeze_panes {
        let x_split_text = freeze_panes.frozen_columns.to_string();
        let y_split_text = freeze_panes.frozen_rows.to_string();
        let top_left_cell =
            cell_reference(freeze_panes.frozen_columns, freeze_panes.frozen_rows + 1);
        let active_pane = match (
            freeze_panes.frozen_columns > 0,
            freeze_panes.frozen_rows > 0,
        ) {
            (true, true) => "bottomRight",
            (true, false) => "topRight",
            (false, true) => "bottomLeft",
            (false, false) => "topLeft",
        };

        let mut pane = BytesStart::new("pane");
        if freeze_panes.frozen_columns > 0 {
            pane.push_attribute(("xSplit", x_split_text.as_str()));
        }
        if freeze_panes.frozen_rows > 0 {
            pane.push_attribute(("ySplit", y_split_text.as_str()));
        }
        pane.push_attribute(("topLeftCell", top_left_cell.as_str()));
        pane.push_attribute(("activePane", active_pane));
        pane.push_attribute(("state", "frozen"));
        writer.write_event(Event::Empty(pane))?;
    }

    if let Some(active_cell) = &sheet.active_cell {
        let mut selection = BytesStart::new("selection");
        selection.push_attribute(("activeCell", active_cell.as_str()));
        selection.push_attribute(("sqref", active_cell.as_str()));
        writer.write_event(Event::Empty(selection))?;
    }

    writer.write_event(Event::End(BytesEnd::new("sheetView")))?;
    writer.write_event(Event::End(BytesEnd::new("sheetViews")))?;
    Ok(())
}

/// Serializes `<cols><col../></cols>` from a sheet's [`ColumnSetting`]s. Each setting is written as
/// its own `<col min="N" max="N"../>` (see `Sheet.column_settings`'s doc comment for why this crate
/// doesn't bother collapsing adjacent columns into a shared range).
fn write_cols<W: Write>(
    writer: &mut Writer<W>,
    column_settings: &[ColumnSetting],
    cell_formats: &[CellFormat],
) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new("cols")))?;
    for setting in column_settings {
        let index_text = (setting.column + 1).to_string();
        let width_text = setting.width.map(|width| width.to_string());
        // `<col style="n">`, same shared `cellXfs` index space as a cell's own `s` attribute (see
        // `style_index`).
        let style_text = style_index(&setting.style, cell_formats).map(|index| index.to_string());

        let mut col = BytesStart::new("col");
        col.push_attribute(("min", index_text.as_str()));
        col.push_attribute(("max", index_text.as_str()));
        if let Some(width_text) = &width_text {
            col.push_attribute(("width", width_text.as_str()));
            col.push_attribute(("customWidth", "1"));
        }
        if let Some(style_text) = &style_text {
            col.push_attribute(("style", style_text.as_str()));
        }
        if setting.hidden {
            col.push_attribute(("hidden", "1"));
        }
        if setting.outline_level != 0 {
            let outline_level_text = setting.outline_level.to_string();
            col.push_attribute(("outlineLevel", outline_level_text.as_str()));
        }
        if setting.collapsed {
            col.push_attribute(("collapsed", "1"));
        }
        writer.write_event(Event::Empty(col))?;
    }
    writer.write_event(Event::End(BytesEnd::new("cols")))?;
    Ok(())
}

/// Serializes `<sheetProtection>` from a [`SheetProtection`]. See `SheetProtection`'s doc comment
/// for why only `sheet`/`password` are written.
fn write_sheet_protection<W: Write>(
    writer: &mut Writer<W>,
    protection: &SheetProtection,
) -> Result<()> {
    let password_text = protection.password.as_deref().map(legacy_password_hash_hex);
    let mut start = BytesStart::new("sheetProtection");
    start.push_attribute(("sheet", "1"));
    if let Some(password_text) = &password_text {
        start.push_attribute(("password", password_text.as_str()));
    }
    writer.write_event(Event::Empty(start))?;
    Ok(())
}

/// Serializes `<protectedRanges>`/`<protectedRange>` (`CT_ProtectedRanges`/ `CT_ProtectedRange`)
/// from a sheet's [`ProtectedRange`]s. Same legacy 16-bit password hash as
/// `write_sheet_protection`.
fn write_protected_ranges<W: Write>(
    writer: &mut Writer<W>,
    protected_ranges: &[ProtectedRange],
) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new("protectedRanges")))?;
    for range in protected_ranges {
        let password_text = range.password.as_deref().map(legacy_password_hash_hex);
        let mut start = BytesStart::new("protectedRange");
        start.push_attribute(("name", range.name.as_str()));
        start.push_attribute(("sqref", range.range.as_str()));
        if let Some(password_text) = &password_text {
            start.push_attribute(("password", password_text.as_str()));
        }
        writer.write_event(Event::Empty(start))?;
    }
    writer.write_event(Event::End(BytesEnd::new("protectedRanges")))?;
    Ok(())
}

/// Serializes `<scenarios>`/`<scenario>`/`<inputCells>` (`CT_Scenarios`/
/// `CT_Scenario`/`CT_InputCells`) from a sheet's [`Scenario`]s. Every scenario is written
/// `locked="1"` (Excel's own default for a newly-created scenario) with no scenario marked
/// `current`/`show` (both `CT_Scenarios` attributes, 0-based indices into the list — left unset
/// since this crate has no notion of "the currently applied scenario").
fn write_scenarios<W: Write>(writer: &mut Writer<W>, scenarios: &[Scenario]) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new("scenarios")))?;
    for scenario in scenarios {
        let count_text = scenario.inputs.len().to_string();
        let mut scenario_start = BytesStart::new("scenario");
        scenario_start.push_attribute(("name", scenario.name.as_str()));
        scenario_start.push_attribute(("locked", "1"));
        scenario_start.push_attribute(("count", count_text.as_str()));
        if let Some(comment) = &scenario.comment {
            scenario_start.push_attribute(("comment", comment.as_str()));
        }
        writer.write_event(Event::Start(scenario_start))?;
        for (cell, value) in &scenario.inputs {
            let mut input_cell = BytesStart::new("inputCells");
            input_cell.push_attribute(("r", cell.as_str()));
            input_cell.push_attribute(("val", value.as_str()));
            writer.write_event(Event::Empty(input_cell))?;
        }
        writer.write_event(Event::End(BytesEnd::new("scenario")))?;
    }
    writer.write_event(Event::End(BytesEnd::new("scenarios")))?;
    Ok(())
}

/// Serializes `<ignoredErrors>`/`<ignoredError>` (`CT_IgnoredErrors`/ `CT_IgnoredError`) from a
/// sheet's [`IgnoredError`]s. One `<ignoredError>` element per entry (see `IgnoredError`'s own doc
/// comment for why this crate doesn't bother merging entries); each boolean flag attribute is only
/// written when `true` (`CT_IgnoredError`'s schema default for every flag is `false`, confirmed via
/// `sml.xsd`), matching this crate's usual "omit schema-default attributes" convention elsewhere
/// (e.g. `Row`/`ColumnSetting` not writing `hidden="0"`).
fn write_ignored_errors<W: Write>(
    writer: &mut Writer<W>,
    ignored_errors: &[IgnoredError],
) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new("ignoredErrors")))?;
    for entry in ignored_errors {
        let mut start = BytesStart::new("ignoredError");
        start.push_attribute(("sqref", entry.range.as_str()));
        if entry.eval_error {
            start.push_attribute(("evalError", "1"));
        }
        if entry.two_digit_text_year {
            start.push_attribute(("twoDigitTextYear", "1"));
        }
        if entry.number_stored_as_text {
            start.push_attribute(("numberStoredAsText", "1"));
        }
        if entry.formula {
            start.push_attribute(("formula", "1"));
        }
        if entry.formula_range {
            start.push_attribute(("formulaRange", "1"));
        }
        if entry.unlocked_formula {
            start.push_attribute(("unlockedFormula", "1"));
        }
        if entry.empty_cell_reference {
            start.push_attribute(("emptyCellReference", "1"));
        }
        if entry.list_data_validation {
            start.push_attribute(("listDataValidation", "1"));
        }
        if entry.calculated_column {
            start.push_attribute(("calculatedColumn", "1"));
        }
        writer.write_event(Event::Empty(start))?;
    }
    writer.write_event(Event::End(BytesEnd::new("ignoredErrors")))?;
    Ok(())
}

/// Serializes each `<filterColumn>`/`<filters>`/`<filter>`
/// (`CT_FilterColumn`/`CT_Filters`/`CT_Filter`) from a sheet's [`AutoFilterColumn`]s, as children
/// of an already-opened `<autoFilter>`. See `AutoFilterColumn`'s doc comment for the scope this
/// supports (a fixed list of checked values only).
fn write_filter_columns<W: Write>(
    writer: &mut Writer<W>,
    columns: &[AutoFilterColumn],
) -> Result<()> {
    for column in columns {
        let col_id_text = column.column_offset.to_string();
        let mut filter_column = BytesStart::new("filterColumn");
        filter_column.push_attribute(("colId", col_id_text.as_str()));
        writer.write_event(Event::Start(filter_column))?;
        writer.write_event(Event::Start(BytesStart::new("filters")))?;
        for value in &column.visible_values {
            let mut filter = BytesStart::new("filter");
            filter.push_attribute(("val", value.as_str()));
            writer.write_event(Event::Empty(filter))?;
        }
        writer.write_event(Event::End(BytesEnd::new("filters")))?;
        writer.write_event(Event::End(BytesEnd::new("filterColumn")))?;
    }
    Ok(())
}

/// Serializes `<sortState ref="..">`/`<sortCondition ref=".." descending="1">`
/// (`CT_SortState`/`CT_SortCondition`) from a [`SortState`]. Shared by both call sites (a
/// worksheet-level `<autoFilter>`'s child and a table's own sibling element) since `CT_SortState`'s
/// own shape is identical in both places, only its *position* in the surrounding sequence differs
/// (see `Sheet.sort_state`/`ExcelTable.sort_state`'s doc comments).
fn write_sort_state<W: Write>(writer: &mut Writer<W>, sort_state: &SortState) -> Result<()> {
    let mut start = BytesStart::new("sortState");
    start.push_attribute(("ref", sort_state.range.as_str()));
    if sort_state.conditions.is_empty() {
        writer.write_event(Event::Empty(start))?;
    } else {
        writer.write_event(Event::Start(start))?;
        for condition in &sort_state.conditions {
            let mut condition_start = BytesStart::new("sortCondition");
            condition_start.push_attribute(("ref", condition.range.as_str()));
            if condition.descending {
                condition_start.push_attribute(("descending", "1"));
            }
            writer.write_event(Event::Empty(condition_start))?;
        }
        writer.write_event(Event::End(BytesEnd::new("sortState")))?;
    }
    Ok(())
}

/// Serializes `<rowBreaks>`/`<colBreaks>` (`CT_PageBreak`, sharing the same shape for both rows and
/// columns) from a sheet's manual page breaks. `max` is each break's `<brk>`'s own `max` attribute
/// — the highest row/column index the break spans, conventionally the format's absolute maximum
/// (`16383` for a row-break's column extent, `1048575` for a column-break's row extent,
/// SpreadsheetML's own row/column count limits) when the break should apply across the sheet's full
/// width/height, matching what real Excel itself writes.
fn write_page_breaks<W: Write>(
    writer: &mut Writer<W>,
    element_name: &str,
    breaks: &[u32],
    max: u32,
) -> Result<()> {
    let count_text = breaks.len().to_string();
    let max_text = max.to_string();
    let mut start = BytesStart::new(element_name);
    start.push_attribute(("count", count_text.as_str()));
    start.push_attribute(("manualBreakCount", count_text.as_str()));
    writer.write_event(Event::Start(start))?;
    for id in breaks {
        let id_text = id.to_string();
        let mut brk = BytesStart::new("brk");
        brk.push_attribute(("id", id_text.as_str()));
        brk.push_attribute(("max", max_text.as_str()));
        brk.push_attribute(("man", "1"));
        writer.write_event(Event::Empty(brk))?;
    }
    writer.write_event(Event::End(BytesEnd::new(element_name)))?;
    Ok(())
}

/// Computes Excel's legacy 16-bit password hash, formatted as 4 uppercase hex digits
/// (`CT_SheetProtection`/`CT_WorkbookProtection`'s `password` attribute) — the algorithm ECMA-376
/// Part 4 §3.3.1.81 documents is itself wrong (confirmed by multiple independent
/// reverse-engineering efforts, e.g. Kohei Yoshida's LibreOffice-contributor writeup and Wouter van
/// Vugt's "Hashing password for use in SpreadsheetML" post); this implements the corrected
/// algorithm those sources converged on, which every version of Excel (including current ones, for
/// backward compatibility) still accepts: rotate a running 15-bit hash left by one bit and XOR each
/// password byte in, in *reverse* order, then a final XOR with a fixed constant and the password's
/// byte length. Only ASCII/ single-byte-per-character passwords are handled correctly — a
/// multi-byte UTF-8 character would need the extra "high byte" conversion step ECMA-376 also
/// documents, which isn't implemented (out of scope, same posture as other scope reductions).
fn legacy_password_hash(password: &str) -> u16 {
    let bytes = password.as_bytes();
    let mut hash: u16 = 0;
    for &byte in bytes.iter().rev() {
        hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7fff);
        hash ^= byte as u16;
    }
    hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7fff);
    hash ^= 0xCE4B; // 0x8000 | ('N' as u16) << 8 | ('K' as u16)
    hash ^= bytes.len() as u16;
    hash
}

fn legacy_password_hash_hex(password: &str) -> String {
    format!("{:04X}", legacy_password_hash(password))
}

/// Serializes `<hyperlinks><hyperlink../></hyperlinks>` from a sheet's [`CellHyperlink`]s.
/// `relationship_ids[i]` is `Some(rId)` for `hyperlinks[i]` when it's an external link (resolved
/// against this worksheet's own `.rels`, computed up front by `Workbook::write_to`), `None` for an
/// internal one (no relationship needed — `location` carries the target directly).
fn write_hyperlinks<W: Write>(
    writer: &mut Writer<W>,
    hyperlinks: &[CellHyperlink],
    relationship_ids: &[Option<String>],
) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new("hyperlinks")))?;
    for (hyperlink, relationship_id) in hyperlinks.iter().zip(relationship_ids) {
        let mut start = BytesStart::new("hyperlink");
        start.push_attribute(("ref", hyperlink.cell.as_str()));
        match (&hyperlink.target, relationship_id) {
            (HyperlinkTarget::External(_), Some(relationship_id)) => {
                start.push_attribute(("r:id", relationship_id.as_str()));
            }
            (HyperlinkTarget::Internal(location), _) => {
                start.push_attribute(("location", location.as_str()));
            }
            // An external target with no resolved relationship id would be a bug in
            // `Workbook::write_to`'s own bookkeeping — writing just `ref` with neither `r:id` nor
            // `location` is at least schema-tolerable (both are optional) rather than panicking.
            (HyperlinkTarget::External(_), None) => {}
        }
        if let Some(tooltip) = &hyperlink.tooltip {
            start.push_attribute(("tooltip", tooltip.as_str()));
        }
        writer.write_event(Event::Empty(start))?;
    }
    writer.write_event(Event::End(BytesEnd::new("hyperlinks")))?;
    Ok(())
}

/// Serializes `<pageSetup>` (`CT_PageSetup`) from a sheet's [`PrintSettings`].
/// `fitToWidth`/`fitToHeight` default to `"0"` ("as many pages as needed") per `CT_PageSetup`'s own
/// convention when only one of the two is set — see `PrintSettings::fit_to_width`'s doc comment.
fn write_page_setup<W: Write>(
    writer: &mut Writer<W>,
    print_settings: &PrintSettings,
) -> Result<()> {
    let mut start = BytesStart::new("pageSetup");
    if let Some(orientation) = print_settings.orientation {
        start.push_attribute(("orientation", page_orientation_str(orientation)));
    }
    // see `PrintSettings.scale`'s doc comment for why this is the caller's own responsibility not
    // to combine confusingly with `fit_to_width`/`fit_to_height`.
    let scale_text = print_settings.scale.map(|value| value.to_string());
    if let Some(scale_text) = &scale_text {
        start.push_attribute(("scale", scale_text.as_str()));
    }
    let fit_to_width_text = print_settings.fit_to_width.unwrap_or(0).to_string();
    let fit_to_height_text = print_settings.fit_to_height.unwrap_or(0).to_string();
    if print_settings.fit_to_width.is_some() || print_settings.fit_to_height.is_some() {
        start.push_attribute(("fitToWidth", fit_to_width_text.as_str()));
        start.push_attribute(("fitToHeight", fit_to_height_text.as_str()));
    }
    writer.write_event(Event::Empty(start))?;
    Ok(())
}

fn page_orientation_str(orientation: PageOrientation) -> &'static str {
    match orientation {
        PageOrientation::Portrait => "portrait",
        PageOrientation::Landscape => "landscape",
    }
}

/// Serializes `<headerFooter><oddHeader>.</oddHeader><oddFooter>.</oddFooter></headerFooter>` from
/// a sheet's [`PrintSettings`] — extended for `differentFirst`/`differentOddEven`
/// first-page/even-page variants. Header/footer text is written verbatim, see
/// `PrintSettings.header`'s doc comment. `CT_HeaderFooter`'s child sequence is `oddHeader?`,
/// `oddFooter?`, `evenHeader?`, `evenFooter?`, `firstHeader?`, `firstFooter?`.
fn write_header_footer<W: Write>(
    writer: &mut Writer<W>,
    print_settings: &PrintSettings,
) -> Result<()> {
    let mut start = BytesStart::new("headerFooter");
    if print_settings.header_first.is_some() || print_settings.footer_first.is_some() {
        start.push_attribute(("differentFirst", "1"));
    }
    if print_settings.header_even.is_some() || print_settings.footer_even.is_some() {
        start.push_attribute(("differentOddEven", "1"));
    }
    writer.write_event(Event::Start(start))?;
    if let Some(header) = &print_settings.header {
        write_element_text(writer, "oddHeader", header)?;
    }
    if let Some(footer) = &print_settings.footer {
        write_element_text(writer, "oddFooter", footer)?;
    }
    if let Some(header) = &print_settings.header_even {
        write_element_text(writer, "evenHeader", header)?;
    }
    if let Some(footer) = &print_settings.footer_even {
        write_element_text(writer, "evenFooter", footer)?;
    }
    if let Some(header) = &print_settings.header_first {
        write_element_text(writer, "firstHeader", header)?;
    }
    if let Some(footer) = &print_settings.footer_first {
        write_element_text(writer, "firstFooter", footer)?;
    }
    writer.write_event(Event::End(BytesEnd::new("headerFooter")))?;
    Ok(())
}

/// Serializes one conditional formatting rule as its own `<conditionalFormatting
/// sqref=".."><cfRule../></conditionalFormatting>` block (see `ConditionalFormattingRule`'s doc
/// comment for why this crate doesn't group multiple rules under one block the way real Excel
/// does). `index` is this rule's position in `Sheet.conditional_formatting_rules`, used both to
/// compute `priority` (1-based) and, via `dxfs`, to resolve `dxfId`.
fn write_conditional_formatting<W: Write>(
    writer: &mut Writer<W>,
    index: usize,
    rule: &ConditionalFormattingRule,
    dxfs: &[CellFormat],
) -> Result<()> {
    let mut conditional_formatting_start = BytesStart::new("conditionalFormatting");
    conditional_formatting_start.push_attribute(("sqref", rule.range.as_str()));
    writer.write_event(Event::Start(conditional_formatting_start))?;

    // `ColorScale`/`DataBar`/`IconSet` ignore `rule.format` entirely (see their own doc comments) —
    // resolving a `dxfId` for them would be meaningless, so this lookup is only used by the
    // variants that actually reference one.
    let dxf_id = dxfs
        .iter()
        .position(|format| format == &rule.format)
        .unwrap_or(0);
    let dxf_id_text = dxf_id.to_string();
    let priority_text = (index + 1).to_string();
    let anchor_cell = range_anchor_cell(&rule.range);

    let mut cf_rule_start = BytesStart::new("cfRule");
    match &rule.condition {
        ConditionalFormattingCondition::CellIs { operator, .. } => {
            cf_rule_start.push_attribute(("type", "cellIs"));
            cf_rule_start.push_attribute(("dxfId", dxf_id_text.as_str()));
            cf_rule_start.push_attribute(("priority", priority_text.as_str()));
            cf_rule_start.push_attribute(("operator", comparison_operator_str(*operator)));
        }
        ConditionalFormattingCondition::Expression { .. } => {
            cf_rule_start.push_attribute(("type", "expression"));
            cf_rule_start.push_attribute(("dxfId", dxf_id_text.as_str()));
            cf_rule_start.push_attribute(("priority", priority_text.as_str()));
        }
        ConditionalFormattingCondition::ColorScale(_) => {
            cf_rule_start.push_attribute(("type", "colorScale"));
            cf_rule_start.push_attribute(("priority", priority_text.as_str()));
        }
        ConditionalFormattingCondition::DataBar { .. } => {
            cf_rule_start.push_attribute(("type", "dataBar"));
            cf_rule_start.push_attribute(("priority", priority_text.as_str()));
        }
        ConditionalFormattingCondition::IconSet(_) => {
            cf_rule_start.push_attribute(("type", "iconSet"));
            cf_rule_start.push_attribute(("priority", priority_text.as_str()));
        }
        ConditionalFormattingCondition::Top10 {
            rank,
            percent,
            bottom,
        } => {
            cf_rule_start.push_attribute(("type", "top10"));
            cf_rule_start.push_attribute(("dxfId", dxf_id_text.as_str()));
            cf_rule_start.push_attribute(("priority", priority_text.as_str()));
            let rank_text = rank.to_string();
            cf_rule_start.push_attribute(("rank", rank_text.as_str()));
            if *percent {
                cf_rule_start.push_attribute(("percent", "1"));
            }
            if *bottom {
                cf_rule_start.push_attribute(("bottom", "1"));
            }
            writer.write_event(Event::Empty(cf_rule_start))?;
            writer.write_event(Event::End(BytesEnd::new("conditionalFormatting")))?;
            return Ok(());
        }
        ConditionalFormattingCondition::AboveAverage {
            above,
            equal_average,
        } => {
            cf_rule_start.push_attribute(("type", "aboveAverage"));
            cf_rule_start.push_attribute(("dxfId", dxf_id_text.as_str()));
            cf_rule_start.push_attribute(("priority", priority_text.as_str()));
            if !*above {
                cf_rule_start.push_attribute(("aboveAverage", "0"));
            }
            if *equal_average {
                cf_rule_start.push_attribute(("equalAverage", "1"));
            }
            writer.write_event(Event::Empty(cf_rule_start))?;
            writer.write_event(Event::End(BytesEnd::new("conditionalFormatting")))?;
            return Ok(());
        }
        ConditionalFormattingCondition::DuplicateValues => {
            cf_rule_start.push_attribute(("type", "duplicateValues"));
            cf_rule_start.push_attribute(("dxfId", dxf_id_text.as_str()));
            cf_rule_start.push_attribute(("priority", priority_text.as_str()));
            writer.write_event(Event::Empty(cf_rule_start))?;
            writer.write_event(Event::End(BytesEnd::new("conditionalFormatting")))?;
            return Ok(());
        }
        ConditionalFormattingCondition::UniqueValues => {
            cf_rule_start.push_attribute(("type", "uniqueValues"));
            cf_rule_start.push_attribute(("dxfId", dxf_id_text.as_str()));
            cf_rule_start.push_attribute(("priority", priority_text.as_str()));
            writer.write_event(Event::Empty(cf_rule_start))?;
            writer.write_event(Event::End(BytesEnd::new("conditionalFormatting")))?;
            return Ok(());
        }
        ConditionalFormattingCondition::ContainsText { operator, .. } => {
            cf_rule_start.push_attribute(("type", text_comparison_operator_type_str(*operator)));
            cf_rule_start.push_attribute(("dxfId", dxf_id_text.as_str()));
            cf_rule_start.push_attribute(("priority", priority_text.as_str()));
            cf_rule_start
                .push_attribute(("operator", text_comparison_operator_type_str(*operator)));
        }
    }
    let text_for_attribute;
    if let ConditionalFormattingCondition::ContainsText { text, .. } = &rule.condition {
        text_for_attribute = text.clone();
        cf_rule_start.push_attribute(("text", text_for_attribute.as_str()));
    }
    writer.write_event(Event::Start(cf_rule_start))?;

    match &rule.condition {
        ConditionalFormattingCondition::CellIs {
            formula1, formula2, ..
        } => {
            write_element_text(writer, "formula", formula1)?;
            if let Some(formula2) = formula2 {
                write_element_text(writer, "formula", formula2)?;
            }
        }
        ConditionalFormattingCondition::Expression { formula } => {
            write_element_text(writer, "formula", formula)?;
        }
        ConditionalFormattingCondition::ColorScale(stops) => {
            writer.write_event(Event::Start(BytesStart::new("colorScale")))?;
            for stop in stops {
                write_cfvo(writer, &stop.position)?;
            }
            for stop in stops {
                let mut color = BytesStart::new("color");
                color.push_attribute(("rgb", stop.color.as_str()));
                writer.write_event(Event::Empty(color))?;
            }
            writer.write_event(Event::End(BytesEnd::new("colorScale")))?;
        }
        ConditionalFormattingCondition::DataBar { min, max, color } => {
            writer.write_event(Event::Start(BytesStart::new("dataBar")))?;
            write_cfvo(writer, min)?;
            write_cfvo(writer, max)?;
            let mut color_start = BytesStart::new("color");
            color_start.push_attribute(("rgb", color.as_str()));
            writer.write_event(Event::Empty(color_start))?;
            writer.write_event(Event::End(BytesEnd::new("dataBar")))?;
        }
        ConditionalFormattingCondition::IconSet(icon_set) => {
            let mut icon_set_start = BytesStart::new("iconSet");
            icon_set_start.push_attribute(("iconSet", icon_set_type_str(*icon_set)));
            writer.write_event(Event::Start(icon_set_start))?;
            for percent in [0, 33, 67] {
                write_cfvo(writer, &CfvoPosition::Percent(percent as f64))?;
            }
            writer.write_event(Event::End(BytesEnd::new("iconSet")))?;
        }
        ConditionalFormattingCondition::ContainsText { operator, text } => {
            write_element_text(
                writer,
                "formula",
                &text_comparison_formula(*operator, anchor_cell, text),
            )?;
        }
        ConditionalFormattingCondition::Top10 { .. }
        | ConditionalFormattingCondition::AboveAverage { .. }
        | ConditionalFormattingCondition::DuplicateValues
        | ConditionalFormattingCondition::UniqueValues => unreachable!("returned early above"),
    }

    writer.write_event(Event::End(BytesEnd::new("cfRule")))?;
    writer.write_event(Event::End(BytesEnd::new("conditionalFormatting")))?;

    Ok(())
}

/// The first cell reference of a range (e.g. `"A1"` from `"A1:D10"`) — used as the anchor cell in
/// an auto-generated `containsText`/. formula (see `write_conditional_formatting`'s `ContainsText`
/// arm) — real Excel always anchors these formulas on the top-left cell of the rule's range.
fn range_anchor_cell(range: &str) -> &str {
    range.split(':').next().unwrap_or(range)
}

/// Doubles any literal `"` in `text` — Excel formula string literals escape an embedded quote by
/// doubling it (`""`), not backslash-escaping it.
fn escape_formula_string(text: &str) -> String {
    text.replace('"', "\"\"")
}

fn text_comparison_formula(
    operator: TextComparisonOperator,
    anchor_cell: &str,
    text: &str,
) -> String {
    let escaped = escape_formula_string(text);
    match operator {
        TextComparisonOperator::Contains => {
            format!(r#"NOT(ISERROR(SEARCH("{escaped}",{anchor_cell})))"#)
        }
        TextComparisonOperator::NotContains => {
            format!(r#"ISERROR(SEARCH("{escaped}",{anchor_cell}))"#)
        }
        TextComparisonOperator::BeginsWith => {
            format!(r#"LEFT({anchor_cell},LEN("{escaped}"))="{escaped}""#)
        }
        TextComparisonOperator::EndsWith => {
            format!(r#"RIGHT({anchor_cell},LEN("{escaped}"))="{escaped}""#)
        }
    }
}

fn text_comparison_operator_type_str(operator: TextComparisonOperator) -> &'static str {
    match operator {
        TextComparisonOperator::Contains => "containsText",
        TextComparisonOperator::NotContains => "notContainsText",
        TextComparisonOperator::BeginsWith => "beginsWith",
        TextComparisonOperator::EndsWith => "endsWith",
    }
}

fn icon_set_type_str(icon_set: IconSetType) -> &'static str {
    match icon_set {
        IconSetType::ThreeTrafficLights => "3TrafficLights1",
        IconSetType::ThreeArrows => "3Arrows",
        IconSetType::ThreeFlags => "3Flags",
        IconSetType::ThreeSymbols => "3Symbols",
    }
}

/// Writes one `<cfvo type=".." val="..">` (`CT_Cfvo`) — `val` is omitted entirely for `Min`/`Max`
/// (see [`CfvoPosition`]'s doc comment).
fn write_cfvo<W: Write>(writer: &mut Writer<W>, position: &CfvoPosition) -> Result<()> {
    let mut cfvo = BytesStart::new("cfvo");
    match position {
        CfvoPosition::Min => {
            cfvo.push_attribute(("type", "min"));
        }
        CfvoPosition::Max => {
            cfvo.push_attribute(("type", "max"));
        }
        CfvoPosition::Percent(value) => {
            cfvo.push_attribute(("type", "percent"));
            let value_text = value.to_string();
            cfvo.push_attribute(("val", value_text.as_str()));
            writer.write_event(Event::Empty(cfvo))?;
            return Ok(());
        }
        CfvoPosition::Number(value) => {
            cfvo.push_attribute(("type", "num"));
            let value_text = value.to_string();
            cfvo.push_attribute(("val", value_text.as_str()));
            writer.write_event(Event::Empty(cfvo))?;
            return Ok(());
        }
    }
    writer.write_event(Event::Empty(cfvo))?;
    Ok(())
}

/// Serializes `<dataValidations count="N">..</dataValidations>` from a sheet's data validation
/// rules.
fn write_data_validations<W: Write>(
    writer: &mut Writer<W>,
    rules: &[DataValidationRule],
) -> Result<()> {
    let count_text = rules.len().to_string();
    let mut start = BytesStart::new("dataValidations");
    start.push_attribute(("count", count_text.as_str()));
    writer.write_event(Event::Start(start))?;

    for rule in rules {
        let (type_str, operator, formula1, formula2): (
            &str,
            Option<ComparisonOperator>,
            &str,
            Option<&str>,
        ) = match &rule.kind {
            DataValidationKind::List { source } => ("list", None, source.as_str(), None),
            DataValidationKind::WholeNumber {
                operator,
                formula1,
                formula2,
            } => (
                "whole",
                Some(*operator),
                formula1.as_str(),
                formula2.as_deref(),
            ),
            DataValidationKind::Decimal {
                operator,
                formula1,
                formula2,
            } => (
                "decimal",
                Some(*operator),
                formula1.as_str(),
                formula2.as_deref(),
            ),
            DataValidationKind::Date {
                operator,
                formula1,
                formula2,
            } => (
                "date",
                Some(*operator),
                formula1.as_str(),
                formula2.as_deref(),
            ),
            DataValidationKind::Time {
                operator,
                formula1,
                formula2,
            } => (
                "time",
                Some(*operator),
                formula1.as_str(),
                formula2.as_deref(),
            ),
            DataValidationKind::TextLength {
                operator,
                formula1,
                formula2,
            } => (
                "textLength",
                Some(*operator),
                formula1.as_str(),
                formula2.as_deref(),
            ),
            DataValidationKind::Custom { formula } => ("custom", None, formula.as_str(), None),
        };

        let mut data_validation_start = BytesStart::new("dataValidation");
        data_validation_start.push_attribute(("type", type_str));
        if let Some(operator) = operator {
            data_validation_start.push_attribute(("operator", comparison_operator_str(operator)));
        }
        if rule.allow_blank {
            data_validation_start.push_attribute(("allowBlank", "1"));
        }
        if rule.input_message.is_some() {
            data_validation_start.push_attribute(("showInputMessage", "1"));
        }
        if rule.error_message.is_some() {
            data_validation_start.push_attribute(("showErrorMessage", "1"));
        }
        if let Some(message) = &rule.input_message {
            if let Some(title) = &message.title {
                data_validation_start.push_attribute(("promptTitle", title.as_str()));
            }
            data_validation_start.push_attribute(("prompt", message.text.as_str()));
        }
        if let Some(message) = &rule.error_message {
            if let Some(title) = &message.title {
                data_validation_start.push_attribute(("errorTitle", title.as_str()));
            }
            data_validation_start.push_attribute(("error", message.text.as_str()));
        }
        data_validation_start.push_attribute(("sqref", rule.range.as_str()));
        writer.write_event(Event::Start(data_validation_start))?;

        write_element_text(writer, "formula1", formula1)?;
        if let Some(formula2) = formula2 {
            write_element_text(writer, "formula2", formula2)?;
        }

        writer.write_event(Event::End(BytesEnd::new("dataValidation")))?;
    }

    writer.write_event(Event::End(BytesEnd::new("dataValidations")))?;

    Ok(())
}

/// Writes a simple `<name>text</name>` element — shared by `<formula>` (conditional formatting) and
/// `<formula1>`/`<formula2>` (data validation), which are otherwise identical in shape.
fn write_element_text<W: Write>(writer: &mut Writer<W>, name: &str, text: &str) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new(name)))?;
    writer.write_event(Event::Text(BytesText::new(text)))?;
    writer.write_event(Event::End(BytesEnd::new(name)))?;
    Ok(())
}

/// Maps a [`ComparisonOperator`] to its ECMA-376 token — shared by
/// `ST_ConditionalFormattingOperator` and `ST_DataValidationOperator`, which happen to define the
/// exact same 8 tokens for these values.
fn comparison_operator_str(operator: ComparisonOperator) -> &'static str {
    match operator {
        ComparisonOperator::LessThan => "lessThan",
        ComparisonOperator::LessThanOrEqual => "lessThanOrEqual",
        ComparisonOperator::Equal => "equal",
        ComparisonOperator::NotEqual => "notEqual",
        ComparisonOperator::GreaterThanOrEqual => "greaterThanOrEqual",
        ComparisonOperator::GreaterThan => "greaterThan",
        ComparisonOperator::Between => "between",
        ComparisonOperator::NotBetween => "notBetween",
    }
}

/// Resolves a cell's `cellXfs` index (its `<c>`'s `s` attribute) from its optional [`CellFormat`] —
/// `None` for a cell with no format (implicitly index 0, so `s` is simply omitted), or `1 +
/// position` of the matching entry in `cell_formats` (see `collect_cell_formats`'s doc comment for
/// why index 0 is always reserved).
fn style_index(format: &Option<CellFormat>, cell_formats: &[CellFormat]) -> Option<usize> {
    format
        .as_ref()
        .and_then(|format| {
            cell_formats
                .iter()
                .position(|candidate| candidate == format)
        })
        .map(|index| index + 1)
}

/// Writes a single `<c>` element, or nothing at all for a plain `CellValue::Empty` cell with no
/// format — real Excel never writes a `<c>` for a genuinely empty, unstyled cell either, it simply
/// omits it (see `CellValue::Empty`'s doc comment). A styled-but-empty cell (a format but no value)
/// is still written, as a self-closing `<c r=".." s=".."/>` with no `<v>` at all.
fn write_cell<W: Write>(
    writer: &mut Writer<W>,
    column_index: usize,
    row_number: usize,
    cell: &Cell,
    shared_strings: &SharedStrings,
    cell_formats: &[CellFormat],
) -> Result<()> {
    let style = style_index(&cell.format, cell_formats);

    match &cell.value {
        CellValue::Empty => {
            if let Some(style) = style {
                let reference = cell_reference(column_index, row_number);
                let style_text = style.to_string();
                let mut start = BytesStart::new("c");
                start.push_attribute(("r", reference.as_str()));
                start.push_attribute(("s", style_text.as_str()));
                writer.write_event(Event::Empty(start))?;
            }
        }

        CellValue::Text(text) => {
            let reference = cell_reference(column_index, row_number);
            let index = shared_strings.plain_indices.get(text).copied().unwrap_or(0);
            let index_text = index.to_string();
            let style_text = style.map(|value| value.to_string());

            let mut start = BytesStart::new("c");
            start.push_attribute(("r", reference.as_str()));
            if let Some(style_text) = &style_text {
                start.push_attribute(("s", style_text.as_str()));
            }
            start.push_attribute(("t", "s"));
            writer.write_event(Event::Start(start))?;
            writer.write_event(Event::Start(BytesStart::new("v")))?;
            writer.write_event(Event::Text(BytesText::new(&index_text)))?;
            writer.write_event(Event::End(BytesEnd::new("v")))?;
            writer.write_event(Event::End(BytesEnd::new("c")))?;
        }

        // `RichText` shares the exact same `<c t="s"><v>N</v></c>` shape as plain `Text` — the only
        // difference is which kind of `<si>` entry `N` resolves to in `xl/sharedStrings.xml` (see
        // `SharedStrings`/`to_shared_strings_xml`).
        CellValue::RichText(runs) => {
            let reference = cell_reference(column_index, row_number);
            let index = shared_strings.index_of_rich(runs).unwrap_or(0);
            let index_text = index.to_string();
            let style_text = style.map(|value| value.to_string());

            let mut start = BytesStart::new("c");
            start.push_attribute(("r", reference.as_str()));
            if let Some(style_text) = &style_text {
                start.push_attribute(("s", style_text.as_str()));
            }
            start.push_attribute(("t", "s"));
            writer.write_event(Event::Start(start))?;
            writer.write_event(Event::Start(BytesStart::new("v")))?;
            writer.write_event(Event::Text(BytesText::new(&index_text)))?;
            writer.write_event(Event::End(BytesEnd::new("v")))?;
            writer.write_event(Event::End(BytesEnd::new("c")))?;
        }

        // A boolean cell (`t="b"`, `<v>0</v>`/`<v>1</v>`).
        CellValue::Boolean(value) => {
            let reference = cell_reference(column_index, row_number);
            let value_text = if *value { "1" } else { "0" };
            let style_text = style.map(|value| value.to_string());

            let mut start = BytesStart::new("c");
            start.push_attribute(("r", reference.as_str()));
            if let Some(style_text) = &style_text {
                start.push_attribute(("s", style_text.as_str()));
            }
            start.push_attribute(("t", "b"));
            writer.write_event(Event::Start(start))?;
            writer.write_event(Event::Start(BytesStart::new("v")))?;
            writer.write_event(Event::Text(BytesText::new(value_text)))?;
            writer.write_event(Event::End(BytesEnd::new("v")))?;
            writer.write_event(Event::End(BytesEnd::new("c")))?;
        }

        // A date value — written as a plain numeric cell (SpreadsheetML has no dedicated date cell
        // type, see `CellValue::Date`'s doc comment).
        CellValue::Date(date) => {
            let reference = cell_reference(column_index, row_number);
            let serial = excel_serial_datetime(date);
            let number_text = serial.to_string();
            let style_text = style.map(|value| value.to_string());

            let mut start = BytesStart::new("c");
            start.push_attribute(("r", reference.as_str()));
            if let Some(style_text) = &style_text {
                start.push_attribute(("s", style_text.as_str()));
            }
            writer.write_event(Event::Start(start))?;
            writer.write_event(Event::Start(BytesStart::new("v")))?;
            writer.write_event(Event::Text(BytesText::new(&number_text)))?;
            writer.write_event(Event::End(BytesEnd::new("v")))?;
            writer.write_event(Event::End(BytesEnd::new("c")))?;
        }

        CellValue::Number(number) => {
            let reference = cell_reference(column_index, row_number);
            let number_text = number.to_string();
            let style_text = style.map(|value| value.to_string());

            let mut start = BytesStart::new("c");
            start.push_attribute(("r", reference.as_str()));
            if let Some(style_text) = &style_text {
                start.push_attribute(("s", style_text.as_str()));
            }
            writer.write_event(Event::Start(start))?;
            writer.write_event(Event::Start(BytesStart::new("v")))?;
            writer.write_event(Event::Text(BytesText::new(&number_text)))?;
            writer.write_event(Event::End(BytesEnd::new("v")))?;
            writer.write_event(Event::End(BytesEnd::new("c")))?;
        }

        CellValue::Formula {
            formula,
            cached_value,
        } => {
            let reference = cell_reference(column_index, row_number);
            let style_text = style.map(|value| value.to_string());

            let mut start = BytesStart::new("c");
            start.push_attribute(("r", reference.as_str()));
            if let Some(style_text) = &style_text {
                start.push_attribute(("s", style_text.as_str()));
            }
            writer.write_event(Event::Start(start))?;

            // `formula: None` writes no `<f>` at all — an honest "we don't have the text" rather
            // than fabricating one (a real `t="shared"` cell with no text now reads back as
            // `CellValue::SharedFormula` instead, see its own doc comment, point 52).
            if let Some(formula_text) = formula {
                writer.write_event(Event::Start(BytesStart::new("f")))?;
                writer.write_event(Event::Text(BytesText::new(formula_text)))?;
                writer.write_event(Event::End(BytesEnd::new("f")))?;
            }
            if let Some(value) = cached_value {
                let value_text = value.to_string();
                writer.write_event(Event::Start(BytesStart::new("v")))?;
                writer.write_event(Event::Text(BytesText::new(&value_text)))?;
                writer.write_event(Event::End(BytesEnd::new("v")))?;
            }

            writer.write_event(Event::End(BytesEnd::new("c")))?;
        }

        // An array (CSE) formula's anchor cell (`t="array"`). See `CellValue::ArrayFormula`'s doc
        // comment: this crate only ever writes the literal `<f>` on the anchor cell, matching real
        // Excel's own convention.
        CellValue::ArrayFormula {
            formula,
            range,
            cached_value,
        } => {
            let reference = cell_reference(column_index, row_number);
            let style_text = style.map(|value| value.to_string());

            let mut start = BytesStart::new("c");
            start.push_attribute(("r", reference.as_str()));
            if let Some(style_text) = &style_text {
                start.push_attribute(("s", style_text.as_str()));
            }
            writer.write_event(Event::Start(start))?;

            let mut f_start = BytesStart::new("f");
            f_start.push_attribute(("t", "array"));
            f_start.push_attribute(("ref", range.as_str()));
            writer.write_event(Event::Start(f_start))?;
            writer.write_event(Event::Text(BytesText::new(formula)))?;
            writer.write_event(Event::End(BytesEnd::new("f")))?;

            if let Some(value) = cached_value {
                let value_text = value.to_string();
                writer.write_event(Event::Start(BytesStart::new("v")))?;
                writer.write_event(Event::Text(BytesText::new(&value_text)))?;
                writer.write_event(Event::End(BytesEnd::new("v")))?;
            }

            writer.write_event(Event::End(BytesEnd::new("c")))?;
        }

        // A shared-formula group cell (`t="shared"`). `ref` is only written on the master cell (see
        // `CellValue::SharedFormula`'s doc comment) — a follower's `<f>` carries just `t`/`si`,
        // matching real Excel's own convention.
        CellValue::SharedFormula {
            formula,
            group_index,
            range,
            cached_value,
        } => {
            let reference = cell_reference(column_index, row_number);
            let style_text = style.map(|value| value.to_string());
            let group_index_text = group_index.to_string();

            let mut start = BytesStart::new("c");
            start.push_attribute(("r", reference.as_str()));
            if let Some(style_text) = &style_text {
                start.push_attribute(("s", style_text.as_str()));
            }
            writer.write_event(Event::Start(start))?;

            let mut f_start = BytesStart::new("f");
            f_start.push_attribute(("t", "shared"));
            f_start.push_attribute(("si", group_index_text.as_str()));
            if let Some(range) = range {
                f_start.push_attribute(("ref", range.as_str()));
            }
            if let Some(formula_text) = formula {
                writer.write_event(Event::Start(f_start))?;
                writer.write_event(Event::Text(BytesText::new(formula_text)))?;
                writer.write_event(Event::End(BytesEnd::new("f")))?;
            } else {
                writer.write_event(Event::Empty(f_start))?;
            }

            if let Some(value) = cached_value {
                let value_text = value.to_string();
                writer.write_event(Event::Start(BytesStart::new("v")))?;
                writer.write_event(Event::Text(BytesText::new(&value_text)))?;
                writer.write_event(Event::End(BytesEnd::new("v")))?;
            }

            writer.write_event(Event::End(BytesEnd::new("c")))?;
        }
    }

    Ok(())
}

/// A cell's `r` attribute (e.g. `"C2"`) from its 0-based column index and 1-based row number.
fn cell_reference(column_index: usize, row_number: usize) -> String {
    format!("{}{row_number}", column_letters(column_index))
}

/// Computes `<dimension>`'s `ref` attribute — the bounding box of every cell a sheet actually
/// claims, in `sheetData` and, if present, its structured table's own `ref` range (which can extend
/// one row past `sheet.rows` for a totals row that's implied rather than stored, see `ExcelTable`'s
/// doc comment). Every row/cell in this crate is anchored at column A, row 1 (see
/// `Sheet.rows`/`Row.cells`'s own doc comments), so the bounding box always starts at `"A1"`. Falls
/// back to `"A1"` alone for a genuinely empty sheet with no table.
fn compute_dimension_ref(sheet: &Sheet) -> String {
    let mut max_row = 0usize;
    let mut max_column_count = 0usize;

    for (row_index, row) in sheet.rows.iter().enumerate() {
        if !row.cells.is_empty() {
            max_row = max_row.max(row_index + 1);
            max_column_count = max_column_count.max(row.cells.len());
        }
    }

    if let Some(table) = &sheet.table {
        if let Some((end_column_index, end_row)) = parse_range_end(&table.range) {
            max_row = max_row.max(end_row);
            max_column_count = max_column_count.max(end_column_index + 1);
        }
    }

    if max_row == 0 || max_column_count == 0 {
        "A1".to_string()
    } else {
        format!("A1:{}{max_row}", column_letters(max_column_count - 1))
    }
}

/// Parses a `"start:end"` range reference's end cell into a 0-based column index and 1-based row
/// number (e.g. `"A1:C10"` → `(2, 10)`) — the inverse of `cell_reference`, used by
/// `compute_dimension_ref`. Returns `None` if `range` isn't in the expected shape.
fn parse_range_end(range: &str) -> Option<(usize, usize)> {
    let (_, end) = range.split_once(':')?;
    let split_at = end.find(|character: char| character.is_ascii_digit())?;
    let (letters, digits) = end.split_at(split_at);
    if letters.is_empty() {
        return None;
    }

    let mut column_index: usize = 0;
    for character in letters.chars() {
        if !character.is_ascii_alphabetic() {
            return None;
        }
        let digit = (character.to_ascii_uppercase() as u8 - b'A' + 1) as usize;
        column_index = column_index * 26 + digit;
    }

    let row = digits.parse::<usize>().ok()?;
    Some((column_index - 1, row))
}

/// Converts an [`ExcelDateTime`] to the serial-day number Excel actually stores for a date/time
/// value (a plain `f64`, the integer part counting days since Excel's own epoch, the fractional
/// part a time-of-day) — see `CellValue::Date`'s doc comment for the scope this supports (proleptic
/// Gregorian calendar, "1900 date system" only).
///
/// Uses Howard Hinnant's well-known `days_from_civil` algorithm (public domain,
/// http://howardhinnant.github.io/date_algorithms.html) to convert the calendar date to a day count
/// relative to the Unix epoch (1970-01-01), then shifts by 25569 — the number of days between
/// Excel's epoch and the Unix epoch *as Excel itself counts them*, which already bakes in Excel's
/// own well-known "1900 was a leap year" bug (it wasn't, but Lotus 1-2-3 had that bug first and
/// Excel deliberately reproduced it for compatibility) — so no separate leap-year-bug correction is
/// needed here, the constant already accounts for it.
fn excel_serial_datetime(date: &ExcelDateTime) -> f64 {
    let days_since_unix_epoch =
        days_from_civil(date.year as i64, date.month as i64, date.day as i64);
    let serial_day = days_since_unix_epoch + 25569;
    let fraction_of_day =
        (date.hour as f64 * 3600.0 + date.minute as f64 * 60.0 + date.second as f64) / 86400.0;
    serial_day as f64 + fraction_of_day
}

/// Howard Hinnant's `days_from_civil`: the number of days since 1970-01-01 for a given
/// proleptic-Gregorian civil date. `y`/`m`/`d` are a full year (e.g. `2026`, not `26`), a 1-based
/// month, and a 1-based day of month.
fn days_from_civil(y: i64, m: i64, d: i64) -> i64 {
    let y = if m <= 2 { y - 1 } else { y };
    let era = if y >= 0 { y } else { y - 399 } / 400;
    let yoe = y - era * 400; // [0, 399]
    let mp = (m + 9) % 12; // [0, 11]
    let doy = (153 * mp + 2) / 5 + d - 1; // [0, 365]
    let doe = yoe * 365 + yoe / 4 - yoe / 100 + doy; // [0, 146096]
    era * 146097 + doe - 719468
}

/// Converts a 0-based column index into Excel's bijective base-26 column letters (`0` → `"A"`, `25`
/// → `"Z"`, `26` → `"AA"`, `27` → `"AB"`..).
fn column_letters(mut index: usize) -> String {
    let mut letters = Vec::new();
    loop {
        let remainder = index % 26;
        letters.push((b'A' + remainder as u8) as char);
        if index < 26 {
            break;
        }
        index = index / 26 - 1;
    }
    letters.iter().rev().collect()
}

/// One resolved font entry in the eventual `xl/styles.xml`'s `<fonts>` collection.
/// `name`/`underline`/`strike` are.
struct FontEntry {
    bold: bool,
    italic: bool,
    size: f64,
    color: String,
    name: String,
    underline: bool,
    strike: bool,
}

/// One resolved fill entry in the eventual `<fills>` collection — the two mandatory entries
/// (`None`/`Gray125`, see this module's own `STYLES_CONTENT_TYPE` doc comment) plus a `Solid`
/// variant for a [`CellFormat::fill_color`]. `Pattern`/`Gradient` are, — see
/// `CellFormat::pattern_fill`'s doc comment for the precedence rule when a format sets more than
/// one fill field.
enum FillEntry {
    None,
    Gray125,
    Solid(String),
    Pattern(PatternFill),
    Gradient(GradientFill),
}

/// One resolved `<border>` entry in the eventual `<borders>` collection. Index 0 is always the
/// mandatory empty border (see `STYLES_CONTENT_TYPE`'s doc comment), matching `fonts`/`fills`'s own
/// "index 0 reserved" convention.
#[derive(Clone, PartialEq)]
struct BorderEntry {
    top: Option<(BorderStyle, String)>,
    bottom: Option<(BorderStyle, String)>,
    left: Option<(BorderStyle, String)>,
    right: Option<(BorderStyle, String)>,
    diagonal: Option<(BorderStyle, String)>,
    diagonal_up: bool,
    diagonal_down: bool,
}

/// One resolved `<xf>` entry in the eventual `<cellXfs>` collection.
/// `wrap_text`/`indent`/`text_rotation`/`shrink_to_fit` and `locked`/`formula_hidden` are.
struct XfEntry {
    num_fmt_id: u32,
    font_id: usize,
    fill_id: usize,
    border_id: usize,
    /// This entry's own `xfId` (index into `cellStyleXfs`) — `0` (the implicit `"Normal"` entry)
    /// unless [`CellFormat::named_style`] resolved to one of `Workbook::named_cell_styles`. Always
    /// `0` on a `cellStyleXfs` entry itself (this crate doesn't model style-to-style chaining).
    xf_id: usize,
    horizontal_alignment: Option<HorizontalAlignment>,
    vertical_alignment: Option<VerticalAlignment>,
    wrap_text: bool,
    indent: Option<u32>,
    text_rotation: Option<u16>,
    shrink_to_fit: bool,
    locked: Option<bool>,
    formula_hidden: Option<bool>,
}

/// A dxf's inline font delta — unlike `FontEntry`, only the attributes a conditional-formatting
/// rule is actually likely to override (bold, italic, color); `size`/`name`/`family` are
/// deliberately not modeled, since a dxf font is a delta applied on top of the cell's own font
/// rather than a full replacement.
struct DxfFontEntry {
    bold: bool,
    italic: bool,
    color: Option<String>,
}

/// One resolved `<dxf>` entry in the eventual `<dxfs>` collection — unlike `XfEntry`, every child
/// is inline/self-contained; it does NOT index into the shared `fonts`/`fills` collections the way
/// `CT_Xf` does.
struct DxfEntry {
    font: Option<DxfFontEntry>,
    num_fmt: Option<(u32, String)>,
    fill_color: Option<String>,
    horizontal_alignment: Option<HorizontalAlignment>,
    vertical_alignment: Option<VerticalAlignment>,
}

/// The fully resolved collections `xl/styles.xml` needs, built from a flat list of distinct
/// [`CellFormat`]s (see `collect_cell_formats`) — mirrors how real Excel's `StylesTable` maintains
/// separate, deduplicated `numFmts`/`fonts`/`fills`/`cellXfs` collections rather than one entry per
/// cell. `cell_xfs[0]` is always the mandatory Excel default (no number format, default font, no
/// fill, no alignment); `cell_xfs[i]` for `i >= 1` corresponds to `cell_formats[i - 1]` — the same
/// indexing `style_index` uses when writing a cell's `s` attribute. `dxfs[i]` corresponds to
/// `dxf_formats[i]` — see `collect_dxfs`'s doc comment for why this is a separate index space with
/// no reserved slot.
struct StylesBuild {
    num_fmts: Vec<(u32, String)>,
    fonts: Vec<FontEntry>,
    fills: Vec<FillEntry>,
    borders: Vec<BorderEntry>,
    cell_xfs: Vec<XfEntry>,
    /// `cellStyleXfs` entries built from `named_cell_styles`, in the same order —
    /// `cell_style_xfs[i]` corresponds to `named_cell_styles[i]`, and is written at `cellStyleXfs`/
    /// `cellStyles` index `i + 1` (index `0` is always the hardcoded `"Normal"` entry, see
    /// `to_styles_xml`).
    cell_style_xfs: Vec<XfEntry>,
    dxfs: Vec<DxfEntry>,
}

/// Converts a [`BorderEdge`] into the `(style, color)` pair `BorderEntry` stores (`None` color
/// resolves to opaque black, Excel's own default).
fn resolve_border_edge(edge: &BorderEdge) -> (BorderStyle, String) {
    (
        edge.style,
        edge.color.clone().unwrap_or_else(|| "FF000000".to_string()),
    )
}

/// Resolves one [`CellFormat`]'s `numFmtId`/`fontId`/`fillId`/`borderId` against the shared,
/// deduplicated `num_fmts`/`fonts`/`fills`/`borders` collections being built up — appending a new
/// entry to whichever collection(s) this format doesn't already match one of. Factored out of
/// `build_styles`'s main loop so the exact same resolution logic (and the exact same shared
/// collections, so e.g. a font reused between an ordinary cell format and a [`NamedCellStyle`]'s
/// own format gets deduplicated too) can also resolve `cellStyleXfs` entries.
fn resolve_format_ids(
    format: &CellFormat,
    num_fmts: &mut Vec<(u32, String)>,
    next_num_fmt_id: &mut u32,
    fonts: &mut Vec<FontEntry>,
    fills: &mut Vec<FillEntry>,
    borders: &mut Vec<BorderEntry>,
) -> (u32, usize, usize, usize) {
    let num_fmt_id = match &format.number_format {
        None => 0,
        Some(code) => match num_fmts.iter().find(|(_, existing)| existing == code) {
            Some((id, _)) => *id,
            None => {
                let id = *next_num_fmt_id;
                *next_num_fmt_id += 1;
                num_fmts.push((id, code.clone()));
                id
            }
        },
    };

    let font_id = if !format.bold
        && !format.italic
        && format.font_size.is_none()
        && format.font_color.is_none()
        && format.font_name.is_none()
        && !format.underline
        && !format.strike
    {
        0
    } else {
        let size = format.font_size.unwrap_or(11.0);
        let color = format
            .font_color
            .clone()
            .unwrap_or_else(|| "FF000000".to_string());
        let name = format
            .font_name
            .clone()
            .unwrap_or_else(|| "Calibri".to_string());
        match fonts.iter().position(|font| {
            font.bold == format.bold
                && font.italic == format.italic
                && font.size == size
                && font.color == color
                && font.name == name
                && font.underline == format.underline
                && font.strike == format.strike
        }) {
            Some(index) => index,
            None => {
                fonts.push(FontEntry {
                    bold: format.bold,
                    italic: format.italic,
                    size,
                    color,
                    name,
                    underline: format.underline,
                    strike: format.strike,
                });
                fonts.len() - 1
            }
        }
    };

    // Gradient, then pattern, then plain solid fill color — see `CellFormat::pattern_fill`'s doc
    // comment for this precedence rule.
    let fill_id =
        if let Some(gradient) = &format.gradient_fill {
            match fills.iter().position(
                |fill| matches!(fill, FillEntry::Gradient(existing) if existing == gradient),
            ) {
                Some(index) => index,
                None => {
                    fills.push(FillEntry::Gradient(gradient.clone()));
                    fills.len() - 1
                }
            }
        } else if let Some(pattern) = &format.pattern_fill {
            match fills.iter().position(
                |fill| matches!(fill, FillEntry::Pattern(existing) if existing == pattern),
            ) {
                Some(index) => index,
                None => {
                    fills.push(FillEntry::Pattern(pattern.clone()));
                    fills.len() - 1
                }
            }
        } else {
            match &format.fill_color {
                None => 0,
                Some(color) => {
                    match fills.iter().position(
                        |fill| matches!(fill, FillEntry::Solid(existing) if existing == color),
                    ) {
                        Some(index) => index,
                        None => {
                            fills.push(FillEntry::Solid(color.clone()));
                            fills.len() - 1
                        }
                    }
                }
            }
        };

    let border_id = if format.border == Border::default() {
        0
    } else {
        let candidate = BorderEntry {
            top: format.border.top.as_ref().map(resolve_border_edge),
            bottom: format.border.bottom.as_ref().map(resolve_border_edge),
            left: format.border.left.as_ref().map(resolve_border_edge),
            right: format.border.right.as_ref().map(resolve_border_edge),
            diagonal: format.border.diagonal.as_ref().map(resolve_border_edge),
            diagonal_up: format.border.diagonal_up,
            diagonal_down: format.border.diagonal_down,
        };
        match borders.iter().position(|existing| existing == &candidate) {
            Some(index) => index,
            None => {
                borders.push(candidate);
                borders.len() - 1
            }
        }
    };

    (num_fmt_id, font_id, fill_id, border_id)
}

fn build_styles(
    cell_formats: &[CellFormat],
    dxf_formats: &[CellFormat],
    named_cell_styles: &[NamedCellStyle],
) -> StylesBuild {
    let mut num_fmts: Vec<(u32, String)> = Vec::new();
    let mut fonts = vec![FontEntry {
        bold: false,
        italic: false,
        size: 11.0,
        color: "FF000000".to_string(),
        name: "Calibri".to_string(),
        underline: false,
        strike: false,
    }];
    let mut fills = vec![FillEntry::None, FillEntry::Gray125];
    let mut borders = vec![BorderEntry {
        top: None,
        bottom: None,
        left: None,
        right: None,
        diagonal: None,
        diagonal_up: false,
        diagonal_down: false,
    }];
    let mut cell_xfs = vec![XfEntry {
        num_fmt_id: 0,
        font_id: 0,
        fill_id: 0,
        border_id: 0,
        xf_id: 0,
        horizontal_alignment: None,
        vertical_alignment: None,
        wrap_text: false,
        indent: None,
        text_rotation: None,
        shrink_to_fit: false,
        locked: None,
        formula_hidden: None,
    }];

    let mut next_num_fmt_id = FIRST_CUSTOM_NUM_FMT_ID;

    for format in cell_formats {
        let (num_fmt_id, font_id, fill_id, border_id) = resolve_format_ids(
            format,
            &mut num_fmts,
            &mut next_num_fmt_id,
            &mut fonts,
            &mut fills,
            &mut borders,
        );

        // Resolves `named_style` to this style's index in `named_cell_styles` —
        // `cellStyleXfs`/`cellStyles` index `0` is always the implicit `"Normal"` entry this crate
        // hardcodes (see `to_styles_xml`), so a named entry found at position `i` in
        // `named_cell_styles` gets `xfId = i + 1`. A name not found here, or `named_style: None`,
        // resolves to `0` (`"Normal"`) — see `CellFormat::named_style`'s doc comment.
        let xf_id = format
            .named_style
            .as_ref()
            .and_then(|name| {
                named_cell_styles
                    .iter()
                    .position(|style| &style.name == name)
            })
            .map(|index| index + 1)
            .unwrap_or(0);

        cell_xfs.push(XfEntry {
            num_fmt_id,
            font_id,
            fill_id,
            border_id,
            xf_id,
            horizontal_alignment: format.horizontal_alignment,
            vertical_alignment: format.vertical_alignment,
            wrap_text: format.wrap_text,
            indent: format.indent,
            text_rotation: format.text_rotation,
            shrink_to_fit: format.shrink_to_fit,
            locked: format.locked,
            formula_hidden: format.formula_hidden,
        });
    }

    // `cellStyleXfs` entries — built from `named_cell_styles`' own formats, sharing the same
    // `fonts`/`fills`/`borders`/`num_fmts` collections as `cellXfs` above (so a font/fill/. reused
    // between an ordinary cell format and a named style is deduplicated too). Each entry's own
    // `xfId` is always left at `0` (this crate doesn't model styles chaining to a *different*
    // parent style, only Excel's flat "one level" gallery).
    let mut cell_style_xfs: Vec<XfEntry> = Vec::new();
    for style in named_cell_styles {
        let (num_fmt_id, font_id, fill_id, border_id) = resolve_format_ids(
            &style.format,
            &mut num_fmts,
            &mut next_num_fmt_id,
            &mut fonts,
            &mut fills,
            &mut borders,
        );
        cell_style_xfs.push(XfEntry {
            num_fmt_id,
            font_id,
            fill_id,
            border_id,
            xf_id: 0,
            horizontal_alignment: style.format.horizontal_alignment,
            vertical_alignment: style.format.vertical_alignment,
            wrap_text: style.format.wrap_text,
            indent: style.format.indent,
            text_rotation: style.format.text_rotation,
            shrink_to_fit: style.format.shrink_to_fit,
            locked: style.format.locked,
            formula_hidden: style.format.formula_hidden,
        });
    }

    // Dxfs share the same `numFmtId` counter (to avoid any possible id collision with cellXfs's
    // custom formats across the whole `styleSheet`) but are never pushed into the shared `num_fmts`
    // Vec — a dxf's `<numFmt>` is inline/self-contained, not an index-reference (see `DxfEntry`'s
    // doc comment).
    let mut dxfs: Vec<DxfEntry> = Vec::new();
    for format in dxf_formats {
        let font = if format.bold || format.italic || format.font_color.is_some() {
            Some(DxfFontEntry {
                bold: format.bold,
                italic: format.italic,
                color: format.font_color.clone(),
            })
        } else {
            None
        };

        let num_fmt = format.number_format.as_ref().map(|code| {
            let id = match num_fmts.iter().find(|(_, existing)| existing == code) {
                Some((id, _)) => *id,
                None => {
                    let id = next_num_fmt_id;
                    next_num_fmt_id += 1;
                    id
                }
            };
            (id, code.clone())
        });

        dxfs.push(DxfEntry {
            font,
            num_fmt,
            fill_color: format.fill_color.clone(),
            horizontal_alignment: format.horizontal_alignment,
            vertical_alignment: format.vertical_alignment,
        });
    }

    StylesBuild {
        num_fmts,
        fonts,
        fills,
        borders,
        cell_xfs,
        cell_style_xfs,
        dxfs,
    }
}

fn border_style_str(style: BorderStyle) -> &'static str {
    match style {
        BorderStyle::Thin => "thin",
        BorderStyle::Medium => "medium",
        BorderStyle::Thick => "thick",
        BorderStyle::Dashed => "dashed",
        BorderStyle::Dotted => "dotted",
        BorderStyle::Double => "double",
        BorderStyle::Hair => "hair",
    }
}

/// Writes one `<border>` edge (`<left>`/`<right>`/`<top>`/`<bottom>`/ `<diagonal>`, `CT_BorderPr`)
/// — a self-closing empty element when `edge` is `None` (no line on this side), or `<name
/// style=".."><color rgb=".."/></name>` otherwise.
fn write_border_edge<W: Write>(
    writer: &mut Writer<W>,
    name: &str,
    edge: &Option<(BorderStyle, String)>,
) -> Result<()> {
    match edge {
        None => {
            writer.write_event(Event::Empty(BytesStart::new(name)))?;
        }
        Some((style, color)) => {
            let mut start = BytesStart::new(name);
            start.push_attribute(("style", border_style_str(*style)));
            writer.write_event(Event::Start(start))?;
            let mut color_start = BytesStart::new("color");
            color_start.push_attribute(("rgb", color.as_str()));
            writer.write_event(Event::Empty(color_start))?;
            writer.write_event(Event::End(BytesEnd::new(name)))?;
        }
    }
    Ok(())
}

/// Maps a [`PatternType`] to its `ST_PatternType` token.
fn pattern_type_str(pattern_type: PatternType) -> &'static str {
    match pattern_type {
        PatternType::Gray125 => "gray125",
        PatternType::Gray0625 => "gray0625",
        PatternType::DarkGray => "darkGray",
        PatternType::MediumGray => "mediumGray",
        PatternType::LightGray => "lightGray",
        PatternType::DarkHorizontal => "darkHorizontal",
        PatternType::DarkVertical => "darkVertical",
        PatternType::DarkDown => "darkDown",
        PatternType::DarkUp => "darkUp",
        PatternType::DarkGrid => "darkGrid",
        PatternType::DarkTrellis => "darkTrellis",
        PatternType::LightHorizontal => "lightHorizontal",
        PatternType::LightVertical => "lightVertical",
        PatternType::LightDown => "lightDown",
        PatternType::LightUp => "lightUp",
        PatternType::LightGrid => "lightGrid",
        PatternType::LightTrellis => "lightTrellis",
    }
}

fn horizontal_alignment_str(alignment: HorizontalAlignment) -> &'static str {
    match alignment {
        HorizontalAlignment::Left => "left",
        HorizontalAlignment::Center => "center",
        HorizontalAlignment::Right => "right",
    }
}

fn vertical_alignment_str(alignment: VerticalAlignment) -> &'static str {
    match alignment {
        VerticalAlignment::Top => "top",
        VerticalAlignment::Center => "center",
        VerticalAlignment::Bottom => "bottom",
    }
}

/// Writes one `<xf>` element (`CT_Xf`) — shared between `cellXfs` and `cellStyleXfs` entries (point
/// 58 made `cellStyleXfs` a dynamic, possibly-multi-entry collection too, no longer just the one
/// hardcoded `"Normal"` line). Always writes `xfId` (unlike `numFmtId`/`fontId`/.., which real
/// Excel also always writes even at `0`, `xfId` is technically optional per `sml.xsd` — this crate
/// writes it unconditionally anyway, matching this function's one previous inline call site's own
/// behavior before this refactor).
fn write_xf<W: Write>(writer: &mut Writer<W>, xf: &XfEntry) -> Result<()> {
    let num_fmt_id_text = xf.num_fmt_id.to_string();
    let font_id_text = xf.font_id.to_string();
    let fill_id_text = xf.fill_id.to_string();
    let border_id_text = xf.border_id.to_string();
    let xf_id_text = xf.xf_id.to_string();

    let mut start = BytesStart::new("xf");
    start.push_attribute(("numFmtId", num_fmt_id_text.as_str()));
    start.push_attribute(("fontId", font_id_text.as_str()));
    start.push_attribute(("fillId", fill_id_text.as_str()));
    start.push_attribute(("borderId", border_id_text.as_str()));
    start.push_attribute(("xfId", xf_id_text.as_str()));
    if xf.num_fmt_id != 0 {
        start.push_attribute(("applyNumberFormat", "1"));
    }
    if xf.border_id != 0 {
        start.push_attribute(("applyBorder", "1"));
    }
    if xf.font_id != 0 {
        start.push_attribute(("applyFont", "1"));
    }
    if xf.fill_id != 0 {
        start.push_attribute(("applyFill", "1"));
    }

    // `CT_Xf`'s sequence: `alignment?`, `protection?`.
    // `wrap_text`/`indent`/`text_rotation`/`shrink_to_fit` and `locked`/`formula_hidden` are.
    let needs_alignment = xf.horizontal_alignment.is_some()
        || xf.vertical_alignment.is_some()
        || xf.wrap_text
        || xf.indent.is_some()
        || xf.text_rotation.is_some()
        || xf.shrink_to_fit;
    let needs_protection = xf.locked.is_some() || xf.formula_hidden.is_some();

    if needs_alignment {
        start.push_attribute(("applyAlignment", "1"));
    }
    if needs_protection {
        start.push_attribute(("applyProtection", "1"));
    }

    if !needs_alignment && !needs_protection {
        writer.write_event(Event::Empty(start))?;
        return Ok(());
    }
    writer.write_event(Event::Start(start))?;

    if needs_alignment {
        let indent_text = xf.indent.map(|value| value.to_string());
        let text_rotation_text = xf.text_rotation.map(|value| value.to_string());
        let mut alignment = BytesStart::new("alignment");
        if let Some(horizontal) = xf.horizontal_alignment {
            alignment.push_attribute(("horizontal", horizontal_alignment_str(horizontal)));
        }
        if let Some(vertical) = xf.vertical_alignment {
            alignment.push_attribute(("vertical", vertical_alignment_str(vertical)));
        }
        if xf.wrap_text {
            alignment.push_attribute(("wrapText", "1"));
        }
        if let Some(indent_text) = &indent_text {
            alignment.push_attribute(("indent", indent_text.as_str()));
        }
        if let Some(text_rotation_text) = &text_rotation_text {
            alignment.push_attribute(("textRotation", text_rotation_text.as_str()));
        }
        if xf.shrink_to_fit {
            alignment.push_attribute(("shrinkToFit", "1"));
        }
        writer.write_event(Event::Empty(alignment))?;
    }

    if needs_protection {
        let mut protection = BytesStart::new("protection");
        if let Some(locked) = xf.locked {
            protection.push_attribute(("locked", if locked { "1" } else { "0" }));
        }
        if let Some(formula_hidden) = xf.formula_hidden {
            protection.push_attribute(("hidden", if formula_hidden { "1" } else { "0" }));
        }
        writer.write_event(Event::Empty(protection))?;
    }

    writer.write_event(Event::End(BytesEnd::new("xf")))?;
    Ok(())
}

/// Serializes `xl/styles.xml` (`CT_Stylesheet`) from the workbook's distinct cell formats — always
/// carries at least the minimum real Excel expects to open a file without a repair prompt (see this
/// module's `STYLES_CONTENT_TYPE` doc comment: one font, two fills in `none`/ `gray125` order, one
/// empty border, one `cellStyleXfs`/`cellXfs` entry), then appends one more `cellXfs` entry (and
/// any `numFmts`/`fonts`/ `fills` entries it needs) per distinct [`CellFormat`] actually used — see
/// `build_styles`. The one font this crate ever writes by default (and reuses for every format that
/// doesn't override any font attribute) is plain black Calibri, referenced by literal RGB rather
/// than a theme color index, since this crate doesn't write `xl/theme/theme1.xml` yet. Also writes
/// `<tableStyles>` from `table_styles`, if any.
fn to_styles_xml(
    cell_formats: &[CellFormat],
    dxf_formats: &[CellFormat],
    table_styles: &[TableStyle],
    named_cell_styles: &[NamedCellStyle],
) -> Result<Vec<u8>> {
    let built = build_styles(cell_formats, dxf_formats, named_cell_styles);
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let mut root = BytesStart::new("styleSheet");
    root.push_attribute(("xmlns", SPREADSHEETML_NAMESPACE));
    writer.write_event(Event::Start(root))?;

    if !built.num_fmts.is_empty() {
        let count = built.num_fmts.len().to_string();
        let mut start = BytesStart::new("numFmts");
        start.push_attribute(("count", count.as_str()));
        writer.write_event(Event::Start(start))?;
        for (id, code) in &built.num_fmts {
            let id_text = id.to_string();
            let mut num_fmt = BytesStart::new("numFmt");
            num_fmt.push_attribute(("numFmtId", id_text.as_str()));
            num_fmt.push_attribute(("formatCode", code.as_str()));
            writer.write_event(Event::Empty(num_fmt))?;
        }
        writer.write_event(Event::End(BytesEnd::new("numFmts")))?;
    }

    let fonts_count = built.fonts.len().to_string();
    let mut fonts_start = BytesStart::new("fonts");
    fonts_start.push_attribute(("count", fonts_count.as_str()));
    writer.write_event(Event::Start(fonts_start))?;
    for font in &built.fonts {
        writer.write_event(Event::Start(BytesStart::new("font")))?;
        if font.bold {
            writer.write_event(Event::Empty(BytesStart::new("b")))?;
        }
        if font.italic {
            writer.write_event(Event::Empty(BytesStart::new("i")))?;
        }
        // `strike`/`u`. `u` is written with no `val` attribute (`CT_UnderlineProperty` defaults to
        // `"single"` when omitted, this crate only ever models a plain single underline, see
        // `CellFormat::underline`'s doc comment).
        if font.strike {
            writer.write_event(Event::Empty(BytesStart::new("strike")))?;
        }
        let size_text = font.size.to_string();
        let mut size = BytesStart::new("sz");
        size.push_attribute(("val", size_text.as_str()));
        writer.write_event(Event::Empty(size))?;
        let mut color = BytesStart::new("color");
        color.push_attribute(("rgb", font.color.as_str()));
        writer.write_event(Event::Empty(color))?;
        if font.underline {
            writer.write_event(Event::Empty(BytesStart::new("u")))?;
        }
        let mut name = BytesStart::new("name");
        name.push_attribute(("val", font.name.as_str()));
        writer.write_event(Event::Empty(name))?;
        let mut family = BytesStart::new("family");
        family.push_attribute(("val", "2"));
        writer.write_event(Event::Empty(family))?;
        writer.write_event(Event::End(BytesEnd::new("font")))?;
    }
    writer.write_event(Event::End(BytesEnd::new("fonts")))?;

    let fills_count = built.fills.len().to_string();
    let mut fills_start = BytesStart::new("fills");
    fills_start.push_attribute(("count", fills_count.as_str()));
    writer.write_event(Event::Start(fills_start))?;
    for fill in &built.fills {
        writer.write_event(Event::Start(BytesStart::new("fill")))?;
        match fill {
            FillEntry::None => {
                let mut pattern = BytesStart::new("patternFill");
                pattern.push_attribute(("patternType", "none"));
                writer.write_event(Event::Empty(pattern))?;
            }
            FillEntry::Gray125 => {
                let mut pattern = BytesStart::new("patternFill");
                pattern.push_attribute(("patternType", "gray125"));
                writer.write_event(Event::Empty(pattern))?;
            }
            FillEntry::Solid(color) => {
                let mut pattern = BytesStart::new("patternFill");
                pattern.push_attribute(("patternType", "solid"));
                writer.write_event(Event::Start(pattern))?;
                let mut fg_color = BytesStart::new("fgColor");
                fg_color.push_attribute(("rgb", color.as_str()));
                writer.write_event(Event::Empty(fg_color))?;
                writer.write_event(Event::End(BytesEnd::new("patternFill")))?;
            }
            // A non-solid pattern fill.
            FillEntry::Pattern(pattern_fill) => {
                let mut pattern = BytesStart::new("patternFill");
                pattern
                    .push_attribute(("patternType", pattern_type_str(pattern_fill.pattern_type)));
                writer.write_event(Event::Start(pattern))?;
                if let Some(fg_color) = &pattern_fill.fg_color {
                    let mut fg = BytesStart::new("fgColor");
                    fg.push_attribute(("rgb", fg_color.as_str()));
                    writer.write_event(Event::Empty(fg))?;
                }
                if let Some(bg_color) = &pattern_fill.bg_color {
                    let mut bg = BytesStart::new("bgColor");
                    bg.push_attribute(("rgb", bg_color.as_str()));
                    writer.write_event(Event::Empty(bg))?;
                }
                writer.write_event(Event::End(BytesEnd::new("patternFill")))?;
            }
            // A linear gradient fill. `degree` is `CT_GradientFill`'s angle attribute (no `type`
            // attribute written, defaulting to `"linear"`, see `GradientFill`'s doc comment).
            FillEntry::Gradient(gradient) => {
                let degree_text = gradient.angle.to_string();
                let mut gradient_start = BytesStart::new("gradientFill");
                gradient_start.push_attribute(("degree", degree_text.as_str()));
                writer.write_event(Event::Start(gradient_start))?;
                for stop in &gradient.stops {
                    let position_text = stop.position.to_string();
                    let mut gs = BytesStart::new("stop");
                    gs.push_attribute(("position", position_text.as_str()));
                    writer.write_event(Event::Start(gs))?;
                    let mut color = BytesStart::new("color");
                    color.push_attribute(("rgb", stop.color.as_str()));
                    writer.write_event(Event::Empty(color))?;
                    writer.write_event(Event::End(BytesEnd::new("stop")))?;
                }
                writer.write_event(Event::End(BytesEnd::new("gradientFill")))?;
            }
        }
        writer.write_event(Event::End(BytesEnd::new("fill")))?;
    }
    writer.write_event(Event::End(BytesEnd::new("fills")))?;

    // Each `<border>` entry's 5 possible children (`left`, `right`, `top`, `bottom`, `diagonal`, in
    // that `CT_Border` sequence order) are each written as `<left style=".."><color
    // rgb=".."/></left>` when set, or a self-closing `<left/>` when not — real Excel always writes
    // all 5, even when empty, so this crate does too (matches the previous hardcoded single empty
    // border this replaces).
    let borders_count = built.borders.len().to_string();
    let mut borders_start = BytesStart::new("borders");
    borders_start.push_attribute(("count", borders_count.as_str()));
    writer.write_event(Event::Start(borders_start))?;
    for border in &built.borders {
        let mut border_start = BytesStart::new("border");
        if border.diagonal_up {
            border_start.push_attribute(("diagonalUp", "1"));
        }
        if border.diagonal_down {
            border_start.push_attribute(("diagonalDown", "1"));
        }
        writer.write_event(Event::Start(border_start))?;
        write_border_edge(&mut writer, "left", &border.left)?;
        write_border_edge(&mut writer, "right", &border.right)?;
        write_border_edge(&mut writer, "top", &border.top)?;
        write_border_edge(&mut writer, "bottom", &border.bottom)?;
        write_border_edge(&mut writer, "diagonal", &border.diagonal)?;
        writer.write_event(Event::End(BytesEnd::new("border")))?;
    }
    writer.write_event(Event::End(BytesEnd::new("borders")))?;

    // `cellStyleXfs` — index `0` is always the implicit `"Normal"` entry this crate hardcodes
    // (matching the fixed single-entry output before point 58 existed); one more entry per
    // [`NamedCellStyle`] in `named_cell_styles`/`built.cell_style_xfs`, same order.
    let cell_style_xfs_count = (1 + built.cell_style_xfs.len()).to_string();
    let mut cell_style_xfs_start = BytesStart::new("cellStyleXfs");
    cell_style_xfs_start.push_attribute(("count", cell_style_xfs_count.as_str()));
    writer.write_event(Event::Start(cell_style_xfs_start))?;
    writer.write_raw(r#"<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>"#)?;
    for xf in &built.cell_style_xfs {
        write_xf(&mut writer, xf)?;
    }
    writer.write_event(Event::End(BytesEnd::new("cellStyleXfs")))?;

    let cell_xfs_count = built.cell_xfs.len().to_string();
    let mut cell_xfs_start = BytesStart::new("cellXfs");
    cell_xfs_start.push_attribute(("count", cell_xfs_count.as_str()));
    writer.write_event(Event::Start(cell_xfs_start))?;
    for xf in &built.cell_xfs {
        write_xf(&mut writer, xf)?;
    }
    writer.write_event(Event::End(BytesEnd::new("cellXfs")))?;

    // `cellStyles` — same index correspondence as `cellStyleXfs` above: index `0` is the hardcoded
    // `"Normal"` entry, index `i + 1` is `named_cell_styles[i]`.
    let cell_styles_count = (1 + named_cell_styles.len()).to_string();
    let mut cell_styles_start = BytesStart::new("cellStyles");
    cell_styles_start.push_attribute(("count", cell_styles_count.as_str()));
    writer.write_event(Event::Start(cell_styles_start))?;
    writer.write_raw(r#"<cellStyle name="Normal" xfId="0" builtinId="0"/>"#)?;
    for (index, style) in named_cell_styles.iter().enumerate() {
        let xf_id_text = (index + 1).to_string();
        let mut cell_style = BytesStart::new("cellStyle");
        cell_style.push_attribute(("name", style.name.as_str()));
        cell_style.push_attribute(("xfId", xf_id_text.as_str()));
        if let Some(builtin_id) = style.builtin_id {
            let builtin_id_text = builtin_id.to_string();
            cell_style.push_attribute(("builtinId", builtin_id_text.as_str()));
        }
        writer.write_event(Event::Empty(cell_style))?;
    }
    writer.write_event(Event::End(BytesEnd::new("cellStyles")))?;

    if !built.dxfs.is_empty() {
        let dxfs_count = built.dxfs.len().to_string();
        let mut dxfs_start = BytesStart::new("dxfs");
        dxfs_start.push_attribute(("count", dxfs_count.as_str()));
        writer.write_event(Event::Start(dxfs_start))?;
        for dxf in &built.dxfs {
            writer.write_event(Event::Start(BytesStart::new("dxf")))?;

            // `CT_Dxf`'s sequence: font, numFmt, fill, alignment (then border/protection/extLst,
            // none of which this crate writes).
            if let Some(font) = &dxf.font {
                writer.write_event(Event::Start(BytesStart::new("font")))?;
                if font.bold {
                    writer.write_event(Event::Empty(BytesStart::new("b")))?;
                }
                if font.italic {
                    writer.write_event(Event::Empty(BytesStart::new("i")))?;
                }
                if let Some(color) = &font.color {
                    let mut color_start = BytesStart::new("color");
                    color_start.push_attribute(("rgb", color.as_str()));
                    writer.write_event(Event::Empty(color_start))?;
                }
                writer.write_event(Event::End(BytesEnd::new("font")))?;
            }

            if let Some((id, code)) = &dxf.num_fmt {
                let id_text = id.to_string();
                let mut num_fmt = BytesStart::new("numFmt");
                num_fmt.push_attribute(("numFmtId", id_text.as_str()));
                num_fmt.push_attribute(("formatCode", code.as_str()));
                writer.write_event(Event::Empty(num_fmt))?;
            }

            if let Some(color) = &dxf.fill_color {
                writer.write_event(Event::Start(BytesStart::new("fill")))?;
                let mut pattern = BytesStart::new("patternFill");
                pattern.push_attribute(("patternType", "solid"));
                writer.write_event(Event::Start(pattern))?;
                // A dxf's solid fill color is `<bgColor>`, not `<fgColor>` as a normal
                // cellXfs-referenced fill is — a real, easy-to-miss Excel/OOXML quirk.
                let mut bg_color = BytesStart::new("bgColor");
                bg_color.push_attribute(("rgb", color.as_str()));
                writer.write_event(Event::Empty(bg_color))?;
                writer.write_event(Event::End(BytesEnd::new("patternFill")))?;
                writer.write_event(Event::End(BytesEnd::new("fill")))?;
            }

            if dxf.horizontal_alignment.is_some() || dxf.vertical_alignment.is_some() {
                let mut alignment = BytesStart::new("alignment");
                if let Some(horizontal) = dxf.horizontal_alignment {
                    alignment.push_attribute(("horizontal", horizontal_alignment_str(horizontal)));
                }
                if let Some(vertical) = dxf.vertical_alignment {
                    alignment.push_attribute(("vertical", vertical_alignment_str(vertical)));
                }
                writer.write_event(Event::Empty(alignment))?;
            }

            writer.write_event(Event::End(BytesEnd::new("dxf")))?;
        }
        writer.write_event(Event::End(BytesEnd::new("dxfs")))?;
    }

    // `<tableStyles>`/`<tableStyle>`/`<tableStyleElement>`. `CT_Stylesheet`'s own sequence puts
    // `tableStyles` right after `dxfs`, before `colors` (not written by this crate, confirmed via
    // `sml.xsd`). Each element's `dxfId` resolves against `dxf_formats` — the very same shared,
    // deduplicated collection a conditional formatting rule's own `dxfId` uses (see
    // `collect_dxfs`).
    if !table_styles.is_empty() {
        let count_text = table_styles.len().to_string();
        let mut table_styles_start = BytesStart::new("tableStyles");
        table_styles_start.push_attribute(("count", count_text.as_str()));
        writer.write_event(Event::Start(table_styles_start))?;
        for style in table_styles {
            let element_count_text = style.elements.len().to_string();
            let mut table_style_start = BytesStart::new("tableStyle");
            table_style_start.push_attribute(("name", style.name.as_str()));
            table_style_start.push_attribute(("pivot", "0"));
            table_style_start.push_attribute(("count", element_count_text.as_str()));
            writer.write_event(Event::Start(table_style_start))?;
            for element in &style.elements {
                let dxf_id = dxf_formats
                    .iter()
                    .position(|format| format == &element.format)
                    .unwrap_or(0);
                let dxf_id_text = dxf_id.to_string();
                let mut element_start = BytesStart::new("tableStyleElement");
                element_start
                    .push_attribute(("type", table_style_element_type_str(element.element_type)));
                element_start.push_attribute(("dxfId", dxf_id_text.as_str()));
                writer.write_event(Event::Empty(element_start))?;
            }
            writer.write_event(Event::End(BytesEnd::new("tableStyle")))?;
        }
        writer.write_event(Event::End(BytesEnd::new("tableStyles")))?;
    }

    writer.write_event(Event::End(BytesEnd::new("styleSheet")))?;

    Ok(writer.into_inner())
}

/// Maps a [`TableStyleElementType`] to its `ST_TableStyleType` token — see that enum's own doc
/// comment for which values are (and aren't) modeled.
fn table_style_element_type_str(element_type: TableStyleElementType) -> &'static str {
    match element_type {
        TableStyleElementType::WholeTable => "wholeTable",
        TableStyleElementType::HeaderRow => "headerRow",
        TableStyleElementType::TotalRow => "totalRow",
        TableStyleElementType::FirstRowStripe => "firstRowStripe",
        TableStyleElementType::SecondRowStripe => "secondRowStripe",
        TableStyleElementType::FirstColumnStripe => "firstColumnStripe",
        TableStyleElementType::SecondColumnStripe => "secondColumnStripe",
        TableStyleElementType::FirstColumn => "firstColumn",
        TableStyleElementType::LastColumn => "lastColumn",
    }
}

/// Serializes `xl/sharedStrings.xml` (`CT_Sst`) from a [`SharedStrings`] collection.
fn to_shared_strings_xml(shared_strings: &SharedStrings) -> Result<Vec<u8>> {
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let mut root = BytesStart::new("sst");
    root.push_attribute(("xmlns", SPREADSHEETML_NAMESPACE));
    let count = shared_strings.total_count.to_string();
    let unique_count = shared_strings.entries.len().to_string();
    root.push_attribute(("count", count.as_str()));
    root.push_attribute(("uniqueCount", unique_count.as_str()));
    writer.write_event(Event::Start(root))?;

    for entry in &shared_strings.entries {
        match entry {
            SharedStringEntry::Plain(text) => {
                writer.write_event(Event::Start(BytesStart::new("si")))?;
                writer.write_event(Event::Start(BytesStart::new("t")))?;
                writer.write_event(Event::Text(BytesText::new(text)))?;
                writer.write_event(Event::End(BytesEnd::new("t")))?;
                writer.write_event(Event::End(BytesEnd::new("si")))?;
            }
            // A rich-text `<si>` holds one `<r>` per run instead of a single `<t>` — extended for
            // `rFont`/`strike`/`u`. `CT_RPrElt`'s sequence: `rFont?`, `charset?`, `family?`, `b?`,
            // `i?`, `strike?`., `color?`, `sz?`, `u?`. — note `rFont` uses that element name, not
            // `name` as `CT_Font` (`xl/styles.xml`'s own font entries) does.
            SharedStringEntry::Rich(runs) => {
                writer.write_event(Event::Start(BytesStart::new("si")))?;
                for run in runs {
                    write_rich_text_run(&mut writer, run)?;
                }
                writer.write_event(Event::End(BytesEnd::new("si")))?;
            }
        }
    }

    writer.write_event(Event::End(BytesEnd::new("sst")))?;

    Ok(writer.into_inner())
}

/// Serializes one `<r>..</r>` run (`CT_RElt`) from a [`RichTextRun`] — shared between
/// `xl/sharedStrings.xml`'s rich-text `<si>` entries and `xl/commentsN.xml`'s rich-text comment
/// bodies — both reuse the exact same `CT_RElt`/`CT_RPrElt` shape. See `to_shared_strings_xml`'s
/// own doc comment on the `Rich` arm for `CT_RPrElt`'s child sequence and the `rFont`-vs-`name`
/// element-name quirk.
fn write_rich_text_run<W: Write>(writer: &mut Writer<W>, run: &RichTextRun) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new("r")))?;
    let has_run_properties = run.bold
        || run.italic
        || run.font_size.is_some()
        || run.font_color.is_some()
        || run.font_name.is_some()
        || run.underline
        || run.strike;
    if has_run_properties {
        writer.write_event(Event::Start(BytesStart::new("rPr")))?;
        if let Some(font_name) = &run.font_name {
            let mut r_font = BytesStart::new("rFont");
            r_font.push_attribute(("val", font_name.as_str()));
            writer.write_event(Event::Empty(r_font))?;
        }
        if run.bold {
            writer.write_event(Event::Empty(BytesStart::new("b")))?;
        }
        if run.italic {
            writer.write_event(Event::Empty(BytesStart::new("i")))?;
        }
        if run.strike {
            writer.write_event(Event::Empty(BytesStart::new("strike")))?;
        }
        if let Some(size) = run.font_size {
            let size_text = size.to_string();
            let mut sz = BytesStart::new("sz");
            sz.push_attribute(("val", size_text.as_str()));
            writer.write_event(Event::Empty(sz))?;
        }
        if let Some(color) = &run.font_color {
            let mut color_start = BytesStart::new("color");
            color_start.push_attribute(("rgb", color.as_str()));
            writer.write_event(Event::Empty(color_start))?;
        }
        if run.underline {
            writer.write_event(Event::Empty(BytesStart::new("u")))?;
        }
        writer.write_event(Event::End(BytesEnd::new("rPr")))?;
    }
    writer.write_event(Event::Start(BytesStart::new("t")))?;
    writer.write_event(Event::Text(BytesText::new(&run.text)))?;
    writer.write_event(Event::End(BytesEnd::new("t")))?;
    writer.write_event(Event::End(BytesEnd::new("r")))?;
    Ok(())
}

/// Serializes `docProps/core.xml` from a workbook's [`DocumentProperties`] — (before this, always
/// empty boilerplate, matching `word-ooxml`'s own before `DocumentProperties` existed there). Every
/// child of `CT_CoreProperties` has `minOccurs="0"`, so an empty `<cp:coreProperties>` root remains
/// schema-valid when every field is `None`.
fn to_core_properties_xml(properties: &DocumentProperties) -> Result<Vec<u8>> {
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let mut root = BytesStart::new("cp:coreProperties");
    root.push_attribute(("xmlns:cp", CORE_PROPERTIES_NAMESPACE));
    root.push_attribute(("xmlns:dc", DC_NAMESPACE));
    root.push_attribute(("xmlns:dcterms", DCTERMS_NAMESPACE));
    root.push_attribute(("xmlns:xsi", XSI_NAMESPACE));

    let has_any = properties.title.is_some()
        || properties.author.is_some()
        || properties.subject.is_some()
        || properties.keywords.is_some();
    if !has_any {
        writer.write_event(Event::Empty(root))?;
        return Ok(writer.into_inner());
    }
    writer.write_event(Event::Start(root))?;

    if let Some(title) = &properties.title {
        write_element_text(&mut writer, "dc:title", title)?;
    }
    if let Some(author) = &properties.author {
        write_element_text(&mut writer, "dc:creator", author)?;
    }
    if let Some(subject) = &properties.subject {
        write_element_text(&mut writer, "dc:subject", subject)?;
    }
    if let Some(keywords) = &properties.keywords {
        write_element_text(&mut writer, "cp:keywords", keywords)?;
    }

    writer.write_event(Event::End(BytesEnd::new("cp:coreProperties")))?;

    Ok(writer.into_inner())
}

/// Serializes `docProps/app.xml` from a workbook's [`DocumentProperties`] — extended by
/// (`Manager`/`HyperlinkBase`). `Application` is always set, to identify this library as the
/// workbook's producer (mirroring `word-ooxml`'s own `to_extended_properties_xml`);
/// `Company`/`Manager`/`HyperlinkBase` are each set only when the matching `DocumentProperties`
/// field is.
fn to_extended_properties_xml(properties: &DocumentProperties) -> Result<Vec<u8>> {
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let mut root = BytesStart::new("Properties");
    root.push_attribute(("xmlns", EXTENDED_PROPERTIES_NAMESPACE));
    writer.write_event(Event::Start(root))?;

    writer.write_event(Event::Start(BytesStart::new("Application")))?;
    writer.write_event(Event::Text(BytesText::new("office-toolkit")))?;
    writer.write_event(Event::End(BytesEnd::new("Application")))?;

    if let Some(company) = &properties.company {
        write_element_text(&mut writer, "Company", company)?;
    }
    if let Some(manager) = &properties.manager {
        write_element_text(&mut writer, "Manager", manager)?;
    }
    if let Some(hyperlink_base) = &properties.hyperlink_base {
        write_element_text(&mut writer, "HyperlinkBase", hyperlink_base)?;
    }

    writer.write_event(Event::End(BytesEnd::new("Properties")))?;

    Ok(writer.into_inner())
}

/// Serializes `docProps/custom.xml` (OPC custom properties) from a workbook's
/// [`DocumentProperties::custom_properties`] — point 56, only called when non-empty. Each entry
/// becomes a `<property>` element (`CT_Property`) with the fixed custom-properties `fmtid`, a `pid`
/// assigned sequentially starting at `FIRST_CUSTOM_PROPERTY_PID` in the caller's own order (not
/// re-sorted), the entry's name, and a single variant-type child chosen from
/// [`CustomPropertyValue`]'s variant (`vt:lpwstr`/`vt:bool`/`vt:i4`/ `vt:r8`) — an exact mirror of
/// `word-ooxml`'s own `to_custom_properties_xml`.
fn to_custom_properties_xml(
    custom_properties: &[(String, CustomPropertyValue)],
) -> Result<Vec<u8>> {
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let mut root = BytesStart::new("Properties");
    root.push_attribute(("xmlns", CUSTOM_PROPERTIES_NAMESPACE));
    root.push_attribute(("xmlns:vt", CUSTOM_PROPERTIES_VT_NAMESPACE));
    writer.write_event(Event::Start(root))?;

    for (index, (name, value)) in custom_properties.iter().enumerate() {
        let pid = FIRST_CUSTOM_PROPERTY_PID + index as i32;

        let mut property = BytesStart::new("property");
        property.push_attribute(("fmtid", CUSTOM_PROPERTY_FMTID));
        property.push_attribute(("pid", pid.to_string().as_str()));
        property.push_attribute(("name", name.as_str()));
        writer.write_event(Event::Start(property))?;

        let (variant_element, text) = match value {
            CustomPropertyValue::Text(text) => ("vt:lpwstr", text.clone()),
            CustomPropertyValue::Bool(value) => (
                "vt:bool",
                if *value {
                    "true".to_string()
                } else {
                    "false".to_string()
                },
            ),
            CustomPropertyValue::Int(value) => ("vt:i4", value.to_string()),
            CustomPropertyValue::Number(value) => ("vt:r8", value.to_string()),
        };
        writer.write_event(Event::Start(BytesStart::new(variant_element)))?;
        writer.write_event(Event::Text(BytesText::new(&text)))?;
        writer.write_event(Event::End(BytesEnd::new(variant_element)))?;

        writer.write_event(Event::End(BytesEnd::new("property")))?;
    }

    writer.write_event(Event::End(BytesEnd::new("Properties")))?;

    Ok(writer.into_inner())
}

/// Serializes `xl/theme/theme1.xml` (`<a:theme>`, `CT_OfficeStyleSheet`). Always the fixed default
/// Office color/font scheme (the same well-known values every new Office document uses out of the
/// box: `dk1`=black, `lt1`=white, `dk2`=`44546A`, `lt2`=`E7E6E6`, `accent1`-`accent6`,
/// `hlink`=`0563C1`, `folHlink`=`954F72`, major/minor Latin font `"Calibri Light"`/`"Calibri"`) —
/// this crate has no notion of a customizable theme model for Excel, unlike `word-ooxml`'s
/// `Theme`/`ColorScheme`/`FontScheme` (out of scope: no cell in this crate's model references a
/// theme color by index, only literal RGB, so the theme's *sole* purpose here is to exist as a
/// schema-complete, real-Excel-shaped part). `fmtScheme` is the same fixed Office "style matrix"
/// boilerplate `word-ooxml`'s own `to_theme_xml` writes verbatim (see that crate's
/// `FIXED_FORMAT_SCHEME_XML` doc comment for why).
fn to_theme_xml() -> Vec<u8> {
    const THEME_XML: &str = concat!(
        r#"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>"#,
        r#"<a:theme xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" name="Office">"#,
        "<a:themeElements>",
        r#"<a:clrScheme name="Office">"#,
        r#"<a:dk1><a:sysClr val="windowText" lastClr="000000"/></a:dk1>"#,
        r#"<a:lt1><a:sysClr val="window" lastClr="FFFFFF"/></a:lt1>"#,
        r#"<a:dk2><a:srgbClr val="44546A"/></a:dk2>"#,
        r#"<a:lt2><a:srgbClr val="E7E6E6"/></a:lt2>"#,
        r#"<a:accent1><a:srgbClr val="4472C4"/></a:accent1>"#,
        r#"<a:accent2><a:srgbClr val="ED7D31"/></a:accent2>"#,
        r#"<a:accent3><a:srgbClr val="A5A5A5"/></a:accent3>"#,
        r#"<a:accent4><a:srgbClr val="FFC000"/></a:accent4>"#,
        r#"<a:accent5><a:srgbClr val="5B9BD5"/></a:accent5>"#,
        r#"<a:accent6><a:srgbClr val="70AD47"/></a:accent6>"#,
        r#"<a:hlink><a:srgbClr val="0563C1"/></a:hlink>"#,
        r#"<a:folHlink><a:srgbClr val="954F72"/></a:folHlink>"#,
        "</a:clrScheme>",
        r#"<a:fontScheme name="Office">"#,
        r#"<a:majorFont><a:latin typeface="Calibri Light"/><a:ea typeface=""/><a:cs typeface=""/></a:majorFont>"#,
        r#"<a:minorFont><a:latin typeface="Calibri"/><a:ea typeface=""/><a:cs typeface=""/></a:minorFont>"#,
        "</a:fontScheme>",
        r#"<a:fmtScheme name="Office">"#,
        "<a:fillStyleLst>",
        r#"<a:solidFill><a:schemeClr val="phClr"/></a:solidFill>"#,
        r#"<a:gradFill rotWithShape="1"><a:gsLst>"#,
        r#"<a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="50000"/><a:satMod val="300000"/></a:schemeClr></a:gs>"#,
        r#"<a:gs pos="35000"><a:schemeClr val="phClr"><a:tint val="37000"/><a:satMod val="300000"/></a:schemeClr></a:gs>"#,
        r#"<a:gs pos="100000"><a:schemeClr val="phClr"><a:tint val="15000"/><a:satMod val="350000"/></a:schemeClr></a:gs>"#,
        r#"</a:gsLst><a:lin ang="16200000" scaled="1"/></a:gradFill>"#,
        r#"<a:gradFill rotWithShape="1"><a:gsLst>"#,
        r#"<a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="100000"/><a:shade val="100000"/><a:satMod val="130000"/></a:schemeClr></a:gs>"#,
        r#"<a:gs pos="100000"><a:schemeClr val="phClr"><a:tint val="50000"/><a:shade val="100000"/><a:satMod val="350000"/></a:schemeClr></a:gs>"#,
        r#"</a:gsLst><a:lin ang="16200000" scaled="0"/></a:gradFill>"#,
        "</a:fillStyleLst>",
        "<a:lnStyleLst>",
        r#"<a:ln w="9525" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"><a:shade val="95000"/><a:satMod val="105000"/></a:schemeClr></a:solidFill><a:prstDash val="solid"/></a:ln>"#,
        r#"<a:ln w="25400" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/></a:ln>"#,
        r#"<a:ln w="38100" cap="flat" cmpd="sng" algn="ctr"><a:solidFill><a:schemeClr val="phClr"/></a:solidFill><a:prstDash val="solid"/></a:ln>"#,
        "</a:lnStyleLst>",
        "<a:effectStyleLst>",
        r#"<a:effectStyle><a:effectLst><a:outerShdw blurRad="40000" dist="20000" dir="5400000" rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="38000"/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle>"#,
        r#"<a:effectStyle><a:effectLst><a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="35000"/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle>"#,
        "<a:effectStyle>",
        r#"<a:effectLst><a:outerShdw blurRad="40000" dist="23000" dir="5400000" rotWithShape="0"><a:srgbClr val="000000"><a:alpha val="35000"/></a:srgbClr></a:outerShdw></a:effectLst>"#,
        r#"<a:scene3d><a:camera prst="orthographicFront"><a:rot lat="0" lon="0" rev="0"/></a:camera><a:lightRig rig="threePt" dir="t"><a:rot lat="0" lon="0" rev="1200000"/></a:lightRig></a:scene3d>"#,
        r#"<a:sp3d><a:bevelT w="63500" h="25400"/></a:sp3d>"#,
        "</a:effectStyle>",
        "</a:effectStyleLst>",
        "<a:bgFillStyleLst>",
        r#"<a:solidFill><a:schemeClr val="phClr"/></a:solidFill>"#,
        r#"<a:gradFill rotWithShape="1"><a:gsLst>"#,
        r#"<a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="40000"/><a:satMod val="350000"/></a:schemeClr></a:gs>"#,
        r#"<a:gs pos="40000"><a:schemeClr val="phClr"><a:tint val="45000"/><a:shade val="99000"/><a:satMod val="350000"/></a:schemeClr></a:gs>"#,
        r#"<a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="20000"/><a:satMod val="255000"/></a:schemeClr></a:gs>"#,
        r#"</a:gsLst><a:path path="circle"><a:fillToRect l="50000" t="-80000" r="50000" b="180000"/></a:path></a:gradFill>"#,
        r#"<a:gradFill rotWithShape="1"><a:gsLst>"#,
        r#"<a:gs pos="0"><a:schemeClr val="phClr"><a:tint val="80000"/><a:satMod val="300000"/></a:schemeClr></a:gs>"#,
        r#"<a:gs pos="100000"><a:schemeClr val="phClr"><a:shade val="30000"/><a:satMod val="200000"/></a:schemeClr></a:gs>"#,
        r#"</a:gsLst><a:path path="circle"><a:fillToRect l="50000" t="50000" r="50000" b="50000"/></a:path></a:gradFill>"#,
        "</a:bgFillStyleLst>",
        "</a:fmtScheme>",
        "</a:themeElements>",
        "</a:theme>",
    );
    THEME_XML.as_bytes().to_vec()
}

/// Serializes `xl/externalLinks/externalLinkN.xml` (`CT_ExternalLink`) from an
/// [`ExternalWorkbookLink`]. `r:id` (on `<externalBook>`) resolves, via this part's own `.rels`
/// (built alongside by `Workbook::write_to`), to the actual external target — always `"rId1"`, this
/// crate only ever writes the one relationship. Storage only: `<sheetDataSet>` (cached cell values
/// from the other workbook) is never written, see `ExternalWorkbookLink`'s doc comment.
fn to_external_link_xml(link: &ExternalWorkbookLink) -> Result<Vec<u8>> {
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let mut root = BytesStart::new("externalLink");
    root.push_attribute(("xmlns", SPREADSHEETML_NAMESPACE));
    writer.write_event(Event::Start(root))?;

    let mut external_book = BytesStart::new("externalBook");
    external_book.push_attribute(("xmlns:r", RELATIONSHIPS_NAMESPACE));
    external_book.push_attribute(("r:id", "rId1"));
    writer.write_event(Event::Start(external_book))?;

    if !link.sheet_names.is_empty() {
        writer.write_event(Event::Start(BytesStart::new("sheetNames")))?;
        for name in &link.sheet_names {
            let mut sheet_name = BytesStart::new("sheetName");
            sheet_name.push_attribute(("val", name.as_str()));
            writer.write_event(Event::Empty(sheet_name))?;
        }
        writer.write_event(Event::End(BytesEnd::new("sheetNames")))?;
    }

    if !link.defined_names.is_empty() {
        writer.write_event(Event::Start(BytesStart::new("definedNames")))?;
        for (name, refers_to) in &link.defined_names {
            let mut defined_name = BytesStart::new("definedName");
            defined_name.push_attribute(("name", name.as_str()));
            defined_name.push_attribute(("refersTo", refers_to.as_str()));
            writer.write_event(Event::Empty(defined_name))?;
        }
        writer.write_event(Event::End(BytesEnd::new("definedNames")))?;
    }

    writer.write_event(Event::End(BytesEnd::new("externalBook")))?;
    writer.write_event(Event::End(BytesEnd::new("externalLink")))?;

    Ok(writer.into_inner())
}

/// Serializes `xl/tables/tableN.xml` (`CT_Table`) from a sheet's [`ExcelTable`]. `table_id` is this
/// table's 1-based, package-wide-unique id (this crate simply reuses the owning sheet's own 1-based
/// position, safe since at most one table per sheet is modeled, see `Sheet.table`'s doc comment).
/// Applies `table.table_style_name`, falling back to the fixed `TableStyleMedium2` built-in style
/// when `None` (this crate's own default since point 15, unchanged by point 50 — see
/// `ExcelTable::table_style_name`'s doc comment).
fn to_table_xml(table: &ExcelTable, table_id: usize) -> Result<Vec<u8>> {
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let table_id_text = table_id.to_string();
    let mut root = BytesStart::new("table");
    root.push_attribute(("xmlns", SPREADSHEETML_NAMESPACE));
    root.push_attribute(("id", table_id_text.as_str()));
    root.push_attribute(("name", table.name.as_str()));
    root.push_attribute(("displayName", table.name.as_str()));
    root.push_attribute(("ref", table.range.as_str()));
    // `totalsRowCount` (not `totalsRowShown` alone) is what actually reserves `table.range`'s last
    // row as the totals row in `CT_Table`'s own schema (`totalsRowShown` only controls whether it's
    // displayed, default `"0"` count means "no totals row at all" regardless of `totalsRowShown`) —
    // Without this attribute, real Excel finds `totalsRowShown="1"` claiming a totals row exists
    // while nothing actually reserves it, and repairs the file by dropping the whole table.
    if table.show_totals_row {
        root.push_attribute(("totalsRowShown", "1"));
        root.push_attribute(("totalsRowCount", "1"));
    } else {
        root.push_attribute(("totalsRowShown", "0"));
    }
    writer.write_event(Event::Start(root))?;

    // The autofilter's own range excludes the totals row (matching real Excel: the dropdown arrows
    // never appear on a totals row).
    let auto_filter_ref = if table.show_totals_row {
        shrink_range_by_one_row(&table.range)
    } else {
        table.range.clone()
    };
    let mut auto_filter = BytesStart::new("autoFilter");
    auto_filter.push_attribute(("ref", auto_filter_ref.as_str()));
    writer.write_event(Event::Empty(auto_filter))?;

    // `CT_Table`'s own sequence puts `sortState` right after `autoFilter`, as its *sibling* (not
    // nested inside it, unlike the worksheet-level case — see `ExcelTable.sort_state`'s doc comment
    // and `write_sort_state`'s own doc comment).
    if let Some(sort_state) = &table.sort_state {
        write_sort_state(&mut writer, sort_state)?;
    }

    let columns_count = table.columns.len().to_string();
    let mut table_columns_start = BytesStart::new("tableColumns");
    table_columns_start.push_attribute(("count", columns_count.as_str()));
    writer.write_event(Event::Start(table_columns_start))?;
    for (index, column) in table.columns.iter().enumerate() {
        write_table_column(&mut writer, index, column)?;
    }
    writer.write_event(Event::End(BytesEnd::new("tableColumns")))?;

    let table_style_name = table
        .table_style_name
        .as_deref()
        .unwrap_or("TableStyleMedium2");
    let mut table_style_info = BytesStart::new("tableStyleInfo");
    table_style_info.push_attribute(("name", table_style_name));
    table_style_info.push_attribute(("showFirstColumn", "0"));
    table_style_info.push_attribute(("showLastColumn", "0"));
    table_style_info.push_attribute(("showRowStripes", "1"));
    table_style_info.push_attribute(("showColumnStripes", "0"));
    writer.write_event(Event::Empty(table_style_info))?;

    writer.write_event(Event::End(BytesEnd::new("table")))?;

    Ok(writer.into_inner())
}

/// Serializes one `<tableColumn>` (`CT_TableColumn`) from a [`TableColumn`] — (plain `name` only),
/// extended for `calculatedColumnFormula`/ `totalsRowFunction`/`totalsRowLabel`/`totalsRowFormula`.
/// `CT_TableColumn`'s child sequence is `calculatedColumnFormula?`, `totalsRowFormula?`.
/// (everything else not modeled).
///
/// A built-in `totals_row_function` (anything but `Custom`) writes only the
/// `totalsRowFunction=".."` attribute, no `<totalsRowFormula>` child at all — real Excel itself
/// never writes one for a built-in function (the function name alone fully determines the
/// `SUBTOTAL(..)` it computes internally). Writing a redundant, auto-synthesized
/// `<totalsRowFormula>` here for every built-in function causes a real Excel installation to reject
/// and strip the whole `<table>` on open. Only `Custom` still writes `<totalsRowFormula>`, since
/// that's the only variant where the formula text isn't implied by the function name.
fn write_table_column<W: Write>(
    writer: &mut Writer<W>,
    index: usize,
    column: &TableColumn,
) -> Result<()> {
    let column_id_text = (index + 1).to_string();
    let mut table_column = BytesStart::new("tableColumn");
    table_column.push_attribute(("id", column_id_text.as_str()));
    table_column.push_attribute(("name", column.name.as_str()));

    // `totalsRowLabel` and `totalsRowFunction` are mutually exclusive on the same column in real
    // Excel's own UI (a totals-row cell either shows a plain text label — e.g. "Total" on the first
    // column — or a computed function, never both at once. A label set alongside a function on the
    // same [`TableColumn`] wins: the function (and any `totals_row_formula`) is simply not written.
    let (totals_row_function, totals_row_formula) = if column.totals_row_label.is_some() {
        (None, None)
    } else {
        let formula = match column.totals_row_function {
            Some(TotalsRowFunction::Custom) => column.totals_row_formula.clone(),
            _ => None,
        };
        (column.totals_row_function, formula)
    };
    if let Some(function) = totals_row_function {
        table_column.push_attribute(("totalsRowFunction", totals_row_function_str(function)));
    }
    if let Some(label) = &column.totals_row_label {
        table_column.push_attribute(("totalsRowLabel", label.as_str()));
    }

    if column.calculated_formula.is_none() && totals_row_formula.is_none() {
        writer.write_event(Event::Empty(table_column))?;
        return Ok(());
    }

    writer.write_event(Event::Start(table_column))?;
    if let Some(formula) = &column.calculated_formula {
        write_element_text(writer, "calculatedColumnFormula", formula)?;
    }
    if let Some(formula) = &totals_row_formula {
        write_element_text(writer, "totalsRowFormula", formula)?;
    }
    writer.write_event(Event::End(BytesEnd::new("tableColumn")))?;
    Ok(())
}

fn totals_row_function_str(function: TotalsRowFunction) -> &'static str {
    match function {
        TotalsRowFunction::Sum => "sum",
        TotalsRowFunction::Average => "average",
        TotalsRowFunction::Count => "count",
        TotalsRowFunction::CountNums => "countNums",
        TotalsRowFunction::Max => "max",
        TotalsRowFunction::Min => "min",
        TotalsRowFunction::StdDev => "stdDev",
        TotalsRowFunction::Var => "var",
        TotalsRowFunction::Custom => "custom",
    }
}

/// Decrements a range reference's ending row by one (e.g. `"A1:D6"` → `"A1:D5"`) — used to exclude
/// a table's totals row from its `<autoFilter>` range. Falls back to returning `range` unchanged if
/// it isn't in the expected `"start:end"` shape or the end row is already `1` (nothing to shrink).
fn shrink_range_by_one_row(range: &str) -> String {
    let Some((start, end)) = range.split_once(':') else {
        return range.to_string();
    };
    let Some(split_at) = end.find(|character: char| character.is_ascii_digit()) else {
        return range.to_string();
    };
    let (letters, digits) = end.split_at(split_at);
    match digits.parse::<usize>() {
        Ok(row) if row > 1 => format!("{start}:{letters}{}", row - 1),
        _ => range.to_string(),
    }
}

/// Serializes `xl/commentsN.xml` (`CT_Comments`) from a sheet's [`CellComment`]s. Authors are
/// deduplicated into `<authors>` in first-appearance order (same principle as this crate's own
/// shared-strings/cell-format deduplication elsewhere), each comment referencing its author by
/// index (`authorId`).
fn to_comments_xml(comments: &[CellComment]) -> Result<Vec<u8>> {
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let mut root = BytesStart::new("comments");
    root.push_attribute(("xmlns", SPREADSHEETML_NAMESPACE));
    writer.write_event(Event::Start(root))?;

    let mut authors: Vec<String> = Vec::new();
    for comment in comments {
        if !authors.contains(&comment.author) {
            authors.push(comment.author.clone());
        }
    }

    writer.write_event(Event::Start(BytesStart::new("authors")))?;
    for author in &authors {
        write_element_text(&mut writer, "author", author)?;
    }
    writer.write_event(Event::End(BytesEnd::new("authors")))?;

    writer.write_event(Event::Start(BytesStart::new("commentList")))?;
    for comment in comments {
        let author_id = authors
            .iter()
            .position(|author| author == &comment.author)
            .unwrap_or(0);
        let author_id_text = author_id.to_string();
        let mut comment_start = BytesStart::new("comment");
        comment_start.push_attribute(("ref", comment.cell.as_str()));
        comment_start.push_attribute(("authorId", author_id_text.as_str()));
        writer.write_event(Event::Start(comment_start))?;
        writer.write_event(Event::Start(BytesStart::new("text")))?;
        // Rich per-run formatting — — falls back to the plain single-run form when `rich_text` is
        // `None`, reusing `write_rich_text_run` (shared with `xl/sharedStrings.xml`'s own rich-text
        // runs).
        match &comment.rich_text {
            Some(runs) => {
                for run in runs {
                    write_rich_text_run(&mut writer, run)?;
                }
            }
            None => {
                writer.write_event(Event::Start(BytesStart::new("r")))?;
                writer.write_event(Event::Start(BytesStart::new("t")))?;
                writer.write_event(Event::Text(BytesText::new(&comment.text)))?;
                writer.write_event(Event::End(BytesEnd::new("t")))?;
                writer.write_event(Event::End(BytesEnd::new("r")))?;
            }
        }
        writer.write_event(Event::End(BytesEnd::new("text")))?;
        writer.write_event(Event::End(BytesEnd::new("comment")))?;
    }
    writer.write_event(Event::End(BytesEnd::new("commentList")))?;

    writer.write_event(Event::End(BytesEnd::new("comments")))?;

    Ok(writer.into_inner())
}

/// Serializes the legacy VML drawing (`xl/drawings/vmlDrawingN.vml`) real Excel still expects
/// alongside `xl/commentsN.xml` for a comment's indicator triangle/popup box to render correctly
/// (see `VML_DRAWING_CONTENT_TYPE`'s doc comment) — one `<v:shape>` per comment, all referencing a
/// single shared `<v:shapetype>`, following the same boilerplate structure real Excel itself
/// generates for this legacy format. Raw string concatenation rather than `xml_core::Writer` — VML
/// is not SpreadsheetML and doesn't need namespace-aware escaping beyond what's already guaranteed
/// by `CellComment.cell`/`author`/`text` not being attacker-controlled in this crate's own
/// tests/examples; a caller passing genuinely untrusted text here should be aware this function
/// does not escape `<`/`>`/`&` inside the comment's row/column-derived shape id, which is always
/// numeric anyway.
fn to_comments_vml(comments: &[CellComment]) -> Vec<u8> {
    let mut vml = String::new();
    vml.push_str(
        r#"<xml xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">
<o:shapelayout v:ext="edit"><o:idmap v:ext="edit" data="1"/></o:shapelayout>
<v:shapetype id="_x0000_t202" coordsize="21600,21600" o:spt="202" path="m,l,21600r21600,l21600,xe">
<v:stroke joinstyle="miter"/>
<v:path gradientshapeok="t" o:connecttype="rect"/>
</v:shapetype>
"#,
    );

    for (index, comment) in comments.iter().enumerate() {
        let shape_id = 1025 + index;
        let (column_index, row_number) = cell_column_and_row(&comment.cell);
        let row_index = row_number.saturating_sub(1);
        vml.push_str(&format!(
            r##"<v:shape id="_x0000_s{shape_id}" type="#_x0000_t202" style='position:absolute;margin-left:59.25pt;margin-top:1.5pt;width:108pt;height:59.25pt;z-index:{index_plus_one};visibility:hidden' fillcolor="#ffffe1" o:insetmode="auto">
<v:fill color2="#ffffe1"/>
<v:shadow on="t" color="black" obscured="t"/>
<v:path o:connecttype="none"/>
<x:ClientData ObjectType="Note">
<x:MoveWithCells/>
<x:SizeWithCells/>
<x:Anchor>{col1}, 15, {row1}, 2, {col2}, 31, {row2}, 4</x:Anchor>
<x:AutoFill>False</x:AutoFill>
<x:Row>{row_index}</x:Row>
<x:Column>{column_index}</x:Column>
</x:ClientData>
</v:shape>
"##,
            shape_id = shape_id,
            index_plus_one = index + 1,
            col1 = column_index,
            row1 = row_index,
            col2 = column_index + 2,
            row2 = row_index + 4,
            row_index = row_index,
            column_index = column_index,
        ));
    }

    vml.push_str("</xml>");
    vml.into_bytes()
}

// ============================================================================,: sheet drawing
// (embedded pictures/charts).
// ============================================================================

/// Serializes one sheet's `xl/drawings/drawingN.xml` (`CT_Drawing`, root `<xdr:wsDr>`) — a
/// `<xdr:twoCellAnchor>` per [`crate::model::DrawingAnchor`], each wrapping either an `<xdr:pic>`
/// or an `<xdr:graphicFrame>` (chart). Every picture/chart referenced from an anchor is also added
/// to `package` here (as `xl/media/imageN.<ext>` / `xl/charts/chartN.xml`), with a matching
/// relationship recorded on `relationships` — the drawing part's own `.rels`, later attached to the
/// `Part` by the caller (see `write_to`'s per-sheet loop). `next_media_index`/`next_chart_index`
/// are threaded through (and bumped) across the whole workbook, not reset per sheet — see their own
/// doc comments in `write_to`.
fn to_drawing_xml(
    drawing: &SheetDrawing,
    relationships: &mut Relationships,
    next_media_index: &mut usize,
    next_chart_index: &mut usize,
    package: &mut Package,
) -> Result<Vec<u8>> {
    let mut writer = Writer::new(Vec::new());

    writer.write_event(Event::Decl(BytesDecl::new(
        "1.0",
        Some("UTF-8"),
        Some("yes"),
    )))?;

    let mut root = BytesStart::new("xdr:wsDr");
    root.push_attribute(("xmlns:xdr", SPREADSHEET_DRAWING_NAMESPACE));
    root.push_attribute(("xmlns:a", DRAWINGML_NAMESPACE));
    root.push_attribute(("xmlns:r", RELATIONSHIPS_NAMESPACE));
    writer.write_event(Event::Start(root))?;

    let mut next_relationship_id = 1usize;

    for (anchor_index, anchor) in drawing.anchors.iter().enumerate() {
        writer.write_event(Event::Start(BytesStart::new("xdr:twoCellAnchor")))?;
        write_cell_anchor_point(&mut writer, "xdr:from", &anchor.from)?;
        write_cell_anchor_point(&mut writer, "xdr:to", &anchor.to)?;

        // The non-visual id (`cNvPr id=".."`) only has to be unique *within this drawing part* — a
        // 1-based counter over this drawing's own anchors is enough.
        let shape_id = (anchor_index + 1) as u32;

        match &anchor.object {
            DrawingObject::Picture(picture) => {
                let relationship_id = format!("rId{next_relationship_id}");
                next_relationship_id += 1;
                let media_index = *next_media_index;
                *next_media_index += 1;
                let entry_name = format!("image{}.{}", media_index, picture.format.extension());
                relationships.add(Relationship {
                    id: relationship_id.clone(),
                    rel_type: IMAGE_RELATIONSHIP_TYPE.to_string(),
                    target: format!("../media/{entry_name}"),
                    target_mode: TargetMode::Internal,
                });
                package.add_part(Part::new(
                    format!("/xl/media/{entry_name}"),
                    picture.format.content_type(),
                    picture.data.clone(),
                ));
                write_sheet_picture(&mut writer, picture, &relationship_id, shape_id)?;
            }
            DrawingObject::Chart(sheet_chart) => {
                let relationship_id = format!("rId{next_relationship_id}");
                next_relationship_id += 1;
                let chart_index = *next_chart_index;
                *next_chart_index += 1;
                relationships.add(Relationship {
                    id: relationship_id.clone(),
                    rel_type: CHART_RELATIONSHIP_TYPE.to_string(),
                    target: format!("../charts/chart{chart_index}.xml"),
                    target_mode: TargetMode::Internal,
                });
                let mut chart_writer = Writer::new(Vec::new());
                chart::write_chart_space(&mut chart_writer, &sheet_chart.chart_space)?;
                package.add_part(Part::new(
                    format!("/xl/charts/chart{chart_index}.xml"),
                    CHART_CONTENT_TYPE,
                    chart_writer.into_inner(),
                ));
                write_sheet_chart(&mut writer, sheet_chart, &relationship_id, shape_id)?;
            }
        }

        // `CT_TwoCellAnchor`'s `clientData` (`CT_AnchorClientData`) is required but every attribute
        // on it is optional with a schema default (`fLocksWithSheet`/`fPrintsWithSheet` both
        // default `true`) — this crate doesn't model either, so a self-closing element with no
        // attributes is schema-valid and matches Excel's own defaults.
        writer.write_event(Event::Empty(BytesStart::new("xdr:clientData")))?;

        writer.write_event(Event::End(BytesEnd::new("xdr:twoCellAnchor")))?;
    }

    writer.write_event(Event::End(BytesEnd::new("xdr:wsDr")))?;

    Ok(writer.into_inner())
}

/// Writes one `<xdr:from>`/`<xdr:to>` marker (`CT_Marker`): `col`/`colOff`/ `row`/`rowOff`, in that
/// schema order.
fn write_cell_anchor_point<W: Write>(
    writer: &mut Writer<W>,
    element_name: &str,
    point: &CellAnchorPoint,
) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new(element_name)))?;

    writer.write_event(Event::Start(BytesStart::new("xdr:col")))?;
    writer.write_event(Event::Text(BytesText::new(&point.column.to_string())))?;
    writer.write_event(Event::End(BytesEnd::new("xdr:col")))?;

    writer.write_event(Event::Start(BytesStart::new("xdr:colOff")))?;
    writer.write_event(Event::Text(BytesText::new(
        &point.column_offset_emu.to_string(),
    )))?;
    writer.write_event(Event::End(BytesEnd::new("xdr:colOff")))?;

    writer.write_event(Event::Start(BytesStart::new("xdr:row")))?;
    writer.write_event(Event::Text(BytesText::new(&point.row.to_string())))?;
    writer.write_event(Event::End(BytesEnd::new("xdr:row")))?;

    writer.write_event(Event::Start(BytesStart::new("xdr:rowOff")))?;
    writer.write_event(Event::Text(BytesText::new(
        &point.row_offset_emu.to_string(),
    )))?;
    writer.write_event(Event::End(BytesEnd::new("xdr:rowOff")))?;

    writer.write_event(Event::End(BytesEnd::new(element_name)))?;
    Ok(())
}

/// Writes one `<xdr:pic>` (`CT_Picture`, the spreadsheet-drawing flavor) —
/// `nvPicPr`/`blipFill`/`spPr`, in that schema order. `blipFill`'s own wrapper is `xdr:blipFill`
/// here (unlike `drawing::write_blip_fill`, which hardcodes `a:blipFill` for its own, different,
/// use as a shape's `spPr` fill choice — see that function's doc comment), so this crate writes the
/// wrapper and the `a:blip`/`a:stretch` children directly rather than reusing that function.
/// `spPr`'s own geometry always writes *something* (the picture's own, when set, otherwise a fixed
/// default rectangle) — real Excel fails to render a picture at all if `spPr` has no geometry
/// element, even with a fill/line present (see this crate's own `write_sheet_picture` for the bug
/// this was caught by); fill/line (if any) are then written directly via
/// `drawing::write_fill`/`write_line`, not through `drawing::write_shape_properties` as a whole
/// (that function alone would skip geometry entirely whenever the caller didn't set one).
fn write_sheet_picture<W: Write>(
    writer: &mut Writer<W>,
    picture: &SheetPicture,
    relationship_id: &str,
    id: u32,
) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new("xdr:pic")))?;

    writer.write_event(Event::Start(BytesStart::new("xdr:nvPicPr")))?;
    let mut c_nv_pr = BytesStart::new("xdr:cNvPr");
    c_nv_pr.push_attribute(("id", id.to_string().as_str()));
    c_nv_pr.push_attribute(("name", picture.name.as_str()));
    writer.write_event(Event::Empty(c_nv_pr))?;
    writer.write_event(Event::Empty(BytesStart::new("xdr:cNvPicPr")))?;
    writer.write_event(Event::End(BytesEnd::new("xdr:nvPicPr")))?;

    writer.write_event(Event::Start(BytesStart::new("xdr:blipFill")))?;
    let mut blip = BytesStart::new("a:blip");
    blip.push_attribute(("r:embed", relationship_id));
    writer.write_event(Event::Empty(blip))?;
    writer.write_event(Event::Start(BytesStart::new("a:stretch")))?;
    writer.write_event(Event::Empty(BytesStart::new("a:fillRect")))?;
    writer.write_event(Event::End(BytesEnd::new("a:stretch")))?;
    writer.write_event(Event::End(BytesEnd::new("xdr:blipFill")))?;

    writer.write_event(Event::Start(BytesStart::new("xdr:spPr")))?;
    // Every real Excel-authored `xdr:pic` this crate's own DrawingML showcase was cross-checked
    // against — genuine Excel output, confirmed by its
    // `a14:hiddenFill`/`a14:hiddenLine`/`a14:useLocalDpi` extensions, a Microsoft-only convention —
    // always carries an `<a:xfrm>` with real off/ext values, even though a `twoCellAnchor`'s own
    // `from`/`to` cell markers are what actually govern the picture's final on-screen
    // position/size. This crate never wrote one at all. Since it's still an open question (pending
    // a real Excel test) whether that absence is what silently hid step 4-8's pictures in the
    // DrawingML showcase once a fill/line was added, or whether a visible custom fill on a picture
    // is simply not something Excel's own rendering path supports at all, this is written
    // defensively to match real Excel's own convention as closely as this crate can without
    // tracking a picture's real EMU size for Excel (unlike `word_ooxml::Image`, `SheetPicture` has
    // none — its sizing is fully anchor-driven) — an explicit, zero-valued `a:xfrm` as a
    // placeholder before a real size is ever assigned.
    writer.write_event(Event::Start(BytesStart::new("a:xfrm")))?;
    let mut off = BytesStart::new("a:off");
    off.push_attribute(("x", "0"));
    off.push_attribute(("y", "0"));
    writer.write_event(Event::Empty(off))?;
    let mut ext = BytesStart::new("a:ext");
    ext.push_attribute(("cx", "0"));
    ext.push_attribute(("cy", "0"));
    writer.write_event(Event::Empty(ext))?;
    writer.write_event(Event::End(BytesEnd::new("a:xfrm")))?;
    // A picture's `xdr:spPr` must also always carry *some* geometry for real Excel to actually
    // render the picture at all: a `spPr` with only a fill/line and no `a:prstGeom`/`a:custGeom` is
    // schema-valid (geometry is optional in `CT_ShapeProperties`) but Excel silently fails to draw
    // the picture in that case (no visible image, just the sheet's own cells showing through). This
    // delegates to `drawing`'s shared helper to resolve geometry-or-default and fill/line, which
    // also covers `shape_properties.effects` (previously never written here, even though it
    // round-tripped correctly on read).
    let empty_properties = drawing::ShapeProperties::default();
    let shape_properties = picture
        .shape_properties
        .as_ref()
        .unwrap_or(&empty_properties);
    drawing::write_geometry_fill_line_effects(writer, shape_properties, "rect")?;
    writer.write_event(Event::End(BytesEnd::new("xdr:spPr")))?;

    writer.write_event(Event::End(BytesEnd::new("xdr:pic")))?;
    Ok(())
}

/// Writes one `<xdr:graphicFrame>` (`CT_GraphicalObjectFrame`) wrapping a `<c:chart r:id=".">` —
/// `nvGraphicFramePr`/`xfrm`/`graphic`, in that schema order. `xfrm` is written with fixed
/// placeholder position/extent (`0,0`/`0,0`): real positioning/sizing is governed by the enclosing
/// `<xdr:twoCellAnchor>`'s `from`/`to` markers, not this element, the same "fixed default for
/// schema-mandatory-but-out-of-scope content" convention already used elsewhere in this workspace
/// (e.g. `w:sectPr`'s fixed page size). Note the element itself is `xdr:xfrm` here, not `a:xfrm` —
/// confirmed via `dml-spreadsheetDrawing.xsd` — so `drawing::write_transform` (which hardcodes
/// `a:xfrm`) cannot be reused.
fn write_sheet_chart<W: Write>(
    writer: &mut Writer<W>,
    sheet_chart: &SheetChart,
    relationship_id: &str,
    id: u32,
) -> Result<()> {
    writer.write_event(Event::Start(BytesStart::new("xdr:graphicFrame")))?;

    writer.write_event(Event::Start(BytesStart::new("xdr:nvGraphicFramePr")))?;
    let mut c_nv_pr = BytesStart::new("xdr:cNvPr");
    c_nv_pr.push_attribute(("id", id.to_string().as_str()));
    c_nv_pr.push_attribute(("name", sheet_chart.name.as_str()));
    writer.write_event(Event::Empty(c_nv_pr))?;
    writer.write_event(Event::Empty(BytesStart::new("xdr:cNvGraphicFramePr")))?;
    writer.write_event(Event::End(BytesEnd::new("xdr:nvGraphicFramePr")))?;

    writer.write_event(Event::Start(BytesStart::new("xdr:xfrm")))?;
    let mut off = BytesStart::new("a:off");
    off.push_attribute(("x", "0"));
    off.push_attribute(("y", "0"));
    writer.write_event(Event::Empty(off))?;
    let mut ext = BytesStart::new("a:ext");
    ext.push_attribute(("cx", "0"));
    ext.push_attribute(("cy", "0"));
    writer.write_event(Event::Empty(ext))?;
    writer.write_event(Event::End(BytesEnd::new("xdr:xfrm")))?;

    writer.write_event(Event::Start(BytesStart::new("a:graphic")))?;
    let mut graphic_data = BytesStart::new("a:graphicData");
    graphic_data.push_attribute(("uri", CHART_NAMESPACE));
    writer.write_event(Event::Start(graphic_data))?;
    let mut c_chart = BytesStart::new("c:chart");
    c_chart.push_attribute(("xmlns:c", CHART_NAMESPACE));
    c_chart.push_attribute(("r:id", relationship_id));
    writer.write_event(Event::Empty(c_chart))?;
    writer.write_event(Event::End(BytesEnd::new("a:graphicData")))?;
    writer.write_event(Event::End(BytesEnd::new("a:graphic")))?;

    writer.write_event(Event::End(BytesEnd::new("xdr:graphicFrame")))?;
    Ok(())
}

/// Extracts a cell reference's (`"C5"`) 0-based column index and 1-based row number — used by
/// `to_comments_vml` to position each comment shape. Tolerant of a malformed reference (returns
/// `(0, 1)`), same forgiving posture as this crate's reader-side equivalent
/// (`reader.rs::column_index_from_reference`).
fn cell_column_and_row(reference: &str) -> (usize, usize) {
    let letters: String = reference
        .chars()
        .take_while(|character| character.is_ascii_alphabetic())
        .collect();
    let digits: String = reference
        .chars()
        .skip_while(|character| character.is_ascii_alphabetic())
        .collect();
    let row_number = digits.parse::<usize>().unwrap_or(1);
    if letters.is_empty() {
        return (0, row_number);
    }
    let mut index: usize = 0;
    for character in letters.chars() {
        let digit = (character.to_ascii_uppercase() as u8 - b'A' + 1) as usize;
        index = index * 26 + digit;
    }
    (index - 1, row_number)
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::model::Row;

    #[test]
    fn writes_column_letters_correctly() {
        assert_eq!(column_letters(0), "A");
        assert_eq!(column_letters(1), "B");
        assert_eq!(column_letters(25), "Z");
        assert_eq!(column_letters(26), "AA");
        assert_eq!(column_letters(27), "AB");
        assert_eq!(column_letters(51), "AZ");
        assert_eq!(column_letters(52), "BA");
        assert_eq!(column_letters(701), "ZZ");
        assert_eq!(column_letters(702), "AAA");
    }

    #[test]
    fn writes_a_cell_reference_from_column_and_row() {
        assert_eq!(cell_reference(0, 1), "A1");
        assert_eq!(cell_reference(2, 5), "C5");
        assert_eq!(cell_reference(26, 11), "AA11");
    }

    #[test]
    fn writes_workbook_with_worksheet_styles_and_shared_strings_parts() {
        let workbook = Workbook::new().with_sheet(
            Sheet::new("Feuil1")
                .with_row(Row::new().with_text("Nombre_1").with_text("Nombre_2"))
                .with_row(Row::new().with_number(10.0).with_number(1.0)),
        );

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        assert!(package.part(MAIN_WORKBOOK_PART_NAME).is_some());
        assert!(package.part("/xl/worksheets/sheet1.xml").is_some());
        assert!(package.part(STYLES_PART_NAME).is_some());
        assert!(package.part(SHARED_STRINGS_PART_NAME).is_some());
        assert!(package.part(CORE_PROPERTIES_PART_NAME).is_some());
        assert!(package.part(EXTENDED_PROPERTIES_PART_NAME).is_some());

        let workbook_xml =
            std::str::from_utf8(&package.part(MAIN_WORKBOOK_PART_NAME).unwrap().data).unwrap();
        assert!(
            workbook_xml.contains(r#"<sheet name="Feuil1" sheetId="1" r:id="rId1"/>"#),
            "{workbook_xml}"
        );

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(
            worksheet_xml.contains(r#"<c r="A1" t="s"><v>0</v></c>"#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"<c r="B1" t="s"><v>1</v></c>"#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"<c r="A2"><v>10</v></c>"#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"<c r="B2"><v>1</v></c>"#),
            "{worksheet_xml}"
        );

        let shared_strings_xml =
            std::str::from_utf8(&package.part(SHARED_STRINGS_PART_NAME).unwrap().data).unwrap();
        assert!(
            shared_strings_xml.contains(r#"count="2" uniqueCount="2""#),
            "{shared_strings_xml}"
        );
        assert!(
            shared_strings_xml.contains("<t>Nombre_1</t>"),
            "{shared_strings_xml}"
        );
        assert!(
            shared_strings_xml.contains("<t>Nombre_2</t>"),
            "{shared_strings_xml}"
        );

        let styles_xml =
            std::str::from_utf8(&package.part(STYLES_PART_NAME).unwrap().data).unwrap();
        assert!(styles_xml.contains(r#"<fills count="2">"#), "{styles_xml}");
        assert!(
            styles_xml.contains(r#"<patternFill patternType="none"/>"#),
            "{styles_xml}"
        );
        assert!(
            styles_xml.contains(r#"<patternFill patternType="gray125"/>"#),
            "{styles_xml}"
        );
        assert!(
            styles_xml.contains(r#"<cellXfs count="1">"#),
            "{styles_xml}"
        );
    }

    #[test]
    fn deduplicates_repeated_text_values_across_the_shared_strings_table() {
        let workbook = Workbook::new().with_sheet(
            Sheet::new("Feuil1")
                .with_row(Row::new().with_text("Répété"))
                .with_row(Row::new().with_text("Répété"))
                .with_row(Row::new().with_text("Unique")),
        );

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let shared_strings_xml =
            std::str::from_utf8(&package.part(SHARED_STRINGS_PART_NAME).unwrap().data).unwrap();
        assert!(
            shared_strings_xml.contains(r#"count="3" uniqueCount="2""#),
            "{shared_strings_xml}"
        );

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(
            worksheet_xml.contains(r#"<c r="A1" t="s"><v>0</v></c>"#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"<c r="A2" t="s"><v>0</v></c>"#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"<c r="A3" t="s"><v>1</v></c>"#),
            "{worksheet_xml}"
        );
    }

    #[test]
    fn omits_a_c_element_entirely_for_an_empty_cell() {
        let workbook = Workbook::new().with_sheet(
            Sheet::new("Feuil1").with_row(
                Row::new()
                    .with_text("A")
                    .with_cell(Cell::new())
                    .with_text("C"),
            ),
        );

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(
            worksheet_xml.contains(r#"<c r="A1" t="s""#),
            "{worksheet_xml}"
        );
        assert!(!worksheet_xml.contains(r#"r="B1""#), "{worksheet_xml}");
        assert!(
            worksheet_xml.contains(r#"<c r="C1" t="s""#),
            "{worksheet_xml}"
        );
    }

    #[test]
    fn writes_a_dynamic_style_with_number_format_font_fill_and_alignment() {
        let format = CellFormat::new()
            .with_number_format("0.00")
            .with_bold(true)
            .with_fill_color("FFFFFF00")
            .with_horizontal_alignment(HorizontalAlignment::Center);

        let workbook = Workbook::new().with_sheet(
            Sheet::new("Feuil1")
                .with_row(Row::new().with_cell(Cell::number(3.5).with_format(format))),
        );

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let styles_xml =
            std::str::from_utf8(&package.part(STYLES_PART_NAME).unwrap().data).unwrap();
        assert!(
            styles_xml.contains(r#"<numFmt numFmtId="164" formatCode="0.00"/>"#),
            "{styles_xml}"
        );
        assert!(styles_xml.contains("<b/>"), "{styles_xml}");
        assert!(
            styles_xml.contains(r#"<fgColor rgb="FFFFFF00"/>"#),
            "{styles_xml}"
        );
        assert!(
            styles_xml.contains(r#"<alignment horizontal="center"/>"#),
            "{styles_xml}"
        );
        assert!(
            styles_xml.contains(r#"<cellXfs count="2">"#),
            "{styles_xml}"
        );

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(
            worksheet_xml.contains(r#"<c r="A1" s="1"><v>3.5</v></c>"#),
            "{worksheet_xml}"
        );
    }

    #[test]
    fn writes_a_styled_but_empty_cell_as_a_self_closing_element() {
        let workbook = Workbook::new().with_sheet(Sheet::new("Feuil1").with_row(
            Row::new().with_cell(Cell::new().with_format(CellFormat::new().with_bold(true))),
        ));

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(
            worksheet_xml.contains(r#"<c r="A1" s="1"/>"#),
            "{worksheet_xml}"
        );
    }

    #[test]
    fn writes_a_formula_cell_with_text_and_cached_value() {
        let workbook = Workbook::new().with_sheet(
            Sheet::new("Feuil1")
                .with_row(Row::new().with_cell(Cell::formula("A1*2").with_cached_value(20.0))),
        );

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(
            worksheet_xml.contains(r#"<c r="A1"><f>A1*2</f><v>20</v></c>"#),
            "{worksheet_xml}"
        );
    }

    #[test]
    fn writes_a_formula_cell_with_no_cached_value_and_no_v_element() {
        let workbook = Workbook::new()
            .with_sheet(Sheet::new("Feuil1").with_row(Row::new().with_formula("A1+1")));

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(
            worksheet_xml.contains(r#"<c r="A1"><f>A1+1</f></c>"#),
            "{worksheet_xml}"
        );
    }

    #[test]
    fn writes_a_cell_is_conditional_formatting_rule_with_its_dxf() {
        use crate::model::ConditionalFormattingRule;

        let format = CellFormat::new()
            .with_bold(true)
            .with_fill_color("FFFFFF00");
        let rule = ConditionalFormattingRule::cell_is(
            "E3:E9",
            ComparisonOperator::GreaterThan,
            "0.5",
            format,
        );

        let workbook =
            Workbook::new().with_sheet(Sheet::new("Feuil1").with_conditional_formatting_rule(rule));

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(
            worksheet_xml.contains(r#"<conditionalFormatting sqref="E3:E9">"#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(
                r#"<cfRule type="cellIs" dxfId="0" priority="1" operator="greaterThan">"#
            ),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains("<formula>0.5</formula>"),
            "{worksheet_xml}"
        );

        let styles_xml =
            std::str::from_utf8(&package.part(STYLES_PART_NAME).unwrap().data).unwrap();
        assert!(styles_xml.contains(r#"<dxfs count="1">"#), "{styles_xml}");
        assert!(styles_xml.contains("<b/>"), "{styles_xml}");
        assert!(
            styles_xml.contains(r#"<bgColor rgb="FFFFFF00"/>"#),
            "{styles_xml}"
        );
    }

    #[test]
    fn writes_an_expression_conditional_formatting_rule_with_two_formulas_for_between() {
        use crate::model::ConditionalFormattingRule;

        let format = CellFormat::new().with_fill_color("FFFF0000");
        let rule =
            ConditionalFormattingRule::cell_is("A1:A10", ComparisonOperator::Between, "1", format)
                .with_second_formula("10");

        let workbook =
            Workbook::new().with_sheet(Sheet::new("Feuil1").with_conditional_formatting_rule(rule));

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(
            worksheet_xml.contains(r#"operator="between""#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains("<formula>1</formula><formula>10</formula>"),
            "{worksheet_xml}"
        );
    }

    #[test]
    fn writes_a_data_validation_list_rule() {
        use crate::model::DataValidationRule;

        let rule = DataValidationRule::list("B2:B20", "\"Oui,Non\"");

        let workbook =
            Workbook::new().with_sheet(Sheet::new("Feuil1").with_data_validation_rule(rule));

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(
            worksheet_xml.contains(r#"<dataValidations count="1">"#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"<dataValidation type="list" allowBlank="1" sqref="B2:B20">"#),
            "{worksheet_xml}"
        );
        // The writer's XML serializer escapes `"` as `&quot;` in text content (valid, if not
        // strictly required, for XML text nodes) — this asserts the escaped form actually produced,
        // not the raw source string.
        assert!(
            worksheet_xml.contains("<formula1>&quot;Oui,Non&quot;</formula1>"),
            "{worksheet_xml}"
        );
    }

    #[test]
    fn writes_a_whole_number_data_validation_rule_with_messages() {
        use crate::model::{DataValidationRule, Message};

        let rule =
            DataValidationRule::whole_number("C2:C20", ComparisonOperator::GreaterThanOrEqual, "0")
                .with_input_message(Message::new("Entrez un nombre positif").with_title("Saisie"))
                .with_error_message(Message::new("Valeur invalide").with_title("Erreur"));

        let workbook =
            Workbook::new().with_sheet(Sheet::new("Feuil1").with_data_validation_rule(rule));

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let worksheet_xml =
            std::str::from_utf8(&package.part("/xl/worksheets/sheet1.xml").unwrap().data).unwrap();
        assert!(worksheet_xml.contains(r#"type="whole""#), "{worksheet_xml}");
        assert!(
            worksheet_xml.contains(r#"operator="greaterThanOrEqual""#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"showInputMessage="1""#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"showErrorMessage="1""#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"promptTitle="Saisie""#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"prompt="Entrez un nombre positif""#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"errorTitle="Erreur""#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains(r#"error="Valeur invalide""#),
            "{worksheet_xml}"
        );
        assert!(
            worksheet_xml.contains("<formula1>0</formula1>"),
            "{worksheet_xml}"
        );
    }

    #[test]
    fn writes_a_workbook_scoped_defined_name() {
        use crate::model::DefinedName;

        let workbook = Workbook::new()
            .with_sheet(Sheet::new("Feuil1"))
            .with_defined_name(DefinedName::new("Plage_Ventes", "Feuil1!$A$1:$C$12"));

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let workbook_xml =
            std::str::from_utf8(&package.part(MAIN_WORKBOOK_PART_NAME).unwrap().data).unwrap();
        assert!(workbook_xml.contains(r#"<definedNames><definedName name="Plage_Ventes">Feuil1!$A$1:$C$12</definedName></definedNames>"#), "{workbook_xml}");
    }

    #[test]
    fn writes_a_sheet_scoped_hidden_defined_name_with_a_comment() {
        use crate::model::DefinedName;

        let defined_name = DefinedName::new("Northwind_Database", "Feuil2!$A$1:$T$47")
            .with_local_sheet_id(1)
            .with_hidden(true)
            .with_comment("Source de donnees externe");

        let workbook = Workbook::new()
            .with_sheet(Sheet::new("Feuil1"))
            .with_sheet(Sheet::new("Feuil2"))
            .with_defined_name(defined_name);

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let workbook_xml =
            std::str::from_utf8(&package.part(MAIN_WORKBOOK_PART_NAME).unwrap().data).unwrap();
        assert!(
            workbook_xml.contains(
                r#"<definedName name="Northwind_Database" localSheetId="1" hidden="1" comment="Source de donnees externe">Feuil2!$A$1:$T$47</definedName>"#
            ),
            "{workbook_xml}"
        );
    }

    #[test]
    fn omits_defined_names_entirely_when_none_are_set() {
        let workbook = Workbook::new().with_sheet(Sheet::new("Feuil1"));

        let buffer = workbook.write_to(std::io::Cursor::new(Vec::new())).unwrap();
        let package = opc::Package::read_from(buffer).unwrap();

        let workbook_xml =
            std::str::from_utf8(&package.part(MAIN_WORKBOOK_PART_NAME).unwrap().data).unwrap();
        assert!(!workbook_xml.contains("definedNames"), "{workbook_xml}");
    }
}