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

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.

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.

Now we only have the worksheet we just created.

You can use the function in a pipeline

This gives

And you can specify that the new worksheet should be at the 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.

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

Some sample runs

Produces

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

And this produces

65 Comments

    lewis

    Hi Luc,
    this looks exactly what I want, but I cant get it to work 🙁

    $inactiveUsers1 = Get-QADUser -SizeLimit 0 -SearchRoot $sourceOu1 -NotLoggedOnFor $InactiveFor -Enabled | Where-Object {$_.description -notlike $DescriptionPrefix}

    Export-Xls $inactiveUsers1 -Path c:\scripts\abc.xls

    this throws up errors on every line similar to this one
    “PrimaryX400Address” is a ReadOnly property.

    + CategoryInfo : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

    Pardha

    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.

    Jason

    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.

    Stephen

    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.

    Jens

    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?

    MD

    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)

      LucD

      @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 ?

    Steve

    @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.

      LucD

      The new function is online, see Export-Xlsx, the sequel, and ordered data.
      I hope it does what you want.

    Steve

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

    Steve

    @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???

      LucD

      @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 🙂

    Steve

    @Jules , I would certainly be interested in seeing your work.

    Thanks for your input!

    Steve Gott

    Jules

    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.

      LucD

      @Jules, great.
      Feel free to send the link to your version.

    Steve

    @Steve
    Thanks much for your help! This is a fantastic script!!!

    Steve

    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?

      LucD

      @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.

    alexis

    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:WindowsassemblyGAC_MSILMicrosoft.Office.Interop.Excel14.0.0.0__71e9bce111e9429cMicrosoft.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?

      LucD

      @Alexis, which Office version are you using ?

    Johnnie Simpers

    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.

      LucD

      Sure, feel free to use the function.

    Walter

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

    Walter

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

    ADu

    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

    Martin

    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 😉

      LucD

      @Martin, great find. I’ll see if I can incorporate that in the function.

    Martin

    @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.

      LucD

      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.

    MB74

    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

    Zach

    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)?

    Walter

    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

      LucD

      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

    Jan Egil Ring

    @LucD
    Hi Luc,

    One workaround is the use of ConvertTo-XML as Jeremy Engel did in his ImportExportExcel module:
    https://gallery.technet.microsoft.com/scriptcenter/7b2fdc8f-a528-4f19-b9ef-f5af349dc906

      LucD

      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.

    Dedrick427

    @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.

      LucD

      Thanks for the feedback Dedrick, I’ll update the script.

    Dedrick427

    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 🙂

      LucD

      That is a very handy improvement.
      I’ll update my function accordingly.

    Walter

    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 🙂

    Walter

    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.

    meilon

    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.

    Suresh

    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

    @jfrmilner

    Hi Can you pls, let us knw wat exact changes were made for office 2007, or attached the working script itself.

    Thanks.

      LucD

      @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.

    Harish

    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

      LucD

      @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.

    Harry

    Does this work on office 2003. If not any chances to modify the script to do so ??

      LucD

      @Harry, I don’t have an Office 2003 to test but I’m afraid it will not work.
      I’ll try to find out.

    NiTRo

    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 ?

      LucD

      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.

        Michael Brux

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

    Harish

    How do I get this function to work as a cmdlet?

      LucD

      @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.

    Donn

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

    jfrmilner

    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]

      LucD

      Thanks. I could only test with Office 2010.
      Apparently it doesn’t work at all with Office XP 😉

    Pcli

    @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

      LucD

      @Pcli, this function was written for PowerShell v2.
      You could try out some of the suggestions in KB968930 fails at installation with Automatic Updates

    Pcli

    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 ?

      LucD

      Are you using PowerShell v2 ?

    Stefan Stranger

    Hi Luc,

    Got it working with the Using-Culture function from Jeffrey. https://blogs.msdn.com/b/powershell/archive/2006/04/25/583235.aspx

    using-culture en-US {get-process | select Name, Id, WS | export-xls -path c:\temp\culture.xls}

    Regards,
    Stefan Stranger

    Stefan Stranger

    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

      LucD

      Hi Stefan,
      You could try adding the -UseCulture parameter on the Export-Csv cmdlet in the script.
      Luc.

    Suresh

    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 *

*
*

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