PowerShellでExcelファイル内の文字列を一括検索、再び

Excelの.xlsxファイルの実態が.zipファイルだと知りました。

０．対象ファイルの拡張子がxlsの場合、一度Excelで開きxlsx形式で保存する。１．エクスプローラーなどでxlsxファイルの拡張子をzipファイルに変更 2．変更したzipファイルを解凍（展開）。 3．解凍したフォルダのxl/medi...

それならばPowerShellスクリプトで内部の.xmlファイルから文字検索が出来るでは無いかと思い試作してみました。

スクリプト
使い方
結果
説明

スクリプト

ファイル名:XlsxSearch.ps1

<#
.SYNOPSIS
 エクセルファイル(.xlsx)から文字を検索する。
.DISCRIPTION
 .xlsxファイルのパスをパイプラインで渡す。
 -Keywordで検索文字を指定する
#>
Param(
    [string]$Keyword,
    [switch]$Help
)

Add-Type -AssemblyName System.IO.Compression.FileSystem

function XlsxSearch
{
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline=$true,Mandatory=$true)]
        [string[]]
        $files,
        [Parameter(ValueFromPipeline=$false,Mandatory=$true)]
        [string]
        $Keyword
    )
    begin {}
    process
    {
        #Write-Host ("Keyword:{0}" -f $Keyword)
        foreach ($file in $files)
        {
            #Write-Host ("File:{0}" -f $file)
           if ($zip = [System.IO.Compression.ZipFile]::OpenRead($file)) {
                try {
                    $words = {}
                    if ($t = $zip.GetEntry("xl/sharedStrings.xml"))
                    {
                        if ($fs = $t.Open()) {
                            $sr = [System.IO.StreamReader]::new($fs)
                            $sharedStrings = [xml]($sr.ReadToEnd())
                            $words = $sharedStrings.sst.si.t
                        }
                    }

                    $zip.Entries | ? {
                        $_.FullName -match "^xl/worksheets/.+\.xml$"
                    } | % {
                        $name = $_.Name
                        if ($fs = $zip.GetEntry($_.FullName).Open()) {
                            $sr = [System.IO.StreamReader]::new($fs)
                            $xmlDoc = [xml]($sr.ReadToEnd())
                            $xmlDoc.worksheet.sheetData.row | % {
                                $_.c | ? { $_.t -eq "s" } | ? { $words[$_.v] -match $Keyword } | % {
                                    [PSCustomObject]@{
                                        File = $file
                                        Sheet = $name
                                        Cell = $_.r
                                        Value = $words[$_.v]
                                    }
                                }
                            }
                        }
                    }
                } finally {
                    $zip.Dispose()
                }
            }
        }
    }
    end {}
}


$args = @($input)

if ($Help -Or ( -Not $OutDir -And $args.Count -eq 0))
{
    Get-Help $PSCommandPath
    Exit 1
}

if ($args.Count -gt 0)
{
    $args | XlsxSearch -Keyword $Keyword
}

使い方

ls ~\Desktop\*.xlsx | % {$_.FullName } | .\XlsxSearch.ps1 -Keyword "日本"

結果

File                                       Sheet      Cell Value
----                                       -----      ---- -----
C:\Users\karet\Desktop\202303171559.xlsx sheet1.xml B1   日本電産

文字列にのみ検索可能、数値は未対応。
テストを殆どしていないので、ご利用する場合はプログラム修正が必須とお考え下さい。
前作はExcelがインストールされている必要がありますが、今作はインストールされていないPCでも動くはず。

PowerShellでExcelファイル内の文字列を一括検索

指定ディレクトリ下のExcelファイル(.xlsx)内に指定した文字列がある場合、ファイル名、シート名、セルの座標、セルの値を返すスクリプトです。沢山あるExcelファイルから特定の文字を含むファイルを探すことが出来ると便利かと作ってみま...

説明

.xlsx(zip)中のファイルを覗いてみたところ、xlディレクトリに各シートがsheet1.xml,sheet2.xml…という風に.xml形式で保存されていました。その.xmlファイルを開いてみると各セルの内容が保存されているようです。ただセル内にセットした文字列は見当たりません。文字列を入力したセルには数値がセットされていました。調べたところ、この数値はxl/sharedStrings.xml内にある文字列の順番に対応するようです。ファイル名がsharedStringですので各シートで扱う文字列を共有することでファイルサイズを小さくするための辞書データだと思われます。
調べた情報はこの程度ですが、この情報を材料にPowerShellスクリプトを組んでみました。
エラー処理を省いていますので文字を含まない.xlsxファイルでエラーが出ています。

また、ファイルの指定はls(Get-ChildItem)で検索した結果をパイプラインで渡すことを想定しています。
その割にlsの結果を直接渡すコードが省かれており、一度%(Foreach-Object)でFullPathプロパティを取り出しそれを渡しています。この文書を書いている時間でコードを直せばよいのですが、そのうち修正出来ればと考えております。
同様に単一ファイルを引数に取る機能も付けるべきだとは思うのですが、そちらも省かれています。

実用する場合はそのあたりを修正する必要があると思います。

スクリプト

使い方

結果

説明

コメント