LeadForge

1.3.0

tool/Output/functions/Export-ExcelFromTemplate.ps1

function Export-ExcelFromTemplate {
    <#
    .SYNOPSIS
        Inject scored contact data into the Excel template workbook.

    .DESCRIPTION
        Copies the template xlsx to the output path, then injects:
        - Contact rows into Detail!tblLeads (A8:AA onward)
        - Run metadata into cfg_* cells on Run Profile sheet
        - Scoring weights, band thresholds, and recency penalty config

        Uses direct ZIP/XML manipulation (System.IO.Compression + System.Xml.Linq)
        to preserve all template features including slicers, charts, pivots,
        and conditional formatting that EPPlus 4.x cannot handle.

    .PARAMETER FlatResults
        Array of ordered hashtables — one per scored contact (27 fields each).

    .PARAMETER OutputFilePath
        Full path for the output xlsx file.

    .PARAMETER Profile
        Parsed profile object (from Default.json or similar).

    .PARAMETER EmailCount
        Number of emails processed in this pipeline run.

    .PARAMETER Version
        LeadForge version string (e.g. "1.3.0").
    #>
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [array]$FlatResults,

        [Parameter(Mandatory)]
        [string]$OutputFilePath,

        [Parameter(Mandatory)]
        [object]$Profile,

        [Parameter(Mandatory)]
        [int]$EmailCount,

        [Parameter(Mandatory)]
        [string]$Version
    )

    Add-Type -AssemblyName System.IO.Compression
    Add-Type -AssemblyName System.IO.Compression.FileSystem

    # Normalise output path to absolute. .NET APIs (ZipFile.Open) resolve relative
    # paths against [Environment]::CurrentDirectory (often C:\WINDOWS\System32),
    # NOT the PowerShell location — so a relative path silently targets the wrong dir.
    if (-not [System.IO.Path]::IsPathRooted($OutputFilePath)) {
        $OutputFilePath = Join-Path (Get-Location).ProviderPath $OutputFilePath
    }
    $OutputFilePath = [System.IO.Path]::GetFullPath($OutputFilePath)

    # Locate template
    $scriptDir = Split-Path $MyInvocation.MyCommand.ScriptBlock.File -Parent
    $templatePath = Join-Path $scriptDir '..' 'templates' 'opportunity-report-template.xlsx'
    if (-not (Test-Path $templatePath)) {
        throw "Excel template not found at: $templatePath"
    }
    $templatePath = (Resolve-Path $templatePath).Path

    # Copy template to output location
    $outputDir = Split-Path $OutputFilePath -Parent
    if (-not (Test-Path $outputDir)) {
        New-Item -ItemType Directory -Path $outputDir -Force | Out-Null
    }
    Copy-Item -Path $templatePath -Destination $OutputFilePath -Force

    # Open the copy as a ZIP archive for modification
    $zip = [System.IO.Compression.ZipFile]::Open($OutputFilePath, [System.IO.Compression.ZipArchiveMode]::Update)

    try {
        $ns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
        $contactCount = $FlatResults.Count
        $dataStartRow = 8
        $newLastRow = $dataStartRow + [Math]::Max($contactCount, 1) - 1

        # --- 1. Inject data into Detail sheet (sheet3.xml) ---
        # Strategy: clone the template's first styled data row (row 8) as a prototype
        # for EVERY new row. This preserves per-cell styles (number formats, borders,
        # banded-table fills, date format) AND the calculated columns AB-AI (Primary
        # Category, research split, Email Year/Age, Final/Band recompute, Audit) which
        # all use [#This Row] structured references — the formula text is identical for
        # every row, so cloning is safe. We only overwrite the A-AA data values.
        $detailEntry = $zip.GetEntry('xl/worksheets/sheet3.xml')
        $detailXml = Read-ZipEntryXml $detailEntry

        $sheetDataName = [System.Xml.Linq.XName]::Get('sheetData', $ns)
        $rowName = [System.Xml.Linq.XName]::Get('row', $ns)
        $sheetData = $detailXml.Root.Descendants($sheetDataName) | Select-Object -First 1

        # Column letters for A-AA (27 data columns) and the record keys they map to
        $colLetters = @('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA')
        $columnKeys = @(
            'Band', 'Urgency', 'Final Score', 'Composite Score', 'Recency Penalty',
            'Contact Name', 'Contact Email', 'Organisation', 'Role',
            'Current Title', 'Current Organisation', 'Still in Role',
            'Telematics Activity', 'Confidence', 'Email Date', 'Category',
            'Strategic Fit', 'Seniority', 'Engagement Warmth', 'Market Activity',
            'Conversation Stage', 'Recency', 'Research Confidence',
            'Recommended Action', 'Action Rationale', 'Source URLs', 'Source File'
        )

        # Capture the prototype data row (row 8) BEFORE removing anything
        $prototypeRow = $sheetData.Elements($rowName) |
        Where-Object { $_.Attribute('r').Value -eq "$dataStartRow" } | Select-Object -First 1
        if (-not $prototypeRow) {
            throw "Template Detail sheet is missing prototype data row $dataStartRow — cannot preserve styles/formulas."
        }

        # Build all new rows from the prototype (clones preserve styles + AB-AI formulas)
        $newRows = New-Object System.Collections.Generic.List[object]
        for ($i = 0; $i -lt $contactCount; $i++) {
            $rowNum = $dataStartRow + $i
            $newRows.Add(
                (New-DetailDataRow -Prototype $prototypeRow -RowNum $rowNum -Record $FlatResults[$i] `
                    -ColumnKeys $columnKeys -ColLetters $colLetters -Namespace $ns)
            )
        }

        # Remove existing data rows (row 8+); keep header row 7 and rows 1-6
        $rowsToRemove = @($sheetData.Elements($rowName) | Where-Object { [int]$_.Attribute('r').Value -ge $dataStartRow })
        foreach ($rowEl in $rowsToRemove) { $rowEl.Remove() }

        # Append the freshly built, fully styled rows
        foreach ($nr in $newRows) { $sheetData.Add($nr) }

        # Remove dangling hyperlinks left over from the template's sample data rows
        # (any ref at row >= dataStartRow). Otherwise they point at now-empty cells and
        # leak the template's sample LinkedIn URLs (stored in the .rels Targets) into
        # every generated report. Header hyperlinks (rows < dataStartRow) are kept.
        $relNs = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
        $ridName = [System.Xml.Linq.XName]::Get('id', $relNs)
        $removedRids = New-Object System.Collections.Generic.List[string]
        $hyperlinksName = [System.Xml.Linq.XName]::Get('hyperlinks', $ns)
        $hyperlinkName = [System.Xml.Linq.XName]::Get('hyperlink', $ns)
        $hyperlinksEl = $detailXml.Root.Element($hyperlinksName)
        if ($hyperlinksEl) {
            $deadLinks = @($hyperlinksEl.Elements($hyperlinkName) | Where-Object {
                    $ref = $_.Attribute('ref')
                    $ref -and $ref.Value -match '^[A-Z]+(\d+)$' -and [int]$Matches[1] -ge $dataStartRow
                })
            foreach ($dl in $deadLinks) {
                $ridAttr = $dl.Attribute($ridName)
                if ($ridAttr) { $removedRids.Add($ridAttr.Value) }
                $dl.Remove()
            }
            if (-not $hyperlinksEl.Elements($hyperlinkName)) { $hyperlinksEl.Remove() }
        }

        # Tighten the sheet dimension to the populated range
        $dimensionName = [System.Xml.Linq.XName]::Get('dimension', $ns)
        $dimensionEl = $detailXml.Root.Element($dimensionName)
        if ($dimensionEl) {
            $dimensionEl.SetAttributeValue('ref', "A1:AI$newLastRow")
        }

        Write-ZipEntryXml $detailEntry $detailXml

        # Drop the now-orphaned hyperlink relationships (their Targets hold the sample URLs)
        if ($removedRids.Count -gt 0) {
            $detailRelEntry = $zip.GetEntry('xl/worksheets/_rels/sheet3.xml.rels')
            if ($detailRelEntry) {
                $detailRelXml = Read-ZipEntryXml $detailRelEntry
                $deadRels = @($detailRelXml.Root.Elements() | Where-Object {
                        $idAttr = $_.Attribute('Id')
                        $idAttr -and $removedRids.Contains($idAttr.Value)
                    })
                foreach ($dr in $deadRels) { $dr.Remove() }
                Write-ZipEntryXml $detailRelEntry $detailRelXml
            }
        }

        # --- 2. Inject Run Profile config (sheet4.xml) ---
        $rpEntry = $zip.GetEntry('xl/worksheets/sheet4.xml')
        $rpXml = Read-ZipEntryXml $rpEntry
        $rpSheetData = $rpXml.Root.Descendants($sheetDataName) | Select-Object -First 1

        # Build config values to inject
        $configCells = [ordered]@{}
        $configCells['B7'] = "LeadForge v$Version"
        $configCells['B8'] = [DateTime]::Now
        $configCells['B9'] = if ($Profile.profile_name) { $Profile.profile_name } else { 'Default' }
        $configCells['B10'] = $Profile.owner.name
        $configCells['B11'] = if ($Profile.owner.aliases) { $Profile.owner.aliases[0].name } else { '' }
        $configCells['B12'] = $Profile.owner.industry
        $configCells['B13'] = $contactCount
        $configCells['B14'] = $EmailCount

        # Weights (A18:B24)
        $weightNames = @('Strategic Fit', 'Seniority', 'Engagement Warmth', 'Market Activity', 'Conversation Stage', 'Recency', 'Research Confidence')
        $weightValues = @($Profile.weights.strategic_fit, $Profile.weights.seniority, $Profile.weights.engagement_warmth, $Profile.weights.market_activity, $Profile.weights.conversation_stage, $Profile.weights.recency, $Profile.weights.research_confidence)
        for ($r = 0; $r -lt 7; $r++) {
            $rowNum = 18 + $r
            $configCells["A$rowNum"] = $weightNames[$r]
            $configCells["B$rowNum"] = $weightValues[$r]
        }

        # Bands (A29:D33)
        $bandLabels = @('Immediate', 'High', 'Medium', 'Low', 'Archive')
        $bandMins = @($Profile.thresholds.band_1_min, $Profile.thresholds.band_2_min, $Profile.thresholds.band_3_min, $Profile.thresholds.band_4_min, 0)
        $bandHex = @('#2ECC71', '#3498DB', '#F39C12', '#E74C3C', '#95A5A6')
        for ($r = 0; $r -lt 5; $r++) {
            $rowNum = 29 + $r
            $configCells["A$rowNum"] = $r + 1
            $configCells["B$rowNum"] = $bandLabels[$r]
            $configCells["C$rowNum"] = $bandMins[$r]
            $configCells["D$rowNum"] = $bandHex[$r]
        }

        # Recency penalty (A37:B41)
        $recLabels = @('< 6 months', "6 months `u{2013} 1 year", "1 `u{2013} 2 years", "2 `u{2013} 4 years", '> 4 years')
        $recValues = @($Profile.recency_penalty.under_6m, $Profile.recency_penalty.'6m_to_1y', $Profile.recency_penalty.'1y_to_2y', $Profile.recency_penalty.'2y_to_4y', $Profile.recency_penalty.over_4y)
        for ($r = 0; $r -lt 5; $r++) {
            $rowNum = 37 + $r
            $configCells["A$rowNum"] = $recLabels[$r]
            $configCells["B$rowNum"] = $recValues[$r]
        }

        # Verticals (A44:A48)
        if ($Profile.owner.verticals) {
            for ($r = 0; $r -lt $Profile.owner.verticals.Count -and $r -lt 5; $r++) {
                $rowNum = 44 + $r
                $configCells["A$rowNum"] = $Profile.owner.verticals[$r]
            }
        }

        # Products (C44+)
        if ($Profile.owner.products) {
            for ($r = 0; $r -lt $Profile.owner.products.Count; $r++) {
                $rowNum = 44 + $r
                $configCells["C$rowNum"] = $Profile.owner.products[$r]
            }
        }

        # Apply config cells to Run Profile sheet
        Set-OpenXmlCells -SheetData $rpSheetData -CellValues $configCells -Namespace $ns

        Write-ZipEntryXml $rpEntry $rpXml

        # --- 3. Resize tblLeads table ---
        $tableEntry = $zip.GetEntry('xl/tables/table1.xml')
        $tableXml = Read-ZipEntryXml $tableEntry
        $tableRoot = $tableXml.Root
        $newRef = "A7:AI$newLastRow"
        $tableRoot.SetAttributeValue('ref', $newRef)
        # Update autoFilter range too
        $autoFilterName = [System.Xml.Linq.XName]::Get('autoFilter', $ns)
        $autoFilter = $tableRoot.Descendants($autoFilterName) | Select-Object -First 1
        if ($autoFilter) {
            $autoFilter.SetAttributeValue('ref', $newRef)
        }
        Write-ZipEntryXml $tableEntry $tableXml

        # --- 4. Update pivot cache source range ---
        # The cache already has refreshOnLoad="1" + saveData="0", so Excel rebuilds the
        # pivots from source on open. We only repoint the source extent and keep the
        # recordCount metadata consistent with the new row count.
        $pivotEntry = $zip.GetEntry('xl/pivotCache/pivotCacheDefinition1.xml')
        if ($pivotEntry) {
            $pivotXml = Read-ZipEntryXml $pivotEntry
            $worksheetSource = $pivotXml.Descendants() | Where-Object { $_.Name.LocalName -eq 'worksheetSource' } | Select-Object -First 1
            if ($worksheetSource) {
                $worksheetSource.SetAttributeValue('ref', $newRef)
            }
            if ($pivotXml.Root.Attribute('recordCount')) {
                $pivotXml.Root.SetAttributeValue('recordCount', $contactCount)
            }
            Write-ZipEntryXml $pivotEntry $pivotXml
        }

        # --- 5. Clear Injection Spec sheet content ---
        $specEntry = $zip.GetEntry('xl/worksheets/sheet6.xml')
        if ($specEntry) {
            $specXml = Read-ZipEntryXml $specEntry
            $specSheetData = $specXml.Root.Descendants($sheetDataName) | Select-Object -First 1
            if ($specSheetData) {
                $specSheetData.RemoveNodes()
            }
            Write-ZipEntryXml $specEntry $specXml
        }

        # --- 6. Force full recalculation on load ---
        # We strip the stale cached values from the calculated columns (AB-AI) when
        # cloning rows, so Excel must recompute them on open. fullCalcOnLoad also
        # refreshes the Overview/Explore KPI and chart-data formulas against the new data.
        $wbEntry = $zip.GetEntry('xl/workbook.xml')
        if ($wbEntry) {
            $wbXml = Read-ZipEntryXml $wbEntry
            $calcPrName = [System.Xml.Linq.XName]::Get('calcPr', $ns)
            $calcPr = $wbXml.Root.Descendants($calcPrName) | Select-Object -First 1
            if ($calcPr) {
                $calcPr.SetAttributeValue('fullCalcOnLoad', '1')
            }
            else {
                $wbXml.Root.Add(
                    [System.Xml.Linq.XElement]::new($calcPrName,
                        [System.Xml.Linq.XAttribute]::new('calcId', '191029'),
                        [System.Xml.Linq.XAttribute]::new('fullCalcOnLoad', '1'))
                )
            }
            Write-ZipEntryXml $wbEntry $wbXml
        }
    }
    finally {
        $zip.Dispose()
    }
}

#region Helper Functions

function New-DetailDataRow {
    <#
    .SYNOPSIS
        Clone the prototype Detail data row and overwrite its A-AA values for one contact.

    .DESCRIPTION
        Deep-clones the template's styled prototype row so every cell keeps its style
        index (number formats, borders, banded fills, date format). Data columns A-AA
        are overwritten with the contact's values; calculated columns AB-AI keep their
        [#This Row] structured-reference formulas (identical text per row) but have their
        stale cached <v> stripped so Excel recomputes them on open.
    #>
    param(
        [System.Xml.Linq.XElement]$Prototype,
        [int]$RowNum,
        $Record,
        [string[]]$ColumnKeys,
        [string[]]$ColLetters,
        [string]$Namespace
    )

    $vName = [System.Xml.Linq.XName]::Get('v', $Namespace)
    $fName = [System.Xml.Linq.XName]::Get('f', $Namespace)
    $isName = [System.Xml.Linq.XName]::Get('is', $Namespace)
    $tElemName = [System.Xml.Linq.XName]::Get('t', $Namespace)   # inline-string <t> element

    # Map data column letter -> record key (A-AA only)
    $keyByLetter = @{}
    for ($i = 0; $i -lt $ColumnKeys.Count; $i++) { $keyByLetter[$ColLetters[$i]] = $ColumnKeys[$i] }

    # Deep clone the prototype row, then re-stamp the row number
    $row = [System.Xml.Linq.XElement]::new($Prototype)
    $row.SetAttributeValue('r', $RowNum)

    foreach ($cell in @($row.Elements())) {
        $refAttr = $cell.Attribute('r')
        if (-not $refAttr) { continue }
        if ($refAttr.Value -match '^([A-Z]+)\d+$') { $colLetter = $Matches[1] } else { continue }
        $newRef = "$colLetter$RowNum"
        $cell.SetAttributeValue('r', $newRef)

        if ($keyByLetter.ContainsKey($colLetter)) {
            # Data column A-AA: overwrite value, preserve the style ('s') attribute
            $key = $keyByLetter[$colLetter]
            $value = $Record[$key]

            $cell.RemoveNodes()
            $cell.SetAttributeValue('t', $null)   # clear cell type; re-set for strings below

            if ($null -eq $value -or ($value -is [string] -and [string]::IsNullOrEmpty($value))) {
                # leave as an empty but still-styled cell
            }
            elseif ($key -eq 'Email Date') {
                $serial = $null
                try {
                    $dateVal = if ($value -is [datetime]) { $value }
                    else { [datetime]::Parse("$value", [System.Globalization.CultureInfo]::InvariantCulture) }
                    $serial = [Math]::Round((($dateVal - [datetime]::new(1899, 12, 30)).TotalDays), 6)
                }
                catch { $serial = $null }

                if ($null -ne $serial) {
                    $cell.Add([System.Xml.Linq.XElement]::new($vName, $serial.ToString([System.Globalization.CultureInfo]::InvariantCulture)))
                }
                else {
                    $cell.SetAttributeValue('t', 'inlineStr')
                    $isEl = [System.Xml.Linq.XElement]::new($isName)
                    $isEl.Add([System.Xml.Linq.XElement]::new($tElemName, "$value"))
                    $cell.Add($isEl)
                }
            }
            elseif ($value -is [int] -or $value -is [long] -or $value -is [double] -or $value -is [decimal] -or $value -is [single]) {
                $cell.Add([System.Xml.Linq.XElement]::new($vName, [System.Convert]::ToString($value, [System.Globalization.CultureInfo]::InvariantCulture)))
            }
            else {
                $cell.SetAttributeValue('t', 'inlineStr')
                $isEl = [System.Xml.Linq.XElement]::new($isName)
                $isEl.Add([System.Xml.Linq.XElement]::new($tElemName, "$value"))
                $cell.Add($isEl)
            }
        }
        else {
            # Calculated column AB-AI: keep formula + style; drop stale cached value
            $vEl = $cell.Element($vName)
            if ($vEl) { $vEl.Remove() }
            # Re-stamp single-cell array-formula ref (AH uses <f t="array" ref="AH8">)
            $fEl = $cell.Element($fName)
            if ($fEl -and $fEl.Attribute('ref')) {
                $fEl.SetAttributeValue('ref', $newRef)
            }
        }
    }

    return $row
}

function Read-ZipEntryXml {
    param([System.IO.Compression.ZipArchiveEntry]$Entry)
    $stream = $Entry.Open()
    try {
        $xdoc = [System.Xml.Linq.XDocument]::Load($stream)
        return $xdoc
    }
    finally {
        $stream.Close()
        $stream.Dispose()
    }
}

function Write-ZipEntryXml {
    param(
        [System.IO.Compression.ZipArchiveEntry]$Entry,
        [System.Xml.Linq.XDocument]$XDoc
    )
    $stream = $Entry.Open()
    try {
        $stream.SetLength(0)
        $settings = [System.Xml.XmlWriterSettings]::new()
        $settings.Indent = $false
        $settings.Encoding = [System.Text.UTF8Encoding]::new($false)
        $settings.CloseOutput = $false
        $writer = [System.Xml.XmlWriter]::Create($stream, $settings)
        $XDoc.WriteTo($writer)
        $writer.Flush()
        $writer.Close()
    }
    finally {
        $stream.Close()
        $stream.Dispose()
    }
}

function New-OpenXmlCell {
    param(
        [string]$CellRef,
        $Value,
        [bool]$IsDate,
        [string]$Namespace,
        [string]$StyleIndex
    )

    if ($null -eq $Value -or ($Value -is [string] -and [string]::IsNullOrEmpty($Value))) {
        return $null
    }

    $cName = [System.Xml.Linq.XName]::Get('c', $Namespace)
    $vName = [System.Xml.Linq.XName]::Get('v', $Namespace)
    $isName = [System.Xml.Linq.XName]::Get('is', $Namespace)
    $tName = [System.Xml.Linq.XName]::Get('t', $Namespace)

    $cellEl = [System.Xml.Linq.XElement]::new($cName, [System.Xml.Linq.XAttribute]::new('r', $CellRef))
    # Preserve the original cell's style index so number formats / borders survive
    if (-not [string]::IsNullOrEmpty($StyleIndex)) {
        $cellEl.SetAttributeValue('s', $StyleIndex)
    }

    if ($IsDate -and $Value) {
        # Convert to Excel serial date number
        try {
            $dateVal = if ($Value -is [DateTime]) { $Value }
            else { [DateTime]::Parse("$Value", [System.Globalization.CultureInfo]::InvariantCulture) }
            $serial = ($dateVal - [DateTime]::new(1899, 12, 30)).TotalDays
            $cellEl.Add([System.Xml.Linq.XElement]::new($vName, [Math]::Round($serial, 6).ToString()))
        }
        catch {
            # Fall back to inline string
            $cellEl.SetAttributeValue('t', 'inlineStr')
            $isEl = [System.Xml.Linq.XElement]::new($isName)
            $isEl.Add([System.Xml.Linq.XElement]::new($tName, "$Value"))
            $cellEl.Add($isEl)
        }
    }
    elseif ($Value -is [int] -or $Value -is [long] -or $Value -is [double] -or $Value -is [decimal]) {
        $cellEl.Add([System.Xml.Linq.XElement]::new($vName, $Value.ToString()))
    }
    else {
        # Inline string
        $cellEl.SetAttributeValue('t', 'inlineStr')
        $isEl = [System.Xml.Linq.XElement]::new($isName)
        $isEl.Add([System.Xml.Linq.XElement]::new($tName, "$Value"))
        $cellEl.Add($isEl)
    }

    return $cellEl
}

function Set-OpenXmlCells {
    param(
        [System.Xml.Linq.XElement]$SheetData,
        [System.Collections.Specialized.OrderedDictionary]$CellValues,
        [string]$Namespace
    )

    $rowXName = [System.Xml.Linq.XName]::Get('row', $Namespace)
    $cellXName = [System.Xml.Linq.XName]::Get('c', $Namespace)

    foreach ($cellRef in $CellValues.Keys) {
        $value = $CellValues[$cellRef]

        # Parse row number from cell reference (e.g. "B7" -> 7, "AA44" -> 44)
        if ($cellRef -match '^([A-Z]+)(\d+)$') {
            $rowNum = [int]$Matches[2]
        }
        else { continue }

        # Find or create the row
        $rowEl = $SheetData.Elements($rowXName) | Where-Object { $_.Attribute('r').Value -eq "$rowNum" } | Select-Object -First 1
        if (-not $rowEl) {
            $rowEl = [System.Xml.Linq.XElement]::new($rowXName, [System.Xml.Linq.XAttribute]::new('r', $rowNum))
            $SheetData.Add($rowEl)
        }

        # Remove existing cell if present, preserving its style index
        $existingCell = $rowEl.Elements($cellXName) | Where-Object { $_.Attribute('r').Value -eq $cellRef } | Select-Object -First 1
        $styleIndex = $null
        if ($existingCell) {
            $sAttr = $existingCell.Attribute('s')
            if ($sAttr) { $styleIndex = $sAttr.Value }
            $existingCell.Remove()
        }

        $isDate = ($cellRef -eq 'B8')  # Only cfg_Generated is a date
        $newCell = New-OpenXmlCell -CellRef $cellRef -Value $value -IsDate $isDate -Namespace $Namespace -StyleIndex $styleIndex
        if ($newCell) {
            $rowEl.Add($newCell)
        }
    }
}

#endregion