コンテンツへスキップ

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

 

import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
type(wb)

wb.get_sheet_names()
sheet=wb.get_sheet_by_name('sheet3')
sheet
sheet.title
another_sheet = wb.active
another_sheet

# シートからセルを取得する。
sheet['A1']
sheet['A1'].value
c = sheet['b1']
c.value

sheet.cell(row=1, column=2).value
for i in range(1, 8, 2):
    print(i, sheet.cell(row=i, column=2).value)

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