excelpowershellexport-to-excelimport-from-excel

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


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!


Solution

  • 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