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?

Short answer: You can't do this using Export-Csv because a .csv is a plain text file that doesn't support multiple sheets like a real Excel workbook. — Filburt
– Filburt, Commented Apr 21, 2012 at 16:09
@Filburt Can i export it to excel? or can i append to the existing result set and put it in the same sheet in csv? — Rockstart
– Rockstart, Commented Apr 21, 2012 at 16:17
It should be possible to append the second result set to the same csv - maybe needs tweaking (adding some empty lines) to look nice in Excel. Otherwise you'd have to create a real .xslx workbook and fill in the result sets using Office automation. — Filburt
– Filburt, Commented Apr 21, 2012 at 16:24

BartekB · Accepted Answer · 2012-05-16 11:26:49Z

3

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

answered May 16, 2012 at 11:26

BartekB

8,71034 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding results to a new Excel sheet using powershell 1.0

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related