LucD notes

Warning: this post has no “virtual” content !

This time I post a function that allows you to export your data to a “real” spreadsheet (XLS format) instead of a CSV file.

The reason for posting this function was a series of threads in the PowerCLI Community by Suresh. Over several threads he has been collecting scripts that create various reports on his vSphere environment. Ultimately he wanted to have a spreadsheet where each report would be stored on a worksheet.

PowerShell has the very handy Export-Csv cmdlet to create CSV files but afaik nothing for creating XLS files

That’s when I decided to “pimp” my XLS creating function I already had to something more suited for general use.

The function uses Excel through a COM object. So yes, you do need to have Excel installed on the client where you use the function. The function first exports the data to a CSV file with the Export-Csv cmdlet. And then does a copy/paste to the worksheet in the XLS file.

Update June 16th 2010: the function has a new parameter -ChartType which allows the addition of a chart on the worksheet.

Update November 2nd 2010: a complete overhaul of the function

• The function now uses the clipboard, instead of a temporary CSV file, to hold the data that needs to be written to the worksheet.
• The function places the array on the clipboard in such a way that it can be correctly pasted into the Worksheet. Thanks to my colleague Géry for his algorithm.
• The function uses the Textbox trick as described in Copy and Paste with Clipboard from PowerShell. With this method we don’t need to start an new PowerShell thread with the STA attribute. And this method is considerably faster !

The script

#requires -version 2

function Export-Xls{
	<#
.SYNOPSIS
	Saves Microsoft .NET Framework objects to a worksheet in an XLS file
.DESCRIPTION
	The Export-Xls function allows you to save Microsoft .NET Framework objects
	to a named worksheet in an Excel file (type XLS). The position of the
	worksheet can be specified.
.NOTES
	Author:  Luc Dekens
.PARAMETER InputObject
	Specifies the objects to be written to the worksheet. The parameter accepts
	objects through the pipeline.
.PARAMETER Path
	Specifies the path to the XLS file.
.PARAMETER WorksheetName
	The name for the new worksheet. If not specified the name will
	be "Sheet" followed by the "Ticks" value
.PARAMETER SheetPosition
	Specifies where the new worksheet will be inserted in the series of
	existing worksheets. You can specify "begin" or "end". The default
	is "begin".
.PARAMETER ChartType
	Specifies the type of chart you want add to the worksheet.
	All types in the [microsoft.Office.Interop.Excel.XlChartType]
	enumeration are accepted.
.PARAMETER NoTypeInformation
	Omits the type information from the worksheet. The default is to
	include the "#TYPE" line.
.PARAMETER AppendWorksheet
	Specifies if the worksheet should keep or remove the existing
	worksheet in the spreadsheet. The default is to append.
.EXAMPLE
	PS> $data = Get-Process | Select-Object Name, Id, WS
	PS> Export-Xls $data C:\Reports\MyWkb.xls -WorksheetName "WS" -AppendWorksheet:$false
.EXAMPLE
	PS> $data = Get-Process | Select-Object Name, Id, WS
	PS> Export-Xls $data C:\Reports\MyWkb.xls -SheetPosition "end"
.EXAMPLE
	PS> $data = Get-Process | Select-Object Name, Id, WS
	PS> Export-Xls $data C:\Reports\MyWkb.xls -WorksheetName "WS" -ChartType "xlColumnClustered"
#>
	param(
	[parameter(ValueFromPipeline = $true,Position=1)]
	[ValidateNotNullOrEmpty()]
	$InputObject,
	[parameter(Position=2)]
	[ValidateNotNullOrEmpty()]
	[string]$Path,
	[string]$WorksheetName = ("Sheet " + (Get-Date).Ticks),
	[string]$SheetPosition = "begin",
	[PSObject]$ChartType,
	[switch]$NoTypeInformation = $true,
	[switch]$AppendWorksheet = $true
	)

	begin{
		[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")
		if($ChartType){
			[microsoft.Office.Interop.Excel.XlChartType]$ChartType = $ChartType
		}

		function Set-ClipBoard{
			param(
				[string]$text
			)
			process{
				Add-Type -AssemblyName System.Windows.Forms
				$tb = New-Object System.Windows.Forms.TextBox
				$tb.Multiline = $true
				$tb.Text = $text
				$tb.SelectAll()
				$tb.Copy()
			}
		}

		function Add-Array2Clipboard {
			param (
				[PSObject[]]$ConvertObject,
				[switch]$Header
			)
			process{
				$array = @()

				if ($Header) {
					$line =""
					$ConvertObject | Get-Member -MemberType Property,NoteProperty,CodeProperty | Select -Property Name | %{
						$line += ($_.Name.tostring() + "`t")
					}
					$array += ($line.TrimEnd("`t") + "`r")
				}
				else {
					foreach($row in $ConvertObject){
						$line =""
						$row | Get-Member -MemberType Property,NoteProperty | %{
							$Name = $_.Name
							if(!$Row.$Name){$Row.$Name = ""}
							$line += ([string]$Row.$Name + "`t")
						}
						$array += ($line.TrimEnd("`t") + "`r")
					}
				}
				Set-ClipBoard $array
			}
		}

		$excelApp = New-Object -ComObject "Excel.Application"
		$originalAlerts = $excelApp.DisplayAlerts
		$excelApp.DisplayAlerts = $false
		if(Test-Path -Path $Path -PathType "Leaf"){
			$workBook = $excelApp.Workbooks.Open($Path)
		}
		else{
			$workBook = $excelApp.Workbooks.Add()
		}
		$sheet = $excelApp.Worksheets.Add($workBook.Worksheets.Item(1))
		if(!$AppendWorksheet){
			$workBook.Sheets | where {$_ -ne $sheet} | %{$_.Delete()}
		}
		$sheet.Name = $WorksheetName
		if($SheetPosition -eq "end"){
			$nrSheets = $workBook.Sheets.Count
			2..($nrSheets) |%{
				$workbook.Sheets.Item($_).Move($workbook.Sheets.Item($_ - 1))
			}
		}
		$sheet.Activate()
		$array = @()
	}

	process{
		$array += $InputObject
	}

	end{
		Add-Array2Clipboard $array -Header:$True
		$selection = $sheet.Range("A1")
		$selection.Select() | Out-Null
		$sheet.Paste()
		$Sheet.UsedRange.HorizontalAlignment = [microsoft.Office.Interop.Excel.XlHAlign]::xlHAlignCenter
		Add-Array2Clipboard $array
		$selection = $sheet.Range("A2")
		$selection.Select() | Out-Null
		$sheet.Paste() | Out-Null
		$selection = $sheet.Range("A1")
		$selection.Select() | Out-Null

		$sheet.UsedRange.EntireColumn.AutoFit() | Out-Null
		$workbook.Sheets.Item(1).Select()
		if($ChartType){
			$sheet.Shapes.AddChart($ChartType) | Out-Null
		}
		$workbook.SaveAs($Path)
		$excelApp.DisplayAlerts = $originalAlerts
		$excelApp.Quit()
		Stop-Process -Name "Excel"
	}
}

Annotations

Line 65-77: This short function place a string in the Textbox control and then uses the Copy method to send the string to the Clipboard. Function by Brian Reiter.

Line 79-107: This concept for this function came from my colleague Géry. The trick is to separate the properties from the array by the TAB character. That way a paste in Excel will place the properties in different cells. The first part of the function handles the property names (or the column headers in Excel), the second part prepares the actual data.

Line 110-111,155: The Excel alerts are temporarily disabled to avoid pop-up messages. The original setting is restored just before leaving the function.

Line 112-117: The function tests if the XLS file exists or not.

Line 118 A new worksheet is added in position 1.

Line 119-121: If the AppendWorksheet switch is $false, all the other worksheets are removed.

Line 123-128: These lines move the worksheet to the end of the list if the SheepPosition parameter states “end”.

Line 134: All the objects from the InputObject are copied to the local $array variable.

Line 138: The property names of the $array variable are copied to the clipboard

Line 139-141: The column headers (the property names) are pasted to the Excel worksheet

Line 142: The column headers are aligned.

Line 143: The data in the $array variable is copied to the clipboard

Line 144-146: The data is pasted into the Excel worksheet 1 row below the headers.

Line 147: Unselect the range that was just copied.

Line 150: The width of all columns in the worksheet is changed so all cell values are visible.

Line 158: Although the Quit method is called, the Excel process stays active. The only way to get rid of it is through a Stop-Process cmdlet.

Examples

Some sample runs I did with Excel 2003.

$data = Get-Process | Select-Object Name, Id, WS
Export-Xls $data D:\Reports\MyWkb.xls

This produces the following

In this installation Excel was configured to start a new spreadsheet with 3 worksheets.

But we can get rid of those by using the -AppendWorksheet switch.

$data = Get-Process | Select-Object Name, Id, WS
Export-Xls $data D:\Reports\MyWkb.xls -AppendWorksheet:$false

Now we only have the worksheet we just created.

You can use the function in a pipeline

Get-Process | Select-Object Name, Id, WS | Export-Xls -Path D:\Reports\MyWkb.xls -WorksheetName "My new sheet"

This gives

And you can specify that the new worksheet should be at the end

Get-Process | Select-Object Name, Id, WS | `
   Export-Xls -Path D:\Reports\MyWkb.xls -WorksheetName "Another sheet" -SheetPosition "end"

This gives

Charts

With the -ChartType parameter you can create a chart on the worksheet.

The accepted values for this parameter are all the values present in the microsoft.Office.Interop.Excel.XlChartType enumtype. If you want to produce a list with the acceptable values you can do something like this.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel") | Out-Null

[microsoft.Office.Interop.Excel.XlChartType] | gm -Static -MemberType Property | %{$_.Name}

It will produce a rather long list of available chart types.

Some sample runs

$data = Get-Process | Sort-Object -Property WorkingSet -Descending | `
  Select-Object Name, @{N="WS";E={$_.WorkingSet/1MB}} -First 10
Export-Xls $data C:\Temp\MyWkb.xls -AppendWorksheet:$false -WorksheetName "WS" -ChartType "xlColumnClustered"

Produces

This short script selects the working set and the peak working set and add a line graph.

$data = Get-Process | Select-Object -First 10 Name, @{N="WS";E={$_.WorkingSet/1MB}},@{N="PM";E={$_.PeakWorkingSet/1MB}}
Export-Xls $data C:\Temp\MyWkb.xls -AppendWorksheet:$false -WorksheetName "WS" -ChartType "xlLine"

And this produces