excel - 在多个 ForEach 循环中将文件称为变量 [PowerShell]

Question

我在我的脚本上取得了一些进展，该脚本会自动更新 excel 文件中的链接而不打开它们。我已经成功地使该函数在单个文件上工作，并输入文件名和要替换的文本。现在我正在尝试扩展它，以便它对脚本目录中的所有文件执行相同的操作。

以下是脚本如何与步骤注释一起使用：

# This part will be responsible from fetching the week number to replace from the script directory name, currently I am testing with pre-determined number
# $wk = Get-Item -Path $PWD | Select-Object -Property BaseName
# $wknn = "$wk".Substring(13,2) -as [int]
$wknn = 41
$wkold = $wknn-1
$wkprev = $wknn-2
$DefaultFiles =  Get-ChildItem | Where-Object {$_.Name -like "*.xls*"}
ForEach($File in $DefaultFiles) 
{

    # Build ZIP file name
    $zipFile =  $_ -ireplace [regex]::Escape(".xlsb"), ".zip"

    # Create temporary folder
    $parent = [System.IO.Path]::GetTempPath();
    [string] $guid = [System.Guid]::NewGuid();
    $tempFolder = Join-Path $parent $guid;
    New-Item -ItemType Directory -Path $tempFolder;

    # Rename file to ZIP
    Rename-Item -Path $_ -NewName $zipFile

    # Not using Expand-Archive because it changes the ZIP format
    C:\7z\7za.exe x "$zipFile" -o"$tempFolder"
    
    # Replace old text with new text. First replace wk-1 to wk and then wk-2 to wk-1
    $fileNames = Get-ChildItem -Path $tempFolder -Recurse -Include *.rels
    foreach ($file in $fileNames)
    {
        (Get-Content -ErrorAction SilentlyContinue $file.FullName) |
        Foreach-Object { $_ -replace $wkold, $wknn } |
        Foreach-Object { $_ -replace $wkprev, $wkold } |
        Set-Content $file.FullName
    }

     # Changing working folder because 7Zip option -w doesn't work
    Set-Location -Path $tempfolder

    # Update archive with new files. Not using Compress-Archive because it changes the ZIP format
    C:\7z\7za.exe u -r "$zipFile" *.*

    # Rename file back to XLSB
    Rename-Item -Path $zipFile -NewName $_
    
    #Move the final .xlsb file back to the script root
    move-Item -Path $_ -destination $PSScriptRoot
    
    #Set location to script root to start over
    Set-Location -Path $PSScriptRoot
    }
}

我遇到了 forEach 循环的问题。我不确定如何在 Build Zip File Name 步骤的第一个循环中引用文件名。之后我想将输出文件移动到脚本根目录时如何引用它。此外，我怀疑 forEach 循环的堆叠可能不那么简单，并且需要在代码中执行额外的步骤，但由于我刚开始使用 CI 没有经验并且找不到我的问题的简单答案。

我非常感谢对我的代码中的语法提供一些帮助:)

标签： excelpowershellzip

Answer 1

我会在主循环之外创建一个临时文件夹并将工作目录设置为该文件夹。然后删除文件夹并在所有完成后重置工作目录。

此外，无需先重命名完成的 zip 文件，然后再将其移回其原始位置，因为您可以同时使用Move-Itemcmdlet 执行此操作。

尝试：

$wknn       = 41
$wkold      = $wknn - 1
$wkprev     = $wknn - 2

$7zipExe    = 'C:\7z\7za.exe'  # path to 7za.exe
$sourcePath = $PSScriptRoot    # path to where the Excel files are

# Create temporary folder
$tempFolder = Join-Path -Path ([System.IO.Path]::GetTempPath()) -ChildPath ((New-Guid).Guid)
$null       = New-Item -ItemType Directory -Path $tempFolder -Force

# retrieve a collection of Excel files (FullNames only). 
# If you ONLY want .xlsb files, set the Filter to '*.xlsb'
$excelFiles = (Get-ChildItem -Path $sourcePath -Filter '*.xls*' -File).FullName

# Changing working folder because 7Zip option -w doesn't work
Set-Location -Path $tempfolder

foreach($File in $excelFiles) {
    # Build ZIP file name
    $zipFile = [System.IO.Path]::ChangeExtension($File, '.zip')

    # Rename file to ZIP
    Rename-Item -Path $File -NewName $zipFile

    # Not using Expand-Archive because it changes the ZIP format
    & $7zipExe x "$zipFile" -o"$tempFolder" | Out-Null

    # Replace old text with new text. First replace wk-1 to wk and then wk-2 to wk-1
    Get-ChildItem -Path $tempFolder -Recurse -Filter '*.rels' -File | ForEach-Object {
        (Get-Content -Path $_.FullName -Raw) -replace $wkold, $wknn -replace $wkprev, $wkold |
        Set-Content $_.FullName
    }

    # Update archive with new files. Not using Compress-Archive because it changes the ZIP format
    & $7zipExe u -r "$zipFile" *.* | Out-Null

    # Rename and Move the updated file back to the original location (overwrite)
    Move-Item -Path $zipFile -Destination $File -Force

    # remove all files from the temporary folder to start fresh
    Remove-Item -Path "$tempfolder\*" -Recurse -Force
}

# Set location back to script root
Set-Location -Path $PSScriptRoot
# remove the temporary folder
Remove-Item -Path $tempfolder -Recurse -Force