コンテンツへスキップ

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)

 

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