How to update an existing Excel file with multiple sheets using PowerShell

Question

How can I update an existing Excel file with multiple sheets using PowerShell.

I'm using the below code for an Excel file with a single sheet, but need to do something similar to an Excel file with multiple sheets (Sheet names are known).
So I need to import all existing sheets, update each sheet individually and then save all changed sheets to a new multi-sheet Excel file.

Import-Module activedirectory

# Read existing Excel file
$xlsx = import-excel 'D:\users.xlsx'

# Get the properties
$Properties = [Collections.Generic.List[Object]]$xlsx[0].psobject.properties.Name

# Add new columns. Position 6 & 7
$Properties.Insert(6, "Name")
$Properties.Insert(7, "Team")

# Obtain name and team from AD for every user (ID)
foreach ($row in $xlsx)
{   
   $user = Get-AD -CN $row.ID

   $row | Add-Member -Name 'Name' -Value $user[0]  -MemberType NoteProperty 
   $row | Add-Member -Name 'Team' -Value $user[1]  -MemberType NoteProperty
}

# Create a new Excel file
$xl = $xlsx | Select-Object -Property $Properties | Export-Excel 'D\new_users.xlsx' -Append -WorksheetName "USERS"

$Sheet = $xl.Workbook.Worksheets["USERS"]

Close-ExcelPackage $xl –Show

Any help much approciated!

Answer 1

You can iterate over each spreadsheet with Get-ExcelSheetInfo then use Import-Excel with the -WorksheetName parameter. If you want to append new columns to each spreadsheet then something like this should work:

Get-ExcelSheetInfo 'D:\users.xlsx' | ForEach-Object {
    $xlsx = Import-Excel $_.Path -WorksheetName $_.Name

    foreach ($row in $xlsx) {
        # do stuff with `$row`
    }

    $xlsx | Export-Excel -Path $_.Path -WorksheetName $_.Name
}

---

Adding a simple example appending columns to an Excel file with 2 Spreadsheets.

First we create a test test.xlsx file with the Id of the first 4 processes on Processes Spreadsheet and the Name of the first 4 services on Services Spreadsheet:

Get-Process | Select-Object Id -First 4 |
    Export-Excel .\test.xlsx -WorksheetName Processes

Get-Service | Select-Object Name -First 4 |
    Export-Excel .\test.xlsx -WorksheetName Services

Looking, at the current file:

Get-ExcelSheetInfo .\test.xlsx | ForEach-Object {
    '- Sheet Name: ' + $_.Name | Out-Host
    Import-Excel $_.Path -WorksheetName $_.Name -AsText Id | Out-Host
}

# Outputs:

- Sheet Name: Processes

Id
--
6392
3964
3604
7708

- Sheet Name: Services

Name
----
AarSvc_9af2c
AJRouter
ALG
AppIDSvc

Now, for the Processes Spreadsheet append columns Id, ProcessName and HandleCount in that order and for the Services Spreadsheet append columns Status, Name and DisplayName.

foreach ($sheet in Get-ExcelSheetInfo .\test.xlsx) {
    if ($sheet.Name -eq 'Processes') {
        # here we handle the logic for the processes sheet
        Import-Excel $sheet.Path -WorksheetName $sheet.Name -AsText Id |
            ForEach-Object { Get-Process -Id $_.Id } |
            Select-Object Id, ProcessName, HandleCount |
            Export-Excel $sheet.Path -WorksheetName $sheet.Name
    }
    elseif ($sheet.Name -eq 'Services') {
        # here we handle the logic for the services sheet
        Import-Excel $sheet.Path -WorksheetName $sheet.Name |
            ForEach-Object { Get-Service -Name $_.Name } |
            Select-Object Status, Name, DisplayName |
            Export-Excel $sheet.Path -WorksheetName $sheet.Name
    }
}

Lastly, check how the result looks like:

Get-ExcelSheetInfo .\test.xlsx | ForEach-Object {
    '- Sheet Name: ' + $_.Name | Out-Host
    Import-Excel $_.Path -WorksheetName $_.Name -AsText Id, HandleCount | Out-Host
}

# Outputs:

- Sheet Name: Processes

Id   ProcessName          HandleCount
--   -----------          -----------
6392 ApplicationFrameHost 271
3964 AsHidSrv             115
3604 AsLdrSrv             134
7708 ATKOSD2              197

- Sheet Name: Services

Status  Name         DisplayName
------  ----         -----------
Stopped AarSvc_9af2c Agent Activation Runtime_9af2c
Stopped AJRouter     AllJoyn Router Service
Stopped ALG          Application Layer Gateway Service
Stopped AppIDSvc     Application Identity