如何用PowerShell向受保护.xlsm文件追加数据及相关疑问

问题解答 #

1. 可以向现有.xlsm文件追加数据，但不能用Out-File/Export-CSV这类文本处理命令——因为xlsm是二进制格式的Excel文件，不是纯文本CSV，这类命令会破坏文件结构导致无法打开，必须用专门操作Excel的方法实现。
2. 无论是用PowerShell的Excel COM对象，还是第三方模块（如ImportExcel），都能指定要更新的工作表，具体实现方式见下文示例。
3. 带工作表保护时，默认情况下Excel合并（或追加数据）操作不可用，因为保护会禁止修改单元格内容、插入行等操作。需要先解除工作表保护，完成数据追加后再重新启用保护；如果保护时勾选了允许“插入行”“编辑区域”等权限，也可以直接操作，但这种情况很少见。

解决方案 #

方案一：使用ImportExcel模块（推荐，语法简洁） #

ImportExcel是PowerShell生态中专门处理Excel的第三方模块，支持xlsm格式，能直接追加数据并指定工作表。

1. 安装模块（仅需执行一次）

Install-Module -Name ImportExcel -Scope CurrentUser -Force

2. 修改原脚本的输出部分

替换原脚本中最后一行的Out-File命令为以下代码：

# 定义目标文件和工作表
$excelPath = "C:\Users\user\Documents\20221111\Netscaler VIPs per Cluster_test.xlsm"
$targetWorksheet = "Sheet1" # 替换为你的目标工作表名称
$protectionPwd = "你的工作表保护密码" # 无密码则删除相关保护操作代码

# 打开Excel文件并处理保护
$excel = Open-ExcelPackage -Path $excelPath
$worksheet = $excel.Workbook.Worksheets[$targetWorksheet]
if ($worksheet.Protection.IsProtected) {
    $worksheet.Protection.Unprotect($protectionPwd)
}

# 追加数据到指定工作表
$result | Export-Excel -Path $excelPath -WorksheetName $targetWorksheet -Append -Force

# 恢复工作表保护（如果之前有）
if ($worksheet.Protection.IsProtected) {
    $worksheet.Protection.Protect($protectionPwd)
}

# 保存并关闭文件
Close-ExcelPackage $excel

方案二：使用原生Excel COM对象（无需额外模块） #

如果无法安装第三方模块，可以用Windows原生的Excel COM对象实现，适合严格限制模块安装的环境。

替换原脚本中最后一行的Out-File命令为以下代码：

$excelPath = "C:\Users\user\Documents\20221111\Netscaler VIPs per Cluster_test.xlsm"
$targetWorksheetName = "Sheet1" # 替换为你的目标工作表名称
$protectionPwd = "你的工作表保护密码" # 无密码则删除相关保护操作代码

# 创建Excel应用对象（后台运行）
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

try {
    # 打开目标文件并定位工作表
    $workbook = $excel.Workbooks.Open($excelPath)
    $worksheet = $workbook.Worksheets.Item($targetWorksheetName)

    # 解除工作表保护（如果有）
    if ($worksheet.ProtectContents) {
        if ($protectionPwd) {
            $worksheet.Unprotect($protectionPwd)
        } else {
            $worksheet.Unprotect()
        }
    }

    # 找到最后一行数据的行号，确定追加起始行
    $lastRow = $worksheet.Cells.Item($worksheet.Rows.Count, 1).End(-4162).Row
    $startRow = $lastRow + 1

    # 写入列名（仅当工作表为空时执行）
    if ($startRow -eq 1) {
        $worksheet.Cells.Item(1, 1) = "name"
        $worksheet.Cells.Item(1, 2) = "ipv46"
        $worksheet.Cells.Item(1, 3) = "port"
        $worksheet.Cells.Item(1, 4) = "curstate"
        $worksheet.Cells.Item(1, 5) = "Device Name"
        $startRow = 2
    }

    # 遍历结果集，逐行写入数据
    foreach ($item in $result) {
        $worksheet.Cells.Item($startRow, 1) = $item.name
        $worksheet.Cells.Item($startRow, 2) = $item.ipv46
        $worksheet.Cells.Item($startRow, 3) = $item.port
        $worksheet.Cells.Item($startRow, 4) = $item.curstate
        $worksheet.Cells.Item($startRow, 5) = $item.'Device Name'
        $startRow++
    }

    # 恢复工作表保护（如果之前有）
    if ($protectionPwd) {
        $worksheet.Protect($protectionPwd)
    } else {
        $worksheet.Protect()
    }

    # 保存文件
    $workbook.Save()
} finally {
    # 清理COM对象，避免Excel进程残留
    $workbook.Close()
    $excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
    [GC]::Collect()
    [GC]::WaitForPendingFinalizers()
}

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