xlogI125’s blog

パソコン作業を効率化したい

Rename-Itemコマンドの入力準備をExcelシートで行う 2

ファイル名 PowerShell Excel StrCmpLogicalW

メモ
Excelシート例
使い捨てスクリプト
参考資料リンク
過去記事

メモ

Sort-Objectコマンドレットでファイル名を並べ替えた場合の順位を併記
文字列を数式の一部として扱うことでExcelシートへの貼り付け時(G/標準)に文字列が変化しないようにした

Excelシート例

Attr	Nat	Sort	BaseName	Ext	NewBaseName	NewExt	WhatIf	Str	Dir
D	1	2	2dir		001)__['2dir']__		-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\2dir' -NewName '001)__[''2dir'']__'	C:\tmp\['99)]
D	2	3	3dir		002)__['3dir']__		-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\3dir' -NewName '002)__[''3dir'']__'	C:\tmp\['99)]
D	3	1	20dir		003)__['20dir']__		-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\20dir' -NewName '003)__[''20dir'']__'	C:\tmp\['99)]
D	4	5	d2ir		004)__['d2ir']__		-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\d2ir' -NewName '004)__[''d2ir'']__'	C:\tmp\['99)]
D	5	6	d3ir		005)__['d3ir']__		-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\d3ir' -NewName '005)__[''d3ir'']__'	C:\tmp\['99)]
D	6	4	d20ir		006)__['d20ir']__		-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\d20ir' -NewName '006)__[''d20ir'']__'	C:\tmp\['99)]
D	7	7	dir2		007)__['dir2']__		-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\dir2' -NewName '007)__[''dir2'']__'	C:\tmp\['99)]
D	8	9	dir3		008)__['dir3']__		-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\dir3' -NewName '008)__[''dir3'']__'	C:\tmp\['99)]
D	9	8	dir20		009)__['dir20']__		-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\dir20' -NewName '009)__[''dir20'']__'	C:\tmp\['99)]
F	1	2	2file	.txt	001)__['2file']__	.txt	-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\2file.txt' -NewName '001)__[''2file'']__.txt'	C:\tmp\['99)]
F	2	3	3file	.txt	002)__['3file']__	.txt	-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\3file.txt' -NewName '002)__[''3file'']__.txt'	C:\tmp\['99)]
F	3	1	20file	.txt	003)__['20file']__	.txt	-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\20file.txt' -NewName '003)__[''20file'']__.txt'	C:\tmp\['99)]
F	4	5	fi2le	.txt	004)__['fi2le']__	.txt	-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\fi2le.txt' -NewName '004)__[''fi2le'']__.txt'	C:\tmp\['99)]
F	5	6	fi3le	.txt	005)__['fi3le']__	.txt	-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\fi3le.txt' -NewName '005)__[''fi3le'']__.txt'	C:\tmp\['99)]
F	6	4	fi20le	.txt	006)__['fi20le']__	.txt	-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\fi20le.txt' -NewName '006)__[''fi20le'']__.txt'	C:\tmp\['99)]
F	7	7	file2	.txt	007)__['file2']__	.txt	-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\file2.txt' -NewName '007)__[''file2'']__.txt'	C:\tmp\['99)]
F	8	9	file3	.txt	008)__['file3']__	.txt	-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\file3.txt' -NewName '008)__[''file3'']__.txt'	C:\tmp\['99)]
F	9	8	file20	.txt	009)__['file20']__	.txt	-WhatIf	Rename-Item -WhatIf -LiteralPath 'C:\tmp\[''99)]\file20.txt' -NewName '009)__[''file20'']__.txt'	C:\tmp\['99)]

使い捨てスクリプト

# [テスト環境]
#  PowerShell 5.1, Excel 2019, Windows 11 (2023年4月頃)
# [スクリプトファイル(.ps1)]
#  <ファイル名>
#   "%USERPROFILE%\Desktop\RenameTest2.ps1"
#  <エンコード>
#   UTF-8 (BOM付き)
# [ショートカット(.lnk)]
#  <リンク先(T)>
#   PowerShell.exe -NoLogo -NoExit -NoProfile -ExecutionPolicy RemoteSigned -File "%USERPROFILE%\Desktop\RenameTest2.ps1"
#  <作業フォルダー(S)>
#   ""

using namespace Microsoft.PowerShell.Commands
using namespace System.IO

Set-StrictMode -Version Latest

$VerbosePreference = "Continue"

if ($args.Length -eq 0) {
  Write-Verbose '$args.Length -eq 0'
  throw
}

Set-Location -LiteralPath (Split-Path -LiteralPath $args[0])

# Group-Objectコマンドレットが出力するGroupInfoオブジェクトを並べ替える
Add-Type -Language CSharp -TypeDefinition @'
namespace MyNS {
  using Microsoft.PowerShell.Commands;
  using System.Collections.Generic;
  using System.Runtime.InteropServices;

  public class StrCmpLWGrInfoName : IComparer<GroupInfo> {
    public int Compare(GroupInfo x, GroupInfo y) {
      return StrCmpLogicalW(x.Name, y.Name);
    }

    [DllImport("Shlwapi.dll", CharSet = CharSet.Unicode)]
    private static extern int StrCmpLogicalW(
      [MarshalAs(UnmanagedType.LPWStr)] string psz1, 
      [MarshalAs(UnmanagedType.LPWStr)] string psz2
    );
  }
}
'@ -ReferencedAssemblies Microsoft.PowerShell.Commands.Utility

# パス($args)からDirectoryInfoまたはFileInfoオブジェクトを作成する
$scriptBlock = {
  param([string[]]$paths)

  $pathDirGrInfos = [GroupInfo[]]@(
    $paths.Where({
      [Directory]::Exists($_)
    }).ForEach({
      [DirectoryInfo]::new($_)
    }) | Group-Object -Property Name
  )

  $pathFileGrInfos = [GroupInfo[]]@(
    $paths.Where({
      [File]::Exists($_)
    }).ForEach({
      [FileInfo]::new($_)
    }) | Group-Object -Property Name
  )

  $comparer = [MyNS.StrCmpLWGrInfoName]::new()

  $comparison = [Delegate]::CreateDelegate(
    [Comparison[GroupInfo]], 
    [MyNS.StrCmpLWGrInfoName]::new(), 
    "Compare"
  )

  [Array]::Sort($pathDirGrInfos, $comparer)
  [Array]::Sort($pathFileGrInfos, $comparison)

  $pso = [PSCustomObject]@{
    Directory = [DirectoryInfo[]]@()
    File = [FileInfo[]]@()
  }

  if ($pathDirGrInfos.Length -ne 0) {
    $pso.Directory = [DirectoryInfo[]]@($pathDirGrInfos.Group)
  }

  if ($pathFileGrInfos.Length -ne 0) {
    $pso.File = [FileInfo[]]@($pathFileGrInfos.Group)
  }

  if (($pso.Directory.Length + $pso.File.Length) -ne $paths.Length) {
    throw
  }

  return $pso
}

$argPSO = &$scriptBlock -paths $args

# Array addition
$argFSIs = @($argPSO.Directory + $argPSO.File)

# ディレクトリとファイルに分ける
$argDirs = @($argFSIs | Where-Object Attributes -Match "Directory")
$argFiles = @($argFSIs | Where-Object Attributes -NotMatch "Directory")

# Pre-sized array
$psoDirs = [Object[]]::new($argDirs.Length)
$psoFiles = [Object[]]::new($argFiles.Length)

for ($i = 0; $i -lt $psoDirs.Length; $i++) {
  $psoDirs[$i] = [PSCustomObject]@{
    Name = $argDirs[$i].Name
    Attr = "D"
    Nat = $i + 1
    BaseName = [Path]::GetFileNameWithoutExtension($argDirs[$i].FullName)
    Ext = ""
    Dir = [Path]::GetDirectoryName($argDirs[$i].FullName)
  }
}

for ($i = 0; $i -lt $psoFiles.Length; $i++) {
  $psoFiles[$i] = [PSCustomObject]@{
    Name = $argFiles[$i].Name
    Attr = "F"
    Nat = $i + 1
    BaseName = [Path]::GetFileNameWithoutExtension($argFiles[$i].FullName)
    Ext = [Path]::GetExtension($argFiles[$i].FullName)
    Dir = [Path]::GetDirectoryName($argFiles[$i].FullName)
  }
}

$psoDirs = @($psoDirs | Sort-Object -Property Name)
$psoFiles = @($psoFiles | Sort-Object -Property Name)

for ($i = 0; $i -lt $psoDirs.Length; $i++) {
  $psoDirs[$i] | Add-Member -NotePropertyMembers @{
    Sort = $i + 1
  }
}

for ($i = 0; $i -lt $psoFiles.Length; $i++) {
  $psoFiles[$i] | Add-Member -NotePropertyMembers @{
    Sort = $i + 1
  }
}

$psoDirs = @($psoDirs | Sort-Object -Property Nat)
$psoFiles = @($psoFiles | Sort-Object -Property Nat)

@($psoDirs | Select-Object -Property @(
  "Attr", "Nat", "Sort", "Name"
)) | Format-Table -AutoSize

@($psoFiles | Select-Object -Property @(
  "Attr", "Nat", "Sort", "Name"
)) | Format-Table -AutoSize

$psObjs = @($psoDirs + $psoFiles)

for ($i = 0; $i -lt $psObjs.Length; $i++) {
  $psObjs[$i] | Add-Member -NotePropertyMembers @{
    NewBaseName = "=CONCAT(`"`",TEXT(B$($i+2),`"000`"),`")`",`"__['`",D$($i+2),`"']__`")"
    NewExt = "=E$($i+2)"
    WhatIf = "=`"-WhatIf`""
    Str = "=`"Rename-Item `" & H$($i+2) & `" -LiteralPath `" & `"'`" & SUBSTITUTE(J$($i+2) & `"\`" & D$($i+2) & E$($i+2), `"'`", `"''`") & `"'`" & `" -NewName `" & `"'`" & SUBSTITUTE(F$($i+2) & G$($i+2), `"'`", `"''`") & `"'`""
  }
  $psObjs[$i].BaseName = "=`"$($psObjs[$i].BaseName)`""
  $psObjs[$i].Ext = "=`"$($psObjs[$i].Ext)`""
  $psObjs[$i].Dir = "=`"$($psObjs[$i].Dir)`""
}

$psObjs = @($psObjs | Select-Object -Property @(
  "Attr"
  "Nat"
  "Sort"
  "BaseName"
  "Ext"
  "NewBaseName"
  "NewExt"
  "WhatIf"
  "Str"
  "Dir"
))

$csv = $psObjs | ConvertTo-Csv -NoTypeInformation
$csv | Set-Clipboard

参考資料リンク

過去記事

dy100ms.hatenadiary.jp