針對Excel下開啟表格圖片顯示 #NAME？編輯欄顯示為 =@_xlfn.DISPIMG( 樣公式的問題，一般需要在 wps 程式下，Ctrl+F 查詢範圍選值，輸入 =DISPIMG 全選，然後再右鍵轉換為浮動圖片。如果是Excel中，則是查詢公式 DISPIMG。
查閱網上得資料得知，可以透過解壓表格檔案，然後 根據公式中的第一引數（通常以 ID 開頭）看 xl_rels\cellimages.xml.rels 目錄下的 name （其值為 dispimg 函式的第一引數）和 r:embed （其值以 rId 開頭）的對應關係，然後再看 Id （ rId 開頭 ） 和 Target（圖片路徑） 的對應關係，進而得到圖片的路徑。
在 LLM 的幫助下進而有以下 PS 指令碼。
function Get-ExcelDispImages {
param (
[Parameter(Mandatory=$true)]
[string]$ExcelPath,
[Parameter(Mandatory=$false)]
[string]$OutputFolder = ".\ExcelImages"
)
# 輔助函式：安全地讀取檔案內容
function Read-FileContent {
param (
[string]$Path
)
try {
# 使用.NET方法直接讀取檔案，避免PowerShell路徑解析問題
if ([System.IO.File]::Exists($Path)) {
return [System.IO.File]::ReadAllText($Path)
}
Write-Host "File not found: $Path" -ForegroundColor Yellow
return $null
}
catch {
Write-Host "Error reading file $Path : $_" -ForegroundColor Yellow
return $null
}
}
try {
# 驗證Excel檔案是否存在
if (-not (Test-Path -LiteralPath $ExcelPath)) {
throw "Excel file not found: $ExcelPath"
}
# 確保ExcelPath是絕對路徑
$ExcelPath = (Get-Item $ExcelPath).FullName
# 建立輸出資料夾（使用絕對路徑）
$OutputFolder = [System.IO.Path]::GetFullPath($OutputFolder)
if (-not (Test-Path -Path $OutputFolder)) {
New-Item -ItemType Directory -Path $OutputFolder -Force | Out-Null
}
# 建立Excel COM物件
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$workbook = $excel.Workbooks.Open($ExcelPath)
# 用於儲存找到的DISPIMG ID和資訊
$dispImgIds = @()
$imageMapping = @{}
# 遍歷所有工作表
foreach ($worksheet in $workbook.Worksheets) {
$usedRange = $worksheet.UsedRange
$rowCount = $usedRange.Rows.Count
$colCount = $usedRange.Columns.Count
for ($row = 1; $row -le $rowCount; $row++) {
for ($col = 1; $col -le $colCount; $col++) {
$cell = $usedRange.Cells($row, $col)
$formula = $cell.Formula
# 檢查是否包含DISPIMG函式並提取所有引數
if ($formula -match 'DISPIMG\("([^"]+)"') {
$imageId = $matches[1]
Write-Host "Found DISPIMG: $formula" -ForegroundColor Gray
# 建立引數列表
$params = @{
'ID' = $imageId
'Cell' = $cell.Address()
'Formula' = $formula
'Worksheet' = $worksheet.Name
}
# 提取所有引數，包括圖片ID
$paramValues = @()
$formula -match 'DISPIMG\((.*?)\)' | Out-Null
$paramString = $matches[1]
$paramValues = $paramString -split ',' | ForEach-Object {
$_ -replace '"', '' -replace '^\s+|\s+$', ''
}
# 儲存所有引數
for ($i = 0; $i -lt $paramValues.Count; $i++) {
$params["Param$i"] = $paramValues[$i]
}
$imageMapping[$imageId] = $params
$dispImgIds += $imageId
}
}
}
}
# 建立臨時目錄
$tempPath = Join-Path ([System.IO.Path]::GetTempPath()) "ExcelTemp"
if (Test-Path $tempPath) {
Remove-Item $tempPath -Recurse -Force
}
New-Item -ItemType Directory -Path $tempPath -Force | Out-Null
# 複製Excel檔案到臨時目錄並解壓
$tempExcel = Join-Path $tempPath "temp.xlsx"
$tempZip = Join-Path $tempPath "temp.zip"
Copy-Item -Path $ExcelPath -Destination $tempExcel -Force
if (Test-Path $tempZip) { Remove-Item $tempZip -Force }
Rename-Item -Path $tempExcel -NewName "temp.zip" -Force
Expand-Archive -Path $tempZip -DestinationPath $tempPath -Force
# 檢查media資料夾並處理圖片
$mediaPath = Join-Path $tempPath "xl\media"
if (Test-Path $mediaPath) {
# 顯示DISPIMG引數和圖片對應關係
Write-Host "`nFound $($imageMapping.Count) DISPIMG functions" -ForegroundColor Cyan
if ($imageMapping.Count -gt 0) {
Write-Host "`n=== DISPIMG Functions Found ===" -ForegroundColor Cyan
Write-Host "Found $($imageMapping.Count) DISPIMG functions" -ForegroundColor Cyan
# 顯示所有找到的DISPIMG函式
Write-Host "`n=== DISPIMG Functions Details ===" -ForegroundColor Yellow
foreach ($id in $imageMapping.Keys) {
$params = $imageMapping[$id]
Write-Host "Cell: [$($params.Worksheet)]$($params.Cell)" -ForegroundColor Gray
Write-Host "Formula: $($params.Formula)" -ForegroundColor Gray
}
# 首先從cellimages.xml獲取DISPIMG ID到rId的對映
$dispImgToRid = @{}
$cellImagesPath = Join-Path $tempPath "xl\cellimages.xml"
Write-Host "`n=== Reading cellimages.xml ===" -ForegroundColor Yellow
Write-Host "Path: $cellImagesPath" -ForegroundColor Gray
if (Test-Path $cellImagesPath) {
try {
$xmlContent = Get-Content $cellImagesPath -Raw -EnCoding UTF8
Write-Host "`nRaw XML Content:" -ForegroundColor Gray
Write-Host $xmlContent
# 使用正規表示式提取所有cellImage元素
$matches = [regex]::Matches($xmlContent, '<etc:cellImage>.*?</etc:cellImage>', [System.Text.RegularExpressions.RegexOptions]::Singleline)
Write-Host "`nFound $($matches.Count) cellImage elements" -ForegroundColor Gray
foreach ($match in $matches) {
$cellImageXml = $match.Value
Write-Host "`nProcessing cellImage element:" -ForegroundColor Gray
# 提取name屬性（包含DISPIMG ID）
if ($cellImageXml -match 'name="([^"]+)"') {
$dispImgId = $matches[1]
Write-Host "Found DISPIMG ID: $dispImgId" -ForegroundColor Gray
# 提取r:embed屬性（包含rId）
if ($cellImageXml -match 'r:embed="(rId\d+)"') {
$rId = $matches[1]
$dispImgToRid[$dispImgId] = $rId
Write-Host " Mapping: DISPIMG ID $dispImgId -> rId $rId" -ForegroundColor Green
}
}
}
}
catch {
Write-Host "Error reading cellimages.xml: $($_.Exception.Message)" -ForegroundColor Red
Write-Host $_.Exception.StackTrace -ForegroundColor Red
}
}
else {
Write-Host "cellimages.xml not found!" -ForegroundColor Red
}
# 從cellimages.xml.rels獲取rId到實際圖片的對映
$ridToImage = @{}
$cellImagesRelsPath = Join-Path $tempPath "xl\_rels\cellimages.xml.rels"
Write-Host "`n=== Reading cellimages.xml.rels ===" -ForegroundColor Yellow
Write-Host "Path: $cellImagesRelsPath" -ForegroundColor Gray
if (Test-Path $cellImagesRelsPath) {
try {
[xml]$relsXml = Get-Content $cellImagesRelsPath -Raw
Write-Host "`nXML Content:" -ForegroundColor Gray
Write-Host $relsXml.OuterXml
$relsXml.Relationships.Relationship | ForEach-Object {
if ($_.Target -match "media/") {
$ridToImage[$_.Id] = $_.Target
Write-Host " rId $($_.Id) -> $($_.Target)" -ForegroundColor Green
}
}
}
catch {
Write-Host "Error reading cellimages.xml.rels: $($_.Exception.Message)" -ForegroundColor Red
Write-Host $_.Exception.StackTrace -ForegroundColor Red
}
}
else {
Write-Host "cellimages.xml.rels not found!" -ForegroundColor Red
}
Write-Host "`n=== Summary of Mappings ===" -ForegroundColor Yellow
Write-Host "DISPIMG ID -> rId mappings:" -ForegroundColor Gray
$dispImgToRid.GetEnumerator() | ForEach-Object {
Write-Host " $($_.Key) -> $($_.Value)" -ForegroundColor Gray
}
Write-Host "`nrId -> Image mappings:" -ForegroundColor Gray
$ridToImage.GetEnumerator() | ForEach-Object {
Write-Host " $($_.Key) -> $($_.Value)" -ForegroundColor Gray
}
# 處理每個DISPIMG函式
Write-Host "`n=== Processing DISPIMG Functions ===" -ForegroundColor Yellow
foreach ($id in $imageMapping.Keys) {
$params = $imageMapping[$id]
Write-Host "`nProcessing: [$($params.Worksheet)]$($params.Cell)" -ForegroundColor Green
Write-Host "Formula: $($params.Formula)" -ForegroundColor Gray
Write-Host "DISPIMG ID: $id" -ForegroundColor Gray
# 從DISPIMG ID查詢對應的rId
$rId = $dispImgToRid[$id]
if ($rId) {
Write-Host "Found rId: $rId" -ForegroundColor Green
# 從rId查詢對應的圖片路徑
$imagePath = $ridToImage[$rId]
if ($imagePath) {
Write-Host "Found image path: $imagePath" -ForegroundColor Green
$imageName = Split-Path $imagePath -Leaf
# 查詢對應的圖片檔案
$mediaFile = Get-ChildItem -LiteralPath $mediaPath | Where-Object { $_.Name -eq $imageName }
if ($mediaFile) {
$outputPath = Join-Path $OutputFolder "$id$($mediaFile.Extension)"
Copy-Item -LiteralPath $mediaFile.FullName -Destination $outputPath -Force
Write-Host "Successfully copied: $imageName -> $outputPath" -ForegroundColor Cyan
# 以 media 資料夾下的檔名複製
# Copy-Item $mediaFile.FullName $(Join-Path $OutputFolder $(Split-Path $imagePath -Leaf))
}
else {
Write-Host "Image file not found: $imageName" -ForegroundColor Red
}
}
else {
Write-Host "No image path found for rId: $rId" -ForegroundColor Red
}
}
else {
Write-Host "No rId found for DISPIMG ID: $id" -ForegroundColor Red
}
}
} else {
Write-Host "No DISPIMG functions found in the workbook." -ForegroundColor Yellow
}
}
}
catch {
Write-Host "錯誤: $($_.Exception.Message)" -ForegroundColor Red
}
finally {
# 清理
if ($workbook) {
$workbook.Close($false)
}
if ($excel) {
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
}
if (Test-Path $tempPath) {
Remove-Item $tempPath -Recurse -Force
}
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
}
一個可能的用法如下
Get-ExcelDispImages -ExcelPath "C:\Users\demo\Documents\dispimg_wps.xlsx" -OutputFolder $pwd\output
生成的圖片在當前目錄下的 output 資料夾下。
參考閱讀：