Here is a script to export object data to an Excel document.

#requires -version 2

<#
.SYNOPSIS
This script takes a regular PowerShell object as input and creates a Microsoft Excel document out of it.

.PARAMETER InputObject
This is the object that will be converted into an Excel document.

.PARAMETER WorksheetName
The name of the worksheet created. If no name is given the default (Sheet1) will be used.

.PARAMETER FormatAsList
If you want the worksheet formated as a list, enable it with this parameter and use ListStyle to define what list style you would like to use.

.PARAMETER ListStyle
Defines what list style you would like to use for formatting. The default is "ListStyleLight1".

.PARAMETER AutoFit
This parameter is on by default, and will autofit all the columns.

.PARAMETER SplitColumn
Defines at what column you would like to do a split. Default value is 0 (off).

.PARAMETER SplitRow
Defines at what row you would like to do a split. Default value is 1 (first row).

.PARAMETER FreezePanes
This parameter is true by default and will freeze the panes based on the split values.

.PARAMETER SaveAs
Path and name of the output file. If this paramter is not defined the default path will be used and the file will be named "excel-out.xlsx"

.EXAMPLE
Get-Service | Select-Object Name,DisplayName,Status | .\Export-Excel.ps1 -SaveAS c:\Services.xlsx

This will generate an Excel document with information about all running services, and save it as Services.xlsx at c:\

.EXAMPLE
Get-Service | .\Export-Excel.ps1 -FormatAsList -SplitColumn 1 -SaveAs c:\Services.xlsx

This will generate a fully formated report of all services on a computer, saved at c:\services.xlsx

.LINK
http://www.lucd.info/2010/05/29/beyond-export-csv-export-xls/
LucD created a similar script

.DESCRIPTION
This script will take any PowerShell object and genereate an Excel document with the result. You can choose to format the list using Excel List Styles as well. 

Note that the script will create a temporary CSV file at the location you are running it from. This file will be deleted before the script is finished.
#>

[CmdletBinding()]

param(
	[Parameter(ValueFromPipeline = $true, Mandatory = $true)]
	$InputObject,
	[String]$WorksheetName,
	[switch]$FormatAsList = $false,
	[string]$ListStyle = "TableStyleLight1",
	[switch]$AutoFit = $true,
	[int]$SplitColumn = 0,
	[int]$SplitRow = 1,
	[switch]$FreezePanes = $true,
	[string]$SaveAs
)

BEGIN {
	function Release-Ref ($ref){
		([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) -gt 0)
		 [System.GC]::Collect()
		 [System.GC]::WaitForPendingFinalizers()
	}

	function Set-Culture([System.Globalization.CultureInfo]$Culture){
		[System.Threading.Thread]::CurrentThread.CurrentUICulture = $Culture
		[System.Threading.Thread]::CurrentThread.CurrentCulture = $Culture
	}

	## Get the current location and set path for temp csv file
	$CurrentLocation = Get-Location
	$csv = $CurrentLocation.Path + "\tmp.csv"
	
	## Check if Excel is installed
	$wmi = Get-WmiObject Win32_Product | Where-Object {$_.Caption -match "excel"}
	if (!$wmi){
		Write-Host "Excel not installed - aborting..."
		Break
	}
	
	## Take a note of the original culture and compare with the language of Excel. If Excel is in english but the culture is not, change the culture to prevent known errors!
	$originalCulture = Get-Culture
	if (($wmi.caption -match "english") -and ($originalCulture.Name -ne "en-US")){Set-Culture en-US}
	
	$array = @()
}

PROCESS {$array += $InputObject}

END {
	## Export object to CSV
	$array | Export-CSV $csv -NoTypeInformation -Encoding UTF8

	## Create excel object
	$excel = New-Object -ComObject Excel.Application
	$excel.DisplayAlerts = $false
	$excel.Visible = $false

	## Add default workbooks and delete two of them
	$workbook = $excel.workbooks.add()
	$workbook.worksheets.item(2).delete()
	$workbook.worksheets.item(2).delete()

	## Define first workbook as our worksheet, and name it
	$worksheet = $workbook.worksheets.item(1)
	if ($WorksheetName){$worksheet.name = $WorksheetName}

	## Open CSV file to a temporary worksheet
	$tmpCSV = $excel.workbooks.open("$csv")
	$tmpsheet = $tmpCSV.worksheets.item(1)

	## Copy data from the temporary worksheet to our regular worksheet
	$tmpsheet.UsedRange.Copy()|out-null
	$worksheet.Paste()

	## Close the temporary worksheet
	$tmpCSV.close()

	## Define range before we start formatting
	$range = $worksheet.UsedRange

	## Define the range as a List Object and add a Table Style
	if ($FormatAsList){
		$listObject = $worksheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange,$range,$null,[Microsoft.Office.Interop.Excel.xlYesNoGuess]::xlYes,$null)
		$listObject.TableStyle = $ListStyle
	}

	## Adjust the colum width
	if ($AutoFit){$range.EntireColumn.AutoFit()|out-null}

	## Define the first row as a new range, and format it bold 
	$firstRow = $worksheet.Range("1:1")
	$firstRow.font.bold = $true

	## Define splits and freezes: I want first row and first column to split and frozen
	$worksheet.application.activewindow.splitcolumn = $SplitColumn
	$worksheet.application.activewindow.splitrow = $SplitRow
	$worksheet.application.activewindow.freezepanes = $FreezePanes

	## Define A1 as a new range to "de-select" the entire used range
	$newRange = $worksheet.Range("A1")
	$newRange.Select()|out-null

	if (!$SaveAs){$SaveAs = $CurrentLocation.Path + "\excel-out.xlsx"}
	
	## Save the workbook
	$workbook.saveas("$SaveAs")

	## Quit Excel and clean up
	$excel.quit()
	Release-Ref($range)|out-null
	Release-Ref($firstRow)|out-null
	Release-Ref($newRange)|out-null
	Remove-Item $csv
	Set-Culture $originalCulture
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s