Script to export to excel

问题:

I Have below script:-- looking for help to convert the output to excel format

$servers = get-content “c:\list.txt”
foreach ($server in $servers)
{
$server
$command = “quser /server:” + $server
invoke-expression $command 
}

when executed getting in below format the output.

server1
 USERNAME              SESSIONNAME        ID  STATE   IDLE TIME  LOGON TIME
 Vdw231                ica-tcp#8           7  Active          .  11/5/2012 10:40 AM
 Vdw232                ica-tcp#60         16  Active      16:18  11/5/2012 2:22 PM
 Vdw233                ica-tcp#71          3  Active          .  11/6/2012 6:10 AM
 Vdw234                ica-tcp#72          1  Active          3  11/6/2012 6:59 AM
 Vdw235                ica-tcp#73          5  Active          .  11/6/2012 6:59 AM
 Vdw236                rdp-tcp#74          2  Active          .  11/6/2012 7:07 AM
server2
 USERNAME              SESSIONNAME        ID  STATE   IDLE TIME  LOGON TIME
 Vdw210                ica-tcp#44         14  Active      13:50  11/5/2012 9:03 AM
 Vdw211                ica-tcp#67          6  Active          .  11/6/2012 1:56 AM
 Vdw212                ica-tcp#70          1  Active         45  11/6/2012 6:34 AM
 Vdw213                ica-tcp#72          9  Active         25  11/6/2012 6:53 AM
 Vdw214  
server3
 USERNAME              SESSIONNAME        ID  STATE   IDLE TIME  LOGON TIME
 Vdw215                rdp-tcp#131         1  Active         19  11/5/2012 1:42 AM
 Vdw216                rdp-tcp#132         4  Active         17  11/5/2012 2:06 AM
 Vdw217                rdp-tcp#143         6  Active          .  11/6/2012 3:31 AM

My requirement is i wanted to convert this output to excel format for submitting to management. Below is the excel format that i am thinking...to have from above script...

回答1:

I've rewritten this, but I didn't test the full script and it's not optimized. If you encounter any problems, feel free to contact me.

    $statuses = @()
    $servers = get-content "c:\list.txt"
    $splitter = [regex]"\s+"

    foreach ($server in $servers)
    {
        $command = "quser /server:$server" 
        $lines = @((invoke-expression $command | Out-String) -split "`n")
        #remove header
        $lines = $lines[1..$lines.count]
        foreach ($line in $lines)
        {
            $attrs = @($splitter.Split($line.Trim(),6))
            if ( $attrs -eq 6 )
            {
                $status = New-Object PSCustomObject -Property @{
                "SERVER"=$server;
                "USERNAME"=$attrs[0];
                "SESSIONNAME"=$attrs[1];
                "ID"=$attrs[2];
                "STATE"=$attrs[3];
                "IDLE_TIME"=$attrs[4];
                "LOGON_TIME"=[datetime]$attrs[5]}

                $statuses += $status
            }
        }
    }

    #your filter here
    #$statuses = $statuses | where{ XXXXX }

    $statuses | Export-Csv G:/test.csv -NoTypeInformation

回答2:

You need to convert PSObject to an excel compatible Array and after you can write this array in excel sheet

include this code in your *.PS1 script, and use like this : get-process | Export-Excel

#=============================================================================
# Convert powershell Object to Array for Excel
#=============================================================================
function ConvertTo-MultiArray {
 <#
    .Notes
        NAME: ConvertTo-MultiArray
        AUTHOR: Tome Tanasovski
        Website: http://powertoe.wordpress.com
        Twitter: http://twitter.com/toenuff
        Version: 1.2
    .Synopsis
        Converts a collection of PowerShell objects into a multi-dimensional array

    .Description
        Converts a collection of PowerShell objects into a multi-dimensional array.  The first row of the array contains the property names.  Each additional row contains the values for each object.

        This cmdlet was created to act as an intermediary to importing PowerShell objects into a range of cells in Exchange.  By using a multi-dimensional array you can greatly speed up the process of adding data to Excel through the Excel COM objects.

    .Parameter InputObject
        Specifies the objects to export into the multi dimensional array.  Enter a variable that contains the objects or type a command or expression that gets the objects. You can also pipe objects to ConvertTo-MultiArray.

    .Inputs
        System.Management.Automation.PSObject
        You can pipe any .NET Framework object to ConvertTo-MultiArray

    .Outputs
        [ref]
        The cmdlet will return a reference to the multi-dimensional array.  To access the array itself you will need to use the Value property of the reference

    .Example
        $arrayref = get-process |Convertto-MultiArray

    .Example
        $dir = Get-ChildItem c:\
        $arrayref = Convertto-MultiArray -InputObject $dir

    .Example
        $range.value2 = (ConvertTo-MultiArray (get-process)).value

    .LINK
        http://powertoe.wordpress.com

#>
    param(
        [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true)]
        [PSObject[]]$InputObject
    )
    BEGIN {
        $objects = @()
        [ref]$array = [ref]$null
    }
    Process {
        $objects += $InputObject
    }
    END {
        $properties = $objects[0].psobject.properties |%{$_.name}
        $array.Value = New-Object 'object[,]' ($objects.Count+1),$properties.count
        # i = row and j = column
        $j = 0
        $properties |%{
            $array.Value[0,$j] = $_.tostring()
            $j++
        }
        $i = 1
        $objects |% {
            $item = $_
            $j = 0
            $properties | % {
                if ($item.($_) -eq $null) {
                    $array.value[$i,$j] = ""
                }
                else {
                    $array.value[$i,$j] = $item.($_).tostring()
                }
                $j++
            }
            $i++
        }
        $array
    }
}
    #=============================================================================
    # Export pipe in Excel file
    #=============================================================================
    function Export-Excel {
        [cmdletBinding()]
        Param(
            [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true)]
            [PSObject[]]$InputObject
        )
        begin{
            $header=$null
            $row=1
            $xl=New-Object -ComObject Excel.Application
            $wb=$xl.WorkBooks.add(1)
            $ws=$wb.WorkSheets.item(1)
            $xl.Visible=$false
            $xl.DisplayAlerts = $false
            $xl.ScreenUpdating = $False
            $objects = @()

            }
        process{
            $objects += $InputObject

        }
        end{
            $array4XL = ($objects | ConvertTo-MultiArray).value

            $starta = [int][char]'a' - 1
            if ($array4XL.GetLength(1) -gt 26) {
                $col = [char]([int][math]::Floor($array4XL.GetLength(1)/26) + $starta) + [char](($array4XL.GetLength(1)%26) + $Starta)
            } else {
                $col = [char]($array4XL.GetLength(1) + $starta)
            }
            $ws.Range("a1","$col$($array4XL.GetLength(0))").value2=$array4XL

            $wb.SaveAs("$([Environment]::GetFolderPath('desktop'))\Export-Excel ($(Get-Date -Format u)).xlsx")
            $xl.Quit()
            Remove-Variable xl
        }
    }

you get