Edit-ReservationWorkbook.ps1

#Requires -Version 5.1
<#
.SYNOPSIS
    Cleans up a reservation workbook: drops column C, stamps the delivery code and
    renames the production version column to STATUS.

.DESCRIPTION
    PowerShell port of src/vbs/ReservationExcelEdit.vbs.

    Works against a workbook that is already open in a running Excel instance,
    which is how the VBScript chain behaved. The Korean header names are
    parameters with defaults, so the same script covers an English export too.

    Returns the number of data rows found.

    This file is stored as UTF-8 with a BOM: Windows PowerShell 5.1 assumes the
    ANSI code page for BOM-less files and would mangle the Korean defaults.

.PARAMETER WorkbookName
    File name of the open workbook. A full path is accepted and reduced to its
    leaf, because Excel indexes the Workbooks collection by file name.

.PARAMETER SupplierCode
    Value written into every data row of the delivery column.

.PARAMETER SheetName
    Worksheet to edit. Default "Sheet1".

.PARAMETER PlantHeader
    Header of the column used to measure how many data rows there are.

.PARAMETER DeliveryHeader
    Header of the column that receives SupplierCode.

.PARAMETER VersionHeader
    Header of the column that is renamed to STATUS and cleared.

.EXAMPLE
    .\Edit-ReservationWorkbook.ps1 -WorkbookName reservation.xlsx -SupplierCode 100023
#>
[CmdletBinding(SupportsShouldProcess)]
param(
    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$WorkbookName,

    [Parameter(Mandatory)]
    [ValidateNotNullOrEmpty()]
    [string]$SupplierCode,

    [string]$SheetName = 'Sheet1',
    [string]$PlantHeader = '플랜트',
    [string]$DeliveryHeader = '납품처',
    [string]$VersionHeader = '생산버전'
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

$HeaderRow = 1
$FirstDataRow = 2
$xlValues = -4163
$xlWhole = 1

function Get-HeaderColumn {
    param($Sheet, [string]$Header)

    $found = $Sheet.Rows($HeaderRow).Find($Header, [Type]::Missing, $xlValues, $xlWhole)
    if ($null -eq $found) { throw "Header not found on row ${HeaderRow}: $Header" }
    $found.Column
}

function Measure-DataRow {
    param($Sheet, [int]$Column)

    $row = $FirstDataRow
    while (-not [string]::IsNullOrWhiteSpace([string]$Sheet.Cells($row, $Column).Value2)) {
        $row++
    }
    $row - $FirstDataRow
}

function Set-ColumnValue {
    param($Sheet, [int]$Column, [int]$RowCount, $Value)

    if ($RowCount -lt 1) { return }

    # one range assignment instead of one COM round trip per cell
    $Sheet.Range(
        $Sheet.Cells($FirstDataRow, $Column),
        $Sheet.Cells($FirstDataRow + $RowCount - 1, $Column)
    ).Value2 = $Value
}

$excel = $null
$workbook = $null
$sheet = $null

try {
    try {
        $excel = [Runtime.InteropServices.Marshal]::GetActiveObject('Excel.Application')
    }
    catch {
        throw "No running Excel instance found. Open the workbook first (see ExcelOpen.vbs)."
    }

    $leaf = Split-Path -Path $WorkbookName -Leaf
    $workbook = $excel.Workbooks | Where-Object { $_.Name -eq $leaf } | Select-Object -First 1
    if ($null -eq $workbook) { throw "Workbook is not open in Excel: $leaf" }

    $sheet = $workbook.Worksheets.Item($SheetName)

    if (-not $PSCmdlet.ShouldProcess("$leaf/$SheetName", 'clean up reservation columns')) {
        return
    }

    $sheet.Range('C:C').EntireColumn.Delete() | Out-Null

    $rowCount = Measure-DataRow -Sheet $sheet -Column (Get-HeaderColumn -Sheet $sheet -Header $PlantHeader)

    Set-ColumnValue -Sheet $sheet -Column (Get-HeaderColumn -Sheet $sheet -Header $DeliveryHeader) `
        -RowCount $rowCount -Value $SupplierCode

    $versionColumn = Get-HeaderColumn -Sheet $sheet -Header $VersionHeader
    $sheet.Cells($HeaderRow, $versionColumn).Value2 = 'STATUS'
    Set-ColumnValue -Sheet $sheet -Column $versionColumn -RowCount $rowCount -Value ''

    $workbook.Save()

    Write-Output $rowCount
}
finally {
    # Excel itself is left running: the instance belongs to the caller.
    foreach ($comObject in @($sheet, $workbook, $excel)) {
        if ($null -ne $comObject) {
            [void][Runtime.InteropServices.Marshal]::ReleaseComObject($comObject)
        }
    }
}