コンテンツへスキップ

実行スクリプト

. 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
        }
    }
}