导出到excel的脚本 [英] Script to export to excel

问题描述

我有以下脚本:-- 寻求帮助将输出转换为 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

我的要求是我想将此输出转换为 excel 格式以提交给管理层.以下是我正在考虑的 excel 格式...从上面的脚本中...

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

推荐答案

我已经重写了这个，但我没有测试完整的脚本，它也没有优化.如果您遇到任何有问题，随时联系我.

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

这篇关于导出到excel的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！