Read only the headers from an Excel file using PowerShell and Import-Excel module

Question

Is it possible to read only the headers from an Excel file using PowerShell and Import-Excel module?

I've an Excel file with multiple sheets. One of them only contains headers and now data rows.
After some updates I need to write the results to a new Excel file.
Everything works fine for the other sheets but for the one with no data I'm unable to copy the headers to the new file.

$xlsx = Import-Excel 'D:\data.xlsx' -WorksheetName 'events'
Write-Host $xlsx

> WARNING: Worksheet 'events' in workbook 'D:\data.xlsx' contains no data in the rows after top row '1'

$prop = [Collections.Generic.List[Object]]$xlsx[0].psobject.properties.Name
Write-Host $prop

> Cannot index into a null array. At D:\Untitled1.ps1:4 char:1
> + $prop = [Collections.Generic.List[Object]]$xlsx[0].psobject.propertie ...
> + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>     + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
>     + FullyQualifiedErrorId : NullArray

Any help much appriciated!

Answer 1

There are 2 options you could use to get the headers of a Spreadsheet with no data:

• -NoHeader switch then you need to target the .Value property instead of the .Name property:

$xlsx = Import-Excel 'D:\data.xlsx' -WorksheetName 'events' -NoHeader
$headers = $xlsx[0].PSObject.Properties.Value

• Open-ExcelPackage and get the value of the .Text property of each Cell:

$pkg = Open-ExcelPackage 'D:\data.xlsx'
$headers = $pkg.Workbook.Worksheets['events'].Cells | ForEach-Object Text
Close-ExcelPackage $pkg