PowerShell遍历服务器生成Excel工作表异常：仅获取单台服务器单设备

问题分析 #

原脚本存在两个核心问题：

• Excel对象重复创建：每次循环都新建Excel实例和工作簿，导致仅最后一台服务器的数据被保存，之前的服务器数据全部丢失。
• 数据填充逻辑错误：直接将打印机集合赋值给单个单元格，且$counter的递增逻辑无法批量填充多台打印机的数据，仅能写入一条记录。

修复遍历与Excel导出的脚本 #

以下脚本会遍历所有服务器，将每台服务器的打印机数据存入同一个Excel文件的独立工作表：

Clear-Host

# 读取服务器列表
$sites = Get-Content -Path "User\user$\user\Documents\Working Folder\2132023\test.txt"

# 初始化Excel对象（仅创建一次）
$ExcelObj = New-Object -ComObject Excel.Application
$ExcelObj.Visible = $true
$ExcelWorkBook = $ExcelObj.Workbooks.Add()

foreach ($site in $sites) {
    try {
        # 获取当前服务器的打印机信息
        $printers = Get-Printer -ComputerName $site -ErrorAction Stop | Select-Object Name, DriverName, PortName, ShareName
        
        # 添加新工作表并重命名
        $ExcelWorkSheet = $ExcelWorkBook.Worksheets.Add()
        $ExcelWorkSheet.Name = $site

        # 设置表头
        $headers = @('Device Name', 'Driver Name', 'Port Name', 'Share Name')
        for ($col = 0; $col -lt $headers.Count; $col++) {
            $ExcelWorkSheet.Cells.Item(1, $col + 1) = $headers[$col]
        }

        # 格式化表头
        $ExcelWorkSheet.Rows.Item(1).Font.Bold = $true
        $ExcelWorkSheet.Rows.Item(1).Font.Size = 15
        $ExcelWorkSheet.Columns.Item("A:D").ColumnWidth = 28

        # 填充打印机数据（从第2行开始）
        $row = 2
        foreach ($printer in $printers) {
            $ExcelWorkSheet.Cells.Item($row, 1) = $printer.Name
            $ExcelWorkSheet.Cells.Item($row, 2) = $printer.DriverName
            $ExcelWorkSheet.Cells.Item($row, 3) = $printer.PortName
            $ExcelWorkSheet.Cells.Item($row, 4) = $printer.ShareName
            $row++
        }
    }
    catch {
        Write-Warning "无法连接服务器 $site : $_"
        # 创建空工作表标记错误
        $ExcelWorkSheet = $ExcelWorkBook.Worksheets.Add()
        $ExcelWorkSheet.Name = "$site (连接失败)"
        $ExcelWorkSheet.Cells.Item(1,1) = "错误信息: $_"
    }
}

# 删除默认的空白工作表（如果存在）
if ($ExcelWorkBook.Worksheets.Name -contains 'Sheet1') {
    $ExcelWorkBook.Worksheets.Item('Sheet1').Delete()
}

# 保存并关闭Excel
$savePath = "\User\User\Documents\Working Folder\2132023\test.xlsx"
$ExcelWorkBook.SaveAs($savePath)
$ExcelWorkBook.Close($true)
$ExcelObj.Quit()

# 释放COM对象（避免进程残留）
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelWorkSheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelWorkBook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelObj) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

添加数据合并与服务器差异对比 #

在上述脚本基础上，添加以下逻辑，将所有服务器的打印机数据合并到一个工作表，并生成差异对比表：

# ---------- 合并所有数据到新工作表 ----------
$mergedSheet = $ExcelWorkBook.Worksheets.Add()
$mergedSheet.Name = "合并数据"

# 设置合并表表头（增加服务器列）
$mergedHeaders = @('服务器名称', 'Device Name', 'Driver Name', 'Port Name', 'Share Name')
for ($col = 0; $col -lt $mergedHeaders.Count; $col++) {
    $mergedSheet.Cells.Item(1, $col + 1) = $mergedHeaders[$col]
}
$mergedSheet.Rows.Item(1).Font.Bold = $true
$mergedSheet.Rows.Item(1).Font.Size = 15
$mergedSheet.Columns.Item("A:E").ColumnWidth = 28

# 遍历所有服务器工作表，填充合并数据
$mergedRow = 2
foreach ($site in $sites) {
    if ($ExcelWorkBook.Worksheets.Name -contains $site) {
        $sheet = $ExcelWorkBook.Worksheets.Item($site)
        $lastRow = $sheet.Cells.Item($sheet.Rows.Count, 1).End(-4162).Row # xlUp = -4162
        if ($lastRow -ge 2) {
            for ($row = 2; $row -le $lastRow; $row++) {
                $mergedSheet.Cells.Item($mergedRow, 1) = $site
                $mergedSheet.Cells.Item($mergedRow, 2) = $sheet.Cells.Item($row, 1).Value2
                $mergedSheet.Cells.Item($mergedRow, 3) = $sheet.Cells.Item($row, 2).Value2
                $mergedSheet.Cells.Item($mergedRow, 4) = $sheet.Cells.Item($row, 3).Value2
                $mergedSheet.Cells.Item($mergedRow, 5) = $sheet.Cells.Item($row, 4).Value2
                $mergedRow++
            }
        }
    }
}

# ---------- 生成服务器差异对比表 ----------
$diffSheet = $ExcelWorkBook.Worksheets.Add()
$diffSheet.Name = "差异对比"

# 设置对比表头
$diffHeaders = @('差异类型', '服务器1', '服务器2', 'Device Name', '差异字段', '服务器1值', '服务器2值')
for ($col = 0; $col -lt $diffHeaders.Count; $col++) {
    $diffSheet.Cells.Item(1, $col + 1) = $diffHeaders[$col]
}
$diffSheet.Rows.Item(1).Font.Bold = $true
$diffSheet.Rows.Item(1).Font.Size = 15
$diffSheet.Columns.Item("A:G").ColumnWidth = 28

# 收集所有服务器的打印机数据到哈希表，键为打印机名称，值为服务器-属性的映射
$printerData = @{}
foreach ($site in $sites) {
    if ($ExcelWorkBook.Worksheets.Name -contains $site) {
        $sheet = $ExcelWorkBook.Worksheets.Item($site)
        $lastRow = $sheet.Cells.Item($sheet.Rows.Count, 1).End(-4162).Row
        if ($lastRow -ge 2) {
            for ($row = 2; $row -le $lastRow; $row++) {
                $printerName = $sheet.Cells.Item($row, 1).Value2
                if (-not $printerData.ContainsKey($printerName)) {
                    $printerData[$printerName] = @{}
                }
                $printerData[$printerName][$site] = [PSCustomObject]@{
                    DriverName = $sheet.Cells.Item($row, 2).Value2
                    PortName   = $sheet.Cells.Item($row, 3).Value2
                    ShareName  = $sheet.Cells.Item($row, 4).Value2
                }
            }
        }
    }
}

# 对比不同服务器的打印机差异
$diffRow = 2
foreach ($printerName in $printerData.Keys) {
    $servers = $printerData[$printerName].Keys
    if ($servers.Count -gt 1) {
        # 对比同一打印机在不同服务器的属性差异
        $serverList = $servers | Sort-Object
        for ($i = 0; $i -lt $serverList.Count; $i++) {
            for ($j = $i + 1; $j -lt $serverList.Count; $j++) {
                $s1 = $serverList[$i]
                $s2 = $serverList[$j]
                $p1 = $printerData[$printerName][$s1]
                $p2 = $printerData[$printerName][$s2]

                # 对比每个属性
                if ($p1.DriverName -ne $p2.DriverName) {
                    $diffSheet.Cells.Item($diffRow, 1) = "属性差异"
                    $diffSheet.Cells.Item($diffRow, 2) = $s1
                    $diffSheet.Cells.Item($diffRow, 3) = $s2
                    $diffSheet.Cells.Item($diffRow, 4) = $printerName
                    $diffSheet.Cells.Item($diffRow, 5) = "Driver Name"
                    $diffSheet.Cells.Item($diffRow, 6) = $p1.DriverName
                    $diffSheet.Cells.Item($diffRow, 7) = $p2.DriverName
                    $diffRow++
                }
                if ($p1.PortName -ne $p2.PortName) {
                    $diffSheet.Cells.Item($diffRow, 1) = "属性差异"
                    $diffSheet.Cells.Item($diffRow, 2) = $s1
                    $diffSheet.Cells.Item($diffRow, 3) = $s2
                    $diffSheet.Cells.Item($diffRow, 4) = $printerName
                    $diffSheet.Cells.Item($diffRow, 5) = "Port Name"
                    $diffSheet.Cells.Item($diffRow, 6) = $p1.PortName
                    $diffSheet.Cells.Item($diffRow, 7) = $p2.PortName
                    $diffRow++
                }
                if ($p1.ShareName -ne $p2.ShareName) {
                    $diffSheet.Cells.Item($diffRow, 1) = "属性差异"
                    $diffSheet.Cells.Item($diffRow, 2) = $s1
                    $diffSheet.Cells.Item($diffRow, 3) = $s2
                    $diffSheet.Cells.Item($diffRow, 4) = $printerName
                    $diffSheet.Cells.Item($diffRow, 5) = "Share Name"
                    $diffSheet.Cells.Item($diffRow, 6) = $p1.ShareName
                    $diffSheet.Cells.Item($diffRow, 7) = $p2.ShareName
                    $diffRow++
                }
            }
        }
    } else {
        # 仅在单个服务器存在的打印机
        $server = $servers[0]
        $diffSheet.Cells.Item($diffRow, 1) = "仅存在于单服务器"
        $diffSheet.Cells.Item($diffRow, 2) = $server
        $diffSheet.Cells.Item($diffRow, 3) = "-"
        $diffSheet.Cells.Item($diffRow, 4) = $printerName
        $diffSheet.Cells.Item($diffRow, 5) = "-"
        $diffSheet.Cells.Item($diffRow, 6) = "-"
        $diffSheet.Cells.Item($diffRow, 7) = "-"
        $diffRow++
    }
}

# 调整工作表顺序（可选）
$mergedSheet.Move($ExcelWorkBook.Worksheets.Item(1))
$diffSheet.Move($mergedSheet.Next())

关键优化点 #

• Excel对象复用：将Excel实例和工作簿的创建移到循环外，确保所有服务器数据存入同一个文件。
• 批量数据填充：遍历每台打印机，逐行写入Excel，解决单条记录的问题。
• 错误处理：添加try/catch捕获服务器连接失败的情况，生成标记工作表。
• COM对象释放：脚本末尾释放Excel COM对象，避免后台残留Excel进程。
• 数据合并与差异对比：新增合并工作表汇总所有数据，差异表展示打印机在不同服务器的存在性和属性差异。

内容的提问来源于stack exchange，提问作者Slyons