readexcel.ps1

# Global Values
$excel = New-Object -ComObject Excel.Application
$server = "."
$db = "school"
$workbook = $excel.Workbooks.Open("C:\Users\mike\Documents\Downloads\databook.xlsx");
$accountToWorksheetMap = @{
    'ACCT' = 'Sheet1'
}

# Global Functions
function exitWithMessage([string] $msg) {
    write-host $msg
    Exit(1) 
}

function readWorksheet([string]$sheetname, [bool]$skipheader) {
    $i = if ($skipheader) { 2 } else { 1 }
    $worksheet = $workbook.worksheets($sheetname)
    [System.Collections.ArrayList]$arrExcelValues = @()
    Do {
        $arrExcelValues.Add(
        [PSCustomObject]@{
            Benchmark = $worksheet.Cells.Item($i,1).Value()
            TargetDate = $worksheet.Cells.Item($i,2).Value()
            Weight = $worksheet.Cells.Item($i,3).Value()
        }) > $null
        $i++
    }
    While ($null -ne $worksheet.Cells.Item($i,1).Value())
    $arrExcelValues    
}

function validateSheetValues($values) {
    $values | format-table
}

function sheetExistsInWorkbook([string] $sheetname) {
    try {
        $workbook.worksheets($sheetname)
    } catch {
        return $false
    }
    return $true
}

# Main
try {
    try {
        # insure expected worksheets exist for given account short names
        foreach ($account in $accountToWorksheetMap.Keys) {
            if ((sheetExistsInWorkbook $accountToWorksheetMap[$account]) -eq $false) {
                $sheet = $accountToWorksheetMap[$account]
                exitWithMessage "'$sheet' not found for account '$account'"
            }
        }

        # validate worksheets
        foreach ($account in $accountToWorksheetMap.Keys) {
            $arrExcelValues = readWorksheet $accountToWorksheetMap[$account] $true
            validateSheetValues $arrExcelValues
        }

        # upload worksheets
        
    } catch {
        exitWithMessage "Unexpected Error"
    }

    # SQL query
    $students = invoke-sqlcmd -serverinstance $server -Database $db -Query "Select * from student" | `
    select-object sno,sname,age,birthday,'home address' | `
    where-object sno -eq 1
    $students | format-table

} 
finally {
    $excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)  > $null
}