ライブラリファイル

function Open-ExcelWorkbook {
    param (
        [string]$FilePath
    )
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $workbook = $excel.Workbooks.Open($FilePath)
    $sheet = $workbook.Worksheets.Item("Sheet1")
    return ,$excel, $workbook, $sheet
}

function Get-ExcelCellValue {
    param (
        [object]$Sheet,
        [int]$Row,
        [int]$Column
    )
    $cell = $Sheet.Cells.Item($Row, $Column)
    return $cell.Value()
}

function Set-ExcelCellValue {
    param (
        [object]$Sheet,
        [int]$Row,
        [int]$Column,
        [string]$Value
    )
    $cell = $Sheet.Cells.Item($Row, $Column)
    $cell.Value() = $Value
}

function Save-ExcelWorkbook {
    param (
        [object]$Workbook,
        [string]$FilePath
    )
    $Workbook.SaveAs($FilePath)
}

function Close-ExcelWorkbook {
    param (
        [object]$Workbook,
        [object]$Excel
    )
    $Workbook.Close()
    $Excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
}

Export-ModuleMember -Function *-Excel*

ライブラリの使用例

Import-Module .\\Excel.psm1

$excel, $workbook, $sheet = Open-ExcelWorkbook -FilePath "C:\\path\\to\\your\\file.xlsx"

Set-ExcelCellValue -Sheet $sheet -Row 1 -Column 1 -Value "Hello, World!"

$value = Get-ExcelCellValue -Sheet $sheet -Row 1 -Column 1
Write-Host $value

Save-ExcelWorkbook -Workbook $workbook -FilePath "C:\\path\\to\\your\\file.xlsx"

Close-ExcelWorkbook -Workbook $workbook -Excel $excel