Possible / best way to import just a table from a worksheet?

[nbphms]

export-excel has a "-tablename" option to export data to a worksheet as a named table (works great). What I'm looking for is the best way to reverse this procedure, i.e. import just the data from an Excel worksheet that is in a specific table name. I don't see a corresponding "-tablename" option on import-excel. If I use "open-excelpackage" I can see the table names under $variable.workbook.worksheets.tables, and I could use the "Address" property on each table to get the -startrow and -endrow for an import, but was hoping for a cleaner way to do it . . .

[scriptingstudio]

You can try this function

function Get-ExcelTable {
	[CmdLetBinding()]
	[OutputType([PSCustomObject])]
	[alias('Get-ExcelDataTable')]
	param (
		[Alias('FullName')][string]$Path,
		[OfficeOpenXml.ExcelPackage]$ExcelPackage,
		[Parameter(ValueFromPipeline)]
		[Alias('TableIndex')]$TableName, # input filter by name or index
		[string[]]$WorksheetName, # input filter
		[Alias('data')][switch]$Content, # forces to retrieve table data; by default output is table names
		[Alias('ie')][switch]$IncludeEmptySheet, # input filter
		[Alias('eh')][switch]$ExcludeHiddenSheet, # input filter
		[Alias('pw')][string]$Password,
		[switch]$PassThru # adds ExcelPackage object to the output
	)

	begin {
		$tabid = [System.Collections.Generic.List[object]]::new()
	}

	process {$tabid.AddRange(@($TableName))}

	end {
		$getcolnumber = {
			param ([string]$columnName)
			$sum = 0
			$columnName.ToCharArray().ForEach{
				$sum *= 26
				$sum += [char]$_.tostring().toupper() - [char]'A'+1
			}
			$sum
		} # END scriptblock

		# auto ParameterSet resolver
		$Excel = if ($ExcelPackage) {$ExcelPackage} 
		elseif ($Path) {
			Open-ExcelPackage -Path $Path -Password:$Password
		} else {
			Write-Warning 'Mandatory parameter missing: neither ExcelPackage nor Path parameter specified.'
			return
		}
		if (-not $Excel) {
			Write-Warning 'Excel object failed to initialize.'
			return
		}

		$WorksheetName = $WorksheetName.where{-not [string]::IsNullOrEmpty($_)}
		$Worksheets = if ($WorksheetName -and $WorksheetName -ne '*') {
			$Excel.Workbook.Worksheets[$WorksheetName]
		} else {
			$Excel.Workbook.Worksheets
		}

		if (-not $Content) {$tabid = $null} # get all tables for tablename mode
		else {$tabid = $tabid.where{$_ -ne $null}} # filter null arguments
		foreach ($ws in $Worksheets) {
			if ($ExcludeHiddenSheet -and $ws.Hidden -ne 'visible') {continue}
			$Tables = if ($tabid.count) {
				$ws.Tables[$tabid]
			} else {
				$ws.Tables
			}

			$tabcollection = [ordered]@{
				WorksheetName = $ws.name
				Tables        = [ordered]@{}
			}

			if ($Content) {
				foreach ($Table in $Tables) {
					if ([string]::IsNullOrEmpty($Table.name)) {continue}
					#if (-not $Table.Address.Address) {continue} # alternative validation way
					$rowCount      = $Table.Address.Rows
					$colCount      = $Table.Address.Columns
					$start,$end    = $Table.Address.Address.Split(':')
					$pos           = $start.IndexOfAny('0123456789'.ToCharArray())
					[int]$startCol = . $getcolnumber $start.Substring(0,$pos)
					[int]$startRow = $start.Substring($pos)
					$tabwidth      = $startCol + $colCount # relative table width - horisontal border
					
					# Table header
					$propertyNames = $ws.Cells[$startRow, $startCol, $startRow, ($tabwidth-1)] | 
					. { process {$_.value}}

					# Performance: further optimization would be reading row batches or even whole table at a time and not single rows
					$tabheight = $startRow + $rowCount + 1 # relative table height - vertical border
					$cols = $tabwidth - $startCol # absolute table width
					$tabcollection['Tables'][$Table.name] = for ($row=$startRow+1; $row -lt $tabheight; $row++) {
						$nextrow = [ordered]@{}
						# Performance: get the entire row first
						[array]$tabrow = $ws.Cells[$row, $startCol, $row, ($tabwidth-1)]
						# Fill output object
						for ($i=0; $i -lt $cols; $i++) {
							$nextrow[$propertyNames[$i]] = $tabrow[$i].value
						}
						[PSCustomObject]$nextrow
					} # rows
				} # table contents
			} 
			else {
				$tabcollection['Tables'] = @($Tables.Name).where{-not [string]::IsNullOrEmpty($_)}
				##$tabcollection['Tables'] = @($Tables).where{$_.Address.Address}.foreach{$_.Name}
			}
			if ($PassThru) {$tabcollection['ExcelPackage'] = $Excel}

			if ($tabcollection['Tables'].count -or $IncludeEmptySheet -or $PassThru) {
				[PSCustomObject]$tabcollection
			}
		} # sheets

		if (-not $ExcelPackage -and -not $PassThru) {
			Close-ExcelPackage -ExcelPackage $Excel -NoSave
		}
	} # end
} # END Get-ExcelTable