Merge 71ef167868 into dc4a5e9db9

Public/Close-ExcelPackage.ps1

@@ -19,9 +19,6 @@ function Close-ExcelPackage {
             try { [OfficeOpenXml.CalculationExtension]::Calculate($ExcelPackage.Workbook) }
             catch { Write-Warning "One or more errors occured while calculating, save will continue, but there may be errors in the workbook." }
         }
-        # Set FullCalcOnLoad to false to prevent Excel from corrupting formulas during recalculation
-        # This fixes issues with table-structured references like [[#This Row],[ColumnName]]
-        $ExcelPackage.Workbook.FullCalcOnLoad = $false
         if ($SaveAs) {
             $SaveAs = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($SaveAs)
             if ($Password) { $ExcelPackage.SaveAs( $SaveAs, $Password ) }

Public/Export-Excel.ps1

@@ -682,9 +682,6 @@
         else {
             if ($ReturnRange) { $dataRange }
 
-            # Set FullCalcOnLoad to false to prevent Excel from corrupting formulas during recalculation
-            # This fixes issues with table-structured references like [[#This Row],[ColumnName]]
-            $pkg.Workbook.FullCalcOnLoad = $false
             if ($Password) { $pkg.Save($Password) }
             else { $pkg.Save() }
             Write-Verbose -Message "Saved workbook $($pkg.File)"

Public/Get-HtmlTable.ps1

@@ -50,9 +50,7 @@ function Get-HtmlTable {
     else {
         $h    = ConvertFrom-Html -Content $r.Content
         if ($TableIndex -is [valuetype]) { $TableIndex += 1}
-        $rows = try {
-               $h.SelectSingleNode("//table[$TableIndex]").SelectNodes(".//tr")
-        } catch {}
+        $rows =    $h.SelectNodes("//table[$TableIndex]//tr")
         if (-not $rows) {Write-Warning "Could not find rows for `"//table[$TableIndex]`" in $Url ."}
         if ( -not  $propertyNames) {
             if (   $tableHeaders  = $rows[$FirstDataRow].SelectNodes("th")) {

Public/Import-Excel.ps1

@@ -1,4 +1,4 @@
-function Import-Excel {
+function Import-Excel {
     [CmdLetBinding()]
     [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingPlainTextForPassword", "")]
     [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSPossibleIncorrectUsageOfAssignmentOperator', '', Justification = 'Intentional')]
@@ -161,7 +161,10 @@
                         #using Hash tables: "we've seen it" is all we need, no need to worry about "seen it before" / "Seen it many times".
                         $colHash = @{ }
                         $rowHash = @{ }
-                        foreach ($cell in $sheet.Cells[$range]) {
+
+                        $cells = $sheet.Cells[$range]
+
+                        foreach ($cell in $cells) {
                             if ($null -ne $cell.Value ) { $colHash[$cell.Start.Column] = 1; $rowHash[$cell.Start.row] = 1 }
                         }
                         $rows = (   $StartRow..$EndRow   ).Where( { $rowHash[$_] })
@@ -217,21 +220,26 @@
                             #Disabled write-verbose for speed
                             #  Write-Verbose "Import row '$R'"
                             $NewRow = [Ordered]@{ }
+
+                            # Get the entire row first
+                            $row = $sheet.Cells[$R, 1, $R, $sheet.Dimension.End.Column]
+
                             if ($TextColRegEx) {
                                 foreach ($P in $PropertyNames) {
+                                    $cell = $row[$R, $P.Column]
                                     $MatchTest = $TextColRegEx.Match($P.value)
                                     if ($MatchTest.groups.name -eq "astext") {
-                                        $NewRow[$P.Value] = $sheet.Cells[$R, $P.Column].Text
+                                        $NewRow[$P.Value] = $cell.Text
                                     }
-                                    elseif ($MatchTest.groups.name -eq "asdate" -and $sheet.Cells[$R, $P.Column].Value -is [System.ValueType]) {
-                                        $NewRow[$P.Value] = [datetime]::FromOADate(($sheet.Cells[$R, $P.Column].Value))
+                                    elseif ($MatchTest.groups.name -eq "asdate" -and $cell.Value -is [System.ValueType]) {
+                                        $NewRow[$P.Value] = [datetime]::FromOADate($cell.Value)
                                     }
-                                    else { $NewRow[$P.Value] = $sheet.Cells[$R, $P.Column].Value }
+                                    else { $NewRow[$P.Value] = $cell.Value }
                                 }
                             }
                             else {
                                 foreach ($P in $PropertyNames) {
-                                    $NewRow[$P.Value] = $sheet.Cells[$R, $P.Column].Value
+                                    $NewRow[$P.Value] = $row[$R, $P.Column].Value
                                     #    Write-Verbose "Import cell '$($Worksheet.Cells[$R, $P.Column].Address)' with property name '$($p.Value)' and value '$($Worksheet.Cells[$R, $P.Column].Value)'."
                                 }
                             }

__tests__/TableFormula.tests.ps1

@@ -1,125 +0,0 @@
-#Requires -Modules @{ ModuleName="Pester"; ModuleVersion="4.0.0" }
-[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments', '', Justification = 'False Positives')]
-param()
-
-Describe "Table Formula Bug Fix" -Tag "TableFormula" {
-    BeforeAll {
-        $WarningAction = "SilentlyContinue"
-    }
-
-    Context "FullCalcOnLoad is set to false to prevent formula corruption" {
-        BeforeAll {
-            $path = "TestDrive:\table_formula.xlsx"
-            Remove-Item -Path $path -ErrorAction SilentlyContinue
-            
-            # Create a table with a blank record
-            $BlankRecordForFile = [PsCustomObject]@{
-                'Action Add' = ''
-                UserName     = ''
-                Address      = ''
-                Name         = ''
-                NewName      = ''
-            }
-
-            # Export as a table
-            $ExcelFile = $BlankRecordForFile | Export-Excel -Path $path -WorksheetName 'Data' `
-                -TableName 'DataTable' -TableStyle 'Light1' -AutoSize:$false -AutoFilter `
-                -BoldTopRow -FreezeTopRow -StartRow 1 -PassThru
-
-            $Worksheet = $ExcelFile.Workbook.Worksheets['Data']
-            
-            # Insert a row and add a complex formula with table-structured references
-            $Worksheet.InsertRow(2, 1)
-            
-            # This formula uses old-style table references [[#This Row],[ColumnName]]
-            # which Excel converts to [@ColumnName] when opening
-            $Formula = '=IFS( [[#This Row],[UserName]]="","", [[#This Row],[Action Add]]=TRUE, CONCAT([[#This Row],[Address]],"-",[[#This Row],[UserName]]), CONCAT([[#This Row],[Address]],"-",[[#This Row],[UserName]]) <> [[#This Row],[Name]], CONCAT([[#This Row],[Address]],"-",[[#This Row],[UserName]]), TRUE, "")'
-            
-            $Cell = $Worksheet.Cells['e2']
-            $Cell.Formula = $Formula
-            
-            Close-ExcelPackage $ExcelFile
-            
-            # Reopen to verify
-            $ExcelFile2 = Open-ExcelPackage -Path $path
-            $Worksheet2 = $ExcelFile2.Workbook.Worksheets['Data']
-        }
-
-        It "Sets fullCalcOnLoad to false in the workbook XML" {
-            # Extract and check the XML directly
-            $TempExtractPath = Join-Path -Path $TestDrive -ChildPath "extracted_$(Get-Random)"
-            Expand-Archive -Path $path -DestinationPath $TempExtractPath -Force
-            $WorkbookXml = Get-Content (Join-Path -Path $TempExtractPath -ChildPath "xl/workbook.xml") -Raw
-            
-            $WorkbookXml | Should -Match 'fullCalcOnLoad="0"'
-            
-            Remove-Item -Path $TempExtractPath -Recurse -Force
-        }
-
-        It "Preserves the formula correctly after save and reopen" {
-            $Cell2 = $Worksheet2.Cells['e2']
-            $Cell2.Formula | Should -Not -BeNullOrEmpty
-            $Cell2.Formula | Should -Match 'IFS\('
-            $Cell2.Formula | Should -Match 'CONCAT\('
-        }
-
-        It "Does not corrupt the formula with extra @ symbols" {
-            $Cell2 = $Worksheet2.Cells['e2']
-            # The formula should not have extra @ symbols added by Excel during recalculation
-            # The specific bug was an @ being inserted before CONCAT in the middle of the formula
-            # We can't test this directly without opening in Excel, but we can verify the formula is unchanged
-            $Cell2.Formula.Length | Should -BeGreaterThan 100
-        }
-
-        AfterAll {
-            if ($ExcelFile2) {
-                Close-ExcelPackage -ExcelPackage $ExcelFile2 -NoSave
-            }
-        }
-    }
-
-    Context "FullCalcOnLoad setting works with different save methods" {
-        It "Sets fullCalcOnLoad to false when using SaveAs" {
-            $path = "TestDrive:\saveas_test.xlsx"
-            $path2 = "TestDrive:\saveas_test2.xlsx"
-            Remove-Item -Path $path, $path2 -ErrorAction SilentlyContinue
-            
-            $data = [PSCustomObject]@{ Name = 'Test' }
-            $excel = $data | Export-Excel -Path $path -PassThru
-            
-            Close-ExcelPackage $excel -SaveAs $path2
-            
-            # Extract and check the XML
-            $TempExtractPath = Join-Path -Path $TestDrive -ChildPath "extracted_saveas_$(Get-Random)"
-            Expand-Archive -Path $path2 -DestinationPath $TempExtractPath -Force
-            $WorkbookXml = Get-Content (Join-Path -Path $TempExtractPath -ChildPath "xl/workbook.xml") -Raw
-            
-            $WorkbookXml | Should -Match 'fullCalcOnLoad="0"'
-            
-            Remove-Item -Path $TempExtractPath -Recurse -Force
-        }
-
-        It "Sets fullCalcOnLoad to false when using Calculate flag" {
-            $path = "TestDrive:\calculate_test.xlsx"
-            Remove-Item -Path $path -ErrorAction SilentlyContinue
-            
-            $data = [PSCustomObject]@{ Name = 'Test'; Value = 100 }
-            $excel = $data | Export-Excel -Path $path -PassThru
-            
-            # Set a formula
-            $ws = $excel.Workbook.Worksheets[1]
-            $ws.Cells['C2'].Formula = 'B2*2'
-            
-            Close-ExcelPackage $excel -Calculate
-            
-            # Extract and check the XML
-            $TempExtractPath = Join-Path -Path $TestDrive -ChildPath "extracted_calc_$(Get-Random)"
-            Expand-Archive -Path $path -DestinationPath $TempExtractPath -Force
-            $WorkbookXml = Get-Content (Join-Path -Path $TempExtractPath -ChildPath "xl/workbook.xml") -Raw
-            
-            $WorkbookXml | Should -Match 'fullCalcOnLoad="0"'
-            
-            Remove-Item -Path $TempExtractPath -Recurse -Force
-        }
-    }
-}