using System.Collections.Concurrent;
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.Expressions;
using System.Reflection;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace Fast.Shared.Logic.Package;

public partial class OpenXmlHelper
{
    /// Returns a list of template formulas that must be passed to PostProcessingForReport.
    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;
    }

    /// Returns a list of template formulas that must be passed to PostProcessingForReport.
    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;
    }

    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);

        var sstPart = doc.WorkbookPart!.GetPartsOfType<SharedStringTablePart>().FirstOrDefault()
            ?? CreateSharedStringTablePart(doc.WorkbookPart);
        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 = CreateNewHeaderRow(selectedProps, headers);
            sheetData.InsertAt(headerRow, 0);
        }
        else
        {
            AppendMissingHeaders(headerRow, selectedProps, doc.WorkbookPart);
            NormalizeCellOrder(headerRow);
            PopulateHeadersFromRow(headerRow, headers, doc.WorkbookPart);
        }

        // Pre-compute property getters to avoid repeating Reflection overhead
        // Use an array aligned with the headers list for O(1) access
        var propertyGetters = new Func<object, object?>[headers.Count];
        for (int i = 0; i < headers.Count; i++)
        {
            if (properties.TryGetValue(headers[i].Name, out var prop))
                propertyGetters[i] = ReflectionCache.GetGetter(prop);
        }

        // Apply number formats from DisplayAttribute.Description
        ApplyStylesFromDisplayAttributes(doc, selectedProps, headers);

        // Pre-compute column styles for date/time columns BEFORE SAX writing
        PreComputeColumnStyles(doc, headers);

        // Fill rows using true SAX writing (no DOM object creation per cell)
        RecreateWorksheetContentFromCollection(wsPart, headerRow, data, headers, propertyGetters, sstCache);

        sstCache.Save();

        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);

        var sstPart = doc.WorkbookPart!.GetPartsOfType<SharedStringTablePart>().FirstOrDefault()
            ?? CreateSharedStringTablePart(doc.WorkbookPart);
        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 = CreateNewHeaderRowFromDataColumns(selectedCols, headers);
            sheetData.InsertAt(headerRow, 0);
        }
        else
        {
            AppendMissingDataColumnHeaders(headerRow, selectedCols, doc.WorkbookPart);
            NormalizeCellOrder(headerRow);
            PopulateHeadersFromRow(headerRow, headers, doc.WorkbookPart);
        }

        // Pre-map headers to DataColumns to avoid repeated lookup
        // Use an array aligned with the headers list for O(1) access
        var colArray = new DataColumn?[headers.Count];
        for (int i = 0; i < headers.Count; i++)
        {
            if (data.Columns.Contains(headers[i].Name))
                colArray[i] = data.Columns[headers[i].Name];
        }

        // Pre-compute column styles for date/time columns BEFORE SAX writing
        PreComputeColumnStyles(doc, headers);

        // Fill rows using true SAX writing (no DOM object creation per cell)
        RecreateWorksheetContentFromDataTable(wsPart, headerRow, data, headers, colArray, sstCache);

        sstCache.Save();

        Debug.WriteLine($"FillSheet(DataTable): {sw.ElapsedMilliseconds} ms");
    }

    /// Pre-computes column styles for date/time/month columns based on header names.
    /// Must be called BEFORE SAX writing since we cannot modify cells after writing.
    private static void PreComputeColumnStyles(SpreadsheetDocument doc, List<HeaderInfo> headers)
    {
        var dateFormat = "m/d/yyyy";
        var monthFormat = "mmm yyyy";
        var timeFormat = "m/d/yyyy h:mm:ss AM/PM";

        foreach (var header in headers)
        {
            // Skip if already has a style (e.g., from DisplayAttribute)
            if (header.StyleIndex > 0) continue;

            var name = header.Name;
            var isDateFormat = name.Contains("day", StringComparison.OrdinalIgnoreCase) ||
                name.Contains("date", StringComparison.OrdinalIgnoreCase) ||
                name.Contains("expiration", StringComparison.OrdinalIgnoreCase);
            var isMonthFormat = name.Contains("month", StringComparison.OrdinalIgnoreCase);
            var isTimeFormat = name.Contains("created", StringComparison.OrdinalIgnoreCase);

            if (isDateFormat)
                header.StyleIndex = GetOrCreateStyleIndex(doc, dateFormat);
            else if (isMonthFormat)
                header.StyleIndex = GetOrCreateStyleIndex(doc, monthFormat);
            else if (isTimeFormat)
                header.StyleIndex = GetOrCreateStyleIndex(doc, timeFormat);
        }

        // Pre-cache StyleIndex strings to avoid per-cell ToString() allocations
        foreach (var header in headers)
        {
            if (header.StyleIndex > 0)
                header.StyleIndexStr = header.StyleIndex.ToString(CultureInfo.InvariantCulture);
        }
    }

    /// True SAX-based worksheet content recreation for generic IEnumerable<T>.
    /// Writes cells using OpenXmlWriter attributes directly, avoiding DOM object creation per cell.
    private static void RecreateWorksheetContentFromCollection<T>(
        WorksheetPart wsPart,
        Row? headerRow,
        IEnumerable<T> data,
        List<HeaderInfo> headers,
        Func<object, object?>[] propertyGetters,
        SharedStringCache sstCache)
    {
        var worksheet = wsPart.Worksheet;
        using var writer = OpenXmlWriter.Create(wsPart);
        writer.WriteStartElement(new Worksheet());

        foreach (var element in worksheet?.Elements() ?? Enumerable.Empty<OpenXmlElement>())
        {
            if (element is not SheetData)
            {
                writer.WriteElement(element);
                continue;
            }

            writer.WriteStartElement(new SheetData());
            if (headerRow != null) writer.WriteElement(headerRow);

            WriteSheetDataFromCollection(writer, data, headers, propertyGetters, sstCache);

            writer.WriteEndElement(); // SheetData
        }
        writer.WriteEndElement(); // Worksheet
    }

    private static void WriteSheetDataFromCollection<T>(
        OpenXmlWriter writer,
        IEnumerable<T> data,
        List<HeaderInfo> headers,
        Func<object, object?>[] propertyGetters,
        SharedStringCache sstCache)
    {
        uint rowIndex = 2;
        bool hasData = false;

        foreach (var record in data)
        {
            hasData = true;
            WriteRowFromItem(writer, rowIndex, record, headers, propertyGetters, sstCache);
            rowIndex++;
        }

        // Write empty row if no data (to prevent table breakage)
        if (!hasData)
        {
            writer.WriteStartElement(new Row(), [new OpenXmlAttribute("r", "", "2")]);
            writer.WriteEndElement();
        }
    }

    private static void WriteRowFromItem<T>(
        OpenXmlWriter writer,
        uint rowIndex,
        T record,
        List<HeaderInfo> headers,
        Func<object, object?>[] propertyGetters,
        SharedStringCache sstCache)
    {
        // Use cached row index string for common indices
        string rowIndexStr = rowIndex < RowIndexCacheSize
            ? _rowIndexCache[rowIndex - 1]
            : rowIndex.ToString(CultureInfo.InvariantCulture);
        writer.WriteStartElement(new Row(), [new OpenXmlAttribute("r", "", rowIndexStr)]);

        for (int i = 0; i < headers.Count; i++)
        {
            var getter = propertyGetters[i];
            object? value = getter != null ? getter(record!) : null;

            WriteCellSax(writer, value, sstCache, headers[i].StyleIndexStr);
        }

        writer.WriteEndElement(); // Row
    }

    /// True SAX-based worksheet content recreation for DataTable.
    private static void RecreateWorksheetContentFromDataTable(
        WorksheetPart wsPart,
        Row? headerRow,
        DataTable data,
        List<HeaderInfo> headers,
        DataColumn?[] colArray,
        SharedStringCache sstCache)
    {
        var worksheet = wsPart.Worksheet;
        using var writer = OpenXmlWriter.Create(wsPart);
        writer.WriteStartElement(new Worksheet());

        foreach (var element in worksheet?.Elements() ?? Enumerable.Empty<OpenXmlElement>())
        {
            if (element is not SheetData)
            {
                writer.WriteElement(element);
                continue;
            }

            writer.WriteStartElement(new SheetData());
            if (headerRow != null) writer.WriteElement(headerRow);

            WriteSheetDataFromDataTable(writer, data, headers, colArray, sstCache);

            writer.WriteEndElement(); // SheetData
        }
        writer.WriteEndElement(); // Worksheet
    }

    private static void WriteSheetDataFromDataTable(
        OpenXmlWriter writer,
        DataTable data,
        List<HeaderInfo> headers,
        DataColumn?[] colArray,
        SharedStringCache sstCache)
    {
        if (data.Rows.Count == 0)
        {
            // Write empty row to prevent table breakage
            writer.WriteStartElement(new Row(), [new OpenXmlAttribute("r", "", "2")]);
            writer.WriteEndElement();
            return;
        }

        uint rowIndex = 2;
        foreach (DataRow record in data.Rows)
        {
            WriteRowFromDataRow(writer, rowIndex, record, headers, colArray, sstCache);
            rowIndex++;
        }
    }

    private static void WriteRowFromDataRow(
        OpenXmlWriter writer,
        uint rowIndex,
        DataRow record,
        List<HeaderInfo> headers,
        DataColumn?[] colArray,
        SharedStringCache sstCache)
    {
        // Use cached row index string for common indices
        string rowIndexStr = rowIndex < RowIndexCacheSize
            ? _rowIndexCache[rowIndex - 1]
            : rowIndex.ToString(CultureInfo.InvariantCulture);
        writer.WriteStartElement(new Row(), [new OpenXmlAttribute("r", "", rowIndexStr)]);

        for (int i = 0; i < headers.Count; i++)
        {
            var col = colArray[i];
            object? value = col != null ? record[col] : null;

            WriteCellSax(writer, value, sstCache, headers[i].StyleIndexStr);
        }

        writer.WriteEndElement(); // Row
    }

    // Pre-allocated attribute arrays for WriteCellSax (to avoid List allocation per cell)
    [ThreadStatic] private static OpenXmlAttribute[]? _cellAttrs1;
    [ThreadStatic] private static OpenXmlAttribute[]? _cellAttrs2;

    // Reusable Cell template to avoid allocation per cell
    private static readonly Cell _cellTemplate = new();

    // Reusable CellValue template to avoid allocation per cell (2M+ allocations for 500k rows)
    [ThreadStatic] private static CellValue? _cellValueTemplate;

    // Pre-cached row index strings for common row indices (avoid 500k string allocations)
    private static readonly string[] _rowIndexCache = InitializeRowIndexCache();
    private const int RowIndexCacheSize = 10000;

    private static string[] InitializeRowIndexCache()
    {
        var cache = new string[RowIndexCacheSize];
        for (int i = 0; i < RowIndexCacheSize; i++)
        {
            cache[i] = (i + 1).ToString(CultureInfo.InvariantCulture);
        }
        return cache;
    }

    /// Writes a single cell using SAX with attributes, avoiding per-cell allocations.
    private static void WriteCellSax(
        OpenXmlWriter writer,
        object? value,
        SharedStringCache sstCache,
        string? styleIndexStr)  // Pre-cached to avoid per-cell ToString
    {
        // Determine cell type and value
        string? cellValue = null;
        string? dataType = null;

        if (value != null && value != DBNull.Value)
        {
            switch (value)
            {
                case string strVal:
                    dataType = "s"; // SharedString
                    cellValue = sstCache.GetStringIndexStr(strVal);
                    break;
                case DateTime dtVal:
                    cellValue = dtVal.ToOADate().ToString(CultureInfo.InvariantCulture);
                    break;
                case DateOnly dateVal:
                    cellValue = dateVal.ToDateTime(TimeOnly.MinValue).ToOADate().ToString(CultureInfo.InvariantCulture);
                    break;
                case int intVal:
                    cellValue = intVal.ToString(CultureInfo.InvariantCulture);
                    break;
                case long longVal:
                    cellValue = longVal.ToString(CultureInfo.InvariantCulture);
                    break;
                case decimal decVal:
                    cellValue = decVal.ToString(CultureInfo.InvariantCulture);
                    break;
                case double dblVal:
                    if (!double.IsNaN(dblVal) && !double.IsInfinity(dblVal))
                        cellValue = dblVal.ToString(CultureInfo.InvariantCulture);
                    break;
                case bool boolVal:
                    dataType = "b"; // Boolean
                    cellValue = boolVal ? "1" : "0";
                    break;
                default:
                    dataType = "s"; // SharedString for other types
                    cellValue = sstCache.GetStringIndexStr(value.ToString() ?? "");
                    break;
            }
        }

        // Use pre-allocated arrays based on attribute count needed (0, 1 or 2 attrs)
        // Build attributes in a simple, allocation-free way
        OpenXmlAttribute[] attrs;
        int attrCount;

        if (dataType != null && styleIndexStr != null)
        {
            _cellAttrs2 ??= new OpenXmlAttribute[2];
            attrs = _cellAttrs2;
            attrs[0] = new OpenXmlAttribute("t", "", dataType);
            attrs[1] = new OpenXmlAttribute("s", "", styleIndexStr);
            attrCount = 2;
        }
        else if (dataType != null)
        {
            _cellAttrs1 ??= new OpenXmlAttribute[1];
            attrs = _cellAttrs1;
            attrs[0] = new OpenXmlAttribute("t", "", dataType);
            attrCount = 1;
        }
        else if (styleIndexStr != null)
        {
            _cellAttrs1 ??= new OpenXmlAttribute[1];
            attrs = _cellAttrs1;
            attrs[0] = new OpenXmlAttribute("s", "", styleIndexStr);
            attrCount = 1;
        }
        else
        {
            // No attributes
            _cellAttrs1 ??= new OpenXmlAttribute[1];
            attrs = _cellAttrs1;
            attrCount = 0;
        }

        // Use ArraySegment to avoid LINQ .Take() iterator allocation
        writer.WriteStartElement(_cellTemplate, new ArraySegment<OpenXmlAttribute>(attrs, 0, attrCount));
        if (cellValue != null)
        {
            // Reuse CellValue template to avoid 2M+ allocations (500k rows × 4 columns)
            _cellValueTemplate ??= new CellValue();
            _cellValueTemplate.Text = cellValue;
            writer.WriteElement(_cellValueTemplate);
        }
        writer.WriteEndElement(); // Cell
    }

    private static Row CreateNewHeaderRow(PropertyInfo[] selectedProps, List<HeaderInfo> headers)
    {
        var headerRow = new Row { RowIndex = 1 };
        int colIndex = 1;

        foreach (var prop in selectedProps)
        {
            string displayName = GetPropertyDisplayName(prop);
            string colLetter = GetColumnName(colIndex);

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

            headers.Add(new HeaderInfo { Name = displayName, ColumnIndex = colIndex, ColumnLetter = colLetter });
            colIndex++;
        }
        return headerRow;
    }

    private static Row CreateNewHeaderRowFromDataColumns(List<DataColumn> selectedCols, List<HeaderInfo> headers)
    {
        var headerRow = new Row { RowIndex = 1 };
        int colIndex = 1;

        foreach (var col in selectedCols)
        {
            string colLetter = GetColumnName(colIndex);

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

            headers.Add(new HeaderInfo { Name = col.ColumnName, ColumnIndex = colIndex, ColumnLetter = colLetter });
            colIndex++;
        }
        return headerRow;
    }

    private static void AppendMissingHeaders(Row headerRow, PropertyInfo[] selectedProps, WorkbookPart? wbPart)
    {
        var existingNames = new HashSet<string>(
            headerRow.Elements<Cell>().Select(c => GetCellValue(c, wbPart) ?? ""),
            StringComparer.OrdinalIgnoreCase);

        int colIndex = headerRow.Elements<Cell>()
            .Select(c => GetColumnIndex(c.CellReference?.Value ?? ""))
            .DefaultIfEmpty(0).Max() + 1;

        foreach (var prop in selectedProps)
        {
            string displayName = GetPropertyDisplayName(prop);
            if (existingNames.Contains(displayName)) continue;

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

    private static void AppendMissingDataColumnHeaders(Row headerRow, List<DataColumn> selectedCols, WorkbookPart? wbPart)
    {
        var existingNames = new HashSet<string>(
            headerRow.Elements<Cell>().Select(c => GetCellValue(c, wbPart) ?? ""),
            StringComparer.OrdinalIgnoreCase);

        int colIndex = headerRow.Elements<Cell>()
            .Select(c => GetColumnIndex(c.CellReference?.Value ?? ""))
            .DefaultIfEmpty(0).Max() + 1;

        foreach (var col in selectedCols)
        {
            if (existingNames.Contains(col.ColumnName)) continue;

            headerRow.Append(new Cell
            {
                CellReference = GetColumnName(colIndex) + "1",
                DataType = CellValues.String,
                CellValue = new CellValue(col.ColumnName),
            });
            colIndex++;
        }
    }

    private static void PopulateHeadersFromRow(Row headerRow, List<HeaderInfo> headers, WorkbookPart? wbPart)
    {
        headers.Clear();
        int currentImplicitIdx = 0;

        foreach (var cell in headerRow.Elements<Cell>())
        {
            var colIdx = GetColumnIndex(cell.CellReference?.Value ?? "");
            if (colIdx == 0) colIdx = currentImplicitIdx + 1;

            headers.Add(new HeaderInfo
            {
                Name = GetCellValue(cell, wbPart) ?? "",
                ColumnIndex = colIdx,
                ColumnLetter = GetColumnName(colIdx),
                StyleIndex = cell.StyleIndex?.Value ?? 0U
            });

            currentImplicitIdx = colIdx;
        }
        headers.Sort((a, b) => a.ColumnIndex.CompareTo(b.ColumnIndex));
    }

    private static string GetPropertyDisplayName(PropertyInfo prop)
    {
        var displayAttr = prop.GetCustomAttribute<DisplayAttribute>();
        return !string.IsNullOrWhiteSpace(displayAttr?.Name)
            ? displayAttr.Name
            : Util.String.GetColDisplayName(prop);
    }

    private static void ApplyStylesFromDisplayAttributes(SpreadsheetDocument doc, PropertyInfo[] selectedProps, List<HeaderInfo> headers)
    {
        foreach (var prop in selectedProps)
        {
            var displayAttr = prop.GetCustomAttribute<DisplayAttribute>();
            if (string.IsNullOrWhiteSpace(displayAttr?.Description)) continue;

            var parts = displayAttr.Description.Split(",");
            if (parts.Length < 4) continue;

            try
            {
                var excelFormat = ConvertKendoNumberFormatToExcelNumberFormat(parts[3]);
                var displayName = GetPropertyDisplayName(prop);
                var styleIndex = GetOrCreateStyleIndex(doc, excelFormat);

                var matchingHeader = headers.Find(h => string.Equals(h.Name, displayName, StringComparison.OrdinalIgnoreCase));
                matchingHeader?.StyleIndex = styleIndex;
            }
            catch { /* Format conversion failed, skip */ }
        }
    }

    public static WorkbookPart GetOrCreateWorkbookPart(SpreadsheetDocument doc)
    {
        if (doc.WorkbookPart != null)
            return doc.WorkbookPart;

        var 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 ?? 1U;
        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();

        if (stylesPart == null)
        {
            stylesPart = wbPart.AddNewPart<WorkbookStylesPart>();
            stylesPart.Stylesheet = CreateDefaultStylesheet();
        }
        else
            stylesPart.Stylesheet ??= CreateDefaultStylesheet();

        return stylesPart;
    }

    private static Stylesheet CreateDefaultStylesheet() => new(
        new Fonts(new Font()),
        new Fills(new Fill(new PatternFill { PatternType = PatternValues.None })),
        new Borders(new Border()),
        new CellFormats(new CellFormat())
    );

    private static SharedStringTablePart CreateSharedStringTablePart(WorkbookPart wbPart)
    {
        var sstPart = wbPart.AddNewPart<SharedStringTablePart>();
        sstPart.SharedStringTable = new SharedStringTable();
        return sstPart;
    }

    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(
            stream,
            ZipArchiveMode.Update,
            true
        );
        ZipArchiveEntry? calcChainEntry = archive.GetEntry(calcChainPath);
        calcChainEntry?.Delete();
    }

    public static void RemoveCalculationChain(SpreadsheetDocument doc)
    {
        var calcChainPart = doc.WorkbookPart?.CalculationChainPart;
        if (calcChainPart != null)
            doc.WorkbookPart!.DeletePart(calcChainPart);
    }

    public class HeaderInfo
    {
        public required string Name { get; set; }
        public int ColumnIndex { get; set; }
        public uint StyleIndex { get; set; }
        /// Pre-cached column letter (e.g., "A", "B", "AA") to avoid per-cell GetColumnName calls
        public string ColumnLetter { get; set; } = "";
        /// Pre-cached StyleIndex.ToString() to avoid per-cell string allocations
        public string? StyleIndexStr { get; set; }
    }

    /// Sets the number formats for specific columns based on their headers.
    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");
    }

    /// Gets or creates a cell style index for the specified number format.
    private static uint GetOrCreateStyleIndex(SpreadsheetDocument doc, string numberFormatCode)
    {
        var stylesPart = GetOrCreateStylesPart(doc);
        var cellFormats = stylesPart.Stylesheet?.CellFormats ??= new CellFormats();

        uint formatId = GetOrCreateNumberFormat(stylesPart.Stylesheet!, numberFormatCode);

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

        if (existingCellFormat != null && cellFormats != null)
            return (uint)cellFormats.Elements<CellFormat>().ToList().IndexOf(existingCellFormat);

        var styleIndex = (uint)(cellFormats?.Count() ?? 0);
        cellFormats?.Append(new CellFormat { NumberFormatId = formatId, ApplyNumberFormat = true });
        cellFormats?.Count = (uint)cellFormats.Count();
        return styleIndex;
    }

    /// Gets or creates a NumberingFormat and returns its ID.
    private static uint GetOrCreateNumberFormat(Stylesheet stylesheet, string numberFormatCode)
    {
        const uint firstCustomFormatId = 164;
        var numberingFormats = stylesheet.NumberingFormats ??= new NumberingFormats();

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

        if (existingFormat != null)
            return existingFormat.NumberFormatId!;

        uint 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;
        return formatId;
    }

    /// 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.
    private static void ApplyColumnFormatByHeader(
        SpreadsheetDocument doc,
        SheetData sheetData,
        List<HeaderInfo> headers,
        string headerSearchText,
        string numberFormatCode,
        bool exactMatch = false)
    {
        var stylesPart = GetOrCreateStylesPart(doc);
        var cellFormats = stylesPart.Stylesheet?.CellFormats ??= new CellFormats();

        uint formatId = GetOrCreateNumberFormat(stylesPart.Stylesheet!, numberFormatCode);

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

        uint styleIndex = (existingCellFormat != null && cellFormats != null)
            ? (uint)cellFormats.Elements<CellFormat>().ToList().IndexOf(existingCellFormat)
            : CreateAndAppendCellFormat(cellFormats!, formatId);

        ApplyStyleToMatchingColumns(sheetData, headers, headerSearchText, exactMatch, styleIndex);
    }

    private static uint CreateAndAppendCellFormat(CellFormats cellFormats, uint formatId)
    {
        var styleIndex = (uint)cellFormats.Count();
        cellFormats.Append(new CellFormat { NumberFormatId = formatId, ApplyNumberFormat = true });
        cellFormats.Count = (uint)cellFormats.Count();
        return styleIndex;
    }

    private static void ApplyStyleToMatchingColumns(
        SheetData sheetData,
        List<HeaderInfo> headers,
        string headerSearchText,
        bool exactMatch,
        uint styleIndex)
    {
        foreach (var header in headers)
        {
            bool isMatch = exactMatch
                ? string.Equals(header.Name, headerSearchText, StringComparison.OrdinalIgnoreCase)
                : header.Name.Contains(headerSearchText, StringComparison.OrdinalIgnoreCase);

            if (!isMatch) continue;

            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);
                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?.CellValue == null || workbookPart == null)
            return null;

        string value = cell.CellValue.InnerText;

        if (cell.DataType?.Value != CellValues.SharedString)
            return value;

        var sstPart = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
        return sstPart?.SharedStringTable != null && int.TryParse(value, out int ssid)
            ? sstPart.SharedStringTable.Elements<SharedStringItem>().ElementAtOrDefault(ssid)?.InnerText
            : null;
    }

    private static void SetCellValue(
        Cell cell,
        object? value,
        SharedStringCache? sstCache = null,
        uint? styleIndex = null,
        CellValues? explicitType = null)
    {
        (cell.DataType, cell.CellValue) = ResolveCellTypeAndValue(value, sstCache, explicitType);

        if (styleIndex is > 0)
            cell.StyleIndex = styleIndex.Value;
    }

    private static (CellValues? DataType, CellValue? CellValue) ResolveCellTypeAndValue(
        object? value,
        SharedStringCache? sstCache,
        CellValues? explicitType)
    {
        if (value == null || value == DBNull.Value)
            return (null, null);

        if (explicitType.HasValue)
            return (explicitType.Value, new CellValue(value.ToString() ?? ""));

        return value switch
        {
            string strVal when sstCache != null =>
                (CellValues.SharedString, new CellValue(sstCache.GetStringIndexStr(strVal))),
            string strVal =>
                (CellValues.String, new CellValue(strVal)),
            DateTime dtVal =>
                (null, new CellValue(dtVal.ToOADate().ToString(CultureInfo.InvariantCulture))),
            DateOnly dateVal =>
                (null, new CellValue(dateVal.ToDateTime(TimeOnly.MinValue).ToOADate().ToString(CultureInfo.InvariantCulture))),
            int or long or decimal or double =>
                FormatNumericValue(Convert.ToDouble(value, CultureInfo.InvariantCulture)),
            bool boolVal =>
                (CellValues.Boolean, new CellValue(boolVal ? "1" : "0")),
            _ =>
                (CellValues.String, new CellValue(value.ToString() ?? ""))
        };
    }

    private static (CellValues? DataType, CellValue? CellValue) FormatNumericValue(double dblVal) =>
        double.IsNaN(dblVal) || double.IsInfinity(dblVal)
            ? (null, null)
            : (CellValues.Number, new CellValue(dblVal.ToString(CultureInfo.InvariantCulture)));

    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)
    {
        var sheet = document.WorkbookPart!.Workbook?.GetFirstChild<Sheets>()?
            .Elements<Sheet>().FirstOrDefault(s => s.Name == sheetName);

        return sheet?.Id?.Value is { } relationshipId
            ? (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId)
            : null;
    }

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

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

        DeleteCellsFromFirstEmptyHeader(doc, sheetName);
    }

    /// deletes cells on or after the column of the first empty header cell
    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)
            ?? throw new ArgumentNullException($"Sheet '{sheetName}' not found.");

        var wsPart = (WorksheetPart)wbPart.GetPartById(sheet.Id!);
        var sheetData = wsPart.Worksheet?.Elements<SheetData>().FirstOrDefault();
        if (sheetData == null)
            return;

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

        string rangeAddress = $"$A$1:${GetColumnName(lastCol)}${lastRow}";
        string rangeDefinition = $"'{sheetName}'!{rangeAddress}";
        string myRangeName = sheetName.Replace(" ", "") + "_Datasource";

        var definedNames = wbPart.Workbook.DefinedNames ??= new DefinedNames();
        var existingName = definedNames.Elements<DefinedName>().FirstOrDefault(dn => dn.Name == myRangeName);

        if (existingName == null)
            definedNames.Append(new DefinedName { Name = myRangeName, Text = rangeDefinition });
        else
            existingName.Text = rangeDefinition;
    }

    private static uint GetRowIndex(string cellReference) =>
        string.IsNullOrEmpty(cellReference) ? 0
            : DigitsRegex().Match(cellReference) is { Success: true } match ? uint.Parse(match.Value) : 0;

    /// 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".
    /// returns The 1-based column index, or 0 if the input is invalid.
    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;
    }

    private static readonly string[] _cachedColumnNames = InitializeColumnNames(1024);

    private static string[] InitializeColumnNames(int count)
    {
        var names = new string[count + 1];
        for (int i = 1; i <= count; i++)
        {
            names[i] = GenerateColumnName(i);
        }
        return names;
    }

    // Converts a 1-based column index to its corresponding Excel column name.
    // Returns an empty string if columnIndex is less than 1.
    private static string GenerateColumnName(int columnIndex)
    {
        var columnName = string.Empty;
        while (columnIndex > 0)
        {
            columnIndex--;
            columnName = Convert.ToChar('A' + (columnIndex % 26)) + columnName;
            columnIndex /= 26;
        }
        return columnName;
    }

    public static string GetColumnName(int columnIndex) =>
        columnIndex > 0 && columnIndex < _cachedColumnNames.Length
            ? _cachedColumnNames[columnIndex]
            : GenerateColumnName(columnIndex);

    /// Sorts cells within a row by their column index.
    /// OpenXML requires cells in a row to be in ascending column order.
    /// Call this after modifying cells in a row to ensure sequential ordering is maintained.
    private static void NormalizeCellOrder(Row row)
    {
        var cells = row.Elements<Cell>()
            .Select(c => (Cell: c, ColumnIndex: GetColumnIndex(c.CellReference?.Value ?? "")))
            .Where(c => c.ColumnIndex > 0)
            .OrderBy(c => c.ColumnIndex)
            .Select(c => c.Cell)
            .ToList();

        row.RemoveAllChildren<Cell>();
        row.Append(cells);
    }

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

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

        wbProps.RefreshAllConnections = new BooleanValue(true);
    }

    public static void SynchronizeTableDefinitionsOnSheet(
        SpreadsheetDocument doc,
        string sheetName)
    {
        var wsPart = GetWorksheetPartByName(doc, sheetName);
        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)
        {
            if (tablePart.Table != null)
                SynchronizeTable(doc, tablePart.Table, sheetData!, lastSheetRow);
        }
    }

    private static void SynchronizeTable(SpreadsheetDocument doc, Table table, SheetData sheetData, uint lastSheetRow)
    {
        if (table.Reference?.Value?.Split(':') is not { Length: 2 } parts || table.TableColumns == null)
            return;

        string startCellRef = parts[0];
        if (string.IsNullOrEmpty(startCellRef))
            return;

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

        var sortedCells = headerRow.Elements<Cell>()
            .Select(c => (Cell: c, Index: GetColumnIndex(c.CellReference?.Value ?? "")))
            .Where(x => x.Index > 0)
            .OrderBy(x => x.Index)
            .ToList();

        int lastColIndex = sortedCells.Select(x => x.Index).DefaultIfEmpty(0).Max();

        // Rebuild TableColumns
        table.TableColumns.RemoveAllChildren<TableColumn>();
        var uniqueNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        uint columnId = 1;

        foreach (var (cell, _) in sortedCells)
        {
            string name = GetCellValue(cell, doc.WorkbookPart) ?? $"Column{columnId}";
            string uniqueName = GetUniqueName(name, uniqueNames);
            table.TableColumns.Append(CreateTableColumnWithUid(columnId++, uniqueName));
        }
        table.TableColumns.Count = (uint)table.TableColumns.ChildElements.Count;

        // Update references
        string newRef = $"{startCellRef}:{GetColumnName(lastColIndex)}{lastSheetRow}";
        table.Reference.Value = newRef;
        if (table.AutoFilter?.Reference != null)
            table.AutoFilter.Reference.Value = newRef;
    }

    private static string GetUniqueName(string baseName, HashSet<string> existingNames)
    {
        string name = baseName;
        int suffix = 1;
        while (!existingNames.Add(name))
            name = $"{baseName}{++suffix}";
        return name;
    }

    private static TableColumn CreateTableColumnWithUid(uint id, string name)
    {
        var col = new TableColumn { Id = id, Name = name };
        col.SetAttribute(new OpenXmlAttribute(
            "xr3", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3",
            $"{{{Guid.NewGuid().ToString().ToUpper()}}}"));
        return col;
    }

    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>();

        var wbPart = doc.WorkbookPart;
        if (wbPart?.Workbook == null)
            return formulas;

        var sheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
        if (sheet?.Id?.Value == null || !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 = BuildCalculatedColumnsLookup(wsPart);

        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!);
                columnIndexToHeaderName[colIndex] = GetCellValue(cell, wbPart) ?? $"Column_{colIndex}";
            }
        }

        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;
    }

    /// adjusts a shared formula for a given target cell
    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;
        int colOffset = GetColumnIndex(targetCellRef) - GetColumnIndex(masterRef);
        int rowOffset = (int)(GetRowIndex(targetCellRef) - GetRowIndex(masterRef));

        string adjustedFormula = ColumnLettersRegex().Replace(baseFormula, m =>
            GetColumnName(GetColumnIndex(m.Value) + colOffset));

        return RowNumbersRegex().Replace(adjustedFormula, m =>
            (int.Parse(m.Value) + rowOffset).ToString(CultureInfo.InvariantCulture));
    }

    private static Dictionary<string, HashSet<string>> BuildCalculatedColumnsLookup(WorksheetPart wsPart)
    {
        var result = new Dictionary<string, HashSet<string>>();
        if (wsPart.TableDefinitionParts == null) return result;

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

            var calculatedNames = tablePart.Table.TableColumns.Elements<TableColumn>()
                .Where(tc => tc.Name != null && tc.GetFirstChild<CalculatedColumnFormula>() != null)
                .Select(tc => tc.Name!.Value!)
                .ToHashSet(StringComparer.OrdinalIgnoreCase);

            if (calculatedNames.Count > 0)
                result[tablePart.Table.DisplayName!] = calculatedNames;
        }
        return result;
    }

    /// check if a cell is within a table's range string (e.g., "A1:G10")
    private static bool IsCellInTable(string cellReference, StringValue? tableReference)
    {
        if (tableReference?.Value?.Split(':') is not { Length: 2 } parts)
            return false;

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

        return cellRow >= GetRowIndex(parts[0]) && cellRow <= GetRowIndex(parts[1])
            && cellCol >= GetColumnIndex(parts[0]) && cellCol <= GetColumnIndex(parts[1]);
    }

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

        if (templateFormulas.Count == 0)
            return;

        dataRowCount = Math.Max(dataRowCount, 1); // add formulas to at least 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 = FixedNamedRangeRegex();

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

            var newFormulaCells = templateFormulas
                .Where(f => !string.IsNullOrEmpty(f.FormulaText))
                .Select(f => CreateFormulaCell(f, currentRowIndex, fixedNamedRangeRegex))
                .ToList();

            var rowCells = row.Elements<Cell>();
            var existingCellsWithIdx = new List<(Cell Cell, int ColumnIndex)>();
            int lastKnownIdx = 0;

            foreach (var c in rowCells)
            {
                int idx = GetColumnIndex(c.CellReference?.Value ?? "");
                if (idx == 0) idx = lastKnownIdx + 1;

                existingCellsWithIdx.Add((c, idx));
                lastKnownIdx = idx;
            }

            var allCells = existingCellsWithIdx
                .Where(c => c.ColumnIndex > 0 && !formulaColIndexes.Contains(c.ColumnIndex))
                .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 (Cell Cell, int ColumnIndex) CreateFormulaCell(
        TemplateFormulaInfo info,
        uint currentRowIndex,
        Regex fixedNamedRangeRegex)
    {
        string cellRef = GetColumnName(info.ColumnIndex) + currentRowIndex;

        string formulaText = fixedNamedRangeRegex.IsMatch(info.FormulaText)
            ? info.FormulaText
            : AdjustFormula(info.FormulaText, (int)(currentRowIndex - info.RowIndex));

        var cell = new Cell
        {
            CellReference = cellRef,
            CellFormula = new CellFormula(formulaText),
            StyleIndex = info.StyleIndex
        };

        return (cell, info.ColumnIndex);
    }

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

        var matches = FormulaRowNumbersRegex().Matches(newFormula);

        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)
    {
        if (wsPart.Worksheet?.GetFirstChild<SheetData>() is { } existingSheetData)
            return existingSheetData;

        var sheetData = new SheetData();
        if (wsPart.Worksheet != null)
            InsertSheetDataAtCorrectPosition(wsPart.Worksheet, sheetData);
        return sheetData;
    }

    private static void InsertSheetDataAtCorrectPosition(Worksheet worksheet, SheetData sheetData)
    {
        var insertAfter = (OpenXmlElement?)worksheet.GetFirstChild<Columns>()
            ?? (OpenXmlElement?)worksheet.GetFirstChild<SheetFormatProperties>()
            ?? worksheet.GetFirstChild<SheetViews>();

        if (insertAfter != null)
            worksheet.InsertAfter(sheetData, insertAfter);
        else
            worksheet.AppendChild(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 = prop.GetCustomAttribute<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)
    {
        foreach (var e in worksheet.Elements<SheetViews>())
            e.Remove();

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

        sheetView.Append(new Pane
        {
            VerticalSplit = 1D,
            TopLeftCell = "A2",
            ActivePane = PaneValues.BottomLeft,
            State = PaneStateValues.Frozen
        });

        sheetView.Append(new Selection
        {
            Pane = PaneValues.BottomLeft,
            ActiveCell = "A2",
            SequenceOfReferences = new ListValue<StringValue> { InnerText = "A2" }
        });

        sheetViews.Append(sheetView);
        InsertSheetViewsAtCorrectPosition(worksheet, sheetViews);
    }

    private static void InsertSheetViewsAtCorrectPosition(Worksheet worksheet, SheetViews sheetViews)
    {
        var insertBefore = (OpenXmlElement?)worksheet.GetFirstChild<SheetFormatProperties>()
            ?? (OpenXmlElement?)worksheet.GetFirstChild<Columns>()
            ?? worksheet.GetFirstChild<SheetData>();

        if (insertBefore != null)
            worksheet.InsertBefore(sheetViews, insertBefore);
        else
            worksheet.Append(sheetViews);
    }

    /// Persists every part that has been modified since the document was opened,
    /// and flushes the changes to disk or memory.
    /// Call once after you finish all edits to avoid the cost of repeated saves.
    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();
    }

    /// manages a SharedStringTable cache for fast access
    /// and a bulk-append at the end
    private static class ReflectionCache
    {
        private static readonly ConcurrentDictionary<Type, ConcurrentDictionary<string, Func<object, object?>>> _cache = new();

        public static Func<object, object?> GetGetter(PropertyInfo prop)
        {
            // Handle null DeclaringType (unlikely for properties but possible in edge cases)
            if (prop.DeclaringType == null) return obj => prop.GetValue(obj);

            var typeCache = _cache.GetOrAdd(prop.DeclaringType, _ => new ConcurrentDictionary<string, Func<object, object?>>());
            return typeCache.GetOrAdd(prop.Name, _ => CreateGetter(prop));
        }

        private static Func<object, object?> CreateGetter(PropertyInfo property)
        {
            var instance = Expression.Parameter(typeof(object), "instance");
            var castInstance = Expression.Convert(instance, property.DeclaringType!);
            var propertyAccess = Expression.Property(castInstance, property);
            var castResult = Expression.Convert(propertyAccess, typeof(object));
            return Expression.Lambda<Func<object, object?>>(castResult, instance).Compile();
        }
    }

    private class SharedStringCache
    {
        private readonly SharedStringTable _sharedStringTable;
        private readonly Dictionary<string, string> _stringCache;
        private readonly List<SharedStringItem> _newItems = new();
        private int _nextIndex;

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

            // We must iterate all items to establish the correct next index,
            // while building a unique lookup for existing strings.
            // If duplicate strings exist in the table, we map to the first occurrence's index,
            // but we MUST increment the logical index for all of them so new items start at the end.
            int i = 0;
            foreach (var item in sst.Elements<SharedStringItem>())
            {
                var text = item.InnerText;
                if (text != null && !_stringCache.ContainsKey(text))
                    _stringCache[text] = i.ToString(CultureInfo.InvariantCulture);

                i++;
            }
            _nextIndex = i;
        }

        /// gets the index string for a given string (caching the ToString result)
        /// returns the string representation of the 0-based index
        public string GetStringIndexStr(string text)
        {
            if (_stringCache.TryGetValue(text, out string? indexStr))
                return indexStr;

            int newIndex = _nextIndex++;
            indexStr = newIndex.ToString(CultureInfo.InvariantCulture);

            _stringCache.Add(text, indexStr);
            _newItems.Add(new SharedStringItem(new Text(text)));

            return indexStr;
        }

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

            // Update counts. Count is total items. UniqueCount is unique items.
            // _nextIndex represents total items (existing + new).
            _sharedStringTable.Count = (uint)_nextIndex;
            _sharedStringTable.UniqueCount = (uint)_stringCache.Count;
        }
    }

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

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

        var columnWidths = GetColumnWidths(ws, sheetData, wbPart.SharedStringTablePart, wbPart.WorkbookStylesPart?.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)
            });
        }

        ws.Elements<Columns>().FirstOrDefault()?.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 int sampleRows = 1000; // limit rows for performance
        const double padding = 2.0;
        const double filterButtonPadding = 3.0; // Extra padding for the header filter dropdown
        // Average character width relative to digit "0" for proportional fonts.
        // Bold text is ~7% wider on average.
        const double regularCharFactor = 1.0;
        const double boldCharFactor = 1.07;

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

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

        // Pre-build shared string lookup array for O(1) access
        string[]? sstLookup = null;
        if (sstPart?.SharedStringTable != null)
        {
            var items = sstPart.SharedStringTable.Elements<SharedStringItem>().ToArray();
            sstLookup = new string[items.Length];
            for (int i = 0; i < items.Length; i++)
                sstLookup[i] = items[i].InnerText ?? "";
        }

        // Build merged cell lookup as a HashSet for O(1) checks
        var mergedCells = new HashSet<(int col, uint row)>();
        if (ws.Elements<MergeCells>().FirstOrDefault() is MergeCells mergeCellsElement)
        {
            foreach (var mergeCell in mergeCellsElement.Elements<MergeCell>())
            {
                if (mergeCell.Reference?.Value == null)
                    continue;

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

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

                for (int c = startCol; c <= endCol; c++)
                    for (uint r = startRow; r <= endRow; r++)
                        mergedCells.Add((c, r));
            }
        }

        foreach (var row in sheetData.Elements<Row>().Take(sampleRows))
        {
            var isHeaderRow = row.RowIndex?.Value == 1U;
            var currentRowIndex = row.RowIndex?.Value ?? 0U;
            double charFactor = isHeaderRow ? boldCharFactor : regularCharFactor;

            // Use 1-based positional index for cells that lack CellReference (SAX-optimized rows)
            int cellPosition = 0;
            foreach (var cell in row.Elements<Cell>())
            {
                cellPosition++; // 1-based column index

                int colIndex;
                uint rowIndex;

                if (cell.CellReference?.Value != null)
                {
                    colIndex = GetColumnIndex(cell.CellReference.Value);
                    rowIndex = GetRowIndex(cell.CellReference.Value);
                }
                else
                {
                    // SAX-written cells without 'r' attribute: use positional index
                    colIndex = cellPosition;
                    rowIndex = currentRowIndex;
                }

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

                if (mergedCells.Count > 0 && mergedCells.Contains((colIndex, rowIndex)))
                    continue;

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

                double width = text.Length * charFactor;

                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, string[]? sstLookup, 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) && sstLookup != null && (uint)idx < (uint)sstLookup.Length)
                return sstLookup[idx];
            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))
                return val.ToString(CultureInfo.CurrentCulture);

            // Try as Date/Time first
            if (TryFormatAsDateTime(val, excelFormat, out string? dateResult))
                return dateResult!;

            return FormatAsNumber(val, excelFormat);
        }

        // Fallback
        return raw;
    }

    private static bool TryFormatAsDateTime(double val, string excelFormat, out string? result)
    {
        result = null;
        string sanitized = SanitizeFormatRegex().Replace(excelFormat, "");

        if (!DateTimeFormatRegex().IsMatch(sanitized))
            return false;

        try
        {
            result = DateTime.FromOADate(val).ToString(excelFormat, CultureInfo.CurrentCulture);
            return true;
        }
        catch
        {
            return false;
        }
    }

    private static string FormatAsNumber(double val, string excelFormat)
    {
        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];

        string cleanFormat = CleanFormatColorsRegex().Replace(activeFormat, "");
        cleanFormat = CleanFormatPaddingRegex().Replace(cleanFormat, "");

        try
        {
            string result = val.ToString(cleanFormat, CultureInfo.CurrentCulture);

            // Add padding space if negative format uses parentheses
            if (activeFormat.Contains("_)") && parts.Length > 1 && parts[1].Contains('('))
                result += " ";

            return result;
        }
        catch (FormatException)
        {
            return val.ToString(CultureInfo.CurrentCulture);
        }
    }

    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 = YearFormatRegex().Replace(fmt, "yy");

        fmt = fmt.Replace("mmmm", "MMMM");
        fmt = fmt.Replace("mmm", "MMM");
        fmt = MonthFormatRegex().Replace(fmt, "MM");
        fmt = SingleMonthFormatRegex().Replace(fmt, "M");

        fmt = fmt.Replace("dd", "dd");
        fmt = SingleDayFormatRegex().Replace(fmt, "d");

        fmt = fmt.Replace("hh", "HH");
        fmt = SingleHourFormatRegex().Replace(fmt, "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, "@" }, // Text

        // 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();
        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>().Select(s => s.SheetId?.Value ?? 0).DefaultIfEmpty(0U).Max() + 1;
        sheets.Append(new Sheet
        {
            Id = wbPart.GetIdOfPart(wsPart),
            SheetId = nextSheetId,
            Name = sheetName,
        });

        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>().Select(s => s.SheetId?.Value ?? 0).DefaultIfEmpty(0U).Max() + 1;
            sheets.Append(new Sheet { Id = wbPart.GetIdOfPart(wsPart), SheetId = sheetId, Name = "Info" });
        }
        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)));
        var cell = GetOrCreateCell(sheetData, (int)rowIndex, GetColumnIndex(string.Concat(cellReference.Where(char.IsLetter))));

        cell.InlineString = new InlineString(new Text(message));
        cell.DataType = CellValues.InlineString;
        cell.CellValue = null;
        cell.StyleIndex = 9; // BoldStyleIndex
    }

    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();
    }

    /// <summary>
    /// Completely reinitializes the workbook by deleting the WorkbookPart and recreating it.
    /// This ensures [Content_Types].xml and relationships are clean with no stale references.
    /// Use this when you want to fully rebuild the workbook content from scratch.
    /// </summary>
    public static void ReinitializeWorkbook(SpreadsheetDocument doc)
    {
        // Delete the existing WorkbookPart if present
        if (doc.WorkbookPart != null)
            doc.DeletePart(doc.WorkbookPart);

        // Create fresh WorkbookPart
        var wbPart = doc.AddWorkbookPart();
        wbPart.Workbook = new Workbook();
        wbPart.Workbook.AppendChild(new Sheets());

        // Initialize the universal stylesheet
        InitializeUniversalStylesheet(doc);

        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)
    {
        var mergeCells = wsPart.Worksheet?.Elements<MergeCells>().FirstOrDefault();
        if (mergeCells == null)
        {
            mergeCells = new MergeCells();
            var insertAfter = wsPart.Worksheet?.Elements<CustomSheetView>().FirstOrDefault()
                ?? (OpenXmlElement?)wsPart.Worksheet?.Elements<SheetData>().First();
            if (insertAfter != null && wsPart.Worksheet != null)
                wsPart.Worksheet.InsertAfter(mergeCells, insertAfter);
        }

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

    public static Cell InsertFormulaCell(int colIndex, int rowIndex, WorksheetPart wsPart, string formula)
    {
        var sheetData = wsPart.Worksheet?.GetFirstChild<SheetData>();
        ArgumentNullException.ThrowIfNull(sheetData, "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)
    {
        if (sheetData.Elements<Row>().FirstOrDefault(r => (r.RowIndex ?? 0U) == rowIndex) is { } existingRow)
            return existingRow;

        var row = new Row { RowIndex = rowIndex };

        if (!keepOrder)
        {
            sheetData.Append(row);
            return row;
        }

        var refRow = sheetData.Elements<Row>().FirstOrDefault(r => (r.RowIndex ?? 0U) > rowIndex);
        if (refRow != null)
            sheetData.InsertBefore(row, refRow);
        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 cellReference = GetColumnName(colIndex) + rowIndex;

        if (row.Elements<Cell>().FirstOrDefault(c =>
            string.Equals(c.CellReference, cellReference, StringComparison.OrdinalIgnoreCase)) is { } existingCell)
            return existingCell;

        var cell = new Cell { CellReference = cellReference };

        if (!keepOrder)
        {
            row.Append(cell);
            return cell;
        }

        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);

        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);
        }
    }

    /// Creates an Excel Table around the filled data range.
    public static void AddTableToSheet(SpreadsheetDocument doc, string sheetName)
    {
        var wsPart = GetWorksheetPartByName(doc, sheetName);
        var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>();
        if (sheetData == null || !sheetData.Elements<Row>().Any())
            return;

        var rows = sheetData.Elements<Row>().ToList();
        int lastRow = rows.Max(r => (int)(r.RowIndex?.Value ?? 0));
        int lastCol = rows.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 = InvalidTableNameCharsRegex().Replace(sheetName, "_");
        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);
    }

    /// Creates an Excel Table around a specific range of cells.
    public static void AddTableToRange(
        WorksheetPart wsPart,
        uint tableId,
        string reference,
        string[] columnNames,
        string? tableName = null,
        bool showRowStripes = false)
    {
        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 = GetUniqueName(name, uniqueNames);
            tableColumns.Append(CreateTableColumnWithUid(colId++, uniqueName));
        }

        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;
    }

    /// 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.
    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);
            }
        }

        /// Adds the specified CellFormat to the cache if an identical format is not already present.
        /// Returns the index of the existing or newly added CellFormat in the cache.
        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;
        }

        /// Adds the specified Border to the cache if an identical border is not already present.
        /// Returns the index of the existing or newly added Border in the cache.
        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;
        }

        /// Returns a clone of the CellFormat at the given index.
        /// 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.
        public CellFormat GetCellFormatClone(uint index) =>
            (CellFormat)_cellFormats[(int)index].CloneNode(true);

        /// Returns a clone of the Border at the given index.
        /// 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.
        public Border GetBorderClone(uint index) =>
            (Border)_borders[(int)index].CloneNode(true);

        /// Commits the cached CellFormats and Borders back to the Stylesheet,
        /// replacing any existing ones.
        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;
        }
    }

    [GeneratedRegex(@"\d+")]
    private static partial Regex DigitsRegex();

    [GeneratedRegex(@"(?<!\$)([A-Z]{1,3})(?=\d)")]
    private static partial Regex ColumnLettersRegex();

    [GeneratedRegex(@"(?<!\$)(\d+)")]
    private static partial Regex RowNumbersRegex();

    [GeneratedRegex(@"\d_|\dPlus")]
    private static partial Regex FixedNamedRangeRegex();

    [GeneratedRegex(@"(?<=\$?[A-Z]+)(?<!\$)[0-9]+", RegexOptions.RightToLeft)]
    private static partial Regex FormulaRowNumbersRegex();

    [GeneratedRegex(@"\[.*?\]|"".*?""")]
    private static partial Regex SanitizeFormatRegex();

    [GeneratedRegex(@"[dyhmsM]|tt", RegexOptions.IgnoreCase)]
    private static partial Regex DateTimeFormatRegex();

    [GeneratedRegex(@"\[[^\]]+\]|\*.")]
    private static partial Regex CleanFormatColorsRegex();

    [GeneratedRegex(@"_.")]
    private static partial Regex CleanFormatPaddingRegex();

    [GeneratedRegex(@"\byy\b")]
    private static partial Regex YearFormatRegex();

    [GeneratedRegex(@"\bmm\b")]
    private static partial Regex MonthFormatRegex();

    [GeneratedRegex(@"\bm\b")]
    private static partial Regex SingleMonthFormatRegex();

    [GeneratedRegex(@"\bd\b")]
    private static partial Regex SingleDayFormatRegex();

    [GeneratedRegex(@"\bh\b")]
    private static partial Regex SingleHourFormatRegex();

    [GeneratedRegex(@"[^A-Za-z0-9_]")]
    private static partial Regex InvalidTableNameCharsRegex();
}
