replace_images_zip_method.ps1 代码如下
param ([Parameter(Mandatory=$true)][string]$ExcelPath,[Parameter(Mandatory=$true)][string]$ImagesFolder,[Parameter(Mandatory=$false)][string]$OutputSuffix = "_new"
)try {# 1. Validate inputsif (-not (Test-Path $ExcelPath)) { throw "Excel file not found: $ExcelPath" }if (-not (Test-Path $ImagesFolder)) { throw "Images folder not found: $ImagesFolder" }$ExcelPath = (Get-Item $ExcelPath).FullName$ImagesFolder = (Get-Item $ImagesFolder).FullName# 2. Prepare output file$dir = Split-Path $ExcelPath -Parent$name = [System.IO.Path]::GetFileNameWithoutExtension($ExcelPath)$ext = [System.IO.Path]::GetExtension($ExcelPath)$outputExcelPath = Join-Path $dir "${name}${OutputSuffix}${ext}"Write-Host "Creating copy: $outputExcelPath" -ForegroundColor CyanCopy-Item $ExcelPath $outputExcelPath -Force# 3. Load ZipFile assemblyAdd-Type -AssemblyName System.IO.Compression.FileSystem# 4. Open the copy as a Zip archive$zip = [System.IO.Compression.ZipFile]::Open($outputExcelPath, [System.IO.Compression.ZipArchiveMode]::Update)# 5. Iterate through images in the folder and replace in zip$imageFiles = Get-ChildItem $ImagesFolder -File$count = 0# Create a map of BaseName -> ZipEntry for faster lookup$zipEntries = @{}foreach ($entry in $zip.Entries) {if ($entry.FullName -like "xl/media/*") {$baseName = [System.IO.Path]::GetFileNameWithoutExtension($entry.Name)$zipEntries[$baseName] = $entry}}foreach ($img in $imageFiles) {$imgBaseName = [System.IO.Path]::GetFileNameWithoutExtension($img.Name)# Look for matching entry by BaseName (ignoring extension)if ($zipEntries.ContainsKey($imgBaseName)) {$targetEntry = $zipEntries[$imgBaseName]$targetName = $targetEntry.NameWrite-Host " Replacing: $targetName (with $($img.Name))" -ForegroundColor Green# Delete existing entry$entryPath = $targetEntry.FullName$targetEntry.Delete()# Create new entry with the ORIGINAL name (preserving extension in Zip)# This "tricks" Excel into accepting the new content (e.g. PNG) as the old file type (e.g. JPEG)# which usually works fine and keeps XML relationships valid.[System.IO.Compression.ZipFileExtensions]::CreateEntryFromFile($zip, $img.FullName, $entryPath)# Update map to avoid double processing (though unlikely with unique names)$zipEntries.Remove($imgBaseName)$count++} else {# Optional: Write-Host " Skipping: $($img.Name) (no match in Excel)" -ForegroundColor DarkGray}}$zip.Dispose()Write-Host "`nSuccess! Replaced $count images." -ForegroundColor CyanWrite-Host "Output saved to: $outputExcelPath" -ForegroundColor Cyan} catch {Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
}
./replace_images_zip_method.ps1 example.xlsx ./imagefolder
结果生成example_new.xlsx
支持 WPS 嵌入图片。观察到 WPS 嵌入转浮动时，图片images目录下的编号会被打乱，反之亦然，但 dispimg 公式中显示的ID，在图片切换为浮动后，会在选择窗格中显示。