Help with Adding Title to the output

[ganapa2000]

Hi,

Please help me in adding the Title to the first Row of the Script. Below is the script

$report = Get-VM | Group-Object -Property {$_.Folder.Id} | Select @{N="Folder Name";E={$folder = $_.Group[0].Folder; $path = $folder.Name; $path}},

  @{N="Cluster";E={Get-Cluster -VM $_.Group}},

  @{N="CPU";E={$_.Group | Measure-Object -Property NumCpu -Sum | Select -ExpandProperty Sum}},

  @{N="Memory(GB)";E={$_.Group | Measure-Object -Property MemoryGB -Sum | Select -ExpandProperty Sum}},

  @{N="ProvisionedSpace(GB)";E={"{0:f1}" -f ($_.Group | Measure-Object -Property ProvisionedSpaceGB -Sum | Select -ExpandProperty Sum)}},

  @{N="Total VM's";E={$_.Group.Count}},

  @{N="PoweredOn VM's";E={($_.Group | Where-Object {$_.PowerState -eq "PoweredOn"}).Count}},

  @{N="PoweredOff VM's";E={($_.Group | Where-Object {$_.PowerState -eq "PoweredOff"}).Count}}

  $sum = $report | Measure-Object -sum "CPU", "Memory(GB)", "ProvisionedSpace(GB)", "Total VM's", "PoweredOn VM's", "PoweredOff VM's"

  $row1 = "" | Select "Folder Name","Cluster","CPU","Memory(GB)","ProvisionedSpace(GB)","Total VM's","PoweredOn VM's","PoweredOff VM's"

  $report += $row1

  $row2 = "" | Select "Folder Name","Cluster","CPU","Memory(GB)","ProvisionedSpace(GB)","Total VM's","PoweredOn VM's","PoweredOff VM's"

  $row2."Folder Name" = 'Total' -- Also I would like to Bold this row

  $row2.CPU = $sum | where{$_.Property -eq 'CPU'} | select -ExpandProperty Sum

  $row2."Memory(GB)" = $sum | where{$_.Property -eq "Memory(GB)"} | select -ExpandProperty Sum

  $row2."ProvisionedSpace(GB)" = $sum | where{$_.Property -eq "ProvisionedSpace(GB)"} | select -ExpandProperty Sum

  $row2."Total VM's" = $sum | where{$_.Property -eq "Total VM's"} | select -ExpandProperty Sum

  $row2."PoweredOn VM's" = $sum | where{$_.Property -eq "PoweredOn VM's"} | select -ExpandProperty Sum

  $row2."PoweredOff VM's" = $sum | where{$_.Property -eq "PoweredOff VM's"} | select -ExpandProperty Sum

  $report += $row2

  $report | Export-Excel -Path $reportlocation -WorksheetName Summary

[LucD · Accepted solution]

Besides the 1st row and column, I don't think that is possible with the ImportExcel module.

You would need to use the Excel COM object, but that has other drwabacks (Excel needs to be installed, working cell by cell...).

Then you could do something like this (and much more)

$excel = New-Object -ComObject excel.application

$workbook = $excel.Workbooks.Add()

$sheet1 = $workbook.Worksheets.Item(1)

$sheet1.Name = 'Test'

$sheet1.Activate() | Out-Null

# Title in bold

$row = 1

$Column = 1

$sheet1.Cells.Item($row,$column) = 'Title'

$sheet1.Cells.Item($row,$column).Font.Bold = $true

# Regular row

$row = 2

$Column = 1

$sheet1.Cells.Item($row,$Column) = 'Value'

$workbook.SaveAs("C:\temp\MyTest.xlsx")

$excel.Quit()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) | Out-Null

[ganapa2000]

Thanks a lot LucD for your quick reply.

I didnt know that there is a limitation with the importexcel module.