powershell 脚本按照 excel 选择窗格显示的将对应图片批量导出（1）
windows 11下，适用于 wps 表格嵌入图片和浮动图片，
#Requires -Version 5.1
<#!
.SYNOPSIS
Export all images from an Excel workbook (.xlsx/.xlsm) and name them by cNvPr/@name (e.g. ID_XXXXXXXX...) when possible.
.DESCRIPTION
- Parses both DrawingML floating pictures (xl/drawings/drawing*.xml) and Cell Images (xl/cellimages.xml).
- Resolves rId -> media mapping via the corresponding .rels files.
- File names priority: cNvPr/@name (or cellImage/@name) if present and valid; otherwise fallback to media filename.
- Ensures unique filenames and preserves original extensions (.png/.jpg/.gif/.bmp/.emf/.wmf etc.).
.EXAMPLE
# Export images to .\ExcelImages folder
powershell -ExecutionPolicy Bypass -File export_excel_images.ps1 -ExcelPath "D:\test\book.xlsx"
.EXAMPLE
# Export images to a custom folder
powershell -ExecutionPolicy Bypass -File export_excel_images.ps1 -ExcelPath "D:\test\book.xlsx" -OutputFolder "D:\out"
#>
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)] [string]$ExcelPath,
[Parameter()] [string]$OutputFolder = ".\ExcelImages",
[Parameter()] [string]$LogCsv,
[switch]$DownloadExternal
)
Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'
function New-TempDir {
$d = Join-Path $env:TEMP ([guid]::NewGuid())
New-Item -ItemType Directory -Path $d | Out-Null
return $d
}
function Clean-Name([string]$n) {
if (-not $n) { return $null }
$clean = ($n -replace '[\\/:*?"<>|]', '_').Trim()
if ([string]::IsNullOrWhiteSpace($clean)) { return $null }
return $clean
}
function Ensure-Unique([string]$dir,[string]$base,[string]$ext) {
$p = Join-Path $dir ($base + $ext)
if (-not (Test-Path $p)) { return $p }
$i = 1
do {
$p = Join-Path $dir ("{0}_{1}{2}" -f $base,$i,$ext)
$i++
} while (Test-Path $p)
return $p
}
function Load-RelsMap([string]$relsPath) {
$map = @{}
if (Test-Path $relsPath -PathType Leaf) {
[xml]$x = Get-Content -LiteralPath $relsPath -Raw
foreach ($rel in $x.Relationships.Relationship) {
# Use GetAttribute to avoid StrictMode errors when attributes are missing
$id = $rel.GetAttribute('Id')
if ($id) {
$target = $rel.GetAttribute('Target')
$tm = $rel.GetAttribute('TargetMode')
if ([string]::IsNullOrWhiteSpace($tm)) { $tm = $null }
$type = $rel.GetAttribute('Type')
$map[$id] = [pscustomobject]@{
Target = $target
TargetMode = $tm
Type = $type
}
}
}
}
return $map
}
function Normalize-Rel($value) {
if ($null -eq $value) { return $null }
if ($value -is [string]) { return [pscustomobject]@{ Target=$value; TargetMode=$null; Type=$null } }
if ($value.PSObject.Properties['Target']) { return $value }
try {
$t = $value.Target
$tm = $value.TargetMode
$ty = $value.Type
return [pscustomobject]@{ Target=$t; TargetMode=$tm; Type=$ty }
} catch { return [pscustomobject]@{ Target=$value.ToString(); TargetMode=$null; Type=$null } }
}
function Resolve-TargetPath([string]$baseDir,[string]$target) {
# Normalize relative segments like ../media/image1.png against baseDir
$combined = Join-Path $baseDir $target
try {
$full = [IO.Path]::GetFullPath($combined)
} catch { $full = $combined }
if (Test-Path -LiteralPath $full -PathType Leaf) { return (Get-Item -LiteralPath $full).FullName }
return $null
}
function Export-ExcelImages {
param([string]$ExcelPath,[string]$OutputFolder)
if (-not (Test-Path -LiteralPath $ExcelPath -PathType Leaf)) {
throw "Excel file not found: $ExcelPath"
}
$ExcelPath = (Get-Item -LiteralPath $ExcelPath).FullName
$OutputFolder = [IO.Path]::GetFullPath($OutputFolder)
if (-not (Test-Path $OutputFolder)) { New-Item -ItemType Directory -Path $OutputFolder | Out-Null }
$tmp = New-TempDir
try {
$zip = Join-Path $tmp 'src.zip'
Copy-Item -LiteralPath $ExcelPath -Destination $zip
Expand-Archive -Path $zip -DestinationPath $tmp -Force
$xlRoot = Join-Path $tmp 'xl'
$drawDir = Join-Path $xlRoot 'drawings'
$mediaDir = Join-Path $xlRoot 'media'
$exported = 0
$log = New-Object System.Collections.Generic.List[object]
# A. DrawingML pictures (floating)
if (Test-Path $drawDir -PathType Container) {
$drawingXmls = Get-ChildItem -LiteralPath $drawDir -Filter '*.xml' -File
foreach ($drawing in $drawingXmls) {
$relsPath = Join-Path $drawing.Directory.FullName ("_rels/{0}.rels" -f $drawing.Name)
$rels = Load-RelsMap -relsPath $relsPath
$pics = Select-Xml -Path $drawing.FullName -XPath "//*[local-name()='pic']"
foreach ($sel in $pics) {
$picNode = $sel.Node
if ($null -eq $picNode) { continue }
$cNvPr = $picNode.SelectSingleNode(".//*[local-name()='cNvPr']")
$name = $null
if ($null -ne $cNvPr) { $name = $cNvPr.GetAttribute('name') }
$blip = $picNode.SelectSingleNode(".//*[local-name()='blip']")
$embedAttr = $null; $linkAttr = $null
if ($null -ne $blip -and $blip.Attributes) {
$embedAttr = $blip.Attributes | Where-Object { $_.LocalName -eq 'embed' } | Select-Object -First 1
$linkAttr = $blip.Attributes | Where-Object { $_.LocalName -eq 'link' } | Select-Object -First 1
}
$embedId = if ($null -ne $embedAttr) { $embedAttr.Value } else { $null }
$linkId = if ($null -ne $linkAttr) { $linkAttr.Value } else { $null }
if (-not $embedId -and -not $linkId) { continue }
$relKey = if ($embedId) { $embedId } else { $linkId }
if ($rels.ContainsKey($relKey)) {
$relObj = Normalize-Rel $rels[$relKey]
$target = $relObj.Target # media path or external url
$tm = $null
if ($relObj -and $relObj.PSObject.Properties['TargetMode']) { $tm = $relObj.TargetMode }
$src = $null
if ($tm -ne 'External') {
$src = Resolve-TargetPath -baseDir $drawing.Directory.FullName -target $target
} elseif ($DownloadExternal -and $target) {
try {
$u = [System.Uri]$target
$ext = [IO.Path]::GetExtension($u.AbsolutePath)
if (-not $ext) { $ext = '.img' }
$tmpBase = if ($name) { Clean-Name $name } else { 'external' }
$tmpDst = Ensure-Unique -dir $OutputFolder -base $tmpBase -ext $ext
Invoke-WebRequest -Uri $u -OutFile $tmpDst -UseBasicParsing -ErrorAction Stop
$src = $tmpDst
} catch {}
}
if (-not $src) { continue }
$ext = [IO.Path]::GetExtension($src)
$base = $null
if ($name -and ($name -match '^ID_[A-F0-9]{32}$')) { $base = $name }
elseif ($name) { $base = Clean-Name $name }
if (-not $base) { $base = [IO.Path]::GetFileNameWithoutExtension($src) }
$dst = Ensure-Unique -dir $OutputFolder -base $base -ext $ext
Copy-Item -LiteralPath $src -Destination $dst -Force
$exported++
$ty = $null
if ($relObj -and $relObj.PSObject.Properties['Type']) { $ty = $relObj.Type }
$log.Add([pscustomobject]@{
SourceXml = $drawing.Name
Kind = 'Drawing'
RId = $relKey
Target = $target
TargetMode= $tm
RelType = $ty
MediaName = [IO.Path]::GetFileName($src)
Output = $dst
NameAttr = $name
})
}
}
}
}
# B. Cell Images (DISPIMG)
$cellImages = Join-Path $xlRoot 'cellimages.xml'
if (Test-Path $cellImages -PathType Leaf) {
$rels = Load-RelsMap -relsPath (Join-Path (Join-Path $xlRoot '_rels') 'cellimages.xml.rels')
$nodes = Select-Xml -Path $cellImages -XPath "//*[local-name()='cellImage']"
foreach ($sel in $nodes) {
$n = $sel.Node
if ($null -eq $n) { continue }
# Name comes from cNvPr in WPS cellimages, not from the cellImage node itself
$cNvPr = $n.SelectSingleNode(".//*[local-name()='cNvPr']")
$name = $null
if ($null -ne $cNvPr) { $name = $cNvPr.GetAttribute('name') }
$blip = $n.SelectSingleNode(".//*[local-name()='blip']")
$embedAttr = $null; $linkAttr = $null
if ($null -ne $blip -and $blip.Attributes) {
$embedAttr = $blip.Attributes | Where-Object { $_.LocalName -eq 'embed' } | Select-Object -First 1
$linkAttr = $blip.Attributes | Where-Object { $_.LocalName -eq 'link' } | Select-Object -First 1
}
$embedId = if ($null -ne $embedAttr) { $embedAttr.Value } else { $null }
$linkId = if ($null -ne $linkAttr) { $linkAttr.Value } else { $null }
if (-not $embedId -and -not $linkId) { continue }
$relKey = if ($embedId) { $embedId } else { $linkId }
if ($rels.ContainsKey($relKey)) {
$relObj = Normalize-Rel $rels[$relKey]
$target = $relObj.Target # media or external url
$tm = $null
if ($relObj -and $relObj.PSObject.Properties['TargetMode']) { $tm = $relObj.TargetMode }
$src = $null
if ($tm -ne 'External') {
$src = Resolve-TargetPath -baseDir $xlRoot -target $target
} elseif ($DownloadExternal -and $target) {
try {
$u = [System.Uri]$target
$ext = [IO.Path]::GetExtension($u.AbsolutePath)
if (-not $ext) { $ext = '.img' }
$tmpBase = if ($name) { Clean-Name $name } else { 'external' }
$tmpDst = Ensure-Unique -dir $OutputFolder -base $tmpBase -ext $ext
Invoke-WebRequest -Uri $u -OutFile $tmpDst -UseBasicParsing -ErrorAction Stop
$src = $tmpDst
} catch {}
}
if (-not $src) { continue }
$ext = [IO.Path]::GetExtension($src)
$base = $null
if ($name -and ($name -match '^ID_[A-F0-9]{32}$')) { $base = $name }
elseif ($name) { $base = Clean-Name $name }
if (-not $base) { $base = [IO.Path]::GetFileNameWithoutExtension($src) }
$dst = Ensure-Unique -dir $OutputFolder -base $base -ext $ext
Copy-Item -LiteralPath $src -Destination $dst -Force
$exported++
$ty = $null
if ($relObj -and $relObj.PSObject.Properties['Type']) { $ty = $relObj.Type }
$log.Add([pscustomobject]@{
SourceXml = 'cellimages.xml'
Kind = 'CellImage'
RId = $relKey
Target = $target
TargetMode= $tm
RelType = $ty
MediaName = [IO.Path]::GetFileName($src)
Output = $dst
NameAttr = $name
})
}
}
}
Write-Host ("完成，导出图片 {0} 张 -> {1}" -f $exported, $OutputFolder) -ForegroundColor Green
if ($LogCsv) {
$append = Test-Path $LogCsv
$log | Export-Csv -Path $LogCsv -Append:$append -NoTypeInformation -Encoding UTF8
Write-Host ("已写入映射日志: {0}" -f $LogCsv) -ForegroundColor Yellow
}
}
finally {
if (Test-Path $tmp) { Remove-Item -Path $tmp -Recurse -Force -ErrorAction SilentlyContinue }
}
}
# Run if invoked as a script
Export-ExcelImages -ExcelPath $ExcelPath -OutputFolder $OutputFolder
一个可能的使用方式如下
powershell -ExecutionPolicy Bypass -File "C:\Users\geyee\my_powershell_script\export_excel_images.ps1" -ExcelPath "C:\Users\geyee\Downloads\混合表格.xlsx" -OutputFolder "C:\OfficeSpace" -LogCsv "C:\OfficeSpace\image_map.csv" -Verbose
浙公网安备 33010602011771号