Beyond Export-Csv: Export-Xls

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

69 thoughts on “Beyond Export-Csv: Export-Xls

  1. Pingback: Get-Scripting Podcast Episode 19 – (Kirk Munro – Quest Software) | CrypticZero

  2. Hello Luc – Excellent one. I’d like to customize a little. Please help me with that.

    1. No grid lines in the worksheet
    2. Data to be pushed down out of the screen. Now data is visible next to the chart but I want a bigger chart and data to be out of the visible worksheet area.
    3. As state above, a bigger chart area.
    4. Add data pointers to the chart columns.

    Again, thanks for the great work around PowerShell and PowerCLI..

    Regards,
    Pardha.

  3. LucD. I am trying to automatically make cover pages in a word document based on the information from an .xlsx spreadsheet. I would like the information written to the word doc based on a command input that would match a number in the first column of cells. The spreadsheet would have a title row and the information would start on row 2 – 100. The spreadsheet would be 100 rows x 10 columns. I would also like to be able to input a title by typing it into the command prompt. The last feature would prompt the user for a to write A, B, C, D, E, or F in the word file; where each letter would match a comment to write in the word file. Is there anything you can do to help? I have been doing some research and have basic writing skills, but this seems way over my head. I have information to format the document for the positioning, fonts, and size of the information that is written from the xlsx file, but I am having a hard time reading the information in the row in the excel file based on any given cell in the first column.

  4. Works great Thanks! but when i use -LastLogon in my command, i get errors. I think it due to accounts that have not been logged in and have not date or time. But everything still works great.

  5. Hi
    Tried the following:
    first: . .\Export-Xlsx.ps1
    then: Get-Command -CommandType Function -Name Export-Xlsx
    That gave me:
    CommandType Name ModuleName
    ———– —- ———-
    Function Export-Xlsx

    Then i ran: Get-Process | select Name,Id,Handles | Export-XLSX c:\temp\Report.xlsx

    this keep giving me lots the following error:
    Export-XLSX : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or
    the input and its properties do not match any of the parameters that take pipeline input.
    At line:1 char:40
    + Get-Process | select Name,Id,Handles | Export-XLSX c:\temp\Report.xlsx
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidArgument: (@{Name=ALMon; Id=2556; Handles=180}:PSObject) [Export-Xlsx], ParameterBindingException
    + FullyQualifiedErrorId : InputObjectNotBound,Export-Xlsx

    Any ideas?

  6. Hi, please help, I get this error on Luc’s original script:
    Get-Member : No object has been specified to the get-member cmdlet. At ps1:89 char:23
    (powershell v.3 – Excel 2010)

    • @MD, it looks as if there was no data passed to the Export-Xls function.
      Can you show how you passed your data to the Export-Xls function ?
      And did you check that there is actually data ?

  7. @LucD
    Thanks very much! I have been really struggling with getting over this hurdle. Currently updating to powershell 3.0 to further explore the [ordered cast] that you previously mentioned.

  8. @Steve

    @LucD
    Here is the broader scope of what I am referring to. This is from my script that uses your function.

    $data = @()
    $VMFolder = “*”
    filter Get-FolderPath {
    $_ | Get-View | % {
    $row = “” | select Name, Path
    $row.Name = $_.Name
    $current = Get-View $_.Parent
    $path = “”
    do {
    $parent = $current
    if($parent.Name -ne “vm”){$path = $parent.Name + “\” + $path}
    $current = Get-View $current.Parent
    } while ($current.Parent -ne $null)
    $row.Path = $path
    $row
    }
    }
    $VMs = Get-Folder $VMFolder | Get-VM
    $Datastores = Get-Datastore | select Name, Id
    $VMHosts = Get-VMHost | select Name, Parent
    ForEach ($VM in $VMs) {
    $VMView = $VM | Get-View
    $VMInfo = {} | Select VMName,Host,Cluster,OS,Folder,IPAddress,ToolsStatus,ToolsVersion,Datastore,NumCPU,MemMb,Powerstate | Sort-Object VMName
    $VMInfo.VMName = $vm.name
    $VMInfo.Host = $vm.vmhost.name
    $VMInfo.Cluster = $vm.vmhost.Parent.Name
    $VMInfo.OS = $vm.Guest.OSFullName
    $VMInfo.Folder = ($vm | Get-Folderpath).Path
    $VMInfo.IPAddress = $vm.Guest.IPAddress[0]
    $VMInfo.ToolsStatus = $VMView.Guest.ToolsStatus
    $VMInfo.ToolsVersion = $VMView.Guest.ToolsVersion
    $VMInfo.Datastore = ($Datastores | where {$_.ID -match (($vmview.Datastore | Select -First 6) | Select Value).Value} | Select Name).Name
    $VMInfo.NumCPU = $vm.NumCPU
    $VMInfo.MemMb = [Math]::Round(($vm.MemoryMB),2)
    $VMInfo.Powerstate = $vm.Powerstate
    $data += $VMInfo
    }

    $data | Select VMName,Host,Cluster,Folder,OS,NumCPU,MemMb,IPAddress,ToolsStatus,ToolsVersion,Datastore,Powerstate | Sort-Object VMName | Out-GridView
    $data = $data | Select VMName,Host,Cluster,Folder,OS,NumCPU,MemMb,IPAddress,ToolsStatus,ToolsVersion,Datastore,Powerstate | Sort-Object VMName
    $Heading = “VMGenInfo”
    if ($data[0] -eq $null){
    Write-Host “No entries for $Heading found”
    }
    else
    {
    Export-Xls $data ($FPath) -SheetPosition “end”
    }

  9. @LucD
    Does anyone have a solution to my issue? I have done a test where I output $data to grid and the data is in the desired format. I then pass that same $data to the Export-Xls function and the data arrives in a different columnar order???

    • @Steve, the basic problem is that pre-PowerShell v3, you couldn’t define an order on a PS object. And in the function I do a Get-Member to fetch the properties of the object that is passed to the function. And that is where you loose the order of the properties.
      But I have talking to someone who has a solution for this problem. Watch this space, there should be an update soon :-)

  10. Hi Luc,

    I’ve discovered your script just after I wrote one of my own. It’s almost similar except for moving the data input to Excel and the method of closing Excel.
    Instead of the clipboard I convert the input data to a multidimensional array and then add it to a range in Excel.
    And instead of closing each and every running instance of Excel I release the Com Objects.
    In terms of speed there’s not much difference. Though the column order is retained with my method.
    If you’re interested I can upload my version somewhere.

  11. I am wondering how I can determine which order my data is placed into columns. I am getting all of my data but the order I select / construct the data in does not represent the way it is placed into columns?

    • @Steve, the reason the columns are not necessarily in the same order in the Worksheet is due to line 89. The function does a Get-Member to get all the properties from your array. And then creates the columns in the Worksheet.
      Till PowerShell v3 there was no fixed order in the properties of an object. Meaning that the Get-Member would not necessarily return the properties in the same order as you added them to your array.
      With the introduction of PowerShell v3 one can now use the [ordered] cast on arrays.
      I will have to do some further tests, but that should fix the nuisance you are seeing. I’ll update the post shortly.

  12. Hi LucD I am very new on this and I have read the post for a solution to my problem, but I did not find it. SO, here is what I got.

    True v2.0.50727 C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\14.0.0.0__71e9bce111e9429c\Microsoft.Office.Interop.Excel.dll
    Exception setting “DisplayAlerts”: “Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))”
    At C:\ExportXls.ps1:111 char:13
    + $excelApp. <<<< DisplayAlerts = $false
    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

    Do you have an idea of what is happening?

  13. Good post. I be taught one thing tougher on totally different blogs everyday. It is going to always be stimulating to learn content material from other writers and follow just a little something from their store. I’d desire to make use of some with the content on my blog whether you don’t mind. Natually I’ll offer you a link on your internet blog. Thanks for sharing.

  14. Hi Luc,

    I got really really really annoyed with the auto-sorting of columns behavior and worked out a work-around. I’ve added an extra parameter “Headers” with a 2-dimensional array containing the fieldname and the custom header title for it. Columns will remain in the same order as defined in the array.

    I’ve also added the sleep()-routine someone mentioned in the comments here. This fixed the aslo annoying “previous versions” issue when opening the excel files afterwards.

    Kind regards

    Walter

    #requires -version 2

    function Export-Xls{
    $data = Get-Process | Select-Object Name, Id, WS
    PS> Export-Xls $data C:\Reports\MyWkb.xlsx -WorksheetName “WS” -AppendWorksheet:$false
    .EXAMPLE
    PS> $data = Get-Process | Select-Object Name, Id, WS
    PS> Export-Xls $data C:\Reports\MyWkb.xlsx -SheetPosition “end”
    .EXAMPLE
    PS> $data = Get-Process | Select-Object Name, Id, WS
    PS> Export-Xls $data C:\Reports\MyWkb.xlsx -WorksheetName “WS” -ChartType “xlColumnClustered”
    .EXAMPLE
    PS> $data = Get-Process | Select-Object Name, Id, WS
    PS> Export-Xls $data C:\Reports\MyWkb.xlsx -WorksheetName “WS” -ChartType “xlColumnClustered” -AutoFilter:$true -SplitRow 1 -SplitColumn 1
    .EXAMPLE
    PS> $header = $headers = @(@(“Name”, “Process Name”),@(“ID”, “id”),@(“WS”, “WS”))
    PS> $data = Get-Process | Select-Object Name, Id, WS
    PS> Export-Xls $data C:\Reports\MyWkb.xlsx -Headers $header
    This will result in a table where column A is titled “Process Name”, column B is titled “id” and column C is titled “WS”
    #>
    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,
    [switch]$AutoFilter = $true,
    [int]$SplitRow = 1,
    [int]$SplitColumn = 0,
    $Headers
    )

    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 =””
    if ($headers) {
    foreach ($column in $headers) {
    $line += ([string]$column[1] + “`t”)
    }
    }
    else {
    $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 =””
    if ($headers) {
    foreach ($column in $headers) {
    if ($row.($column[0])) {
    $val = [string] $row.($column[0])
    }
    else {
    $val = “”
    }
    $line += ($val + “`t”)
    #$line += ($row.([string]$column[0]) + “`t”)
    }
    }
    else {
    $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
    }
    }

    [System.Threading.Thread]::CurrentThread.CurrentCulture = “en-US”
    $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))
    }
    }

    if (($SplitRow -gt 0) -or ($SplitColumn -gt 0)) {
    $excelApp.ActiveWindow.SplitRow = $SplitRow
    $excelApp.ActiveWindow.SplitColumn = $SplitColumn
    $excelApp.ActiveWindow.FreezePanes = $true
    }

    $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

    if ($AutoFilter) {
    $sheet.UsedRange.EntireColumn.AutoFilter()
    }
    $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()
    Sleep -s 3
    Stop-Process -Name “Excel”
    }
    }

  15. Pingback: Troubleshooting SQL Server with PowerShell–Gathering Performance Counters | $hell Your Experience !!! #PowerShellLifeStyle

  16. Hi Luc,

    I’ve been using your script quite a lot but it lacked 2 features I really missed in it: AutoFilter and FreezePanes. Those 2 make the output a whole lot better. Today I really got tired of not having them in it.. so I pulled up my sleeves and did it myself :)

    I added 3 new parameters:
    * $AutoFilter (wheter or not to apply an autofilter action on the top row)
    * $SplitRow (the row te split, default is top row)
    * $SpiltColumn (the column to split, default is none)

    Enjoy

    #requires -version 2

    function Export-Xls{
    $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”
    .EXAMPLE
    PS> $data = Get-Process | Select-Object Name, Id, WS
    PS> Export-Xls $data C:\Reports\MyWkb.xls -WorksheetName “WS” -ChartType “xlColumnClustered” -AutoFilter:$true -SplitRow 1 -SplitColumn 1
    #>
    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,
    [switch]$AutoFilter = $true,
    [int]$SplitRow = 1,
    [int]$SplitColumn = 0
    )

    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
    }
    }

    [System.Threading.Thread]::CurrentThread.CurrentCulture = “en-US”
    $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))
    }
    }

    if (($SplitRow -gt 0) -or ($SplitColumn -gt 0)) {
    $excelApp.ActiveWindow.SplitRow = $SplitRow
    $excelApp.ActiveWindow.SplitColumn = $SplitColumn
    $excelApp.ActiveWindow.FreezePanes = $true
    }

    $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

    if ($AutoFilter) {
    $sheet.UsedRange.EntireColumn.AutoFilter()
    }
    $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”
    }
    }

  17. hi LucD,
    I am new to Powershell, so this function is amazing for a person like me.

    I am collecting stats for my Db and putting them in multiple CSV files( say 2 CSV files). From the CSV files, i am exporting them to a single Excel sheet, so I primarily have a loop

    $results += Invoke-Sqlcmd -InputFile $SqlScriptFile -ServerInstance $InstanceName -Database $DatabaseName

    $results | export-csv $csvFilePath -NoTypeInformation

    if(!(Test-path -Path $excelFilePath))
    {

    Export-Xls -InputObject $results -Path $excelFilePath -WorksheetName $DatabaseName -SheetPosition “end” -AppendWorksheet:$false

    }

    else
    {

    Export-Xls -InputObject $results -Path $excelFilePath -WorksheetName $DatabaseName -SheetPosition “end”

    }

    I used the above Export-Xls function to translate multiple CSV files to a single Excel file.

    What I am seeing is that the Excel sheet gets created, the multiple csv files gets coalesced to a single Execl sheet. However no data is ever written to the execl sheet.

    I was wondering if you could please guide me.

    Thanks,
    ADu

  18. when field is numeric and is 0 (zero) the if(!$Row.$Name){$Row.$Name = “”} (line “100”).
    I replaced it with if($Row.$Name.Length -eq 0){$Row.$Name = “”} and allready OK ;)

  19. @LucD
    very great :)
    But my Powershell can’t load [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.Office.Interop.Excel”)
    :(
    I have Excel 2003 and Win XP :p.
    But this is only for charts, really? My work is chartsless :)
    thx.

    • Hi Martin, your assumption is correct, that DLL is only needed when using charts. If you change line 60-63 into

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

      it should work with your Office version.

  20. Pingback: Visualizing and Automating SQL Server health with PowerShell and Excel | $hell Your Experience !!! #PowerShellLifeStyle

  21. Pingback: PowerShell Script: Export-ExcelSuperTemple

  22. Hi Luc

    Thanks for a great function, I’ve been using this for a while now and
    it works great.
    One question though.
    When I have more columns than rows in my output, the chart (“xlColumnClustered”) gets inverted.
    Is there a way to control this in your function to always get the same view in the chart?

    This is an example of a variable from my script, and where I get less datastores than columns the chart gets inverted:

    $Global:EXAMPLE = Get-View -ViewType Datastore | Where-Object {$_.Parent -match “Folder-group-sSomething”}| `
    Select-Object -Property @{N=”Disk Name”; E={$_.Name}},
    @{N=”TotalSpaceGB”; E={[Math]::Round($_.Summary.Capacity/1GB,0)}},
    @{N=”UsedSpaceGB”;E={[Math]::Round(($_.Summary.Capacity – $_.Summary.FreeSpace)/1GB,0)}},
    @{N=”ProvisionedSpaceGB”;E={[Math]::Round(($_.Summary.Capacity – $_.Summary.FreeSpace + $_.Summary.Uncommitted)/1GB,0)}} |
    Sort-Object -Property “TotalSpaceGB”

    Best Regards

    Mathias

  23. Hi Luc,
    This is really one of the most useful functions ever!!!
    One question though,
    How can I modify the size of the graph? Or have it show in a different worksheet (as a larger size)?

  24. Hi Luc,

    for some reason this isn’t working. The command .\Export-XLS $data -Path c:\temp\test.xls doesn’t generate a file at all. Nor does it give any feedback or error.

    I tried this on Windows 7 x86 with an English Office 2010 installation (but with some language packs installed on it).

    Can you give any advise on this? Since this function really would be GREAT :)

    Kind regards

    Walter

    • Hi Walter, from the sample line you give, I guess you placed the script in a .ps1 file called Export-Xls.ps1 ?
      If that is the case, you will first have to dot-source the file, so that the PowerShell knows the function.

      . .\Export-Xls.ps1

      Note the blank between the 2 dots !
      You should now see the function when you do

      Get-Command -CommandType Function -Name Export-Xls

      Now you can use the function as any other cmdlet.

      Export-XLS -InputObject $data -Path c:\temp\test.xls

    • Hi Jan, thanks for the link.
      In fact at one point I did something similar, a double foreach loop over all the rows and columns. But the problem with that solution is that it is very slow. That’s why I used the clipboard solution, which is much faster imho.

  25. @LucD
    Actually, that didn’t work as I planned, it just killed the lowest numbered Excel PID. I had to do this:

    $pre = get-process -Name EXCEL
    $excelApp = New-Object -ComObject “Excel.Application”
    $post = get-process -Name EXCEL
    Foreach ($excelproc in $post) {
    $test = 0
    Foreach ($pred in $pre) {
    if ($pred.Id -eq $excelproc.Id) {$test++}
    }
    if ($test -eq 0) {break}
    }

    I’m sure there might be a simpler way. But this one actually works.

  26. I modified this a bit to only close the process that it created by including this where you create the Excel process (line 109)

    $pre = get-process -Name EXCEL
    $excelApp = New-Object -ComObject “Excel.Application”
    $post = get-process -Name EXCEL
    Foreach ($exlproc in $post) {
    if ($pre -notcontains $exlproc) { break }
    }
    Remove-Variable pre
    Remove-Variable post

    And this in the place of stop-process:

    $exlproc.kill()

    Doesn’t force close my current work :)

  27. one other remark: when saving the files as .xls I get the error “the file (…) is in a different format than specified by the file extension. (…)” when opening the file. When saving as .xlsx this doesn’t occur. So mind the X :)

  28. hi Luc

    great script! But unfortunately it expects your office environment to be in English while mine is Dutch… I worked around it by adding this line
    [System.Threading.Thread]::CurrentThread.CurrentCulture = “en-US”
    right above
    $excelApp = New-Object -ComObject “Excel.Application”
    but ideally it shouldn’t matter what language your Office (or Windows) environment is.

  29. Hi there,

    big thanks for the function, it’s great and saves some time converting the CSVs to Excel sheets. But I have one problem: I work with arrays in one script and I plan to use them more often after I found out how easy they are to use. The problem with Export-Xls is, that the columns are now sorted in alphabetical order and not the way I defined them.

    If you need a script to test with, I can mail it to you, just tell me where to.

    2nd, I use Excel 2010 on my computer so I have to change the file extension to xlsx (maybe a warning by the script if version is 2007/12 or above?) and Excel is telling me that it found a previous version of the document. I fixed this error by adding a “sleep -s 3″ into the script right before you kill the process in line 158 to let Excel actually quit.

  30. Hi Luc,

    I’m getting the below given error when i try to use this function to export a xls file.

    Unable to find type [microsoft.Office.Interop.Excel.XlHAlign]: make sure that the assembly containing this type is loaded.
    At :line:101 char:82
    + $Sheet.UsedRange.HorizontalAlignment = [microsoft.Office.Interop.Excel.XlHAlign] <<<< ::xlHAlignCenter

    Already @jfrmilner has mentioned this but he has used it for chart i think. But i'm not using chart and just trying to export to xls file and getting this error. I'm running on W2K3 & Excel2007 machine. Please help.

    • @Harry, I think the change is mentioned in @jfrmilner’s reply.
      He changed line 62 from

      [microsoft.Office.Interop.Excel.XlChartType]$ChartType = $ChartType

      to

      $chartType = “microsoft.office.interop.excel.xlChartType” -as [type]

      Unfortunately I don’t have an Office 2007 version handy to test this right now.

  31. Hi Luc,

    I am getting the following error when executing Export-Xls $data C:\MyWkb.xls -AppendWorksheet:$false -WorksheetName “WS” -ChartType “xlLine”

    ##################################################

    Method invocation failed because [System.__ComObject] doesn’t contain a method named ‘AddChart’.
    At C:\Users\d40229g\desktop\Harish\export-xls.ps1:153 char:26
    + $sheet.Shapes.AddChart <<<< ($ChartType) | Out-Null
    + CategoryInfo : InvalidOperation: (AddChart:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

    • @Harish, which Office version do you have installed on the PC where you run the script ? This error typically appears with some older version of Office.

  32. Hi Luc. i’m using your great piece of script to easily create a graph from PowerCLI data but it is possible to fixe the abscissa reference because in your process/workingset example if you replace N=”WS” by N=”AWS” the first column is not “name” anymore and the graphic is mixed up ?

    • Hi NiTRo, the problem is caused by the fact that the Get-Member cmdlet, which the script uses to get the column headers, always returns them in alphabetical order.
      At the moment I have no clue how the order of properties on the Select-Object cmdlet can be retrieved.
      The -Property parameter of the New-Object cmdlet has a similar “problem”. You have no control over the order of the properties.
      Let me think if I can come up with a bypass.

      • Just wondering if there was ever a solution to the problem where the excel paste was done in alphabetical order?

  33. Pingback: How to Get Data into an Excel Spreadsheet Very Quickly with PowerShell – Cont’d « Tome's Land of IT

    • @Harish. There are many ways of doing that (aren’t there always in PowerShell).
      You can store the function in one of your profiles, include in the script file, dot-source it from the command prompt…
      And then you can call the function/cmdlet in your other statements.
      See some examples at the bottom of the post.

  34. Any thoughts on how to tweak the script to work with Office 2003 – shy of upgrading to office 2010?

  35. This is really excellent; I really like the Chart option.

    I got a minor issue (Win7 – Office 2007) with “Unable to find type [microsoft.Office.Interop.Excel.XlChartType]: make sure that the assembly containing this type is loaded.”
    So i changed that line to this:
    $chartType = “microsoft.office.interop.excel.xlChartType” -as [type]

  36. @LucD
    No its v1

    V2 wont install for some reason …

    When i run WindowsXP-KB968930-x86-ENG
    it fails with a message : Access is Denied

    Current config :
    Win Xp sp3
    Domain account with Local admin rights
    IE 7

  37. Export-Xls

    When i run the scrip it Displays the error below
    Missing closing ‘)’ in expression.
    At :line:45 char:2
    + [ <<<< ValidateNotNullOrEmpty()]

    some thing i need to do ?

  38. Hi Luc,

    Thanks for this great function. I only needed to change my Region and Language format from Dutch to English to get it working.

    Have to figure out how I can have it work with my regional settings though.

    Regards,
    Stefan Stranger

  39. Thanks Luc, this is a “real” value function for people who want a combine output’s of multiple related scripts into a single excel sheet with separate worksheet for each script or category.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>