wip

.github/workflows/ci.yml

@@ -3,11 +3,8 @@ on:
     branches:
       - master
       - Set-up-GHA-CI/CD
-    paths-ignore:
-      - 'Examples/**'
       
   pull_request:
-  
 
 jobs:
   validate:

Examples/Charts/ChartDataSeparatePage.ps1

@@ -1,35 +0,0 @@
-$data = ConvertFrom-Csv @"
-Region,State,Units,Price
-West,Texas,927,923.71
-North,Tennessee,466,770.67
-East,Florida,520,458.68
-East,Maine,828,661.24
-West,Virginia,465,053.58
-North,Missouri,436,235.67
-South,Kansas,214,992.47
-North,North Dakota,789,640.72
-South,Delaware,712,508.55
-"@
-
-$xlfile = "$PSScriptRoot\spike.xlsx"
-Remove-Item $xlfile -ErrorAction SilentlyContinue
-
-$xlpkg = $data | Export-Excel $xlfile -WorksheetName Data -AutoNameRange -PassThru
-
-$null = Add-Worksheet -ExcelPackage $xlpkg -WorksheetName Summary -Activate
-
-$params = @{
-    Worksheet = $xlpkg.Summary
-    Title     = "Sales by Region"
-    ChartType = 'ColumnClustered'
-    
-    # XRange    = "Data!A2:A10"
-    # YRange    = "Data!C2:C10"
-
-    XRange    = 'Data!Region'
-    YRange    = 'Data!Units'
-}
-
-Add-ExcelChart  @params
-
-Close-ExcelPackage $xlpkg -Show

ImportExcel.psd1

@@ -6,7 +6,7 @@
     RootModule         = 'ImportExcel.psm1'
 
     # Version number of this module.
-    ModuleVersion      = '7.8.10'
+    ModuleVersion      = '7.8.4'
 
     # ID used to uniquely identify this module
     GUID               = '60dd4136-feff-401a-ba27-a84458c57ede'
@@ -54,7 +54,6 @@ Check out the How To Videos https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5
         'Export-Excel',
         'Export-ExcelSheet',        
         'Get-ExcelColumnName',
-        'Get-ExcelFileSchema',
         'Get-ExcelFileSummary', 
         'Get-ExcelSheetDimensionAddress',       
         'Get-ExcelSheetInfo',
@@ -150,7 +149,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 +214,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 = ''
 
-}
+}

Private/ArgumentCompletion.ps1

@@ -67,8 +67,9 @@ function WorksheetArgumentCompleter {
     param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)
     $xlPath = $fakeBoundParameter['Path']
     if (Test-Path -Path $xlPath) {
-        $xlSheet = Get-ExcelSheetInfo -Path $xlPath
-        $WorksheetNames = $xlSheet.Name
+        $xlpkg = Open-ExcelPackage -ReadOnly -Path $xlPath
+        $WorksheetNames = $xlPkg.Workbook.Worksheets.Name
+        Close-ExcelPackage -nosave -ExcelPackage $xlpkg
         $WorksheetNames.where( { $_ -like "*$wordToComplete*" }) | foreach-object {
             New-Object -TypeName System.Management.Automation.CompletionResult -ArgumentList "'$_'",
             $_ , ([System.Management.Automation.CompletionResultType]::ParameterValue) , $_

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/ConvertFrom-ExcelData.ps1

@@ -8,8 +8,7 @@ function ConvertFrom-ExcelData {
         [ScriptBlock]$ScriptBlock,
         [Alias("Sheet")]
         $WorksheetName = 1,
-		[Alias('HeaderRow', 'TopRow')]
-        [int]$StartRow = 1,
+        [int]$HeaderRow = 1,
         [string[]]$Header,
         [switch]$NoHeader,
         [switch]$DataOnly

Public/ConvertTo-ExcelXlsx.ps1

@@ -45,18 +45,11 @@ function ConvertTo-ExcelXlsx {
             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) {
-                $Excel.ActiveWorkbook.Close()
-            }
-            
-            $Excel.Quit()
-        }
+        $Excel.Visible = $false
+        $null = $Excel.Workbooks.Open($xlsFile.FullName)
+        $Excel.ActiveWorkbook.SaveAs($xlsxPath, $xlFixedFormat)
+        $Excel.ActiveWorkbook.Close()
+        $Excel.Quit()
     }
 }
 

Public/Get-ExcelFileSchema.ps1

@@ -1,47 +0,0 @@
-function Get-ExcelFileSchema {
-    <#
-        .SYNOPSIS
-            Gets the schema of an Excel file.
-
-        .DESCRIPTION
-            The Get-ExcelFileSchema function gets the schema of an Excel file by returning the property names of the first row of each worksheet in the file.
-
-        .PARAMETER Path
-            Specifies the path to the Excel file.
-
-        .PARAMETER Compress
-            Indicates whether to compress the json output.
-
-        .OUTPUTS
-            Json
-
-        .EXAMPLE
-            Get-ExcelFileSchema -Path .\example.xlsx
-    #>
-
-    [CmdletBinding()]
-    param(
-        [Parameter(ValueFromPipelineByPropertyName, Mandatory)]
-        [Alias('FullName')]    
-        $Path,
-        [Switch]$Compress
-    )
-
-    Begin {
-        $result = @()
-    }
-
-    Process {        
-        $excelFiles = Get-ExcelFileSummary $Path
-        
-        foreach ($excelFile in $excelFiles) {
-            $data = Import-Excel $Path -WorksheetName $excelFile.WorksheetName | Select-Object -First 1
-            $names = $data[0].PSObject.Properties.name
-            $result += $excelFile | Add-Member -MemberType NoteProperty -Name "PropertyNames" -Value $names -PassThru
-        }
-    }
-
-    End {
-        $result | ConvertTo-Json -Compress:$Compress
-    }
-}

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

@@ -37,7 +37,8 @@
         [ValidateNotNullOrEmpty()]
         [String]$Password,
         [Int[]]$ImportColumns,
-        [Switch]$Raw
+        [Switch]$Raw,
+        [Switch]$UseSpectre
     )
     end {
         $sw = [System.Diagnostics.Stopwatch]::StartNew()
@@ -252,6 +253,9 @@
                         $entry.Value
                     }
                 }
+                elseif ($UseSpectre) {
+                    $xlBook | Format-SpectreTable
+                }
                 elseif ($Worksheet.Count -eq 1) {
                     $xlBook["$targetSheetname"]
                 }

README.md

@@ -19,13 +19,10 @@ Consider donating. Thank you!
 <br>
 <br>
 
-[![Follow on Twitter](https://img.shields.io/twitter/follow/dfinke.svg?style=social&label=Follow%20%40dfinke)](https://twitter.com/dfinke)
-[![Subscribe on YouTube](https://img.shields.io/youtube/channel/subscribers/UCP47ZkO5EDkoI2sr-3P4ShQ
-)](https://youtube.com/@dougfinke/)
-<br/>
-<br/>
+[![Build Status](https://dougfinke.visualstudio.com/ImportExcel/_apis/build/status/dfinke.ImportExcel?branchName=master)](https://dougfinke.visualstudio.com/ImportExcel/_build)
 [![](https://img.shields.io/powershellgallery/v/ImportExcel.svg)](https://www.powershellgallery.com/packages/ImportExcel)
 [![](https://img.shields.io/powershellgallery/dt/ImportExcel.svg)](https://www.powershellgallery.com/packages/ImportExcel)
+[![](https://img.shields.io/badge/License-Apache%202.0-blue.svg)](https://github.com/dfinke/ImportExcel/tree/70ab9e46c776e96fb287682d5b9b4b51a0ec3bac/LICENSE.txt)
 <a href="https://www.paypal.com/paypalme/DougCharlesFinke"><img src="https://img.shields.io/badge/Donate-PayPal-green.svg" alt="Donate"></a>
 
 # Overview

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

__tests__/Get-ExcelFileSchema.tests.ps1

@@ -1,54 +0,0 @@
-if (-not (Get-command Import-Excel -ErrorAction SilentlyContinue)) {
-    Import-Module $PSScriptRoot\..\ImportExcel.psd1
-}
-
-Describe "Test getting the schema of an Excel file" -Tag GetExcelFileSchema {
-
-    BeforeAll {
-        $script:excelFile = "TestDrive:\test.xlsx"
-        $data = ConvertFrom-Csv @"
-Region,State,Units,Price
-West,Texas,927,923.71
-North,Tennessee,466,770.67
-East,Florida,520,458.68
-East,Maine,828,661.24
-West,Virginia,465,053.58
-North,Missouri,436,235.67
-South,Kansas,214,992.47
-North,North Dakota,789,640.72
-South,Delaware,712,508.55
-"@
-        $data | Export-Excel $excelFile 
-    }
-
-    It "Test Get-ExcelFileSchema function exists" {        
-        $function = Get-Command Get-ExcelFileSchema -ErrorAction SilentlyContinue
-        $function | Should -Not -Be $null
-    }
-
-    It "Test Get-ExcelFileSchema returns json" {
-        $actual = Get-ExcelFileSchema -Path $excelFile
-        $actual | Should -Not -Be $null
-        $actual | Should -BeOfType [string]        
-    }
-
-    It "Test Get-ExcelFileSchema correct json" {
-        $actual = Get-ExcelFileSchema -Path $excelFile
-        $actual = $actual | ConvertFrom-Json 
-
-        $actual.ExcelFile | Should -BeExactly "test.xlsx"
-        $actual.WorksheetName | Should -BeExactly "Sheet1"
-        $actual.Visible | Should -Be $true
-        $actual.Rows | Should -Be 10
-        $actual.Columns | Should -Be 4
-        $actual.Address | Should -BeExactly "A1:D10"
-        
-        $actual.Path | Should -BeExactly ("TestDrive:" + [System.IO.Path]::DirectorySeparatorChar)
-        
-        $actual.PropertyNames.Count | Should -Be 4
-        $actual.PropertyNames[0] | Should -BeExactly "Region"
-        $actual.PropertyNames[1] | Should -BeExactly "State"
-        $actual.PropertyNames[2] | Should -BeExactly "Units"
-        $actual.PropertyNames[3] | Should -BeExactly "Price"
-    }
-}

changelog.md

@@ -1,60 +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
-
-# 7.8.8
-
-- Fix the release
-
-# 7.8.7
-
-- Thanks to [Phil Bossman](https://github.com/pbossman) for the PR and fixing this.
-    
-    Now, back again, you can type `Import-Excel .\yearlySales.xlsx`, press <ctrl+space> and get a list of the worksheets in the Excel file
-
-    ![alt text](images/AutoCompleteSheetNames.png)    
-
-# Infrastructure change
-
-- Thank you to [RipFence](https://github.com/RipFence) who asked how to place a chart on a different sheet from the data and then did a PR adding the example.
-- added `ignore` so files checked into examples do not trigger a CI run
-
-# 7.8.6
-
-- Thank you [John Boyne](https://github.com/kyllath)
-    - Add missing parameter aliases to align with caller/callee
-
-# 7.8.5
-
-- Added `Get-ExcelFileSchema` to get the schema of an Excel file.
-- This was added to support interacting with `ChatGPT`. Passing the schema to the `ChatGPT` via `PowerShellAI` let's you ask questions about the data including generating code based on the schema.
-
-```powershell
-Get-ExcelFileSchema .\salesData.xlsx
-```
-
-```json
-{
-  "ExcelFile": "salesData.xlsx",
-  "WorksheetName": "Sheet1",
-  "Visible": true,
-  "Rows": 10,
-  "Columns": 4,
-  "Address": "A1:D10",
-  "Path": ".",
-  "PropertyNames": [
-    "Region",
-    "State",
-    "Units",
-    "Price"
-  ]
-}
-```
-
 # 7.8.x
 
 Thanks to [Thomas Hofkens](https://github.com/thkn-hofa)