excel - 使用 PowerShell 删除已知的 Excel 密码

Question

我有这个 PowerShell 代码，可以循环指定目录中的 Excel 文件；引用已知密码列表来查找正确的密码；然后打开、解密该文件并将其保存到新目录。

但它的执行速度没有我想要的那么快(它是更大的 ETL 流程的一部分，并且是一个瓶颈)。此时，我可以更快地手动删除密码，因为脚本需要约 40 分钟才能解密 40 个工作簿，同时引用约 50 个密码的列表。

是否缺少一个可以加快速度的 cmdlet 或函数(或某些东西)、处理过程中被忽视的缺陷，或者 PowerShell 可能不是完成这项工作的正确工具？

原始代码(更新后的代码可以在下面找到):

$ErrorActionPreference = "SilentlyContinue"

CLS

# Paths
$encrypted_path = "C:\PoShTest\Encrypted\"
$decrypted_Path = "C:\PoShTest\Decrypted\"
$original_Path =  "C:\PoShTest\Originals\"
$password_Path =  "C:\PoShTest\Passwords\Passwords.txt"

# Load Password Cache
$arrPasswords = Get-Content -Path $password_Path

# Load File List
$arrFiles = Get-ChildItem $encrypted_path

# Create counter to display progress
[int] $count = ($arrfiles.count -1)

# Loop through each file
$arrFiles| % {
    $file  = get-item -path $_.fullname
    # Display current file
    write-host "Processing" $file.name -f "DarkYellow"
    write-host "Items remaining: " $count `n

    # Excel xlsx
    if ($file.Extension -eq ".xlsx") {

    # Loop through password cache
        $arrPasswords | % {
            $passwd = $_

            # New Excel Object
            $ExcelObj = $null
            $ExcelObj = New-Object -ComObject Excel.Application
            $ExcelObj.Visible = $false

            # Attempt to open file
            $Workbook = $ExcelObj.Workbooks.Open($file.fullname,1,$false,5,$passwd)
            $Workbook.Activate()

            # if password is correct - Save new file without password to $decrypted_Path
                if ($Workbook.Worksheets.count -ne 0) {
                    $Workbook.Password=$null
                    $savePath = $decrypted_Path+$file.Name
                    write-host "Decrypted: " $file.Name -f "DarkGreen"
                    $Workbook.SaveAs($savePath)
            # Close document and Application
                    $ExcelObj.Workbooks.close()
                    $ExcelObj.Application.Quit()

            # Move original file to $original_Path
                    move-item $file.fullname -Destination $original_Path -Force
                }
                else {
            # Close document and Application
                    write-host "PASSWORD NOT FOUND: " $file.name -f "Magenta"
                    $ExcelObj.Close()
                    $ExcelObj.Application.Quit()
                }
        }

    }

$count--
# Next File
}

Write-host "`n Processing Complete" -f "Green"

更新的代码:

# Get Current EXCEL Process ID's so they are not affected but the scripts cleanup
# SilentlyContinue in case there are no active Excels
$currentExcelProcessIDs = (Get-Process excel -ErrorAction SilentlyContinue).Id

$a = Get-Date

$ErrorActionPreference = "SilentlyContinue"

CLS

# Paths
$encrypted_path = "C:\PoShTest\Encrypted"
$decrypted_Path = "C:\PoShTest\Decrypted\"
$processed_Path = "C:\PoShTest\Processed\"
$password_Path  = "C:\PoShTest\Passwords\Passwords.txt"

# Load Password Cache
$arrPasswords = Get-Content -Path $password_Path

# Load File List
$arrFiles = Get-ChildItem $encrypted_path

# Create counter to display progress
[int] $count = ($arrfiles.count -1)

# New Excel Object
$ExcelObj = $null
$ExcelObj = New-Object -ComObject Excel.Application
$ExcelObj.Visible = $false

# Loop through each file
$arrFiles| % {
    $file  = get-item -path $_.fullname
    # Display current file
    write-host "`n Processing" $file.name -f "DarkYellow"
    write-host "`n Items remaining: " $count `n

    # Excel xlsx
    if ($file.Extension -like "*.xls*") {

    # Loop through password cache
        $arrPasswords | % {
            $passwd = $_

            # Attempt to open file
            $Workbook = $ExcelObj.Workbooks.Open($file.fullname,1,$false,5,$passwd)
            $Workbook.Activate()

            # if password is correct, remove $passwd from array and save new file without password to $decrypted_Path
                if ($Workbook.Worksheets.count -ne 0) 

                {   
                    $Workbook.Password=$null
                    $savePath = $decrypted_Path+$file.Name
                    write-host "Decrypted: " $file.Name -f "DarkGreen"
                    $Workbook.SaveAs($savePath)

             # Added to keep Excel process memory utilization in check
                    $ExcelObj.Workbooks.close()

             # Move original file to $processed_Path
                    move-item $file.fullname -Destination $processed_Path -Force

                }
                else {
            # Close Document
                    $ExcelObj.Workbooks.Close()
                }
        }

    }



$count--
# Next File
}
# Close Document and Application
    $ExcelObj.Workbooks.close()
    $ExcelObj.Application.Quit()

Write-host "`nProcessing Complete!" -f "Green"
Write-host "`nFiles w/o a matching password can be found in the Encrypted folder."
Write-host "`nTime Started   : " $a.ToShortTimeString()
Write-host "Time Completed : " $(Get-Date).ToShortTimeString()
Write-host "`nTotal Duration : " 
NEW-TIMESPAN –Start $a –End $(Get-Date)

# Remove any stale Excel processes created by this script's execution
Get-Process excel -ErrorAction SilentlyContinue | Where-Object{$currentExcelProcessIDs -notcontains $_.id} | Stop-Process

Answer 1

如果不出意外的话，我确实看到了一个明显的性能问题，应该很容易解决。您将打开一个新的 Excel 实例来测试每个文档的每个密码。 40 个工作簿和 50 个密码意味着您一次打开了 2000 个 Excel 实例。

您应该能够继续使用同一个，而不会影响功能。将此代码从最内层循环中取出

# New Excel Object
$ExcelObj = $null
$ExcelObj = New-Object -ComObject Excel.Application
$ExcelObj.Visible = $false

以及将关闭该进程的代码片段。它也需要脱离循环。

$ExcelObj.Close()
$ExcelObj.Application.Quit()

如果这没有足够的帮助，您将不得不考虑对作业等进行某种并行处理。我在CodeReview.SE中有一个基本的解决方案。我的回答做了类似的事情。

基本上，它的作用是同时运行多个 Excel，其中每个 Excel 都处理一大块文档，其运行速度比一个 Excel 处理所有这些文档的速度要快。就像我在链接的答案中所做的那样，我警告使用 PowerShell 实现 Excel COM 的自动化。 COM 对象并不总是能够正确释放，并且锁可能会保留在文件或进程上。

<小时/>

无论成功与否，您都会循环查找所有 50 个密码。这意味着您可以第一次找到正确的密码，但您仍然要尝试其他 49 个!在循环中设置一个标志，以便在发生这种情况时中断内部循环。

就密码逻辑而言，你是这样说的

At this point I can remove the passwords faster manually since the script takes ~40 minutes

为什么你可以做得更快？你知道什么是剧本不知道的。我不认为您能够胜过脚本，但完全按照脚本执行操作。

据我所知，另一个建议是保留/跟踪成功的密码和关联的文件名。这样，当它再次被处理时，您就会知道要尝试的第一个密码。