コンテンツへスキップ

$oldSystemPath = [System.Environment]::GetEnvironmentVariable("path","Machine")
$newSystemPath = $oldSystemPath + ";" + <追加したいパス>
[System.Environment]::SetenvironmentVariable("Path",$newSystemPath,"Machine")

パスの確認

[System.Environment]::GetEnvironmentVariable("path","Machine") -split ";"

# 1Mバイトのファイルを作成
@(1..10) | Foreach{
    $random_bin = new-object byte[] (1024*1024); (new-object Random).NextBytes($random_bin); [IO.File]::WriteAllBytes("c:\tmp\test$_.dat", $random_bin)
}
# 最終更新日が任意のファイルを作成
for($i=0;$i -le 90;$i++){
    $file = $i.tostring() + ".log"
    $filepath = (join-path D:\log $file)
    new-item $filepath |out-null
    $j = "-" + $i
    Set-ItemProperty $filepath -Name LastWriteTime -Value (get-date).addDays($j) 
}

 

$a=@(3,2,1,5,4,8)
$b=@(4,2,3,1,5,6)
Compare-Object ($a|sort-object) ($b|sort-object) -IncludeEqual | foreach-object {
    $direction = $_.SideIndicator
    $value = $_.InputObject
    if( $direction -eq "<="){
        write-host("aにだけあり[{0}]" -f $value)
    } elseif ( $direction -eq "=>"){
        write-host("bにだけあり[{0}]" -f $value)
    } else {
        write-host("aとbにあり[{0}]" -f $value)
    }
}

 

# download itextsharp.dll.
# Invoke-WebRequest -Uri 'https://github.com/itext/itextsharp/releases/download/5.5.10/itextsharp-all-5.5.10.zip'

param(
    [parameter(mandatory=$false)][string]$ITextLibraryPath = "E:\tmp\itextsharp.dll",
    [parameter(mandatory=$false)][string]$InputFilePath    = "E:\tmp\input.pdf",
    [parameter(mandatory=$false)][string]$OutputFilePath   = "E:\tmp\output.txt"
)

[System.Reflection.Assembly]::LoadFrom($ITextLibraryPath) | Out-Null
$reader = New-Object iTextSharp.text.pdf.PdfReader -ArgumentList $InputFilePath
$pages = $reader.numberofpages

for($i=1;$i -le $pages;$i++){
    write-output("=============== page=[{0}] ===============" -f $i) >>$OutputFilePath
    [iTextSharp.text.pdf.parser.PdfTextExtractor]::GetTextFromPage($reader,$i) >> $OutputFilePath
}

 

bookから全シートの全データを抽出する。

■使い方

. .\ExcelDataClass.ps1

$e = new-object ExcelDataClass("c:\test.xlsx")

$e.ShowAllData()

■ExcelDataClass.ps1のコード

class ExcelDataClass{
    [string]$BookFilePath
    [System.Collections.Hashtable]$AllDataHash

    ExcelDataClass([string]$filepath){
        $this.BookFilePath = $filepath
        $excel = New-Object -ComObject Excel.Application
        $workbook = $excel.workbooks.open($filepath)
        $SheetList = new-object System.collections.arrayList
        $workbook.Sheets | foreach-object {
            $SheetName = $_.name
            $SheetList.add($SheetName)
        }
        $this.AllDataHash = new-object System.Collections.Hashtable
        foreach($SheetName in $SheetList){
            $ColHash = new-object System.Collections.Hashtable
            $sheet = $workbook.worksheets.Item($SheetName)
            $lastRow = $sheet.UseDrange.Rows.Count
            $lastCol = $sheet.UsedRange.Columns.Count
            for($col=1;$col -le $lastCol;$col++){
                $RowHash = new-object System.Collections.Hashtable
                for($row=1;$row -le $lastRow;$row++){
                    $val = $sheet.Cells.Item($row,$col).text.trim()
                    if($val -eq ""){
                        $val = "-"
                    }
                    $RowNum = ([string]$row).PadLeft(3,"0")
                    $RowHash.$RowNum = $val
                }
                $ColNum = ([string]$col).PadLeft(4,"0")
                $ColHash.$ColNum = $RowHash             
            }
            [void][System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($sheet)
            $this.AllDataHash.$SheetName = $ColHash
        }
        $workbook.close()
        $excel.Quit()
        [void][System.Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel)
    }

    [void]ShowAllData(){
        $this.AllDataHash.keys.getEnumerator() | sort-object value | foreach-object {
            $SheetName = $_
            $this.AllDataHash.$SheetName.keys.getEnumerator() | sort-object |foreach-object {
                $Col = $_
                $this.AllDataHash.$SheetName.$Col.keys.getEnumerator() | sort-object |foreach-object {
                    $Row = $_
                    $this.AllDataHash.$SheetName.$Col.$Row | foreach-object {
                        $v = $_
                        write-host("Sheet name:[{0}],Col:[{1}],Row:[{2}],Value:[{3}]" -f $SheetName,$Col,$Row,$v)
                    }
                }
            }
        }
    }

    [int]GetSheetLastCol([string]$sheetName){
        return $this.AllDataHash.$sheetName."0001".count
    }

    [int]GetSheetLastRow([string]$sheetName){
        return $this.AllDataHash.$sheetName.count
    }

    [system.collections.ArrayList]GetSheetList(){
        $a = new-object system.collections.ArrayList
        foreach($sheetName in $this.AllDataHash.keys){
            $a.add($sheetName)
        }
        return $a
    }
}

 

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $true


# 既存のブックを開く場合
$book = $excel.Workbooks.Open($bookfile)
# 新規の場合
$book = $excel.workbooks.add()

# シート追加
$excel.Worksheets.Add()

# シートの名前を変更
$book.Sheets(1).Name = "その1"
$book.Sheets(2).Name = "その2"
$book.Sheets(3).Name = "その3"


# セルに入力
$sheet = $book.Sheets("その1")
# $sheet.Cells.Item(行,列)
$sheet.Cells.Item(1, 2) = "テスト"
$sheet.Cells.Item(1, 3) = "テスト2"
$sheet.Cells.Item(1, 4) = "テスト3"
$sheet.Cells.Item(2, 2) = "テスト3--------------------------"

# 罫線を引く
$sheet.Range("A1:C3").Borders.LineStyle = 1

# 列の幅を自動調整
$sheet.Columns.AutoFit()

# セルの色を変更する。
$sheet.Cells.Item( 2, 2).Interior.ColorIndex = 3

# 終了
$book.SaveAs("${HOME}\Desktop\test.xlsx")
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)

 

param(
    [parameter(mandatory=$true)][string]$filename
)

#$filename = "E:\my-document\powershell+word\テスト.docx"

$objWord = New-Object -Com Word.Application
$objWord.Visible = $false
$objDocument = $objWord.Documents.Open($filename)
$paras = $objDocument.Paragraphs

$pattern = "\[.*\]:.*\[.*\]"

foreach ($para in $paras) 
{ 
    #Write-Output $para.Range.Text
    $line = $para.Range.Text
    $line = $line.trim()
    if ($line -match $pattern) {
        write-output "Patternを含む=[" + $line + "]"
    } else {
        write-output "Pattern含まない=[" + $line + "]"
    }
}

 

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()