コンテンツへスキップ

rename-item <古いファイルサイズ名> -newName <新しいファイル名>

ファイル名に括弧がある場合は、-LiteralPathを付けないとうまくいかない。
rename-item -LiteralPath <古いファイルサイズ名> -newName <新しいファイル名>

ディレクトリ内のファイル名を一括変更する場合

foreach( $file in get-childitem){
    if (!($file.name | select-string 'InterFM')){
        continue
    } 

    $newName = $file.name.replace('[InterFM897]','')
    $newName = $newName.replace('(TimeFree)','')
    $newName = $newName.replace('1800.mp3','.mp3')

    if (Test-Path $newName) {
        write-host('既にファイルが存在します。[{0}],[{1}]' -F $file.name, $newName)
        remove-item -LiteralPath $file.name
    } else {
        rename-item -LiteralPath $file.name -newName $newName
    }
}

 

■変更前
[InterFM897]Barakan Beat(TimeFree)_201904141800.mp3
■変更後
Barakan Beat_20190414.mp3

# logger.ps1
class Logger{
    [string]$file
    [bool]$isWriteHost
    [bool]$isOutputFile

    Logger(){
        $this.isWriteHost = $TRUE
        $this.isOutputFile = $TRUE
    }

    [void]setLogfile([string]$file){
        $this.file = $file
    }

    [string]buildMessage([string]$message){
        $dt = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
        $message = ("{0} $message" -f $dt, $message)
        return $message
    }

    [void]outputFile([string]$message){
        if($this.isOutputFile){
            write-output $message | Add-content $this.file -Encoding Default
        }
    }

    [void]outputConsole([string]$message){
        if($this.isWriteHost){
            write-host($message)
        }
    }
}

class MyLogger:Logger{
    [int]$logLevel
    [System.Collections.Hashtable]$hash_level

    logger(){
        $this.logLebel = 1
    }

    [void]setLevel([string]$level){
        $this.hash_level = new-object System.Collections.Hashtable
        $this.hash_level."DEBUG" = 1
        $this.hash_level."INFO" = 2
        $this.hash_level."WARNING" = 3
        $this.hash_level."ERROR" = 4
        $this.hash_level."CRITICAL" = 5
        $this.logLevel = $this.hash_level.$level
    }

    [void]output([string]$message,[int]$num){
        $num = $num
        $message = $this.buildMessage($message)
        if($num -ge $this.logLevel){
            $this.outputFile($message)
            $this.outputConsole($message)
        }
    }

    [void]debug([string]$msg){
        $message = ("DEBUG: {0}" -f $msg)
        $this.output($message,1)
    }
    [void]info([string]$msg){
        $message = ("INFO: {0}" -f $msg)
        $this.output($message,2)
    }
    [void]warning([string]$msg){
        $message = ("WARNING: {0}" -f $msg)
        $this.output($message,3)
    }
    [void]error([string]$msg){
        $message = ("ERROR: {0}" -f $msg)
        $this.output($message,4)
    }
    [void]critical([string]$msg){
        $message = ("CRITICAL: {0}" -f $msg)
        $this.output($message,5)
    }
}

使い方

function outlog(){
    $logger.debug("test loglevel is debug.")
    $logger.info("test loglevel is info.")
    $logger.warning("test loglevel is warning.")
    $logger.error("test loglevel is error.")
    $logger.critical("test loglevel is critical.")
}

. E:\my-document\Programing\powershell\logger.ps1

del .\test.log

#-------------------------------------#
$logfile = "test.log"
$logger = new-object MyLogger
$logger.setLogfile($logfile)

write-host("LogLevel = 指定なし(DEBUG), isWriteHost = 指定なし(TRUE), isOutputFile = 指定なし()TRUE")
outlog
sleep(5)

write-host("")
write-host("LogLevel = WARNING, isWriteHost = TRUE, isOutputFile = TRUE")
$logger.isWritehost = $TRUE
$logger.isOutputFile = $TRUE
$logger.setLevel("WARNING")
outlog
sleep(5)

write-host("")
write-host("LogLevel = CRITICAL, isWriteHost = FALSE, isOutputFile = TRUE")
$logger.isWritehost = $FALSE
$logger.isOutputFile = $TRUE
$logger.setLevel("CRITICAL")
outlog
sleep(5)

write-host("")
write-host("LogLevel = DEBUG, isWriteHost = TRUE, isOutputFile = FALSE")
$logger.isWritehost = $TRUE
$logger.isOutputFile = $FALSE
$logger.setLevel("DEBUG")
outlog
sleep(5)

 

Excelを簡単に扱えるように、
モジュールを作ってみた。

ExcelHandlerクラス

class ExcelHandler{
    [object]$excel
    [object]$book
    [object]$sheet
    [int]$lastCol
    [int]$lastRow
    [array]$sheetList

    ExcelHandler(){
        $this.excel = New-Object -ComObject Excel.Application
        $this.excel.Visible = $false
    }

    [void]setBook([string]$file){
        $this.book =$this.excel.Workbooks.Open($file)
        $s = @()
        $this.sheetList = $this.book.Sheets | ForEach-Object {
             $s += $_.Name
        }
        $this.sheetList = $s
    }

    [array]getSheetList(){
        return $this.sheetList
    }
    
    [void]setSheet([string]$sheet){
        $this.sheet = $this.book.worksheets.Item($sheet)
        $this.lastRow = $this.sheet.UsedRange.Rows.Count
        $this.lastCol = $this.sheet.UsedRange.Columns.Count
    }

    [int]getLastCol(){
        return $this.lastCol
    }

    [int]getLastRow(){
        return $this.lastRow
    }

    [void]quit(){
        $this.book.Close()
        $this.excel.Quit()
        if($this.sheet){
            [void][System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($this.sheet)
        }
        [void][System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($this.excel)
    }
}

class Excel2Csv : ExcelHandler{
    [void]outputCsvByKeyword([string]$ColKeyword, [int]$ColKeywordRow, [array]$ParamRows){
        $col = 1
        while($col -le $this.lastCol){
            $s = $this.sheet.cells.item($ColKeywordRow, $col).text
            if ($s -match $ColKeyword){
                $line = ''
                foreach($j in $ParamRows){
                    $val = $this.sheet.cells.item($j,$col).text
                    $line += $val + ","
                }
                $line = $line -replace ",$",""
                write-host($line)
            }
            $col += 1
        }
    }

    [void]outputCsvAllCell(){
        $col = 1
        while($col -le $this.lastcol){
            $line = ''
            $row = 1
            while($row -le $this.lastRow){
                $val = $this.sheet.cells.item($row,$col).text
                $line += $val + ","
                $row += 1
            }
            $line = $line -replace ",$",""
            write-host($line)
            $col += 1
        }
    }
}

使い方

param(
    [parameter(mandatory=$false)][ValidateScript({Test-Path $_})]
    [string]$BookFile = "E:\my-document\Book1.xlsx",
    [parameter(mandatory=$false)][string]$SheetName = "sheet1",
    [parameter(mandatory=$false)][string]$ColKeyword = "値",
    [parameter(mandatory=$false)][int]$ColKeywordRow = 1,
    [parameter(mandatory=$false)][array]$ParamRows = (2,3,5)
)

. E:\my-document\ExcelHandler.ps1
$e = New-Object Excel2Csv
$e.setBook($BookFile)
$e.setSheet($SheetName)
write-host("対象列にキーワードを含む行を対象")
$e.outputCsvByKeyword($ColKeyword, $ColKeywordRow, $ParamRows)
write-host("全てのセルを表示")
$e.outputCsvAllCell()
write-host("bookに含むシートを表示")
$e.getSheetList()
write-host("-----")
$e.setSheet("Sheet3")
write-host("対象列にキーワードを含む行を対象")
$e.outputCsvByKeyword($ColKeyword, $ColKeywordRow, $ParamRows)
write-host("全てのセルを表示")
$e.outputCsvAllCell()
write-host("対象列にキーワードを含む行を対象")
$e.outputCsvByKeyword("デフォルト", $ColKeywordRow, $ParamRows)

$e.quit()

実行スクリプト

. E:\my-document\エクセル\TestClass.ps1
$t = new-object TestClass
. E:\my-document\ExcelHandler.ps1
$e = new-object ExcelHandler
$e.openbook("","visible")

$ColOperationDataFileName           = 1
$ColOperationDataCategory           = 2
$ColOperationDataParameter          = 3
$ColParameterSheetParameterFileName = 4
$ColParameterSheetCategory          = 5
$ColParameterSheetParameter         = 6

# 作業手順書データをエクセルに出力する。
$e.setSheet("sheet1")
$e.InsertSheetCellsItem($t.OperationDataArrayList(),1,1)
$e.sheet.Columns.AutoFit()|out-null

# パラメータシートのデータをエクセルに出力する。
$e.setSheet("sheet2")
$e.InsertSheetCellsItem($t.ParameterSheetDataArrayList(),1,1)
$e.sheet.Columns.AutoFit() |out-null

# 突合チェック
# 突合用シート作成
$e.setSheet("sheet3")
<# タイトル列を作成する。#>
$e.sheet.cells.item(1,1) = "作業手順書名"
$e.sheet.cells.item(1,2) = "作業手順書カテゴリ"
$e.sheet.cells.item(1,3) = "作業手順書パラメータ"
$e.sheet.cells.item(1,4) = "パラメータファイル名"
$e.sheet.cells.item(1,5) = "カテゴリ"
$e.sheet.cells.item(1,6) = "パラメータ"
$e.sheet.cells.item(1,7) = "host1"
$e.sheet.cells.item(1,8) = "host2"
$e.sheet.cells.item(1,9) = "host3"
$e.setColorCells(3,1,1,9)

<#
パラメータシートから、以下の列がユニークな行を出力する。
チェックする列
 - パラメータ名
  - 仕様書カテゴリ
  - 仕様書パラメータ
#>
$row=2
$CheckedData = new-object System.collections.arrayList
$t.ParameterSheetDataArrayList() | select-object -skip 1 | foreach-object {
    $col = 3
    $parameterName = $_[0].Trim()
    $category      = $_[2].Trim()
    $parameter     = $_[3].Trim()
    $data = $parameterName + $category + $parameter
    write-output("[{0}]:sheet3に出力:[{1}]" -f $row,$data)
    if(!($CheckedData.contains($data))){
        $e.sheet.cells.item($row,$ColParameterSheetParameterFileName) = $parameterName
        $e.sheet.cells.item($row,$ColParameterSheetCategory) = $category
        $e.sheet.cells.item($row,$ColParameterSheetParameter) = $parameter
        $CheckedData.add($data) | out-null
        $row++
    } else {
        write-output("[{0}]既に含まれる:[{1}]" -f $row, $data)
    }
}

$e.sheet.Columns.AutoFit() |out-null

<#
sheet1のデータとsheet2のデータを比較する。
存在しない作業手順書は行の最終行に追加する。
#>
# 再度シート3を読み込む
$e.setSheet("sheet3") 
$lastRow = $e.getLastRow()

$NotExistOperationData = new-object System.collections.arrayList

$t.OperationDataArrayList() | select-object -skip 1 | foreach-object {
    $OperationData = $_
    $isExist = "FALSE"
    $OperationDataFileName  = $_[0]
    $OperationDataCategory  = $_[1]
    $OperationDataParameter = $_[2]

    $strRegex = [regex]::Escape($strRegex)
    $strRegex = $OperationDataCategory -replace "<n>$","[0-9]+"
    $strRegex = "^" + $strRegex + "$"
    $categoryRegex = [regex]$strRegex
    #write-output("手順書名=[{0}]:categoryRegex=[{1}],parameter=[{2}]" -f $OperationDataFileName,$strRegex,$OperationDataParameter)
    for($row=2;$row -le $lastRow;$row++){
        $parameterFileName, $category, $parameter = $e.getCellItemText($row,4,6)
        write-output("比較:[{0}]:[{1}][{2}({3})][{4}]:[{5}][{6}][{7}]" -f $row,
                                                                         $OperationDataFileName,
                                                                         $OperationDataCategory,
                                                                         $strRegex,
                                                                         $OperationDataParameter,
                                                                         $parameterFileName,
                                                                         $category,
                                                                         $parameter)
        if( ($OperationDataFileName -match "^本番" ) -and ($parameterFileName -match "^検証") ){
            write-output("本番スキップ:[{0}],[{1}]" -f $OperationDataFileName,$parameterFileName)
            continue
        } elseif ( ($OperationDataFileName -match "^検証") -and ($parameterFileName -match "^本番") ){
            write-output("検証スキップ:[{0}],[{1}]" -f $OperationDataFileName,$parameterFileName)
            continue
        } else {
            write-output("---")
        }
         
        if (($category -match $categoryRegex) -and ($OperationDataParameter -eq $parameter)) {
            write-output("追加:[{0}]:[{1}][{2}][{3}]" -f $row,$OperationDataFileName,$OperationDataCategory,$OperationDataParameter)
            $e.sheet.cells.item($row,$ColOperationDataFileName) = $OperationDataFileName
            $e.sheet.cells.item($row,$ColOperationDataCategory) = $OperationDataCategory
            $e.sheet.cells.item($row,$ColOperationDataParameter) = $OperationDataParameter
            $isExist = "TRUE"
        } else {
            write-output("追加なし")
        }
    }
    if($isExist -eq "FALSE"){
        $NotExistOperationData.add(@($OperationData))
    }
}

$e.setSheet("sheet3")
$row = $e.getLastRow() + 1

$NotExistOperationData | foreach-object {
    $OperationDataFileName = $_[0]
    $OperationDataCategory = $_[1]
    $OperationDataParameter = $_[2]
    $e.sheet.cells.item($row,$ColOperationDataFileName) = $OperationDataFileName
    $e.sheet.cells.item($row,$ColOperationDataCategory) = $OperationDataCategory
    $e.sheet.cells.item($row,$ColOperationDataParameter) = $OperationDataParameter
    $row++
}
class TestClass{
    TestClass(){}

    [system.collections.ArrayList]GetSeetArrayList(){
        $ArrayList = new-object system.collections.ArrayList
        $ArrayList.add("host1")
        $ArrayList.add("host2")
        $ArrayList.add("_host1")
        $ArrayList.add("_host2")
        return $ArrayList
    }

    [system.collections.ArrayList]OperationDataArrayList(){
        $ArrayList = new-object system.collections.ArrayList
        $ArrayList.Add(@("filename","category","parameter"))
        $ArrayList.Add(@("本番環境_osインストール手順書","os","ホスト名"))
        $ArrayList.Add(@("本番環境_osインストール手順書","os","IPアドレス"))
        $ArrayList.Add(@("本番環境_osインストール手順書","os","アカウント"))
        $ArrayList.Add(@("共通環境_javaインストール手順書","java<n>","version"))
        $ArrayList.Add(@("共通環境_javaインストール手順書","java<n>","ディレクトリ"))
        $ArrayList.Add(@("検証環境_osインストール手順書","os","ホスト名"))
        $ArrayList.Add(@("検証環境_osインストール手順書","os","IPアドレス"))
        $ArrayList.Add(@("検証環境_osインストール手順書","os","アカウント"))
        $ArrayList.Add(@("検証環境_osインストール手順書","os1","アカウント"))
        return $ArrayList
    }

    [system.collections.ArrayList]ParameterSheetDataArrayList(){
        $ArrayList = new-object System.Collections.ArrayList
        $ArrayList.Add(@("パラメータ名",      "シート名", "カテゴリ", "パラメータ", "値"))
        $ArrayList.Add(@("本番環境パラメータ", "host1",   "os",      "ホスト名",     "maccarel"))
        $ArrayList.Add(@("本番環境パラメータ", "host1",   "os",      "IPアドレス",   "1.1.1.1"))
        $ArrayList.Add(@("本番環境パラメータ", "host1",   "java1",   "version",     "7"))
        $ArrayList.Add(@("本番環境パラメータ", "host1",   "java1",   "ディレクトリ", "/usr/java7"))
        $ArrayList.Add(@("本番環境パラメータ", "host1",   "java2",   "version",     "6"))
        $ArrayList.Add(@("本番環境パラメータ", "host1",   "java2",   "ディレクトリ", "/usr/java6"))
        $ArrayList.Add(@("本番環境パラメータ", "host1",   "os",      "アカウント", "user1"))
        $ArrayList.Add(@("検証環境パラメータ", "host2",   "os",      "ホスト名",     "sarmon"))
        $ArrayList.Add(@("検証環境パラメータ", "host2",   "os",      "IPアドレス",   "2.2.2.2"))
        $ArrayList.Add(@("検証環境パラメータ", "host2",   "os1",     "アカウント",   "2.2.2.2"))
        $ArrayList.Add(@("検証環境パラメータ", "host2",   "os2",     "アカウント",   "2.2.2.2"))
        $ArrayList.Add(@("本番環境パラメータ", "host3",   "os",      "ホスト名",     "bonito"))        
        $ArrayList.Add(@("本番環境パラメータ", "host3",   "osmisscategory",    "手順書にないパラメータ",     "手順書になし"))        
        return $ArrayList
    }
}

class ExcelHandler{
    [object]$excel
    [object]$book
    [object]$sheet
    [int]$lastCol
    [int]$lastRow
    [array]$sheetList

    ExcelHandler(){
        $this.excel = New-Object -ComObject Excel.Application
    }

    [void]openbook([string]$bookfile,[string]$isVisible){
        if($isVisible -ne ""){
            $this.excel.Visible = $true
        }
        if($bookfile -ne ""){
            $this.book = $this.excel.Workbooks.Open($bookfile)
        } else {
            $this.book = $this.excel.workbooks.add()
        }

    }

    <# setBook
    [void]setBook([string]$file){
        $this.book =$this.excel.Workbooks.Open($file)
        $s = @()
        $this.sheetList = $this.book.Sheets | ForEach-Object {
             $s += $_.Name
        }
        $this.sheetList = $s
    }
    #>

    [array]getSheetList(){
        return $this.book.Sheets
    }
    
    [void]setSheet([string]$sheet){
        $this.sheet   = $this.book.worksheets.Item($sheet)
        $this.lastRow = $this.sheet.UsedRange.Rows.Count
        $this.lastCol = $this.sheet.UsedRange.Columns.Count
    }

    [int]getLastCol(){
        return $this.lastCol
    }

    [int]getLastRow(){
        return $this.lastRow
    }

    [void]quit(){
        $this.book.Close()
        $this.excel.Quit()
        if($this.sheet){
            [void][System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($this.sheet)
        }
        [void][System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($this.excel)
    }

    [array]getCellItemText([int]$row,[int]$ColStart, [int]$ColEnd){
        $array = @()
        for($i=$ColStart; $i -le $ColEnd;$i++){
            $array += $this.sheet.cells.item($row,$i).text
        }
        return $array
    }

    [void]InsertSheetCellsItem([system.collections.ArrayList]$data,[int]$startRow,[int]$startCol){
        $data | foreach-object {
            $ChiledData = $_
            $col = $startCol
            $ChiledData  | foreach-object {
                $this.sheet.Cells.Item($startRow,$col) = $_
                $col++
            }
            $startRow++
        }
    }

    [void]setColorCells([int]$color, [int]$row, [int]$startCol, [int]$endCol) {
        for($col=$startCol;$col -le $endCol;$col++) {
            $this.sheet.Cells.Item($row,$col).Interior.ColorIndex = $color
        }
    }
}

 

作成中

<# 
    パラメータ
    # network
    ifName,ifIndex,Ipaddress,MacAddress,Gateway
    # bonding
    @(ifIndex1,ifIndex2),Ipaddress,MacAddress,Gateway
    # routing
#>

$server = New-Object System.Collections.Hashtable

# ルーティング
$routing1 = ("aのルーティング","bのルーティング")
$routing2 = "cのルーティング"

$server.s001 = @{
    # key は ifIndex
    "network" = @{
        1 = @{
            "name"      = "インターフェース1";
            "ipaddress" = "192.168.1.100";
            "netmask"   = "255.255.255.0";
        };
        2 = @{
            "name"      = "インターフェース2";
            "ipaddress" = "192.168.2.100";
            "netmask"   = "255.255.255.0";
        };
        3 = @{
            "name"      = "インターフェース3";
            "ipaddress" = "";
            "netmask"   = "";
        };
        4 = @{
            "name"      = "インターフェース4";
            "ipaddress" = "";
            "netmask"   = "";
        };
    };

    "bonding" = @{ 
        1 = @{
            "interfaceList" = @(3,4);
            "ipaddress"     = "192.168.3.100";
            "netmask"       = "255.255.255.0";
        };
    };

    "routing" = @($routing1,$routing2);
}

$server.s002 = @{
    "network" = @{
        1 = @{
            "name"      = "インターフェース1";
            "ipaddress" = "192.168.1.101";
            "netmask"   = "255.255.255.0";
        };
        2 = @{
            "name"      = "インターフェース2";
            "ipaddress" = "192.168.2.101";
            "netmask"   = "255.255.255.0";
        };
        3 = @{
            "name"      = "インターフェース3";
            "ipaddress" = "";
            "netmask"   = "";
        };
        4 = @{
            "name"      = "インターフェース4";
            "ipaddress" = "";
            "netmask"   = "";
        };
    };

    "bonding" = @{ 
        1 = @{
            "interfaceList" = @(3,4);
            "ipaddress"     = "192.168.3.101";
            "netmask"       = "255.255.255.0";
        };
    };

    "routing" = $routing;
}
<#
#>

param(
    $servername
)

#
$s = $server.$servername

# Network Adapter設定
write-host("# ネットワークアダプター設定")
foreach($key in $s.network.keys) {
    $nic = $s.network.$key
    if($nic.ipaddress){
        write-host("ifIndex=[{0}],name=[{1}],ipaddress=[{2}],netmask=[{3}]" -f $key,$nic.name,$nic.ipaddress,$nic.netmask)
    }
}

# Bonding設定
write-host("# Bonding設定")
foreach($key in $s.bonding.keys){
    $bond = $s.bonding.$key
    if($bond.ipaddress){
        write-host("bondInterface=[{0}],ipaddress=[{1}],netmask=[{2}]" -f ($bond.interfaceList -join ","),$bond.ipaddress,$bond.netmask)
    }
}

# ルーティング設定
write-host("# Routhing設定")
if($routing){
        write-host("[{0}]" -f $routing)
}