以下の様に「担当エリア」項目に同じ県が並ぶExcelファイルがあったとします。このExcelファイルを県ごとの担当者にメールに添付して送信したいが他の県の担当者の情報は見せたくない。
このような場合、Excelファイルを県の数分コピーして不要な県の行を削除する作業を何度も繰り返すことになります。サンプルのExcelファイルぐらいであれば何とかなるかもしれませんが、これが何百行あり４７都道府県に分割するとなったら、うんざりします。
というわけでPowershellスクリプトを書いてみました。
スクリプト
<#
.SYNOPSIS
Excelのxlsxファイルを指定する項目で分割する
<CommonParameters> はサポートしていません
.DESCRIPTION
縦に同じ値が並ぶ項目を、同じ値で分類分けし、各々同じ値のみ含むxlsxファイルに分割する。
.EXAMPLE
Split-ExcelBook [-Path] "ファイル名" [[-StartCell] "A2"] [[-SheetName] "Sheet1"]
.PARAMETER Path
.xlsxファイルの名前
.PARAMETER StartCell
分割するキーの列の開始セル
デフォルト:A2
.PARAMETER SheetName
対象シート名
デフォルト:1番目のシート
#>
function Split-ExcelBook
{
param(
[string]
$Path,
[string]
$StartCell="A2",
[string]
$SheetName=$Null
)
$Excel = New-Object -ComObject Excel.Application # Excel初期化
if (-not($Excel)) {
Write-Debug 'Excelの初期化失敗'
return
}
if (-not(Test-Path -LiteralPath $Path)) {
Write-Debug "${Path}が存在しない"
return
}
$sn = 1
if ($SheetName) {
$sn = $SheetName
}
# 分割するキーワードを取得
$keywords = $Excel.Workbooks.Open($Path) | % {
$_.Worksheets[$sn] | % {
$s = $_.Range($StartCell)
$e = $_.Cells($_.UsedRange.Rows.Count(),`
$_.UsedRange.Rows.Count())
$_.Range($s, $e).Rows | % {
$_.Cells.Item(1,1).Value()
} | Sort-Object -Unique
}
$_.Close() | Out-Null
}
# 分割ループ
$keywords | % {
$keyword = $_
$dir = [System.IO.Path]::GetDirectoryName($Path)
$base = [System.IO.Path]::GetFileNameWithoutExtension($Path)
$ext = [System.IO.Path]::GetExtension($Path)
$dest = Join-Path $dir ($base + $keyword + $ext)
Copy-Item -LiteralPath $Path -Destination $dest -Force
$Excel.Workbooks.Open($dest) | % {
$_.Worksheets[$sn] | % {
$MaxRow = $_.UsedRange.Rows.Count()
for($i = $_.Range($StartCell).Row(); $i -le $MaxRow; $i++) {
if ($keyword -ne $_.Cells($i, 1).Value()) {
if($_.Rows($i).Delete()) {
$i--
$MaxRow--
}
}
}
}
$_.Save()
$_.Close() | Out-Null
}
}
$Excel.Quit()
$Excel = $null
[System.GC]::Collect() # ガーベージコレクト
}
if (-not($MyInvocation.PSCommandPath)) {
$Path = "C:\sample.xlsx"
$StartCell = "A2"
$SheetName = "Sheet1"
Split-ExcelBook $Path $StartCell $SheetName
}説明
やっていることは、-StartCellオプションで指定した担当エリアの項目に含まれる値（○○県）を配列化し、配列の数分だけファイルをコピーします。コピーしたファイルから指定担当エリアの県以外の行を順次削除しています。
分割後のサンプル（宮崎県)
こんな感じになります。
コメント