using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.IO.Compression;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Fast.Logic;

public class OpenXmlHelper
{
    /// <summary>
    /// used for reporting
    /// </summary>
    /// <returns>
    /// A list of template formulas that must be passed to PostProcessingForReport.
    /// </returns>
    public static List<TemplateFormulaInfo> FillSheetForReport<T>(
        SpreadsheetDocument doc,
        string sheetName,
        List<T> data)
    {
        var sw = Stopwatch.StartNew();
        var templateFormulas = GetTemplateFormulaInfo(doc, sheetName);

        ClearSheet(doc, sheetName);
        FillSheet(doc, sheetName, data);

        Debug.WriteLine($"FillSheetForReport(): {sw.ElapsedMilliseconds} ms");
        return templateFormulas;
    }

    /// <summary>
    /// used for reporting
    /// </summary>
    /// <returns>
    /// A list of template formulas that must be passed to PostProcessingForReport.
    /// </returns>
    public static List<TemplateFormulaInfo> FillSheetForReport(
        SpreadsheetDocument doc,
        string sheetName,
        DataTable data)
    {
        var sw = Stopwatch.StartNew();
        var templateFormulas = GetTemplateFormulaInfo(doc, sheetName);

        ClearSheet(doc, sheetName);
        FillSheet(doc, sheetName, data);

        Debug.WriteLine($"FillSheetForReport(): {sw.ElapsedMilliseconds} ms");
        return templateFormulas;
    }

    /// <summary>
    /// used for reporting
    /// </summary>
    public static void PostProcessingForReport(
        SpreadsheetDocument doc,
        string sheetName,
        List<TemplateFormulaInfo> templateFormulas,
        int dataRowCount,
        bool freezeFirstRow = true)
    {
        var sw = Stopwatch.StartNew();
        var wsPart = GetOrCreateWorksheetPart(doc, sheetName);
        var sheetData = EnsureSheetDataExists(wsPart);
        ApplyTemplateFormulas(sheetData, templateFormulas, dataRowCount);

        var ws = wsPart.Worksheet;
        if (freezeFirstRow)
        {
            FreezeFirstRow(ws);
        }

        AutoFitColumns(ws, doc.WorkbookPart!);

        CreateNamedRange(doc, sheetName);
        SynchronizeTableDefinitionsOnSheet(doc, sheetName);
        EnableRefreshAllOnOpen(doc);

        SetFullCalculationOnLoad(doc);
        RemoveCalculationChain(doc);
        Debug.WriteLine($"PostProcessingForReport(): {sw.ElapsedMilliseconds} ms");
    }

    public static MemoryStream GetExportFileStream<T>(
        List<T>? items,
        List<string>? columnNames = null)
    {
        var itemsCopy = items?.ToList() ?? new List<T>();
        var stream = new MemoryStream();

        using (var doc = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
        {
            InitializeUniversalStylesheet(doc);

            string sheetName = "Sheet1";
            var ws = AddEmptySheet(doc, sheetName);

            FillSheet(doc, sheetName, itemsCopy, columnNames);
            FreezeFirstRow(ws);
            AddTableToSheet(doc, sheetName);
            AutoFitColumns(ws, doc.WorkbookPart!);

            SaveAllChanges(doc);
        }

        stream.Seek(0, SeekOrigin.Begin);
        return stream;
    }

    public static void FillSheet<T>(
        SpreadsheetDocument doc,
        string sheetName,
        IEnumerable<T> data,
        IEnumerable<string>? columnNames = null)
    {
        var sw = Stopwatch.StartNew();

        var wsPart = GetOrCreateWorksheetPart(doc, sheetName);

        SharedStringTablePart? sstPart = doc.WorkbookPart!.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
        if (sstPart == null)
        {
            sstPart = doc.WorkbookPart.AddNewPart<SharedStringTablePart>();
            sstPart.SharedStringTable = new SharedStringTable();
        }
        var sstCache = new SharedStringCache(sstPart.SharedStringTable);

        var sheetData = EnsureSheetDataExists(wsPart);

        List<string> stringsToRemove = new() { "_id", "_ids", "_id_2", "ID", "IDs", "Id", "Ids", "ReceiptTransports" };

        var allItemProps = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance) ?? Array.Empty<PropertyInfo>();

        PropertyInfo[] selectedProps;

        if (columnNames != null)
        {
            // Only keep requested columns that exist
            selectedProps = columnNames
                .Select(name => allItemProps.FirstOrDefault(p => p.Name.Equals(name, StringComparison.OrdinalIgnoreCase)))
                .Where(p => p != null)
                .ToArray()!;
        }
        else
        {
            // All props except ones matching "stringsToRemove" suffixes
            selectedProps = allItemProps
                .Where(p => !stringsToRemove
                    .Any(suffix => p.Name.EndsWith(suffix, StringComparison.OrdinalIgnoreCase)))
                .ToArray();
        }

        // Map headers to props
        var properties = selectedProps
            .ToDictionary(
                p =>
                {
                    var displayAttr = p.GetCustomAttribute<DisplayAttribute>();
                    return !string.IsNullOrWhiteSpace(displayAttr?.Name)
                        ? displayAttr.Name  // use [Display(Name=...)]
                        : Util.String.GetColDisplayName(p);
                },
                p => p, StringComparer.OrdinalIgnoreCase);

        // Build header row
        var headerRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == 1U);

        var headers = new List<HeaderInfo>();

        if (headerRow == null)
        {
            headerRow = new Row { RowIndex = 1 };
            int colIndex = 1;
            foreach (var prop in selectedProps)
            {
                var displayAttr = prop.GetCustomAttribute<DisplayAttribute>();
                string displayName = !string.IsNullOrWhiteSpace(displayAttr?.Name)
                    ? displayAttr.Name
                    : Util.String.GetColDisplayName(prop);
                string colLetter = GetColumnName(colIndex);

                var cell = new Cell
                {
                    CellReference = colLetter + "1",
                    DataType = CellValues.String,
                    CellValue = new CellValue(displayName),
                };
                headerRow.Append(cell);

                headers.Add(new HeaderInfo
                {
                    Name = displayName,
                    ColumnIndex = colIndex,
                });
                colIndex++;
            }
            sheetData.InsertAt(headerRow, 0);
        }
        else
        {
            // If header row exists, append new ones
            var existingHeaderNames = new HashSet<string>(
                headerRow.Elements<Cell>().Select(c => (GetCellValue(c, doc.WorkbookPart) ?? "")),
                StringComparer.OrdinalIgnoreCase
            );

            int colIndex = headerRow.Elements<Cell>().Count() + 1;
            foreach (var prop in selectedProps)
            {
                var displayAttr = prop.GetCustomAttribute<DisplayAttribute>();
                string displayName = !string.IsNullOrWhiteSpace(displayAttr?.Name)
                    ? displayAttr.Name
                    : Util.String.GetColDisplayName(prop);
                if (!existingHeaderNames.Contains(displayName))
                {
                    string colLetter = GetColumnName(colIndex);
                    var cell = new Cell
                    {
                        CellReference = colLetter + "1",
                        DataType = CellValues.String,
                        CellValue = new CellValue(displayName),
                    };
                    headerRow.Append(cell);
                    colIndex++;
                }
            }

            headers.Clear();
            foreach (var cell in headerRow.Elements<Cell>())
            {
                headers.Add(new HeaderInfo
                {
                    Name = (GetCellValue(cell, doc.WorkbookPart) ?? ""),
                    ColumnIndex = GetColumnIndex(cell.CellReference?.Value ?? ""),
                    StyleIndex = cell.StyleIndex?.Value ?? 0U
                });
            }
        }

        // Fill rows
        uint rowIndex = 2;

        if (data.Count() == 0)
        {
            // add an empty row because tables break without any data rows
            var emptyRow = new Row { RowIndex = rowIndex };
            sheetData.Append(emptyRow);

            SetGeneralColumnFormats(doc, sheetData, headers);
            Debug.WriteLine($"FillSheet<T>(): {sw.ElapsedMilliseconds} ms");
            return;
        }

        var newRows = new List<Row>(data.Count());
        foreach (var record in data)
        {
            var newRow = new Row { RowIndex = rowIndex };
            var newCells = new List<Cell>(headers.Count);
            foreach (var header in headers)
            {
                string colLetter = GetColumnName(header.ColumnIndex);
                Cell newCell;

                if (properties.TryGetValue(header.Name, out var prop))
                {
                    object? value = prop.GetValue(record);
                    newCell = CreateCell(colLetter, rowIndex, value, sstCache, header.StyleIndex);
                }
                else
                {
                    newCell = new Cell { CellReference = colLetter + rowIndex, StyleIndex = header.StyleIndex };
                }
                newCells.Add(newCell);
            }
            newRow.Append(newCells);
            newRows.Add(newRow);
            rowIndex++;
        }
        sheetData.Append(newRows);

        sstCache.Save();

        // Apply number formats from DisplayAttribute.Description (if they exist)
        foreach (var prop in selectedProps)
        {
            var displayAttr = prop.GetCustomAttribute<DisplayAttribute>();
            if (!string.IsNullOrWhiteSpace(displayAttr?.Description))
            {
                var parts = displayAttr.Description.Split(",");
                if (parts.Length >= 4)
                {
                    try
                    {
                        var excelFormat = ConvertKendoNumberFormatToExcelNumberFormat(parts[3]);
                        var displayName = !string.IsNullOrWhiteSpace(displayAttr.Name)
                            ? displayAttr.Name
                            : Util.String.GetColDisplayName(prop);

                        ApplyColumnFormatByHeader(doc, sheetData, headers, displayName, excelFormat, exactMatch: true);
                    }
                    catch
                    {
                        // Just skip invalid formats
                    }
                }
            }
        }

        SetGeneralColumnFormats(doc, sheetData, headers);
        Debug.WriteLine($"FillSheet<T>(): {sw.ElapsedMilliseconds} ms");
    }

    public static void FillSheet(
        SpreadsheetDocument doc,
        string sheetName,
        DataTable data,
        IEnumerable<string>? columnNames = null)
    {
        var sw = Stopwatch.StartNew();

        if (data.Columns.Count == 0)
            return;

        var wsPart = GetOrCreateWorksheetPart(doc, sheetName);

        SharedStringTablePart? sstPart = doc.WorkbookPart!.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
        if (sstPart == null)
        {
            sstPart = doc.WorkbookPart.AddNewPart<SharedStringTablePart>();
            sstPart.SharedStringTable = new SharedStringTable();
        }
        var sstCache = new SharedStringCache(sstPart.SharedStringTable);

        var sheetData = EnsureSheetDataExists(wsPart);

        // If columnNames given, filter datatable columns
        var selectedCols = columnNames != null
            ? columnNames.Where(n => data.Columns.Contains(n)).Select(n => data.Columns[n]!).ToList()
            : data.Columns.Cast<DataColumn>().ToList();

        var headerRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == 1U);

        var headers = new List<HeaderInfo>();

        if (headerRow == null)
        {
            headerRow = new Row { RowIndex = 1 };
            int colIndex = 1;
            foreach (var col in selectedCols)
            {
                string colLetter = GetColumnName(colIndex);
                var cell = new Cell
                {
                    CellReference = colLetter + "1",
                    DataType = CellValues.String,
                    CellValue = new CellValue(col.ColumnName),
                };
                headerRow.Append(cell);

                headers.Add(new HeaderInfo { Name = col.ColumnName, ColumnIndex = colIndex });
                colIndex++;
            }
            sheetData.InsertAt(headerRow, 0);
        }
        else
        {
            var existingHeaderNames = new HashSet<string>(
                headerRow.Elements<Cell>().Select(cell => (GetCellValue(cell, doc.WorkbookPart) ?? "")),
                StringComparer.OrdinalIgnoreCase
            );

            int colIndex = headerRow.Elements<Cell>().Count() + 1;

            var newHeaderNames = (
                from DataColumn col in selectedCols
                where !existingHeaderNames.Contains(col.ColumnName)
                select col.ColumnName
            ).ToList();

            foreach (var headerName in newHeaderNames)
            {
                string colLetter = GetColumnName(colIndex);
                var cell = new Cell
                {
                    CellReference = colLetter + "1",
                    DataType = CellValues.String,
                    CellValue = new CellValue(headerName),
                };
                headerRow.Append(cell);
                colIndex++;
            }

            headers.Clear();
            foreach (var cell in headerRow.Elements<Cell>())
            {
                headers.Add(
                    new HeaderInfo
                    {
                        Name = (GetCellValue(cell, doc.WorkbookPart) ?? ""),
                        ColumnIndex = GetColumnIndex(cell.CellReference?.Value ?? ""),
                        StyleIndex = cell.StyleIndex?.Value ?? 0U,
                    }
                );
            }
        }

        // Fill rows
        uint rowIndex = 2;

        if (data.Rows.Count == 0)
        {
            // add an empty row because tables break without any data rows
            var emptyRow = new Row { RowIndex = rowIndex };
            sheetData.Append(emptyRow);

            SetGeneralColumnFormats(doc, sheetData, headers);
            Debug.WriteLine($"FillSheet(): {sw.ElapsedMilliseconds} ms");
            return;
        }

        var newRows = new List<Row>(data.Rows.Count);
        foreach (DataRow record in data.Rows)
        {
            var newRow = new Row { RowIndex = rowIndex };
            var newCells = new List<Cell>(headers.Count);

            foreach (var header in headers)
            {
                string colLetter = GetColumnName(header.ColumnIndex);
                DataColumn? col = selectedCols.FirstOrDefault(c =>
                    c.ColumnName.Equals(header.Name, StringComparison.OrdinalIgnoreCase));

                object? value = col != null && !record.IsNull(col) ? record[col] : null;
                var newCell = CreateCell(colLetter, rowIndex, value, sstCache, header.StyleIndex);
                newCells.Add(newCell);
            }

            newRow.Append(newCells);
            newRows.Add(newRow);
            rowIndex++;
        }
        sheetData.Append(newRows);

        sstCache.Save();
        SetGeneralColumnFormats(doc, sheetData, headers);
        Debug.WriteLine($"FillSheet(DataTable): {sw.ElapsedMilliseconds} ms");
    }

    public static WorkbookPart GetOrCreateWorkbookPart(SpreadsheetDocument doc)
    {
        var wbPart = doc.WorkbookPart;
        if (wbPart == null)
        {
            wbPart = doc.AddWorkbookPart();
            wbPart.Workbook = new Workbook();
            wbPart.Workbook.Append(new Sheets());
        }
        return wbPart;
    }

    public static WorksheetPart GetOrCreateWorksheetPart(SpreadsheetDocument doc, string sheetName)
    {
        var wsPart = GetWorksheetPartByName(doc, sheetName);
        if (wsPart != null)
            return wsPart;

        var wbPart = doc.WorkbookPart!;
        wsPart = wbPart.AddNewPart<WorksheetPart>();
        wsPart.Worksheet = new Worksheet(new SheetData());

        var sheets = wbPart.Workbook.Sheets ?? wbPart.Workbook.AppendChild(new Sheets());
        uint newSheetId = sheets.Elements<Sheet>()
            .Select(s => (uint)s.SheetId!).DefaultIfEmpty(0U).Max() + 1;
        var newSheet = new Sheet
        {
            Id = wbPart.GetIdOfPart(wsPart),
            SheetId = newSheetId,
            Name = sheetName
        };
        sheets.Append(newSheet);

        return wsPart;
    }

    public static WorkbookStylesPart GetOrCreateStylesPart(SpreadsheetDocument doc)
    {
        var wbPart = GetOrCreateWorkbookPart(doc);

        var stylesPart = wbPart.GetPartsOfType<WorkbookStylesPart>().FirstOrDefault()
            ?? wbPart.AddNewPart<WorkbookStylesPart>();

        return stylesPart;
    }

    public static void SetFullCalculationOnLoad(SpreadsheetDocument doc)
    {
        var wb = doc.WorkbookPart?.Workbook;
        if (wb == null)
            return;

        var calcProps = wb.Elements<CalculationProperties>().FirstOrDefault();
        if (calcProps == null)
        {
            calcProps = new CalculationProperties();
            wb.Append(calcProps);
        }
        calcProps.FullCalculationOnLoad = true;
    }

    public static void RemoveCalculationChain(MemoryStream stream)
    {
        const string calcChainPath = "xl/calcChain.xml";

        using (
            ZipArchive archive = new ZipArchive(
                stream,
                ZipArchiveMode.Update,
                true
            )
        )
        {
            ZipArchiveEntry? calcChainEntry = archive.GetEntry(calcChainPath);

            if (calcChainEntry != null)
            {
                calcChainEntry.Delete();
            }
        }
    }

    public static void RemoveCalculationChain(SpreadsheetDocument doc)
    {
        var wbPart = doc.WorkbookPart;

        if (wbPart == null)
            return;

        var calcChainPart = wbPart.CalculationChainPart;

        if (calcChainPart != null)
        {
            wbPart.DeletePart(calcChainPart);
        }
    }

    public class HeaderInfo
    {
        public required string Name { get; set; }
        public int ColumnIndex { get; set; }
        public uint StyleIndex { get; set; }
    }

    /// <summary>
    /// Sets the number formats for specific columns based on their headers.
    /// </summary>
    private static void SetGeneralColumnFormats(
        SpreadsheetDocument doc,
        SheetData sheetData,
        List<HeaderInfo> headers)
    {
        var sw = Stopwatch.StartNew();
        var dateFormat = "m/d/yyyy";
        ApplyColumnFormatByHeader(doc, sheetData, headers, "day", dateFormat);
        ApplyColumnFormatByHeader(doc, sheetData, headers, "date", dateFormat);
        ApplyColumnFormatByHeader(doc, sheetData, headers, "expiration", dateFormat);

        var monthFormat = "mmm yyyy";
        ApplyColumnFormatByHeader(doc, sheetData, headers, "month", monthFormat);

        var timeFormat = "m/d/yyyy h:mm:ss AM/PM";
        ApplyColumnFormatByHeader(doc, sheetData, headers, "created", timeFormat);
        ApplyColumnFormatByHeader(doc, sheetData, headers, "modified", timeFormat);
        Debug.WriteLine($"SetColumnFormats(): {sw.ElapsedMilliseconds} ms");
    }

    /// <summary>
    /// Sets the number format for all columns whose header contains the given search text.
    /// Call this after you have built the headers and appended all data rows.
    /// </summary>
    private static void ApplyColumnFormatByHeader(
        SpreadsheetDocument doc,
        SheetData sheetData,
        List<HeaderInfo> headers,
        string headerSearchText,
        string numberFormatCode,
        bool exactMatch = false)
    {
        var stylesPart = GetOrCreateStylesPart(doc);

        // add a custom number format if it doesn't exist
        uint formatId = 0U;
        // Excel's first custom number format ID.  built-in Excel number formats use IDs from 0 to 163.
        const uint firstCustomFormatId = 164;

        var numberingFormats = stylesPart.Stylesheet.NumberingFormats
            ?? (stylesPart.Stylesheet.NumberingFormats = new NumberingFormats());
        var cellFormats = stylesPart.Stylesheet.CellFormats
            ?? (stylesPart.Stylesheet.CellFormats = new CellFormats());

        // Look for existing NumberingFormat
        var existingFormat = numberingFormats.Elements<NumberingFormat>()
            .FirstOrDefault(nf => nf.FormatCode?.Value == numberFormatCode);

        if (existingFormat == null)
        {
            formatId = numberingFormats.Elements<NumberingFormat>().Any()
                ? numberingFormats.Elements<NumberingFormat>().Max(nf => nf.NumberFormatId!.Value) + 1
                : firstCustomFormatId;

            numberingFormats.Append(new NumberingFormat
            {
                NumberFormatId = formatId,
                FormatCode = numberFormatCode
            });
            numberingFormats.Count = (uint)numberingFormats.ChildElements.Count;
        }
        else
        {
            formatId = existingFormat.NumberFormatId!;
        }

        // Look for existing CellFormat using this NumberFormatId
        var existingCellFormat = cellFormats.Elements<CellFormat>().FirstOrDefault(cf => cf.NumberFormatId?.Value == formatId);

        uint styleIndex = 0U;
        if (existingCellFormat == null)
        {
            styleIndex = (uint)cellFormats.Count();
            cellFormats.Append(new CellFormat { NumberFormatId = formatId, ApplyNumberFormat = true });
            cellFormats.Count = (uint)cellFormats.Count();
        }
        else
        {
            styleIndex = (uint)cellFormats.Elements<CellFormat>().ToList().IndexOf(existingCellFormat);
        }

        // Apply style to matching columns
        foreach (var header in headers)
        {
            bool isMatch = exactMatch
                ? string.Equals(header.Name, headerSearchText, StringComparison.OrdinalIgnoreCase)
                : header.Name.Contains(headerSearchText, StringComparison.OrdinalIgnoreCase);

            if (isMatch)
            {
                // Skip the header row (RowIndex == 1) to avoid overriding table header styles.
                // We only want to apply this number format to the actual data rows.
                foreach (var row in sheetData.Elements<Row>().Where(r => r.RowIndex?.Value > 1U))
                {
                    var cell = row.Elements<Cell>()
                        .FirstOrDefault(c => GetColumnIndex(c.CellReference?.Value ?? "") == header.ColumnIndex);
                    if (cell != null)
                    {
                        cell.StyleIndex = styleIndex;
                    }
                }
            }
        }
    }

    private static string ConvertKendoNumberFormatToExcelNumberFormat(string kendoNumberFormat)
    {
        return kendoNumberFormat switch
        {
            "{0:c2}" => @"$#,##0.00_);[Red]($#,##0.00)",
            "{0:c3}" => @"$#,##0.000_);[Red]($#,##0.000)",
            "{0:c4}" => @"$#,##0.0000_);[Red]($#,##0.0000)",
            "{0:c5}" => @"$#,##0.00000_);[Red]($#,##0.00000)",
            "{0:c6}" => @"$#,##0.000000_);[Red]($#,##0.000000)",
            "{0:n0}" => @"#,##0",
            "{0:n1}" => @"#,##0.0",
            "{0:n2}" => @"#,##0.00",
            "{0:n3}" => @"#,##0.000",
            "{0:n4}" => @"#,##0.0000",
            "{0:n5}" => @"#,##0.00000",
            "{0:n6}" => @"#,##0.000000",
            "{0:d}" => @"m/d/yyyy",
            "{0:dd}" => @"d",
            "{0:p5}" => @"0.00000%",
            "MMM yyyy" => @"mmm yyyy",
            _ => throw new Exception("number format conversion not found"),
        };
    }

    public static string? GetCellValue(Cell? cell, WorkbookPart? workbookPart)
    {
        if (cell == null || workbookPart == null || cell.CellValue == null)
            return null;

        string value = cell.CellValue.InnerText;

        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            SharedStringTablePart? sstPart = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
            if (sstPart?.SharedStringTable != null && int.TryParse(value, out int ssid))
            {
                return sstPart.SharedStringTable.Elements<SharedStringItem>().ElementAtOrDefault(ssid)?.InnerText;
            }
            return null;
        }
        else
        {
            return value;
        }
    }

    private static void SetCellValue(
        Cell cell,
        object? value,
        SharedStringCache? sstCache = null,
        uint? styleIndex = null,
        CellValues? explicitType = null)
    {
        if (value == null)
        {
            cell.CellValue = null;
            cell.DataType = null;
        }
        else if (explicitType.HasValue)
        {
            cell.DataType = explicitType.Value;
            cell.CellValue = new CellValue(value.ToString() ?? string.Empty);
        }
        else if (value is string strVal)
        {
            if (sstCache != null)
            {
                var strIndex = sstCache.GetStringIndex(strVal).ToString(CultureInfo.InvariantCulture);
                cell.DataType = CellValues.SharedString;
                cell.CellValue = new CellValue(strIndex);
            }
            else
            {
                cell.DataType = CellValues.String;
                cell.CellValue = new CellValue(strVal);
            }
        }
        else if (value is DateTime dtVal)
        {
            cell.CellValue = new CellValue(dtVal.ToOADate().ToString(CultureInfo.InvariantCulture));
        }
        else if (value is DateOnly dateOnlyVal)
        {
            // Convert DateOnly to DateTime (at midnight) and then to OADate.
            var dt = dateOnlyVal.ToDateTime(TimeOnly.MinValue);
            cell.CellValue = new CellValue(dt.ToOADate().ToString(CultureInfo.InvariantCulture));
        }
        else if (value is int or long or decimal or double)
        {
            double dblVal = Convert.ToDouble(value, CultureInfo.InvariantCulture);
            if (double.IsNaN(dblVal) || double.IsInfinity(dblVal))
            {
                cell.CellValue = null;
            }
            else
            {
                cell.DataType = CellValues.Number;
                cell.CellValue = new CellValue(dblVal.ToString(CultureInfo.InvariantCulture));
            }
        }
        else if (value is bool boolVal)
        {
            cell.DataType = CellValues.Boolean;
            cell.CellValue = new CellValue(boolVal ? "1" : "0");
        }
        else
        {
            cell.DataType = CellValues.String;
            cell.CellValue = new CellValue(value.ToString() ?? string.Empty);
        }

        if (styleIndex.HasValue && styleIndex.Value > 0)
        {
            cell.StyleIndex = styleIndex.Value;
        }
    }

    private static Cell CreateCell(
        string header,
        uint row,
        object? value,
        SharedStringCache sstCache,
        uint styleIndex)
    {
        var cell = new Cell { CellReference = header + row };
        SetCellValue(cell, value, sstCache, styleIndex);
        return cell;
    }

    private static WorksheetPart? GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
    {
        IEnumerable<Sheet> sheets = document.WorkbookPart!.Workbook.GetFirstChild<Sheets>()!.Elements<Sheet>()
            .Where(s => s.Name == sheetName);
        if (!sheets.Any())
            return null;

        string relationshipId = sheets.First().Id!.Value!;
        return (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
    }

    /// <summary>
    /// removes all rows and cells, except for the header row
    /// </summary>
    public static void ClearSheet(SpreadsheetDocument doc, string sheetName)
    {
        var wsPart = GetOrCreateWorksheetPart(doc, sheetName);
        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();
        if (sheetData == null)
            return;

        var rowsToRemove = sheetData.Elements<Row>()
            .Where(r => r.RowIndex == null || r.RowIndex.Value != 1U)
            .ToList();

        foreach (var row in rowsToRemove)
        {
            row.Remove();
        }

        DeleteCellsFromFirstEmptyHeader(doc, sheetName);
    }

    /// <summary>
    /// deletes cells on or after the column of the first empty header cell
    /// </summary>
    private static void DeleteCellsFromFirstEmptyHeader(SpreadsheetDocument doc, string sheetName)
    {
        var wsPart = GetOrCreateWorksheetPart(doc, sheetName);
        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();
        if (sheetData == null)
            return;

        var headerRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == 1U);
        if (headerRow == null)
            return;

        int maxColIndex = headerRow.Elements<Cell>()
            .Select(cell => GetColumnIndex(cell.CellReference?.Value ?? ""))
            .DefaultIfEmpty(0)
            .Max();

        // search the header row from column 1 up to maxColIndex for the first missing or empty header cell
        int firstEmptyColIndex = 0;
        for (int col = 1; col <= maxColIndex; col++)
        {
            string colLetter = GetColumnName(col);
            string cellRef = colLetter + "1";
            var cell = headerRow.Elements<Cell>()
                .FirstOrDefault(c => string.Equals(c.CellReference?.Value, cellRef, StringComparison.OrdinalIgnoreCase));
            string? value = GetCellValue(cell, doc.WorkbookPart);

            if (cell == null || string.IsNullOrWhiteSpace(value))
            {
                firstEmptyColIndex = col;
                break;
            }
        }

        if (firstEmptyColIndex == 0)
            return; // no empty header cell found

        // delete cells on or after the column of the first empty header cell
        var cellsToRemove = sheetData.Elements<Row>()
            .SelectMany(row => row.Elements<Cell>()
                .Where(cell => firstEmptyColIndex <= GetColumnIndex(cell.CellReference?.Value ?? "")))
            .ToList();

        foreach (var cell in cellsToRemove)
        {
            cell.Remove();
        }
    }

    public static void CreateNamedRange(SpreadsheetDocument doc, string sheetName)
    {
        var wbPart = doc.WorkbookPart!;
        var sheet = wbPart.Workbook.Sheets?.Elements<Sheet>().FirstOrDefault(s => s.Name == sheetName);
        if (sheet == null)
            throw new Exception($"Sheet '{sheetName}' not found.");

        var wsPart = (WorksheetPart)wbPart.GetPartById(sheet.Id!);

        // find the last used row and column
        var sheetData = wsPart.Worksheet.Elements<SheetData>().First();

        int lastRow = 1;
        int lastCol = 1;
        foreach (var row in sheetData.Elements<Row>())
        {
            if (row.RowIndex != null && row.RowIndex > lastRow)
                lastRow = (int)row.RowIndex.Value;

            foreach (var cell in row.Elements<Cell>())
            {
                int colIndex = GetColumnIndex(cell.CellReference?.Value ?? "");
                if (colIndex > lastCol)
                    lastCol = colIndex;
            }
        }

        string lastColLetter = GetColumnName(lastCol);
        string rangeAddress = $"$A$1:${lastColLetter}${lastRow}";

        var rangeDefintion = $"'{sheetName}'!{rangeAddress}";

        var definedNames = wbPart.Workbook.DefinedNames ?? wbPart.Workbook.AppendChild(new DefinedNames());

        string myRangeName = sheetName.Replace(" ", "") + "_Datasource";
        var existingName = definedNames.Elements<DefinedName>().FirstOrDefault(dn => dn.Name == myRangeName);
        if (existingName == null)
        {
            var definedName = new DefinedName
            {
                Name = myRangeName,
                Text = rangeDefintion
            };
            definedNames.Append(definedName);
        }
        else
        {
            existingName.Text = rangeDefintion;
        }
    }

    private static uint GetRowIndex(string cellReference)
    {
        if (string.IsNullOrEmpty(cellReference))
            return 0;

        var match = Regex.Match(cellReference, @"\d+");
        return match.Success ? uint.Parse(match.Value) : 0;
    }

    /// <summary>
    /// Calculates the column index from a cell reference like "A1" or "BC23".
    /// You can also pass in just the column letters, like "A" or "BC".
    /// </summary>
    /// <returns>The 1-based column index, or 0 if the input is invalid.</returns>
    public static int GetColumnIndex(string cellReference)
    {
        int colIndex = 0;
        foreach (char ch in cellReference)
        {
            if (char.IsLetter(ch))
            {
                colIndex *= 26;
                colIndex += (ch - 'A' + 1);
            }
            else
            {
                break;
            }
        }
        return colIndex;
    }

    /// <summary>
    /// Converts a 1-based column index to its corresponding Excel column name.
    /// </summary>
    /// <param name="columnIndex">
    /// The 1-based column index (e.g., 1 = "A", 26 = "Z", 27 = "AA").
    /// </param>
    /// <returns>
    /// The Excel column name (e.g., "A", "B", ..., "Z", "AA", "AB", ...).
    /// Returns an empty string if <paramref name="columnIndex"/> is less than 1.
    /// </returns>
    public static string GetColumnName(int columnIndex)
    {
        var columnName = string.Empty;
        while (columnIndex > 0)
        {
            columnIndex--;
            columnName = Convert.ToChar('A' + (columnIndex % 26)) + columnName;
            columnIndex /= 26;
        }
        return columnName;
    }

    public static void EnableRefreshAllOnOpen(SpreadsheetDocument doc)
    {
        var wb = doc.WorkbookPart?.Workbook;
        if (wb == null)
            return;


        var wbProps = wb.WorkbookProperties;
        if (wbProps == null)
        {
            wbProps = new WorkbookProperties();
            wb.InsertAt(wbProps, 0);
        }

        wbProps.RefreshAllConnections = new BooleanValue(true);
    }

    public static void SynchronizeTableDefinitionsOnSheet(
        SpreadsheetDocument doc,
        string sheetName)
    {
        var wsPart = GetWorksheetPartByName(doc, sheetName);
        if (wsPart == null)
            return;

        var sheetData = wsPart.Worksheet.Elements<SheetData>().FirstOrDefault();
        if (sheetData == null)
            return;

        uint lastSheetRow = sheetData.Elements<Row>()
            .Select(r => r.RowIndex?.Value ?? 0U)
            .DefaultIfEmpty(1U)
            .Max();

        foreach (var tablePart in wsPart.TableDefinitionParts)
        {
            var table = tablePart.Table;
            if (table.Reference == null || table.TableColumns == null)
                continue;

            string startCellRef = table.Reference.Value?.Split(':')[0] ?? string.Empty;
            if (string.IsNullOrEmpty(startCellRef))
                continue;

            uint headerRowIndex = GetRowIndex(startCellRef);
            var headerRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == headerRowIndex);
            if (headerRow == null)
                continue;

            var headerCells = headerRow.Elements<Cell>().ToList();
            int lastTableColIndex = headerCells
                .Where(c => c.CellReference != null)
                .Select(c => GetColumnIndex(c.CellReference?.Value ?? string.Empty))
                .DefaultIfEmpty(0)
                .Max();

            // rebuild the TableColumns collection to match the sheet's header row
            table.TableColumns.RemoveAllChildren<TableColumn>();
            var uniqueColumnNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            uint columnId = 1;

            var sortedHeaderCells = headerCells
                .Select(c => new { Cell = c, Index = GetColumnIndex(c.CellReference?.Value ?? "") })
                .Where(x => x.Index > 0)
                .OrderBy(x => x.Index)
                .ToList();

            foreach (var headerInfo in sortedHeaderCells)
            {
                var headerName = GetCellValue(headerInfo.Cell, doc.WorkbookPart) ?? $"Column{columnId}";

                string uniqueHeaderName = headerName;
                int duplicateCount = 1;
                while (uniqueColumnNames.Contains(uniqueHeaderName))
                {
                    duplicateCount++;
                    uniqueHeaderName = $"{headerName}{duplicateCount}";
                }
                uniqueColumnNames.Add(uniqueHeaderName);

                var newColumn = new TableColumn { Id = columnId, Name = uniqueHeaderName };
                newColumn.SetAttribute(
                    new OpenXmlAttribute(
                        "xr3", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3",
                        $"{{{Guid.NewGuid().ToString().ToUpper()}}}"
                    )
                );
                table.TableColumns.Append(newColumn);
                columnId++;
            }
            table.TableColumns.Count = (uint)table.TableColumns.ChildElements.Count;

            string endColLetter = GetColumnName(lastTableColIndex);
            string newReference = $"{startCellRef}:{endColLetter}{lastSheetRow}";
            table.Reference.Value = newReference;

            if (table.AutoFilter?.Reference != null)
            {
                table.AutoFilter.Reference.Value = newReference;
            }
        }
    }

    public static void InitializeBasicStylesheet(SpreadsheetDocument doc)
    {
        var stylesPart = GetOrCreateStylesPart(doc);
        stylesPart.Stylesheet = new Stylesheet(
                new Fonts(new Font()),
                new Fills(new Fill()),
                new Borders(new Border()),
                new CellStyleFormats(new CellFormat()),
                new CellFormats(new CellFormat())
            );
    }

    public static void InitializeUniversalStylesheet(SpreadsheetDocument doc)
    {
        var stylesPart = GetOrCreateStylesPart(doc);
        stylesPart.Stylesheet = GetUniversalStylesheet();
    }

    private static Stylesheet GetUniversalStylesheet()
    {
        var stylesheet = new Stylesheet();

        var numberingFormats = new NumberingFormats();
        uint customFormatId = 164;
        var numberFormatsMap = new Dictionary<string, uint>
        {
            { "#,##0.00;-#,##0.00", customFormatId++ },
            { "#,##0;-#,##0", customFormatId++ },
            { "$#,##0.00;($#,##0.00)", customFormatId++ },
            { "$#,##0;($#,##0)", customFormatId++ },
            { "0.00%", customFormatId++ },
            { "mm/dd/yyyy", customFormatId++ },
            { "mmm yyyy", customFormatId++ },
            { "mm/dd/yyyy hh:mm AM/PM", customFormatId++ }
        };

        foreach (var format in numberFormatsMap)
        {
            numberingFormats.Append(
                new NumberingFormat
                {
                    NumberFormatId = format.Value,
                    FormatCode = format.Key
                }
            );
        }
        numberingFormats.Count = (uint)numberingFormats.ChildElements.Count;
        stylesheet.Append(numberingFormats);

        var fonts = new Fonts();
        fonts.Append(
            new Font(
                new FontSize() { Val = 10 },
                new FontName() { Val = "Arial" }
            )
        ); // FontId = 0 (default)
        fonts.Append(new Font(new Bold())); // FontId = 1
        fonts.Append(new Font(new FontSize { Val = 11 }, new Bold())); // FontId = 2
        fonts.Append(
            new Font(new FontSize { Val = 11 }, new Bold(), new Italic())
        ); // FontId = 3
        fonts.Append(
            new Font(new Color { Rgb = HexBinaryValue.FromString("FF008000") })
        ); // FontId = 4
        fonts.Append(
            new Font(
                new Bold(),
                new Color { Rgb = HexBinaryValue.FromString("FF008000") }
            )
        ); // FontId = 5
        fonts.Append(new Font(new FontSize { Val = 10 }, new Bold())); // FontId = 6
        fonts.Count = (uint)fonts.ChildElements.Count;
        stylesheet.Append(fonts);

        var fills = new Fills();
        fills.Append(
            new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }
        );
        fills.Append(
            new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }
        );
        fills.Append(
            new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFADD8E6") } } }
        );
        fills.Append(
            new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFFFFACD") } } }
        );
        fills.Append(
            new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFD3D3D3") } } }
        );
        fills.Count = (uint)fills.ChildElements.Count;
        stylesheet.Append(fills);

        var borders = new Borders();
        borders.Append(new Border());
        borders.Append(
            new Border
            {
                LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin, Color = new Color { Auto = true } },
                RightBorder = new RightBorder { Style = BorderStyleValues.Thin, Color = new Color { Auto = true } },
                TopBorder = new TopBorder { Style = BorderStyleValues.Thin, Color = new Color { Auto = true } },
                BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin, Color = new Color { Auto = true } },
                DiagonalBorder = new DiagonalBorder()
            }
        );
        borders.Append(
            new Border
            {
                LeftBorder = new LeftBorder { Style = BorderStyleValues.Thick, Color = new Color { Auto = true } },
                RightBorder = new RightBorder { Style = BorderStyleValues.Thick, Color = new Color { Auto = true } },
                TopBorder = new TopBorder { Style = BorderStyleValues.Thick, Color = new Color { Auto = true } },
                BottomBorder = new BottomBorder { Style = BorderStyleValues.Thick, Color = new Color { Auto = true } },
                DiagonalBorder = new DiagonalBorder()
            }
        );
        borders.Count = (uint)borders.ChildElements.Count;
        stylesheet.Append(borders);

        var cellStyleFormats = new CellStyleFormats();
        cellStyleFormats.Append(new CellFormat());
        cellStyleFormats.Count = (uint)cellStyleFormats.ChildElements.Count;
        stylesheet.Append(cellStyleFormats);

        var cellFormats = new CellFormats();
        cellFormats.Append(new CellFormat()); // StyleIndex = 0 (default)

        foreach (var format in numberFormatsMap)
        {
            cellFormats.Append(
                new CellFormat { NumberFormatId = format.Value, FontId = 0, FillId = 0, BorderId = 0, ApplyNumberFormat = true }
            ); // StyleIndex = 1-8
        }

        cellFormats.Append(
            new CellFormat { FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true }
        ); // StyleIndex = 9 BoldStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 2, FillId = 0, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Left }, ApplyFont = true, ApplyAlignment = true }
        ); // StyleIndex = 10 BoldSize11StyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 3, FillId = 0, BorderId = 0, Alignment = new Alignment { WrapText = false, Horizontal = HorizontalAlignmentValues.Left }, ApplyFont = true, ApplyAlignment = true }
        ); // StyleIndex = 11 BoldItalicSize11StyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 2, FillId = 2, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Left }, ApplyFont = true, ApplyFill = true, ApplyAlignment = true }
        ); // StyleIndex = 12 BoldLightBlueStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 1, FillId = 3, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Left }, ApplyFont = true, ApplyFill = true, ApplyAlignment = true }
        ); // StyleIndex = 13 BoldLightGoldenrodYellowStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 0, FillId = 4, BorderId = 0, ApplyFill = true }
        ); // StyleIndex = 14 LightGrayBackgroundStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 0, FillId = 0, BorderId = 0, ApplyBorder = false }
        ); // StyleIndex = 15 ThinBorderStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 0, FillId = 0, BorderId = 0, ApplyBorder = false }
        ); // StyleIndex = 16 ThickBorderStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true, ApplyBorder = false }
        ); // StyleIndex = 17 BoldThinBorderStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 0, FillId = 0, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Center, WrapText = true }, ApplyAlignment = true }
        ); // StyleIndex = 18 CenteredWrappedTextStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 5, FillId = 0, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Center, WrapText = true }, ApplyFont = true, ApplyAlignment = true }
        ); // StyleIndex = 19 BoldGreenCenteredWrappedTextStyleIndex
        cellFormats.Append(
            new CellFormat { NumberFormatId = numberFormatsMap["#,##0;-#,##0"], FontId = 0, FillId = 0, BorderId = 0, ApplyNumberFormat = true, ApplyBorder = false }
        ); // StyleIndex = 20 NumberNoDecimalsStyleIndex2
        cellFormats.Append(
            new CellFormat { NumberFormatId = numberFormatsMap["#,##0;-#,##0"], FontId = 0, FillId = 0, BorderId = 0, ApplyNumberFormat = true, ApplyBorder = false }
        );  // StyleIndex = 21 NumberNoDecimalsStyleIndex3
        cellFormats.Append(
            new CellFormat { NumberFormatId = numberFormatsMap["#,##0;-#,##0"], FontId = 0, FillId = 4, BorderId = 0, ApplyNumberFormat = true, ApplyFill = true, ApplyBorder = false }
        ); // StyleIndex = 22 NumberNoDecimalsLightGrayStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 5, FillId = 0, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Center, WrapText = true }, ApplyFont = true, ApplyBorder = false, ApplyAlignment = true }
        ); // StyleIndex = 23 BoldGreenCenteredWrappedTextStyleIndex2
        cellFormats.Append(
            new CellFormat { FontId = 1, FillId = 0, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Left }, ApplyFont = true, ApplyAlignment = true }
        ); // StyleIndex = 24 BoldLeftAlignedStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 6, FillId = 3, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Center }, ApplyFont = true, ApplyFill = true, ApplyAlignment = true }
        ); // StyleIndex = 25 Bold10PtCenteredLightGoldenrodYellowStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 0, FillId = 3, BorderId = 0, ApplyFill = true }
        ); // StyleIndex = 26 LightGoldenrodYellowBackgroundOnlyStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 2, FillId = 3, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Left }, ApplyFont = true, ApplyFill = true, ApplyAlignment = true }
        ); // StyleIndex = 27 BoldSize11YellowBgLeftAlignedStyleIndex
        cellFormats.Append(
            new CellFormat { FontId = 2, FillId = 3, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Center }, ApplyFont = true, ApplyFill = true, ApplyAlignment = true }
        ); // StyleIndex = 28 Bold11PtCenteredLightGoldenrodYellowStyleIndex
        cellFormats.Append(
            new CellFormat { NumberFormatId = numberFormatsMap["#,##0;-#,##0"], FontId = 0, FillId = 0, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Center }, ApplyNumberFormat = true, ApplyBorder = false }
        ); // StyleIndex = 29 NumberNoDecimalsCenteredStyleIndex
        cellFormats.Append(
            new CellFormat { NumberFormatId = numberFormatsMap["#,##0;-#,##0"], FontId = 1, FillId = 0, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Center }, ApplyFont = true, ApplyNumberFormat = true, ApplyBorder = false }
        ); // StyleIndex = 30 NumberNoDecimalsCenteredBoldStyleIndex
        cellFormats.Append(
            new CellFormat { NumberFormatId = numberFormatsMap["#,##0;-#,##0"], ApplyNumberFormat = true, FontId = 0, FillId = 4, BorderId = 0, Alignment = new Alignment { Horizontal = HorizontalAlignmentValues.Center, WrapText = true }, ApplyAlignment = true, ApplyFill = true }
        ); // StyleIndex = 31 NumberNoDecimalsLightGrayBackgroundCenteredStyleIndex
        cellFormats.Append(
            new CellFormat { NumberFormatId = numberFormatsMap["#,##0;-#,##0"], FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true, ApplyNumberFormat = true, ApplyBorder = false }
        ); // StyleIndex = 32 NumberNoDecimalsBoldStyleIndex
        cellFormats.Append(
            new CellFormat { NumberFormatId = numberFormatsMap["#,##0;-#,##0"], ApplyNumberFormat = true, FontId = 0, FillId = 4, BorderId = 0, ApplyFill = true }
        ); // StyleIndex = 33 NumberNoDecimalsLightGrayBackgroundStyleIndex

        cellFormats.Count = (uint)cellFormats.ChildElements.Count;
        stylesheet.Append(cellFormats);

        var cellStyles = new CellStyles();
        cellStyles.Append(
            new CellStyle { Name = "Normal", FormatId = 0, BuiltinId = 0 }
        );
        cellStyles.Count = (uint)cellStyles.ChildElements.Count;
        stylesheet.Append(cellStyles);

        stylesheet.Append(new DifferentialFormats());
        stylesheet.Append(new TableStyles());

        return stylesheet;
    }

    public class TemplateFormulaInfo
    {
        public string HeaderName { get; set; } = string.Empty;
        public string CellReference { get; set; } = string.Empty;
        public string FormulaText { get; set; } = string.Empty;
        public int ColumnIndex { get; set; }
        public uint RowIndex { get; set; }
        public uint? StyleIndex { get; set; }
        public bool IsTableCalculatedColumn { get; set; }
    }

    public static List<TemplateFormulaInfo> GetTemplateFormulaInfo(
        SpreadsheetDocument doc,
        string sheetName)
    {
        var formulas = new List<TemplateFormulaInfo>();

        if (doc.WorkbookPart?.Workbook == null)
        {
            return formulas;
        }

        var wbPart = doc.WorkbookPart;
        var sheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
        if (sheet?.Id?.Value == null)
        {
            return formulas;
        }

        if (!wbPart.TryGetPartById(sheet.Id.Value, out OpenXmlPart? part)
            || part is not WorksheetPart wsPart)
        {
            return formulas;
        }

        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();
        if (sheetData == null)
        {
            return formulas;
        }

        // build a lookup of all calculated column names on this sheet
        var calculatedColumnNamesByTable = new Dictionary<string, HashSet<string>>();
        if (wsPart.TableDefinitionParts != null)
        {
            foreach (var tablePart in wsPart.TableDefinitionParts)
            {
                if (tablePart.Table?.DisplayName == null || tablePart.Table.TableColumns == null)
                    continue;

                var calculatedNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
                foreach (var tableCol in tablePart.Table.TableColumns.Elements<TableColumn>())
                {
                    // if a column has this element, it's a calculated column.
                    if (tableCol.Name != null && tableCol.GetFirstChild<CalculatedColumnFormula>() != null)
                    {
                        calculatedNames.Add(tableCol.Name.Value!);
                    }
                }

                if (calculatedNames.Any())
                {
                    calculatedColumnNamesByTable[tablePart.Table.DisplayName!] = calculatedNames;
                }
            }
        }

        var columnIndexToHeaderName = new Dictionary<int, string>();
        var headerRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == 1U);
        if (headerRow != null)
        {
            foreach (var cell in headerRow.Elements<Cell>().Where(c => c.CellReference?.Value != null))
            {
                int colIndex = GetColumnIndex(cell.CellReference!.Value!);
                string headerText = GetCellValue(cell, wbPart) ?? $"Column_{colIndex}";
                columnIndexToHeaderName[colIndex] = headerText;
            }
        }

        var dataRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == 2U);
        if (dataRow == null)
        {
            return formulas;
        }

        var sharedFormulaMasterCells = new Dictionary<uint, Cell>();
        foreach (var cell in dataRow.Elements<Cell>())
        {
            var sharedFormulaIndex = cell.CellFormula?.SharedIndex?.Value;
            var formulaText = cell.CellFormula?.Text;

            var isFormulaMasterCell = sharedFormulaIndex.HasValue && !string.IsNullOrEmpty(formulaText);
            if (isFormulaMasterCell)
            {
                sharedFormulaMasterCells[sharedFormulaIndex!.Value] = cell;
            }
        }

        foreach (var cell in dataRow.Elements<Cell>())
        {
            var cellRef = cell.CellReference?.Value;

            if (cellRef == null)
                continue;

            var formulaText = cell.CellFormula?.Text;
            var sharedFormulaIndex = cell.CellFormula?.SharedIndex?.Value;

            Cell? formulaMasterCell = null;
            var isChildFormula = sharedFormulaIndex.HasValue
                && string.IsNullOrEmpty(formulaText)
                && sharedFormulaMasterCells.TryGetValue(sharedFormulaIndex.Value, out formulaMasterCell);

            if (isChildFormula)
            {
                formulaText = AdjustSharedFormula(formulaMasterCell!, cellRef);
            }

            if (string.IsNullOrEmpty(formulaText))
                continue;

            var colIndex = GetColumnIndex(cellRef);

            string headerName = columnIndexToHeaderName.TryGetValue(colIndex, out var name)
                ? name
                : $"Column_{colIndex}";

            // check if this column is a table calculated column
            var isTableCalculatedColumn = false;
            var containingTable = wsPart.TableDefinitionParts?
                .FirstOrDefault(tdp => IsCellInTable(cellRef, tdp.Table?.Reference?.Value));

            if (containingTable?.Table?.DisplayName != null)
            {
                string tableName = containingTable.Table.DisplayName!;
                if (calculatedColumnNamesByTable.TryGetValue(tableName, out var calculatedNames)
                    && calculatedNames.Contains(headerName))
                {
                    isTableCalculatedColumn = true;
                }
            }

            formulas.Add(
                new TemplateFormulaInfo
                {
                    HeaderName = headerName,
                    CellReference = cellRef,
                    FormulaText = formulaText,
                    ColumnIndex = colIndex,
                    RowIndex = dataRow.RowIndex!.Value, // should be 2
                    StyleIndex = cell.StyleIndex?.Value,
                    IsTableCalculatedColumn = isTableCalculatedColumn
                }
            );
        }

        return formulas;
    }

    /// <summary>
    /// adjusts a shared formula for a given target cell
    /// </summary>
    private static string AdjustSharedFormula(Cell masterCell, string targetCellRef)
    {
        var baseFormula = masterCell?.CellFormula?.Text;

        if (baseFormula == null)
            return string.Empty;

        var masterRef = masterCell?.CellReference?.Value ?? string.Empty;
        var masterCol = GetColumnIndex(masterRef);
        var masterRow = GetRowIndex(masterRef);

        var targetCol = GetColumnIndex(targetCellRef);
        var targetRow = GetRowIndex(targetCellRef);

        int colOffset = targetCol - masterCol;
        int rowOffset = (int)(targetRow - masterRow);

        // adjust column references
        string adjustedFormula = Regex.Replace(baseFormula, @"(?<!\$)([A-Z]{1,3})(?=\d)", m =>
        {
            var colName = m.Value;
            int colNum = GetColumnIndex(colName);
            return GetColumnName(colNum + colOffset);
        });

        // adjust row references
        adjustedFormula = Regex.Replace(adjustedFormula, @"(?<!\$)(\d+)", m =>
        {
            int rowNum = int.Parse(m.Value);
            return (rowNum + rowOffset).ToString(CultureInfo.InvariantCulture);
        });

        return adjustedFormula;
    }

    /// <summary>
    /// check if a cell is within a table's range string (e.g., "A1:G10")
    /// </summary>
    private static bool IsCellInTable(string cellReference, StringValue? tableReference)
    {
        if (tableReference?.Value == null)
            return false;

        string[] parts = tableReference.Value.Split(':');
        if (parts.Length != 2)
            return false;

        int cellCol = GetColumnIndex(cellReference);
        uint cellRow = GetRowIndex(cellReference);

        int startCol = GetColumnIndex(parts[0]);
        uint startRow = GetRowIndex(parts[0]);
        int endCol = GetColumnIndex(parts[1]);
        uint endRow = GetRowIndex(parts[1]);

        return cellRow >= startRow && cellRow <= endRow && cellCol >= startCol && cellCol <= endCol;
    }

    public static void ApplyTemplateFormulas(
        SheetData targetSheetData,
        List<TemplateFormulaInfo> templateFormulas,
        int dataRowCount)
    {
        var sw = Stopwatch.StartNew();

        if (templateFormulas.Count == 0)
            return;

        if (dataRowCount <= 0)
            dataRowCount = 1; // add formulas to the empty data row

        const uint firstDataRowIndex = 2U;
        uint lastDataRowIndex = (uint)dataRowCount + 1;

        var formulaColIndexes = new HashSet<int>(
            templateFormulas.Select(f => f.ColumnIndex)
        );

        var rowMap = targetSheetData.Elements<Row>()
            .Where(r => r.RowIndex?.Value != null)
            .ToDictionary(r => r.RowIndex!.Value);

        // regex to detect special named ranges that shouldn't be adjusted
        var fixedNamedRangeRegex = new Regex(@"\d_|\dPlus");

        for (uint currentRowIndex = firstDataRowIndex; currentRowIndex <= lastDataRowIndex; currentRowIndex++)
        {
            if (!rowMap.TryGetValue(currentRowIndex, out Row? row))
                continue;

            var newFormulaCells = new List<(Cell Cell, int ColumnIndex)>(templateFormulas.Count);
            foreach (var formulaInfo in templateFormulas)
            {
                if (string.IsNullOrEmpty(formulaInfo.FormulaText))
                    continue;

                string columnLetter = GetColumnName(formulaInfo.ColumnIndex);
                string cellRef = columnLetter + currentRowIndex;

                string newFormulaText;
                if (fixedNamedRangeRegex.IsMatch(formulaInfo.FormulaText))
                {
                    newFormulaText = formulaInfo.FormulaText;
                }
                else
                {
                    int rowOffset = (int)(currentRowIndex - formulaInfo.RowIndex);
                    newFormulaText = AdjustFormula(formulaInfo.FormulaText, rowOffset);
                }

                var newCell = new Cell
                {
                    CellReference = cellRef,
                    CellFormula = new CellFormula(newFormulaText)
                };

                if (formulaInfo.StyleIndex.HasValue)
                {
                    newCell.StyleIndex = formulaInfo.StyleIndex.Value;
                }

                newFormulaCells.Add((newCell, formulaInfo.ColumnIndex));
            }

            // filter out existing cells in formula columns
            var existingCellsWithIndex = row.Elements<Cell>()
                .Select(
                    c => (Cell: c, ColumnIndex: GetColumnIndex(c.CellReference?.Value ?? string.Empty))
                )
                .Where(c => c.ColumnIndex > 0 && !formulaColIndexes.Contains(c.ColumnIndex));

            var allCells = existingCellsWithIndex
                .Concat(newFormulaCells)
                .OrderBy(c => c.ColumnIndex)
                .Select(c => c.Cell)
                .ToList();

            row.RemoveAllChildren<Cell>();
            row.Append(allCells);
        }
        Debug.WriteLine($"ApplyTemplateFormulas(): {sw.ElapsedMilliseconds} ms ({templateFormulas.Count} formula columns with {dataRowCount} rows).");
    }

    private static string AdjustFormula(string formula, int rowIncrement)
    {
        string newFormula = formula;

        var matches = Regex.Matches(newFormula, @"(?<=\$?[A-Z]+)(?<!\$)[0-9]+", RegexOptions.RightToLeft);

        foreach (Match match in matches)
        {
            string originalRow = match.Value;
            int originalRowNumber = int.Parse(originalRow);
            int newRowNumber = originalRowNumber + rowIncrement;
            string replacement = newRowNumber.ToString(CultureInfo.InvariantCulture);

            newFormula = newFormula.Remove(match.Index, match.Length).Insert(match.Index, replacement);
        }

        return newFormula;
    }

    public static SheetData EnsureSheetDataExists(WorksheetPart wsPart)
    {
        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();
        if (sheetData == null)
        {
            sheetData = new SheetData();
            var cols = wsPart.Worksheet.GetFirstChild<Columns>();
            if (cols != null)
            {
                wsPart.Worksheet.InsertAfter(sheetData, cols);
            }
            else
            {
                var formatProps = wsPart.Worksheet.GetFirstChild<SheetFormatProperties>();
                if (formatProps != null)
                {
                    wsPart.Worksheet.InsertAfter(sheetData, formatProps);
                }
                else
                {
                    var views = wsPart.Worksheet.GetFirstChild<SheetViews>();
                    if (views != null)
                    {
                        wsPart.Worksheet.InsertAfter(sheetData, views);
                    }
                    else
                    {
                        wsPart.Worksheet.AppendChild(sheetData);
                    }
                }
            }
        }
        return sheetData;
    }

    public static DataTable ToDataTable<T>(List<T> items)
    {
        var dataTable = new DataTable(typeof(T).Name);

        var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        foreach (var prop in props)
        {
            // use the [Column] attribute name if it exists, otherwise use the property name
            var columnAttribute = (ColumnAttribute?)prop.GetCustomAttribute(typeof(ColumnAttribute), false);
            var columnName = columnAttribute?.Name ?? prop.Name;

            // set the column type, handling nullable types properly
            var underlyingType = Nullable.GetUnderlyingType(prop.PropertyType);
            dataTable.Columns.Add(columnName, underlyingType ?? prop.PropertyType);
        }

        foreach (var item in items)
        {
            var values = new object[props.Length];
            for (var i = 0; i < props.Length; i++)
            {
                values[i] = props[i].GetValue(item, null) ?? DBNull.Value;
            }
            dataTable.Rows.Add(values);
        }

        return dataTable;
    }

    public static void FreezeFirstRow(Worksheet worksheet)
    {
        // Remove existing SheetViews
        var existing = worksheet.Elements<SheetViews>().ToList();
        foreach (var e in existing)
            e.Remove();

        var sheetViews = new SheetViews();
        var sheetView = new SheetView { WorkbookViewId = 0U };

        var pane = new Pane
        {
            VerticalSplit = 1D,
            TopLeftCell = "A2",
            ActivePane = PaneValues.BottomLeft,
            State = PaneStateValues.Frozen
        };

        var selection = new Selection
        {
            Pane = PaneValues.BottomLeft,
            ActiveCell = "A2",
            SequenceOfReferences = new ListValue<StringValue> { InnerText = "A2" }
        };

        sheetView.Append(pane);
        sheetView.Append(selection);
        sheetViews.Append(sheetView);

        var sheetFormatPr = worksheet.GetFirstChild<SheetFormatProperties>();
        if (sheetFormatPr != null)
        {
            worksheet.InsertBefore(sheetViews, sheetFormatPr);
        }
        else
        {
            var cols = worksheet.GetFirstChild<Columns>();
            if (cols != null)
            {
                worksheet.InsertBefore(sheetViews, cols);
            }
            else
            {
                var sheetData = worksheet.GetFirstChild<SheetData>();
                if (sheetData != null)
                    worksheet.InsertBefore(sheetViews, sheetData);
                else
                    worksheet.Append(sheetViews);
            }
        }
    }

    /// <summary>
    /// Persists every part that has been modified since the document was opened,
    /// and flushes the changes to disk or memory.
    /// </summary>
    /// <remarks>
    /// Call once after you finish all edits to avoid the cost of repeated saves.
    /// </remarks>
    public static void SaveAllChanges(SpreadsheetDocument doc)
    {
        ArgumentNullException.ThrowIfNull(doc);

        var wbPart = doc.WorkbookPart;
        if (wbPart is null)
            return;

        // save every worksheet part
        foreach (var wsPart in wbPart.WorksheetParts)
        {
            wsPart.Worksheet.Save();
        }

        // save the workbook (sheets, defined names, calc props, etc.)
        wbPart.Workbook.Save();

        // save shared string table
        wbPart.SharedStringTablePart?.SharedStringTable.Save();

        // save styles
        wbPart.WorkbookStylesPart?.Stylesheet.Save();

        // save pivotcache definitions
        foreach (var p in wbPart.PivotTableCacheDefinitionParts)
        {
            p.PivotCacheDefinition.Save();
        }

        // flush the package to disk/memory
        doc.Save();
    }

    /// <summary>
    /// manages a SharedStringTable cache for fast access
    /// and a bulk-append at the end
    /// </summary>
    private class SharedStringCache
    {
        private readonly SharedStringTable _sharedStringTable;
        private readonly Dictionary<string, int> _stringCache;
        private readonly List<SharedStringItem> _newItems = new();

        /// <summary>
        /// initializes the cache by pre-loading all existing strings from the table
        /// </summary>
        public SharedStringCache(SharedStringTable sst)
        {
            _sharedStringTable = sst;

            _stringCache = sst.Elements<SharedStringItem>()
                .Select((item, index) => new { Text = item.InnerText, Index = index })
                .Where(x => x.Text is not null)
                .GroupBy(x => x.Text) // Handle duplicate entries by grouping
                .ToDictionary(g => g.Key, g => g.First().Index);
        }

        /// <summary>
        /// gets the index for a given string.  if the string is new, it's cached and
        /// queued for bulk insertion
        /// </summary>
        /// <param name="text">the string to get the index for</param>
        /// <returns>the 0-based index of the string in the SharedStringTable</returns>
        public int GetStringIndex(string text)
        {
            if (_stringCache.TryGetValue(text, out int index))
                return index;

            int newIndex = _stringCache.Count;
            _stringCache.Add(text, newIndex);
            _newItems.Add(new SharedStringItem(new Text(text)));

            return newIndex;
        }

        /// <summary>
        /// does a single bulk append of all new strings to the SharedStringTable
        /// and updates its count properties
        /// </summary>
        public void Save()
        {
            if (_newItems.Count > 0)
            {
                _sharedStringTable.Append(_newItems);
            }

            var cacheCount = (uint)_stringCache.Count;
            _sharedStringTable.Count = cacheCount;
            _sharedStringTable.UniqueCount = cacheCount;
        }
    }

    public static void AutoFitColumns(Worksheet ws, WorkbookPart wbPart)
    {
        const double maxWidth = 50.0;

        var sheetData = ws.GetFirstChild<SheetData>();
        if (sheetData == null || !sheetData.HasChildren || wbPart == null)
            return;

        var sstPart = wbPart.SharedStringTablePart;
        var stylesheet = wbPart.WorkbookStylesPart?.Stylesheet;

        var columnWidths = GetColumnWidths(ws, sheetData, sstPart, stylesheet);
        if (columnWidths.Count == 0)
            return;

        var cols = new Columns();
        foreach (var kvp in columnWidths.OrderBy(k => k.Key))
        {
            cols.Append(new Column
            {
                Min = (uint)kvp.Key,
                Max = (uint)kvp.Key,
                CustomWidth = true,
                Width = Math.Min(kvp.Value, maxWidth)
            });
        }

        var existing = ws.Elements<Columns>().FirstOrDefault();
        if (existing != null)
            existing.Remove();

        var sheetFormatPr = ws.GetFirstChild<SheetFormatProperties>();
        if (sheetFormatPr != null)
        {
            ws.InsertAfter(cols, sheetFormatPr);
        }
        else
        {
            var views = ws.GetFirstChild<SheetViews>();
            if (views != null)
                ws.InsertAfter(cols, views);
            else
                ws.InsertBefore(cols, sheetData);
        }
    }

    private static Dictionary<int, double> GetColumnWidths(
        Worksheet ws,
        SheetData sheetData,
        SharedStringTablePart? sstPart,
        Stylesheet? stylesheet)
    {
        const string fontName = "Arial";
        const float fontSize = 10f;
        const int sampleRows = 1000; // limit rows for performance
        const double padding = 2.0;
        const double filterButtonPadding = 3.0; // Extra padding for the header filter dropdown

        var hasTable = ws.WorksheetPart?.TableDefinitionParts.Any() ?? false;

        var maxWidths = new Dictionary<int, double>();

        if (!SixLabors.Fonts.SystemFonts.TryGet(fontName, out var fontFamily))
            fontFamily = SixLabors.Fonts.SystemFonts.Families.First();

        // Create two fonts: one for data, one for the bold header
        var regularFont = fontFamily.CreateFont(fontSize, SixLabors.Fonts.FontStyle.Regular);
        var boldFont = fontFamily.CreateFont(fontSize, SixLabors.Fonts.FontStyle.Bold);

        var regularTextOptions = new SixLabors.Fonts.TextOptions(regularFont);
        var boldTextOptions = new SixLabors.Fonts.TextOptions(boldFont);

        // Max Digit Width can be based on the regular font
        double mdw = SixLabors.Fonts.TextMeasurer.MeasureSize("0", regularTextOptions).Width;

        // Get all merged cell ranges
        var mergedRanges = new List<(int startCol, uint startRow, int endCol, uint endRow)>();
        if (ws.Elements<MergeCells>().FirstOrDefault() is MergeCells mergeCells)
        {
            foreach (var mergeCell in mergeCells.Elements<MergeCell>())
            {
                if (mergeCell.Reference?.Value == null)
                    continue;

                string[] parts = mergeCell.Reference.Value.Split(':');
                if (parts.Length != 2)
                    continue;

                mergedRanges.Add((
                    startCol: GetColumnIndex(parts[0]),
                    startRow: GetRowIndex(parts[0]),
                    endCol: GetColumnIndex(parts[1]),
                    endRow: GetRowIndex(parts[1])
                ));
            }
        }

        foreach (var row in sheetData.Elements<Row>().Take(sampleRows))
        {
            var isHeaderRow = row.RowIndex?.Value == 1U;
            var textOptions = isHeaderRow ? boldTextOptions : regularTextOptions;

            foreach (var cell in row.Elements<Cell>())
            {
                if (cell.CellReference?.Value == null)
                    continue;

                var colIndex = GetColumnIndex(cell.CellReference.Value);
                var rowIndex = GetRowIndex(cell.CellReference.Value);

                if (colIndex == 0 || rowIndex == 0)
                    continue;

                bool isMerged = mergedRanges.Any(r =>
                    colIndex >= r.startCol && colIndex <= r.endCol &&
                    rowIndex >= r.startRow && rowIndex <= r.endRow);

                if (isMerged)
                    continue;

                string text = GetFormattedCellText(cell, sstPart, stylesheet);
                if (string.IsNullOrEmpty(text))
                    continue;

                var size = SixLabors.Fonts.TextMeasurer.MeasureSize(text, textOptions);
                double width = size.Width / mdw;

                if (isHeaderRow && hasTable)
                {
                    width += filterButtonPadding;
                }

                if (!maxWidths.TryGetValue(colIndex, out double currentMaxWidth) || width > currentMaxWidth)
                {
                    maxWidths[colIndex] = width;
                }
            }
        }

        // Add padding
        return maxWidths.ToDictionary(kvp => kvp.Key, kvp => kvp.Value + padding);
    }

    private static string GetFormattedCellText(Cell cell, SharedStringTablePart? sstPart, Stylesheet? stylesheet)
    {
        if (cell == null || cell.CellValue == null)
            return string.Empty;

        string raw = cell.CellValue.InnerText;

        // Shared String
        if (cell.DataType?.Value == CellValues.SharedString)
        {
            if (int.TryParse(raw, out int idx) && sstPart?.SharedStringTable != null)
                return sstPart.SharedStringTable.Elements<SharedStringItem>().ElementAtOrDefault(idx)?.InnerText ?? raw;
            return raw;
        }

        // Inline string
        if (cell.DataType?.Value == CellValues.InlineString)
            return cell.InnerText ?? raw;

        // Boolean
        if (cell.DataType?.Value == CellValues.Boolean)
            return raw == "1" ? "TRUE" : "FALSE";

        // Numbers (plain, currency, dates, percentages)
        if (double.TryParse(raw, NumberStyles.Any, CultureInfo.InvariantCulture, out double val))
        {
            uint styleIndex = cell.StyleIndex?.Value ?? 0U;
            string? excelFormat = ResolveNumberFormat(styleIndex, stylesheet);

            if (!string.IsNullOrEmpty(excelFormat))
            {
                string sanitizedFormat = Regex.Replace(excelFormat, @"\[.*?\]|\"".*?\""", "");

                // Try as Date/Time first
                if (Regex.IsMatch(sanitizedFormat, @"[dyhmsM]|tt", RegexOptions.IgnoreCase))
                {
                    try
                    {
                        var dt = DateTime.FromOADate(val);
                        return dt.ToString(excelFormat, CultureInfo.CurrentCulture);
                    }
                    catch
                    {
                        // If conversion fails, fall through to numeric formatting
                    }
                }

                var parts = excelFormat.Split(';');
                string activeFormat = val > 0 ? parts[0]
                                    : val < 0 && parts.Length > 1 ? parts[1]
                                    : val == 0 && parts.Length > 2 ? parts[2]
                                    : parts[0];

                // Create a version of the format that .NET's ToString() can understand.
                // This means removing colors [Red], fill characters *x, and padding _x
                string cleanFormat = Regex.Replace(activeFormat, @"\[[^\]]+\]|\*.", "");
                cleanFormat = Regex.Replace(cleanFormat, @"_.", "");

                string formattedValue;
                try
                {
                    // Format the number using the cleaned string.
                    formattedValue = val.ToString(cleanFormat, CultureInfo.CurrentCulture);
                }
                catch (FormatException)
                {
                    return val.ToString(CultureInfo.CurrentCulture); // Fallback
                }

                // Add an extra space for padding if the negative format uses parenthesis.
                if (activeFormat.Contains("_)"))
                {
                    if (parts.Length > 1 && parts[1].Contains('('))
                    {
                        formattedValue += " ";
                    }
                }

                return formattedValue;
            }

            // No format applied
            return val.ToString(CultureInfo.CurrentCulture);
        }

        // Fallback
        return raw;
    }

    private static string? ResolveNumberFormat(uint styleIndex, Stylesheet? stylesheet)
    {
        if (stylesheet?.CellFormats == null)
            return null;

        var cf = stylesheet.CellFormats.ElementAtOrDefault((int)styleIndex) as CellFormat;
        if (cf?.NumberFormatId == null)
            return null;

        uint id = cf.NumberFormatId.Value;

        string? formatCode = stylesheet.NumberingFormats?
            .Elements<NumberingFormat>()
            .FirstOrDefault(f => f.NumberFormatId?.Value == id)?
            .FormatCode?.Value;

        if (string.IsNullOrEmpty(formatCode))
        {
            if (BuiltInFormats.TryGetValue(id, out var builtIn))
                formatCode = builtIn;
        }

        if (string.IsNullOrEmpty(formatCode))
            return null;

        return ConvertExcelFormatToDotNet(formatCode);
    }

    private static string ConvertExcelFormatToDotNet(string excelFormat)
    {
        var fmt = excelFormat;

        // Order matters (longest first)
        fmt = Regex.Replace(fmt, @"\byy\b", "yy");

        fmt = fmt.Replace("mmmm", "MMMM");
        fmt = fmt.Replace("mmm", "MMM");
        fmt = Regex.Replace(fmt, @"\bmm\b", "MM");
        fmt = Regex.Replace(fmt, @"\bm\b", "M");

        fmt = fmt.Replace("dd", "dd");
        fmt = Regex.Replace(fmt, @"\bd\b", "d");

        fmt = fmt.Replace("hh", "HH");
        fmt = Regex.Replace(fmt, @"\bh\b", "H");

        fmt = fmt.Replace("ss", "ss");
        fmt = fmt.Replace("AM/PM", "tt", StringComparison.OrdinalIgnoreCase);

        return fmt;
    }

    // Full built-in NumberFormatId mapping (Excel IDs 0–163).
    // Note: IDs >= 50 vary by locale; here we use English (US) defaults.
    private static readonly Dictionary<uint, string> BuiltInFormats = new()
    {
        { 0, "General" },
        { 1, "0" },
        { 2, "0.00" },
        { 3, "#,##0" },
        { 4, "#,##0.00" },

        { 5, "$#,##0_);($#,##0)" },
        { 6, "$#,##0_);[Red]($#,##0)" },
        { 7, "$#,##0.00_);($#,##0.00)" },
        { 8, "$#,##0.00_);[Red]($#,##0.00)" },

        { 9, "0%" },
        { 10, "0.00%" },
        { 11, "0.00E+00" },
        { 12, "# ?/?" },
        { 13, "# ??/??" },

        { 14, "m/d/yyyy" },
        { 15, "d-mmm-yy" },
        { 16, "d-mmm" },
        { 17, "mmm-yy" },

        { 18, "h:mm AM/PM" },
        { 19, "h:mm:ss AM/PM" },
        { 20, "h:mm" },
        { 21, "h:mm:ss" },
        { 22, "m/d/yyyy h:mm" },

        // 23–36: reserved / not used

        { 37, "#,##0_);(#,##0)" },
        { 38, "#,##0_);[Red](#,##0)" },
        { 39, "#,##0.00_);(#,##0.00)" },
        { 40, "#,##0.00_);[Red](#,##0.00)" },

        // 41–44: variants of currency & accounting
        { 41, "_(* #,##0_);_(* (#,##0);_(* \"-\"_);_(@_)" },
        { 42, "_(\"$\"* #,##0_);_(\"$\"* (#,##0);_(* \"-\"_);_(@_)" },
        { 43, "_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)" },
        { 44, "_(\"$\"* #,##0.00_);_(\"$\"* (#,##0.00);_(* \"-\"??_);_(@_)" },

        { 45, "mm:ss" },
        { 46, "[h]:mm:ss" },
        { 47, "mmss.0" },
        { 48, "##0.0E+0" },
        { 49, "@" }, // Text

        // 50–163: locale‑dependent built‑ins
        // English (US) defaults shown here
        { 50, "m/d/yyyy" },
        { 51, "d-mmm-yy" },
        { 52, "d-mmm" },
        { 53, "mmm-yy" },
        { 54, "h:mm" },
        { 55, "h:mm:ss" },
        { 56, "h:mm AM/PM" },
        { 57, "h:mm:ss AM/PM" },
        { 58, "m/d/yyyy h:mm" },
        { 59, "0" },
        { 60, "0.00" },
        { 61, "#,##0" },
        { 62, "#,##0.00" },
        { 63, "$#,##0_);($#,##0)" },
        { 64, "$#,##0.00_);($#,##0.00)" },

        { 65, "mm:ss" },
        { 66, "[h]:mm:ss" },
        { 67, "mmss.0" },
        { 68, "m/d/yyyy" },
        { 69, "d-mmm-yy" },
        { 70, "d-mmm" },
        { 71, "mmm-yy" },
        { 72, "h:mm" },
        { 73, "h:mm:ss" },
        { 74, "h:mm AM/PM" },
        { 75, "h:mm:ss AM/PM" },
        { 76, "m/d/yyyy h:mm" },
        { 77, "mm:ss" },
        { 78, "[h]:mm:ss" },
        { 79, "mmss.0" },
        { 80, "0.000" },
        { 81, "#,##0.00_);(#,##0.00)" },

        { 82, "m/d/yyyy" },
        { 83, "d-mmm-yy" },
        { 84, "d-mmm" },
        { 85, "mmm-yy" },
        { 86, "h:mm" },
        { 87, "h:mm:ss" },
        { 88, "h:mm AM/PM" },
        { 89, "h:mm:ss AM/PM" },
        { 90, "m/d/yyyy h:mm" },
        { 91, "mm:ss" },
        { 92, "[h]:mm:ss" },
        { 93, "mmss.0" },
        { 94, "0.000" },
        { 95, "#,##0" },
        { 96, "#,##0.00" },
        { 97, "$#,##0_);($#,##0)" },
        { 98, "$#,##0.00_);($#,##0.00)" },
        { 99, "@" },

        // Safe generic placeholders for 100–163
        { 100, "General" }, { 101, "General" }, { 102, "General" }, { 103, "General" },
        { 104, "General" }, { 105, "General" }, { 106, "General" }, { 107, "General" },
        { 108, "General" }, { 109, "General" }, { 110, "General" }, { 111, "General" },
        { 112, "General" }, { 113, "General" }, { 114, "General" }, { 115, "General" },
        { 116, "General" }, { 117, "General" }, { 118, "General" }, { 119, "General" },
        { 120, "General" }, { 121, "General" }, { 122, "General" }, { 123, "General" },
        { 124, "General" }, { 125, "General" }, { 126, "General" }, { 127, "General" },
        { 128, "General" }, { 129, "General" }, { 130, "General" }, { 131, "General" },
        { 132, "General" }, { 133, "General" }, { 134, "General" }, { 135, "General" },
        { 136, "General" }, { 137, "General" }, { 138, "General" }, { 139, "General" },
        { 140, "General" }, { 141, "General" }, { 142, "General" }, { 143, "General" },
        { 144, "General" }, { 145, "General" }, { 146, "General" }, { 147, "General" },
        { 148, "General" }, { 149, "General" }, { 150, "General" }, { 151, "General" },
        { 152, "General" }, { 153, "General" }, { 154, "General" }, { 155, "General" },
        { 156, "General" }, { 157, "General" }, { 158, "General" }, { 159, "General" },
        { 160, "General" }, { 161, "General" }, { 162, "General" }, { 163, "General" }
    };

    public static Worksheet AddEmptySheet(SpreadsheetDocument document, string sheetName)
    {
        var wbPart = document.WorkbookPart ?? document.AddWorkbookPart();
        if (wbPart.Workbook == null)
        {
            wbPart.Workbook = new Workbook();
        }

        var sheets = wbPart.Workbook.GetFirstChild<Sheets>() ?? wbPart.Workbook.AppendChild(new Sheets());

        var wsPart = wbPart.AddNewPart<WorksheetPart>();
        wsPart.Worksheet = new Worksheet(new SheetData());

        uint nextSheetId = sheets.Elements<Sheet>().Any() ? sheets.Elements<Sheet>().Max(s => s.SheetId?.Value ?? 0) + 1 : 1;
        var newSheet = new Sheet
        {
            Id = wbPart.GetIdOfPart(wsPart),
            SheetId = nextSheetId,
            Name = sheetName,
        };
        sheets.Append(newSheet);

        return wsPart.Worksheet;
    }

    public static void AddOrModifyInfoSheet(SpreadsheetDocument document, string cellReference, string message)
    {
        var wbPart = document.WorkbookPart ?? document.AddWorkbookPart();
        wbPart.Workbook ??= new Workbook();

        var sheets = wbPart.Workbook.GetFirstChild<Sheets>() ?? wbPart.Workbook.AppendChild(new Sheets());
        var infoSheet = sheets.Elements<Sheet>().FirstOrDefault(s => s.Name == "Info");
        WorksheetPart wsPart;

        if (infoSheet == null)
        {
            wsPart = wbPart.AddNewPart<WorksheetPart>();
            wsPart.Worksheet = new Worksheet(new SheetData());

            uint sheetId = sheets.Elements<Sheet>().Any()
                ? sheets.Elements<Sheet>().Max(s => s.SheetId?.Value ?? 0) + 1
                : 1;
            infoSheet = new Sheet
            {
                Id = wbPart.GetIdOfPart(wsPart),
                SheetId = sheetId,
                Name = "Info"
            };
            sheets.Append(infoSheet);
        }
        else
        {
            wsPart = (WorksheetPart)wbPart.GetPartById(infoSheet.Id!.Value!);
        }

        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>() ?? wsPart.Worksheet.AppendChild(new SheetData());

        uint rowIndex = uint.Parse(string.Concat(cellReference.Where(char.IsDigit)));
        string columnName = string.Concat(cellReference.Where(char.IsLetter));

        var cell = GetOrCreateCell(sheetData, (int)rowIndex, GetColumnIndex(columnName));

        cell.InlineString = new InlineString(new Text(message));
        cell.DataType = CellValues.InlineString;
        cell.CellValue = null;

        const uint boldStyle = 9;
        cell.StyleIndex = boldStyle;
    }

    public static void RemoveAllWorksheets(SpreadsheetDocument doc)
    {
        var wbPart = doc.WorkbookPart;
        if (wbPart == null)
            return;

        var wsParts = wbPart.WorksheetParts.ToList();
        foreach (var wsPart in wsParts)
        {
            wbPart.DeletePart(wsPart);
        }

        var sheets = wbPart.Workbook.Sheets;
        sheets?.RemoveAllChildren<Sheet>();

        wbPart.Workbook.Save();
    }

    public static Cell InsertCell(
        int colIndex,
        int rowIndex,
        WorksheetPart wsPart,
        object value,
        CellValues? dataType = null)
    {
        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>()
                        ?? throw new InvalidOperationException("SheetData not found in worksheet.");

        var cell = GetOrCreateCell(sheetData, rowIndex, colIndex);
        SetCellValue(cell, value, explicitType: dataType);
        return cell;
    }

    public static void MergeCells(WorksheetPart wsPart, string startCellRef, string endCellRef)
    {
        MergeCells mergeCells;
        if (wsPart.Worksheet.Elements<MergeCells>().Any())
        {
            mergeCells = wsPart.Worksheet.Elements<MergeCells>().First();
        }
        else
        {
            mergeCells = new MergeCells();
            if (wsPart.Worksheet.Elements<CustomSheetView>().Any())
            {
                wsPart.Worksheet.InsertAfter(mergeCells,
                    wsPart.Worksheet.Elements<CustomSheetView>().First());
            }
            else
            {
                wsPart.Worksheet.InsertAfter(mergeCells,
                    wsPart.Worksheet.Elements<SheetData>().First());
            }
        }

        mergeCells.Append(new MergeCell() { Reference = $"{startCellRef}:{endCellRef}" });
    }

    public static Cell InsertFormulaCell(int colIndex, int rowIndex, WorksheetPart wsPart, string formula)
    {
        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();
        if (sheetData == null)
            throw new InvalidOperationException("SheetData not found in worksheet.");

        var cell = GetOrCreateCell(sheetData, rowIndex, colIndex);

        cell.CellFormula = new CellFormula(formula);
        cell.DataType = null;

        return cell;
    }

    private static Row GetOrCreateRow(SheetData sheetData, uint rowIndex, bool keepOrder = true)
    {
        var row = sheetData.Elements<Row>().FirstOrDefault(r => (r.RowIndex ?? 0U) == rowIndex);
        if (row != null)
            return row;

        row = new Row { RowIndex = rowIndex };

        if (keepOrder)
        {
            var refRow = sheetData.Elements<Row>().FirstOrDefault(r => (r.RowIndex ?? 0U) > rowIndex);
            if (refRow != null)
                sheetData.InsertBefore(row, refRow);
            else
                sheetData.Append(row);
        }
        else
        {
            sheetData.Append(row);
        }

        return row;
    }

    private static Cell GetOrCreateCell(
        SheetData sheetData,
        int rowIndex,
        int colIndex,
        bool keepOrder = true)
    {
        var row = GetOrCreateRow(sheetData, (uint)rowIndex, keepOrder);

        string columnName = GetColumnName(colIndex);
        string cellReference = columnName + rowIndex;

        var cell = row.Elements<Cell>().FirstOrDefault(c =>
            string.Equals(c.CellReference, cellReference, StringComparison.OrdinalIgnoreCase));
        if (cell != null)
            return cell;

        cell = new Cell { CellReference = cellReference };

        if (keepOrder)
        {
            var refCell = row.Elements<Cell>().FirstOrDefault(c =>
                string.Compare(c.CellReference, cellReference, StringComparison.OrdinalIgnoreCase) > 0);
            if (refCell != null)
                row.InsertBefore(cell, refCell);
            else
                row.Append(cell);
        }
        else
        {
            row.Append(cell);
        }

        return cell;
    }

    private static void ApplyStyleWithPreservedBorder(Cell cell, uint styleIndex, StylesheetCache cache)
    {
        var newStyle = cache.GetCellFormatClone(styleIndex);

        if (cell.StyleIndex != null)
        {
            var existingStyle = cache.GetCellFormatClone(cell.StyleIndex.Value);
            if (existingStyle.BorderId != null)
            {
                newStyle.BorderId = existingStyle.BorderId.Value;
                newStyle.ApplyBorder = true;
            }
        }

        cell.StyleIndex = cache.AddCellFormat(newStyle);
    }

    private static IEnumerable<Cell> GetCellsInRange(
        SheetData sheetData,
        int startRow,
        int startCol,
        int rowCount,
        int colCount)
    {
        int endRow = startRow + rowCount - 1;
        int endCol = startCol + colCount - 1;

        for (int rowIndex = startRow; rowIndex <= endRow; rowIndex++)
        {
            for (int colIndex = startCol; colIndex <= endCol; colIndex++)
            {
                yield return GetOrCreateCell(sheetData, rowIndex, colIndex);
            }
        }
    }

    public static void ApplyStyleToCellWithPreservedBorder(
        WorksheetPart wsPart,
        StylesheetCache stylesheetCache,
        int colIndex,
        int rowIndex,
        object value,
        uint styleIndex)
    {
        var cell = InsertCell(colIndex, rowIndex, wsPart, value);
        ApplyStyleWithPreservedBorder(cell, styleIndex, stylesheetCache);
    }

    public static void ApplyStyleToRangeWithPreservedBorder(
        WorksheetPart wsPart,
        StylesheetCache stylesheetCache,
        int startRow,
        int startCol,
        int rowCount,
        int colCount,
        uint styleIndex)
    {
        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>()
                        ?? throw new InvalidOperationException("SheetData not found.");

        foreach (var cell in GetCellsInRange(sheetData, startRow, startCol, rowCount, colCount))
        {
            ApplyStyleWithPreservedBorder(cell, styleIndex, stylesheetCache);
        }
    }

    public static void ApplyOutlineBorderToRange(
        WorksheetPart wsPart,
        StylesheetCache stylesheetCache,
        int startRow,
        int startCol,
        int rowCount,
        int colCount,
        BorderStyleValues borderStyle)
    {
        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>()
                        ?? throw new InvalidOperationException("SheetData not found.");

        int endRow = startRow + rowCount - 1;
        int endCol = startCol + colCount - 1;

        foreach (var cell in GetCellsInRange(sheetData, startRow, startCol, rowCount, colCount))
        {
            int rowIndex = (int)GetRowIndex(cell.CellReference!);
            int colIndex = GetColumnIndex(cell.CellReference!);

            bool isTop = rowIndex == startRow;
            bool isBottom = rowIndex == endRow;
            bool isLeft = colIndex == startCol;
            bool isRight = colIndex == endCol;

            if (!(isTop || isBottom || isLeft || isRight))
                continue;

            var style = stylesheetCache.GetCellFormatClone(cell.StyleIndex ?? 0U);
            var border = stylesheetCache.GetBorderClone(style.BorderId ?? 0U);

            if (isTop) border.TopBorder = new TopBorder { Style = borderStyle };
            if (isBottom) border.BottomBorder = new BottomBorder { Style = borderStyle };
            if (isLeft) border.LeftBorder = new LeftBorder { Style = borderStyle };
            if (isRight) border.RightBorder = new RightBorder { Style = borderStyle };

            style.BorderId = stylesheetCache.AddBorder(border);
            style.ApplyBorder = true;

            cell.StyleIndex = stylesheetCache.AddCellFormat(style);
        }
    }

    /// <summary>
    /// Creates an Excel Table around the filled data range.
    /// </summary>
    public static void AddTableToSheet(SpreadsheetDocument doc, string sheetName)
    {
        var wsPart = GetWorksheetPartByName(doc, sheetName);
        if (wsPart == null)
            return;

        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();
        if (sheetData == null || !sheetData.Elements<Row>().Any())
            return;

        int lastRow = sheetData.Elements<Row>().Max(r => (int)(r.RowIndex?.Value ?? 0));
        int lastCol = sheetData.Elements<Row>()
            .SelectMany(r => r.Elements<Cell>())
            .Select(c => GetColumnIndex(c.CellReference?.Value ?? ""))
            .DefaultIfEmpty(0)
            .Max();

        if (lastRow < 1 || lastCol < 1)
            return;

        string endColName = GetColumnName(lastCol);
        string tableRange = $"A1:{endColName}{lastRow}";

        uint tableId = (uint)(doc.WorkbookPart!.GetPartsOfType<TableDefinitionPart>().Count() + 1);

        // Sanitize table name
        string safeName = Regex.Replace(sheetName, @"[^A-Za-z0-9_]", "_");
        string tableName = $"{safeName}_Table{tableId}";

        // Extract column names
        var headerRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == 1U);
        var headers = new List<string>();
        if (headerRow != null)
        {
            var headerCells = headerRow.Elements<Cell>()
                .Select(c => new { Cell = c, Index = GetColumnIndex(c.CellReference?.Value ?? "") })
                .Where(x => x.Index > 0 && x.Index <= lastCol)
                .OrderBy(x => x.Index);

            int colId = 1;
            foreach (var item in headerCells)
            {
                // Fill in gaps if headers are missing
                while (colId < item.Index)
                {
                    headers.Add($"Column{colId}");
                    colId++;
                }
                headers.Add(GetCellValue(item.Cell, doc.WorkbookPart) ?? $"Column{colId}");
                colId++;
            }
        }

        AddTableToRange(wsPart, tableId, tableRange, headers.ToArray(), tableName, showRowStripes: false);
    }

    /// <summary>
    /// Creates an Excel Table around a specific range of cells.
    /// </summary>
    public static void AddTableToRange(
        WorksheetPart wsPart,
        uint tableId,
        string reference,
        string[] columnNames,
        string? tableName = null,
        bool showRowStripes = false)
    {
        // Generate a safe table name if none provided
        if (string.IsNullOrEmpty(tableName))
        {
            tableName = $"Table{tableId}";
        }

        var tableDefPart = wsPart.AddNewPart<TableDefinitionPart>($"rIdTable_{tableId}");

        var table = new Table
        {
            Id = tableId,
            Name = tableName,
            DisplayName = tableName,
            Reference = reference,
            TotalsRowShown = false
        };

        table.AddNamespaceDeclaration("xr3",
            "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");

        var autoFilter = new AutoFilter { Reference = reference };

        var tableColumns = new TableColumns { Count = (uint)columnNames.Length };
        var uniqueNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

        uint colId = 1;
        foreach (var name in columnNames)
        {
            string uniqueName = name;
            int dup = 1;
            while (!uniqueNames.Add(uniqueName))
            {
                dup++;
                uniqueName = $"{name}_{dup}";
            }

            var col = new TableColumn { Id = colId, Name = uniqueName };

            col.SetAttribute(
                new OpenXmlAttribute("xr3", "uid",
                    "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3",
                    $"{{{Guid.NewGuid().ToString().ToUpper()}}}")
            );

            tableColumns.Append(col);
            colId++;
        }

        var tableStyleInfo = new TableStyleInfo
        {
            Name = "TableStyleMedium2",
            ShowFirstColumn = false,
            ShowLastColumn = false,
            ShowRowStripes = showRowStripes,
            ShowColumnStripes = false
        };

        table.Append(autoFilter);
        table.Append(tableColumns);
        table.Append(tableStyleInfo);

        tableDefPart.Table = table;

        var tableParts = wsPart.Worksheet.GetFirstChild<TableParts>();
        if (tableParts == null)
        {
            tableParts = new TableParts();
            wsPart.Worksheet.Append(tableParts);
        }

        tableParts.Append(new TablePart { Id = wsPart.GetIdOfPart(tableDefPart) });
        tableParts.Count = (uint)tableParts.ChildElements.Count;
    }

    /// <summary>
    /// Handles caching of the CellFormats and Borders in a Stylesheet
    /// to speed up operations and avoid duplicates.
    ///
    /// When you're done making changes, call CommitToStylesheet()
    /// to write the cached formats and borders back to the Stylesheet.
    /// </summary>
    public class StylesheetCache
    {
        private readonly Stylesheet _stylesheet;

        private readonly List<CellFormat> _cellFormats = new();
        private readonly List<Border> _borders = new();

        /// <summary>
        /// Lookup dictionaries to avoid adding duplicates.
        /// The key is the OuterXml of the CellFormat or Border,
        /// and the value is the index in the corresponding list.
        /// </summary>
        private readonly Dictionary<string, uint> _cellFormatLookup = new();
        private readonly Dictionary<string, uint> _borderLookup = new();

        public StylesheetCache(Stylesheet stylesheet)
        {
            _stylesheet = stylesheet;

            // Load existing formats into cache.
            var existingFormats = _stylesheet.CellFormats?.Elements<CellFormat>()
                ?? Enumerable.Empty<CellFormat>();

            foreach (var cf in existingFormats)
            {
                // Clone to detach from parent before adding to cache.
                var clone = (CellFormat)cf.CloneNode(true);
                _cellFormats.Add(clone);
                _cellFormatLookup[clone.OuterXml] = (uint)(_cellFormats.Count - 1);
            }

            // Load existing borders into cache.
            var existingBorders = _stylesheet.Borders?.Elements<Border>()
                ?? Enumerable.Empty<Border>();

            foreach (var b in existingBorders)
            {
                // Clone to detach from parent before adding to cache.
                var clone = (Border)b.CloneNode(true);
                _borders.Add(clone);
                _borderLookup[clone.OuterXml] = (uint)(_borders.Count - 1);
            }
        }

        /// <summary>
        /// Adds the specified CellFormat to the cache if an identical format is not already present.
        /// </summary>
        /// <returns>
        /// The index of the existing or newly added CellFormat in the cache.
        /// </returns>
        public uint AddCellFormat(CellFormat cellFormat)
        {
            string key = cellFormat.OuterXml;

            // If an identical format already exists, return its index.
            if (_cellFormatLookup.TryGetValue(key, out uint idx))
                return idx;

            // Clone to detach from parent before adding to cache.
            var newFormat = (CellFormat)cellFormat.CloneNode(true);
            _cellFormats.Add(newFormat);

            var newIndex = (uint)(_cellFormats.Count - 1);
            _cellFormatLookup[key] = newIndex;

            return newIndex;
        }

        /// <summary>
        /// Adds the specified Border to the cache if an identical border is not already present.
        /// </summary>
        /// <returns>
        /// The index of the existing or newly added Border in the cache.
        /// </returns>
        public uint AddBorder(Border border)
        {
            string key = border.OuterXml;

            // If an identical border already exists, return its index.
            if (_borderLookup.TryGetValue(key, out uint idx))
                return idx;

            // Clone to detach from parent before adding to cache.
            var newBorder = (Border)border.CloneNode(true);
            _borders.Add(newBorder);

            var newIndex = (uint)(_borders.Count - 1);
            _borderLookup[key] = newIndex;

            return newIndex;
        }

        /// <summary>
        /// Returns a clone of the CellFormat at the given index.
        /// </summary>
        /// <returns>
        /// Clones are returned to prevent modifications to the cached version.
        ///
        /// To modify a CellFormat, get the clone, make your changes,
        /// then add it back to the cache with AddCellFormat() to get a new index.
        /// </returns>
        public CellFormat GetCellFormatClone(uint index) =>
            (CellFormat)_cellFormats[(int)index].CloneNode(true);

        /// <summary>
        /// Returns a clone of the Border at the given index.
        /// </summary>
        /// <returns>
        /// Clones are returned to prevent modifications to the cached version.
        ///
        /// To modify a Border, get the clone, make your changes,
        /// then add it back to the cache with AddBorder() to get a new index.
        /// </returns>
        public Border GetBorderClone(uint index) =>
            (Border)_borders[(int)index].CloneNode(true);

        /// <summary>
        /// Commits the cached CellFormats and Borders back to the Stylesheet,
        /// replacing any existing ones.
        /// </summary>
        public void CommitToStylesheet()
        {
            var newCellFormats = new CellFormats(_cellFormats);
            newCellFormats.Count = (uint)_cellFormats.Count;
            _stylesheet.CellFormats = newCellFormats;

            var newBorders = new Borders(_borders);
            newBorders.Count = (uint)_borders.Count;
            _stylesheet.Borders = newBorders;
        }
    }

}
