コンテンツへスキップ

Powershell Excelその4

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