Merge 71ef167868 into dc4a5e9db9

Public/Add-ExcelChart.ps1

@@ -113,57 +113,6 @@ function Add-ExcelChart {
         if ($null -ne $XMinValue) { $chart.XAxis.MinValue = $XMinValue }
         if ($null -ne $XMaxValue) { $chart.XAxis.MaxValue = $XMaxValue }
         if ($XAxisNumberformat) { $chart.XAxis.Format = (Expand-NumberFormat $XAxisNumberformat) }
-        
-        # Fix for category axis (used in line charts, etc.): EPPlus doesn't serialize MajorUnit/MinorUnit to XML for category axes
-        # We need to manually add these elements to the XML if they were specified
-        if ($XMajorUnit -or $XMinorUnit) {
-            $chartXml = $chart.ChartXml
-            $nsManager = New-Object System.Xml.XmlNamespaceManager($chartXml.NameTable)
-            $null = $nsManager.AddNamespace("c", "http://schemas.openxmlformats.org/drawingml/2006/chart")
-            $catAx = $chartXml.SelectSingleNode("//c:catAx", $nsManager)
-            
-            if ($catAx) {
-                # Category axis exists - need to add majorUnit/minorUnit elements
-                if ($XMajorUnit) {
-                    $existingMajorUnit = $catAx.SelectSingleNode("c:majorUnit", $nsManager)
-                    if (-not $existingMajorUnit) {
-                        $majorUnitElement = $chartXml.CreateElement("c", "majorUnit", "http://schemas.openxmlformats.org/drawingml/2006/chart")
-                        $null = $majorUnitElement.SetAttribute("val", $XMajorUnit)
-                        # Insert after scaling element or at the beginning
-                        $scalingNode = $catAx.SelectSingleNode("c:scaling", $nsManager)
-                        if ($scalingNode) {
-                            $null = $catAx.InsertAfter($majorUnitElement, $scalingNode)
-                        } else {
-                            $null = $catAx.PrependChild($majorUnitElement)
-                        }
-                    } else {
-                        $null = $existingMajorUnit.SetAttribute("val", $XMajorUnit)
-                    }
-                }
-                
-                if ($XMinorUnit) {
-                    $existingMinorUnit = $catAx.SelectSingleNode("c:minorUnit", $nsManager)
-                    if (-not $existingMinorUnit) {
-                        $minorUnitElement = $chartXml.CreateElement("c", "minorUnit", "http://schemas.openxmlformats.org/drawingml/2006/chart")
-                        $null = $minorUnitElement.SetAttribute("val", $XMinorUnit)
-                        # Insert after majorUnit if it exists, otherwise after scaling
-                        $majorUnitNode = $catAx.SelectSingleNode("c:majorUnit", $nsManager)
-                        if ($majorUnitNode) {
-                            $null = $catAx.InsertAfter($minorUnitElement, $majorUnitNode)
-                        } else {
-                            $scalingNode = $catAx.SelectSingleNode("c:scaling", $nsManager)
-                            if ($scalingNode) {
-                                $null = $catAx.InsertAfter($minorUnitElement, $scalingNode)
-                            } else {
-                                $null = $catAx.PrependChild($minorUnitElement)
-                            }
-                        }
-                    } else {
-                        $null = $existingMinorUnit.SetAttribute("val", $XMinorUnit)
-                    }
-                }
-            }
-        }
 
         if ($YAxisTitleText) {
             $chart.YAxis.Title.Text = $YAxisTitleText

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 {
+        $rows =    $h.SelectNodes("//table[$TableIndex]//tr")
-               $h.SelectSingleNode("//table[$TableIndex]").SelectNodes(".//tr")
-        } catch {}
         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]) {
+                                    elseif ($MatchTest.groups.name -eq "asdate" -and $cell.Value -is [System.ValueType]) {
-                                        $NewRow[$P.Value] = [datetime]::FromOADate(($sheet.Cells[$R, $P.Column].Value))
+                                        $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__/Add-ExcelChart-XAxis.Tests.ps1

@@ -1,135 +0,0 @@
-# Test for X-Axis configuration on line charts
-# This test verifies that XMajorUnit and XMinorUnit parameters work correctly for line charts
-
-Describe "X-Axis Configuration for Line Charts" {
-    BeforeAll {
-        $path = "TestDrive:\xaxis_test.xlsx"
-    }
-
-    Context "X-Axis MajorUnit and MinorUnit on Line Charts" {
-        BeforeAll {
-            # Create test data
-            $excel = 1..7 | ForEach-Object { 
-                [pscustomobject][ordered]@{
-                    XValue = @(5, 10, 15, 20, 25, 30, 35)[$_ - 1]
-                    YValue = @(18, 11, 23, 17, 12, 32, 12)[$_ - 1]
-                } 
-            } | Export-Excel -Path $path -WorksheetName "Test" -PassThru -AutoNameRange
-            
-            # Add chart with X-axis configuration
-            Add-ExcelChart -Worksheet $excel.Workbook.Worksheets["Test"] `
-                -ChartType Line `
-                -XRange "XValue" `
-                -YRange "YValue" `
-                -Title "Test Chart" `
-                -XMinValue 0 `
-                -XMaxValue 40 `
-                -XMajorUnit 10 `
-                -XMinorUnit 5 `
-                -YMinValue 0 `
-                -YMaxValue 40 `
-                -YMajorUnit 10 `
-                -YMinorUnit 5
-            
-            Close-ExcelPackage $excel
-            
-            # Reopen to verify
-            $excel = Open-ExcelPackage -Path $path
-            $ws = $excel.Workbook.Worksheets["Test"]
-            $chart = $ws.Drawings[0]
-        }
-        
-        It "Set XAxis.MinValue correctly" {
-            $chart.XAxis.MinValue | Should -Be 0
-        }
-        
-        It "Set XAxis.MaxValue correctly" {
-            $chart.XAxis.MaxValue | Should -Be 40
-        }
-        
-        It "Set XAxis.MajorUnit correctly" {
-            $chart.XAxis.MajorUnit | Should -Be 10
-        }
-        
-        It "Set XAxis.MinorUnit correctly" {
-            $chart.XAxis.MinorUnit | Should -Be 5
-        }
-        
-        It "Added MajorUnit to category axis XML" {
-            $xml = $chart.ChartXml
-            $nsmgr = New-Object System.Xml.XmlNamespaceManager($xml.NameTable)
-            $nsmgr.AddNamespace("c", "http://schemas.openxmlformats.org/drawingml/2006/chart")
-            $catAx = $xml.SelectSingleNode("//c:catAx", $nsmgr)
-            $majorUnit = $catAx.SelectSingleNode("c:majorUnit", $nsmgr)
-            
-            $majorUnit | Should -Not -BeNullOrEmpty
-            $majorUnit.val | Should -Be "10"
-        }
-        
-        It "Added MinorUnit to category axis XML" {
-            $xml = $chart.ChartXml
-            $nsmgr = New-Object System.Xml.XmlNamespaceManager($xml.NameTable)
-            $nsmgr.AddNamespace("c", "http://schemas.openxmlformats.org/drawingml/2006/chart")
-            $catAx = $xml.SelectSingleNode("//c:catAx", $nsmgr)
-            $minorUnit = $catAx.SelectSingleNode("c:minorUnit", $nsmgr)
-            
-            $minorUnit | Should -Not -BeNullOrEmpty
-            $minorUnit.val | Should -Be "5"
-        }
-        
-        AfterAll {
-            Close-ExcelPackage $excel -NoSave
-        }
-    }
-    
-    Context "X-Axis without MajorUnit and MinorUnit" {
-        BeforeAll {
-            $path2 = "TestDrive:\xaxis_test2.xlsx"
-            # Create test data without X-axis units
-            $excel2 = 1..7 | ForEach-Object { 
-                [pscustomobject][ordered]@{
-                    XValue = @(5, 10, 15, 20, 25, 30, 35)[$_ - 1]
-                    YValue = @(18, 11, 23, 17, 12, 32, 12)[$_ - 1]
-                } 
-            } | Export-Excel -Path $path2 -WorksheetName "Test" -PassThru -AutoNameRange
-            
-            # Add chart WITHOUT X-axis MajorUnit/MinorUnit
-            Add-ExcelChart -Worksheet $excel2.Workbook.Worksheets["Test"] `
-                -ChartType Line `
-                -XRange "XValue" `
-                -YRange "YValue" `
-                -Title "Test Chart"
-            
-            Close-ExcelPackage $excel2
-            
-            # Reopen to verify
-            $excel2 = Open-ExcelPackage -Path $path2
-            $ws2 = $excel2.Workbook.Worksheets["Test"]
-            $chart2 = $ws2.Drawings[0]
-        }
-        
-        It "Should not add MajorUnit to category axis XML when not specified" {
-            $xml = $chart2.ChartXml
-            $nsmgr = New-Object System.Xml.XmlNamespaceManager($xml.NameTable)
-            $nsmgr.AddNamespace("c", "http://schemas.openxmlformats.org/drawingml/2006/chart")
-            $catAx = $xml.SelectSingleNode("//c:catAx", $nsmgr)
-            $majorUnit = $catAx.SelectSingleNode("c:majorUnit", $nsmgr)
-            
-            $majorUnit | Should -BeNullOrEmpty
-        }
-        
-        It "Should not add MinorUnit to category axis XML when not specified" {
-            $xml = $chart2.ChartXml
-            $nsmgr = New-Object System.Xml.XmlNamespaceManager($xml.NameTable)
-            $nsmgr.AddNamespace("c", "http://schemas.openxmlformats.org/drawingml/2006/chart")
-            $catAx = $xml.SelectSingleNode("//c:catAx", $nsmgr)
-            $minorUnit = $catAx.SelectSingleNode("c:minorUnit", $nsmgr)
-            
-            $minorUnit | Should -BeNullOrEmpty
-        }
-        
-        AfterAll {
-            Close-ExcelPackage $excel2 -NoSave
-        }
-    }
-}