Replace-ExcelSheetVariable.ps1

param ( $TemplateFile, $Destination, $Styles, $Values )

$ErrorActionPreference = 'Stop'

$TemplateFile = 'C:\Users\Administrator\Desktop\Source.xlsx'
$Destination  = 'C:\Users\Administrator\Desktop\Output.xlsx'
$Styles = '
{
    "マイ シート": {
        "MyArea1": "Variable1",
        "MyArea2": "Variable2"
    }
}' | ConvertFrom-Json

$Values = '
{
    "Variable1": "書き込みテスト値1",
    "Variable2": "書き込みテスト値2"
}' | ConvertFrom-Json

try {
    $Excel      = New-Object -ComObject Excel.Application
    $Workbooks  = $Excel.Workbooks
    $Workbook   = $Workbooks.Open($TemplateFile)
    $Worksheets = $Excel.Worksheets

    foreach ( $SheetName in $Styles | gm -MemberType NoteProperty | % Name ) {
        $Worksheet = $Worksheets.Item($SheetName)
        try {
            $Worksheet.Activate()
            $SheetStyle = $Styles.$SheetName
            foreach ( $AreaName in $SheetStyle | gm -MemberType Properties | % Name ) {
                $Range = $Worksheet.Range($AreaName)
                try {
                    $ValueName = $SheetStyle.$AreaName
                    $Value     = $Values.$ValueName
                    write "[$($AreaName)] $($ValueName)($($Value))"
                    $Range.Value2 = $Value
                } finally {
                    [Void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($Range); rv Range
                }
            }
        } finally {
            [Void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($Worksheet); rv Worksheet
        }
    }
    #$Excel.DisplayAlerts = $false
    $Workbook.SaveAs($Destination)
} finally {
    if ($Workbook)   { $Workbook.Close(0) }
    if ($Excel)      { $Excel.Quit() }
    if ($Worksheets) { [Void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($Worksheets); rv Worksheets }
    if ($Workbook)   { [Void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($Workbook);   rv Workbook   }
    if ($Workbooks)  { [Void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($Workbooks);  rv Workbooks  }
    if ($Excel)      { [Void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel);      rv Excel      }
}