Adding results to a new Excel sheet using powershell 1.0

Question

I have two powershell cmdlets to generate process report and services report.

eg: Get-process and Get-services

I know export-csv c:\test.csv will export the result to excel sheets.

But I need the result of the first cmdlet to export to the first sheet and the second cmdlet to second sheet of the same csv file.

How do I do this?

Answer 1

IMO the best option here is just to use excel automation. E.g. ConvertTo-Excel.ps1:

param (
    [ValidatePattern('\.csv$')]
    [ValidateScript({
        Test-Path $_
    })]
    [string[]]$Path
)

$FullPath = $Path | ForEach-Object {
    (Resolve-Path $_).ProviderPath
}

$First, $Rest = $FullPath

$Excel = New-Object -ComObject Excel.Application

$Book = $Excel.Workbooks.Open($First)

foreach ($Item in $Rest) {
    $Next = $Excel.Workbooks.Open($Item)
    $Next.ActiveSheet.Move($Book.ActiveSheet)
}

$Excel.Visible = $true

You can run above script with .\ConvertTo-Excel.ps1 -Path .\services.csv, .\process.csv, ....csv And have all csv combined, in reversed order.

HTH Bartek