Convert specific table of excel sheet to JSON using PowerShell

ExcelToJson.ps1

param (
    # Excel name
    [Parameter(Mandatory=$true)]
    [string]$InputFileFullPath,
    # Sheet name
    [Parameter(Mandatory=$true)]
    [string]$SubjectName,
    # Identifier for the table
    [Parameter(Mandatory=$true)]
    [string]$ClassName
)

#region Open Excel file
$excelApplication = New-Object -ComObject Excel.Application
$excelApplication.DisplayAlerts = $false
$Workbook = $excelApplication.Workbooks.Open($InputFileFullPath)

# Find sheet
$sheet = $Workbook.Sheets | Where-Object { $_.Name -eq $SubjectName }

if (-not $sheet) {
    throw "Could not find subject '$SubjectName' in the workbook"
}
#endregion Open Excel file

#region Grab the table within sheet to work with
# Find the cell where Class name is mentioned
$found           = $sheet.Cells.Find($ClassName)
$beginAddress    = $Found.Address(0, 0, 1, 1).Split('!')[1]
$beginRowAddress = $beginAddress.Substring(1, 2)
# Header row starts 1 row after the class name
$startHeaderRowNumber = [int]$beginRowAddress + 2
# Student data row starts 1 row after header row
$startDataRowNumber = $startHeaderRowNumber + 1
$beginColumnAddress = $beginAddress.Substring(0,1)
# ASCII number of column
$startColumnHeaderNumber = [BYTE][CHAR]$beginColumnAddress - 65 + 1
#endregion Grab the table within sheet to work with

#region Extract Header Columns Name
$Headers          = @{}
$numberOfColumns  = 0
$foundHeaderValue = $true

while ($foundHeaderValue -eq $true) {
    $headerCellValue = $sheet.Cells.Item(
        $startHeaderRowNumber,
        ($numberOfColumns + $startColumnHeaderNumber)
    ).Text

    if ($headerCellValue.Trim().Length -eq 0) {
        $foundHeaderValue = $false
    } else {
        $numberOfColumns++
        if ($Headers.ContainsValue($headerCellValue)) {
            # Do not add any duplicate column again.
        } else {
            $Headers.$numberOfColumns = $headerCellValue
        }
    }
}
#endregion Extract Header Columns Name

#region Extract Student Information Rows
$results   = @()
$rowNumber = $startDataRowNumber
$finish    = $false

while ($finish -eq $false) {
    if ($rowNumber -gt 1) {
        $result = @{}

        foreach ($columnNumber in $Headers.GetEnumerator()) {
            $columnName = $columnNumber.Value
            # Student data row, student data column number
            $cellValue = $sheet.Cells.Item(
                $rowNumber,
                ($columnNumber.Name + ($startColumnHeaderNumber - 1))
            ).Value2

            if ($cellValue -eq $null) {
                $finish = $true
                break
            }

            $result.Add($columnName.Trim(),$cellValue.Trim())
        }

        if ($finish -eq $false) {
            # Adding Excel sheet row number for validation
            $result.Add("RowNumber",$rowNumber)
            $results += $result
            $rowNumber++
        }
    }
}
#endregion Extract Student Information Rows

#region Create JSON file and close Excel file
$inputFileName = Split-Path $InputFileFullPath -leaf
$inputFileName = $inputFileName.Split('.')[0]
# Output file name will be "ABCDSchool-Science-Class 6.json"
$jsonOutputFileName     = "$inputFileName-$SubjectName-$ClassName.json"
$jsonOutputFileFullPath = [System.IO.Path]::GetFullPath($jsonOutputFileName)

Write-Host "Converting sheet '$SubjectName' to '$jsonOutputFileFullPath'"

$null = $results |
    ConvertTo-Json |
    Out-File -Encoding ASCII -FilePath $jsonOutputFileFullPath
$null = $excelApplication.Workbooks.Close()
$null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject(
    $excelApplication
)
#endregion Create JSON file and close Excel file