Jan 182018
 
Please Share...

PowerShell provides multiple ways to access Excel. My favorite so far is via COMObject Excel.Application. This object provides direct access to Excel workbooks, worksheets. In this short article we’ll see how to accomplish this using PowerShell and Excel’s COMObject.

Instantiating Excel COM Object using New-Object PowerShell CmdLet

Using following line of code to instantiate Excel COM object.

$excelObj = New-Object -ComObject Excel.Application
Creating an Excel workbook
$excelWB = $excelObj.WorkBooks.Add()
Get reference to a worksheet
$excelWS = $excelWB.WorkSheets.Item(1)
Get reference to an excel worksheet cell

Change attributes of a Cell using following code…

$excelWS.Cells.Item(1, 1) = "Test"
$excelWS.Cells.Item(1, 1).Font.Bold = $true
Sample code to fill out an Excel worksheet

I’m here using Get-Process cmdlet to generate some data in tabular format. But basically, below script uses above logic to fill out the sheet…

$excelObj = New-Object -ComObject Excel.Application
$excelWB = $excelObj.workbooks.Add()
$excelWS = $excelWB.WorkSheets.Item(1)

#Get processes running on my machine
$procs = Get-Process

#Get all column names, process class' property name
$ColumnNames = $procs[0].GetType().GetMembers() | Where-Object{$_.MemberType -eq "Property" -and $_.MemberType -ne "Module"} | %{$_.Name}

#Make main excel window visible
$excelObj.Visible = $true

#Add excel column title
$cIndex = 0; #Column index
$ColumnNames | %{$cIndex++;$excelWS.Cells.Item(1, $cIndex).Font.Bold=$true;$excelWS.Cells.Item(1, $cIndex)=$_}

#Add process information into excel rows
$rIndex = 1;$procs | %{$cIndex = 0;$rIndex++;$proc=$_;$ColumnNames | %{$cIndex++;$excelWS.Cells.Item($rIndex, $cIndex) = "$($proc.$($_))"}}
Output

Another option

PowerShell now does come with built in Excel support if you’re ok to install following module…

Install-Module ImportExcel

With this module in place you can run commands like…

Get-Process | Where Company | Export-Excel C:\Temp\ps.xlsx -Show -IncludePivotTable -PivotRows Company -PivotData @{Handles=”sum”} -IncludePivotChart -ChartType PieExploded3D

This will create an excel workbook with data in a pivot table. For this case we’ve set PivotData to sum of handles based on product company.

References

https://blogs.technet.microsoft.com/heyscriptingguy/2015/11/25/introducing-the-powershell-excel-module-2/

Please Share...

  2 Responses to “PowerShell TidBits: Creating Excel Workbook and filling out data into WorkSheets”

  1. You can also check out my videos on using my PowerShell Excel module

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.