Private/Update-ExcelTableDataOptimized.ps1
|
<#
.SYNOPSIS Updates an Excel worksheet by appending a new row of data and optionally filtering out blank rows. .DESCRIPTION The Update-ExcelTableDataOptimized function appends a new row of data to an Excel worksheet. It reads existing data, removes blank rows, appends the new entry, and exports everything back to the Excel file as a formatted table. This function uses the ImportExcel PowerShell module and is ideal for maintaining logs or periodic report entries. .PARAMETER ExcelPath The full file path to the Excel file. This file will be read and updated. .PARAMETER WorksheetName The name of the worksheet in the Excel file to update or create. .PARAMETER ValuesToUpdate A hashtable representing a new row of values to append to the Excel worksheet. Keys in the hashtable must match the column headers. .PARAMETER TableName The name to assign to the Excel table for formatting and referencing. .EXAMPLE $logData = @{ "Date" = (Get-Date).ToString("yyyy-MM-dd HH:mm:ss") "User" = $env:USERNAME "Activity" = "Excel Update" } Update-ExcelTableDataOptimized -ExcelPath "C:\Reports\ActivityLog.xlsx" ` -WorksheetName "Log" ` -ValuesToUpdate $logData ` -TableName "ActivityLog" .EXAMPLE # Update a report with custom data and clear previous blank rows Update-ExcelTableDataOptimized -ExcelPath "D:\Output\Monthly.xlsx" ` -WorksheetName "Summary" ` -ValuesToUpdate @{ "Month" = "April"; "Total" = 123 } ` -TableName "MonthlySummary" .NOTES Requires the ImportExcel module. Install it using: Install-Module ImportExcel #> function Update-ExcelTableDataOptimized { [CmdletBinding()] param ( [Parameter(Mandatory)] [string]$ExcelPath, [Parameter(Mandatory)] [string]$WorksheetName, [Parameter(Mandatory)] [hashtable]$ValuesToUpdate, [Parameter(Mandatory)] [string]$TableName ) try { if (-not (Get-Module -ListAvailable -Name ImportExcel)) { throw "The 'ImportExcel' module is not available. Install it with 'Install-Module ImportExcel'." } Import-Module ImportExcel -Force # Load existing data $existingData = @() if (Test-Path $ExcelPath) { $imported = Import-Excel -Path $ExcelPath -WorksheetName $WorksheetName -ErrorAction SilentlyContinue if ($imported) { # Filter out blank rows $existingData = $imported | Where-Object { $_.PSObject.Properties.Value -match '\S' } } } # Convert hashtable to custom object $newRow = [PSCustomObject]@{} foreach ($key in $ValuesToUpdate.Keys) { $newRow | Add-Member -MemberType NoteProperty -Name $key -Value $ValuesToUpdate[$key] } # Combine rows safely $combinedData = @() if ($existingData) { $combinedData += $existingData } $combinedData += $newRow # Export back to Excel $combinedData | Export-Excel -Path $ExcelPath ` -WorksheetName $WorksheetName ` -TableName $TableName ` -TableStyle Medium2 ` -AutoFilter ` -AutoNameRange ` -ClearSheet Write-LogEntry -Message "Excel updated successfully at '$ExcelPath'. Total rows: $($combinedData.Count)" } catch { Write-LogEntry -Message "Failed to update Excel table: $_" } } |