Public/Get-FirstRowWithHeadersFromExcel.ps1
|
<#
.SYNOPSIS Retrieves the first row with headers from a specified Excel worksheet. .DESCRIPTION This function reads an Excel file and extracts the first row of data along with the headers. It returns the data as a hashtable where keys are the column headers and values are the corresponding cell values from the first row. .PARAMETER ExcelPath The path to the Excel file. .PARAMETER WorksheetName The name of the worksheet to read from. Defaults to "Processes". .OUTPUTS System.Collections.Hashtable A hashtable containing the headers and the corresponding values from the first row of the specified worksheet. .EXAMPLE $excelPath = "C:\path\to\your\file.xlsx" $rowData = Get-FirstRowWithHeadersFromExcel -ExcelPath $excelPath $rowData | Format-Table -AutoSize .EXAMPLE $excelPath = "C:\path\to\your\file.xlsx" $rowData = Get-FirstRowWithHeadersFromExcel -ExcelPath $excelPath -WorksheetName "Sheet1" $rowData | Format-Table -AutoSize #> function Get-FirstRowWithHeadersFromExcel { [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [string]$ExcelPath, [Parameter(Mandatory = $false)] [string]$WorksheetName = "Processes" ) try { # Import only the first row of data $data = Import-Excel -Path $ExcelPath -WorksheetName $WorksheetName -StartRow 1 -EndRow 2 if (-not $data) { Write-Warning "No data found in the first row or file is empty." return @{} } # Initialize an empty hashtable $rowData = @{} # Assuming $data is a PSCustomObject, directly add each property to the hashtable foreach ($prop in $data.PSObject.Properties) { $rowData[$prop.Name] = $prop.Value } return $rowData } catch { Write-Error "Failed to read Excel file: $_" } } |