PowerShell调用命令导出到Excel

Question

PowerShell调用命令导出到Excel

excelpowershellexport-to-excelinvoke-command

3

这段PowerShell代码有一些问题：

$vmslist = Import-Csv -Path .\vmsList.csv -Delimiter ',' | ForEach-Object {
    $_.Servers
    $vms = [string]$env:COMPUTERNAME
    $currentVms = $_.App_Central
    function Check-DiskSpace {
        $results = Invoke-Command -ComputerName $currentVms -Credential $Cred -ScriptBlock {
            $driveDetails = Get-PSDrive | Where-Object { $_.Name -eq 'D' } | ForEach-Object {
                $totalSpace = $_.Free + $_.Used
                $name = $_.Root
                $vms = $env:COMPUTERNAME

                $dDriveFreeSpace = [int]('{0:N2}' -f ($_.Free / 1Gb))
                $dDriveUsedSpace = [int]('{0:N2}' -f ($_.Used / 1Gb))
                $dDriveTotalSpace = [int]('{0:N2}' -f ($dDriveFreeSpace + $dDriveUsedSpace))

                $freePercent = '{0:N1}' -f ( ($dDriveFreeSpace / $dDriveTotalSpace) * 100)
                $usedPercent = '{0:N1}' -f ( ($dDriveUsedSpace / $dDriveTotalSpace) * 100)
                Write-Host 'Used % of' $_.Root "is $usedPercent%"
                Write-Host 'Free % of' $_.Root "is $freePercent%"
            }


            if($freePercent -le '30') {
                $Global:issue = 'Nearly Full!'
            }
            if ($freePercent -eq '0.0') {
                $Global:issue = 'WARNING - FULL CLEAR SPACE NOW!'
            }
            if($freePercent -gt '30') {
                $Global:issue = 'No Issues'
            }

            Write-Output "$env:COMPUTERNAME, Drive $name, $usedPercent% Full, $Global:issue"
            $Global:test3 = @()
            $Global:test3 += [PSCustomObject]@{
                ComputerName = "$env:COMPUTERNAME"
                Drive        = "$name" 
                UsedPercent  = "$usedPercent%"
                Comments     = "$Global:issue"
            }

            $test3 | Format-Table

        }
        Export-Excel -Path 'C:\temp\ProcessData.xlsx' -InputObject $results -AutoSize -AutoFilter -Append
        return $results
    }
}

大家好，

我在导出到Excel时遇到了问题。我想要将$env:COMPUTERNAME、驱动器 $name、$usedPercent% Full、$Global:issue 作为标题与从每个调用命令中提取的数据一起导出。不幸的是，我只能在Excel中得到这个：

然而，当我使用OutFile命令将文本输出到磁盘检查.txt文件时，我得到了以下内容：

这就是我希望数据在我的Excel文件中呈现的方式。

- dcwilli5

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Santiago Squarzon · Accepted Answer

你的代码比它应该的要复杂得多，你可以按照以下方式简化它。只需注意，Invoke-Command 可以并行运行远程调用，默认情况下为 32 个线程，可以使用其 -ThrottleLimit 参数进行增加。

$vms = Import-Csv -Path .\vmsList.csv -Delimiter ','

# run this code in all VMs in parallel (32 threads by default)
Invoke-Command -ComputerName $vms.App_Central -Credential $Cred -ScriptBlock {
    $driveDetails = Get-PSDrive -Name D -ErrorAction SilentlyContinue

    if(-not $driveDetails) {
        Write-Host "No D drive found on $env:ComputerName"
        return
    }

    $totalSpace = $driveDetails.Free + $driveDetails.Used
    $freeSpace = $driveDetails.Free / $totalSpace
    $usedSpace = $driveDetails.Used / $totalSpace

    Write-Host ('Used % of {0} in {1} is {2:P0}' -f $driveDetails.Root, $env:ComputerName, $usedSpace)
    Write-Host ('Free % of {0} in {1} is {2:P0}' -f $driveDetails.Root, $env:ComputerName, $freeSpace)

    # lets assume it is gt than 0.3 (30%)
    $issue = 'No Issues'

    if($freeSpace -le 0.3) {
        $issue = 'Nearly Full!'
    }
    elseif($freeSpace -eq 0.0) {
        $issue = 'WARNING - FULL CLEAR SPACE NOW!'
    }

    [PSCustomObject]@{
        ComputerName = $env:ComputerName
        Drive        = $driveDetails.Root
        UsedPercent  = '{0:P0}' -f $usedSpace
        Comments     = $issue
    }
} | Export-Excel -Path 'C:\temp\ProcessData.xlsx' -AutoSize -AutoFilter