allow configuring cache directory

ImportExcel.psd1

@@ -6,7 +6,7 @@
     RootModule         = 'ImportExcel.psm1'
 
     # Version number of this module.
-    ModuleVersion      = '7.8.10'
+    ModuleVersion      = '7.8.9'
 
     # ID used to uniquely identify this module
     GUID               = '60dd4136-feff-401a-ba27-a84458c57ede'
@@ -150,7 +150,7 @@ Check out the How To Videos https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5
             Tags         = @("Excel", "EPPlus", "Export", "Import")
 
             # The web address of an icon which can be used in galleries to represent this module
-            #IconUri = 
+            #IconUri = "http://pesterbdd.com/images/Pester.png"
 
             # The web address of this module's project or support homepage.
             ProjectUri   = "https://github.com/dfinke/ImportExcel"
@@ -215,4 +215,4 @@ Check out the How To Videos https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5
     # Default prefix for commands exported from this module. Override the default prefix using Import-Module -Prefix.
     # DefaultCommandPrefix = ''
 
-}
+}

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/ConvertTo-ExcelXlsx.ps1

@@ -3,60 +3,109 @@ function ConvertTo-ExcelXlsx {
     param
     (
         [parameter(Mandatory = $true, ValueFromPipeline)]
-        [string]$Path,
+        [string[]]$Path,
         [parameter(Mandatory = $false)]
-        [switch]$Force
+        [switch]$Force,
+        [parameter(Mandatory = $false)]
+        [switch]$CacheToTemp,
+        [parameter(Mandatory = $false)]
+        [string]$CacheDirectory
     )
     process {
-        if (-Not ($Path | Test-Path) ) {
+        try {
-            throw "File not found"
+            foreach ($singlePath in $Path) {
-        }
+                if (-Not ($singlePath | Test-Path) ) {
-        if (-Not ($Path | Test-Path -PathType Leaf) ) {
+                    throw "File not found"
-            throw "Folder paths are not allowed"
+                }
-        }
+                if (-Not ($singlePath | Test-Path -PathType Leaf) ) {
+                    throw "Folder paths are not allowed"
+                }
 
-        $xlFixedFormat = 51 #Constant for XLSX Workbook
+                $xlFixedFormat = 51 #Constant for XLSX Workbook
-        $xlsFile = Get-Item -Path $Path
+                $xlsFile = Get-Item -Path $singlePath
-        $xlsxPath = "{0}x" -f $xlsFile.FullName
+                $destinationXlsxPath = [System.IO.Path]::ChangeExtension($xlsFile.FullName, ".xlsx")
 
-        if ($xlsFile.Extension -ne ".xls") {
+                if ($xlsFile.Extension -ne ".xls") {
-            throw "Expected .xls extension"
+                    throw "Expected .xls extension"
-        }
+                }
 
-        if (Test-Path -Path $xlsxPath) {
+                if (Test-Path -Path $destinationXlsxPath) {
-            if ($Force) {
+                    if ($Force) {
-                try {
+                        try {
-                    Remove-Item $xlsxPath -Force
+                            Remove-Item $destinationXlsxPath -Force
+                        }
+                        catch {
+                            throw "{0} already exists and cannot be removed. The file may be locked by another application." -f $destinationXlsxPath
+                        }
+                        Write-Verbose $("Removed {0}" -f $destinationXlsxPath)
+                    }
+                    else {
+                        throw "{0} already exists!" -f $destinationXlsxPath
+                    }
+                }
+
+                if ($null -eq $Excel)
+                {
+                    try {
+                        $Excel = New-Object -ComObject "Excel.Application"
+                    }
+                    catch {
+                        throw "Could not create Excel.Application ComObject. Please verify that Excel is installed."
+                    }
+                }
+
+                if ($CacheToTemp) {
+                    if (-not $CacheDirectory) {
+                        $CacheDirectory = [System.IO.Path]::GetTempPath()
+                    }
+                    $tempPath = [System.IO.Path]::Combine($CacheDirectory, [System.IO.Path]::GetFileName($xlsFile.FullName))
+                    Write-Host ("Using Temp path: {0}" -f $tempPath)
+                    Copy-Item -Path $xlsFile.FullName -Destination $tempPath -Force
+                    $fileToProcess = $tempPath
+                }
+                else {
+                    $fileToProcess = $xlsFile.FullName
+                }
+
+                $xlsxPath = [System.IO.Path]::ChangeExtension($fileToProcess, ".xlsx")
+
+                try {  
+                    $Excel.Visible = $false
+                    $workbook = $Excel.Workbooks.Open($fileToProcess, $null, $true)
+                    if ($null -eq $workbook) {
+                        Write-Host "Failed to open workbook"
+                    } else {
+                        $workbook.SaveAs($xlsxPath, $xlFixedFormat)
+                        
+                        if ($CacheToTemp) {
+                            Copy-Item -Path $xlsxPath -Destination $destinationXlsxPath -Force
+                        }
+                    }
                 }
                 catch {
-                    throw "{0} already exists and cannot be removed. The file may be locked by another application." -f $xlsxPath
+                    Write-Error ("Failed to convert {0} to XLSX." -f $xlsFile.FullName)
+                    throw
                 }
-                Write-Verbose $("Removed {0}" -f $xlsxPath)
+                finally {
-            }
+                    if ($null -ne $workbook) {
-            else {
+                        $workbook.Close()
-                throw "{0} already exists!" -f $xlsxPath
+                        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
-            }
+                        $workbook = $null
-        }
+                    }
 
-        try {
+                    if ($CacheToTemp) {
-            $Excel = New-Object -ComObject "Excel.Application"
+                        Remove-Item -Path $tempPath -Force
-        }
+                        Remove-Item -Path $xlsxPath -Force
-        catch {
+                    }
-            throw "Could not create Excel.Application ComObject. Please verify that Excel is installed."
+                }
-        }
+            }
-
-        try {  
-            $Excel.Visible = $false
-            $null = $Excel.Workbooks.Open($xlsFile.FullName, $null, $true)
-            $Excel.ActiveWorkbook.SaveAs($xlsxPath, $xlFixedFormat)
         }
         finally {
-            if ($null -ne $Excel.ActiveWorkbook) {
+            if ($null -ne $Excel) {
-                $Excel.ActiveWorkbook.Close()
+                $Excel.Quit()
+                [System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel) | Out-Null
+                $Excel = $null
             }
-            
-            $Excel.Quit()
         }
     }
 }
-

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")) {

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

changelog.md

@@ -1,7 +1,3 @@
-# 7.8.10
-
-- Thank you https://github.com/evenmartinsen for the PR to fix the AV
-
 # 7.8.9
 
 - Thanks to (Edward Miller)[https://github.com/edwardmiller-mesirow] for improving `ConvertTo-ExcelXlsx`and making it more robust