Add Get-Excel TableItem | TableContent functions

[dfinke]

Model after Get-ChildItem and Get-Content.

Look to implement so table info and contents can be retrived by either name or index.

[scriptingstudio]

does It imply that a table should be a table technically not just a visual table?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[dfinke]

Not sure why I miss some of these "messages".

Good question. Could you say a bit more? This would be based on the tables collection in the xlsx/sheet

[scriptingstudio]

Xlsx/sheet can contain both tables as excel objects (technical table) and tables as just sets of cells (visual table). What kind of tables do you mean?

[dfinke]

Not just a set of tables.

[scriptingstudio]

Prototype to start with: https://github.com/dfinke/ImportExcel/blob/master/GetExcelTable.ps1

function Get-ExcelTableItem {
	[CmdLetBinding()]
	param (
		[string]$Path,
		$ExcelPackage,
		[Parameter(ValueFromPipelineByPropertyName, ValueFromPipeline)]
		[string[]]$WorksheetName
	)

	begin {
		$Excel = if ($ExcelPackage) {$ExcelPackage} 
		elseif ($Path) {
			$Path = (Resolve-Path $Path).ProviderPath
			$Stream = [System.IO.FileStream]::new($Path, 'Open', 'Read', 'ReadWrite')
			[OfficeOpenXml.ExcelPackage]::new($Stream)
		}
	} # begin

	end {
		if (-not $Excel) {return}
		$Worksheets = if ($WorksheetName) {
			$Excel.Workbook.Worksheets[$WorkSheetName]
		} else {
			$Excel.Workbook.Worksheets
		}
		foreach ($ws in $Worksheets) {
			$Tables = if ($TableName) {
				$ws.Tables[$TableName]
			} else {
				$ws.Tables
			}

			foreach ($TableName in $ws.Tables.Name) {
				[PSCustomObject]@{
					WorksheetName = $ws.Name
					TableName     = $TableName
				}
			} # tables
		} # sheets

		if (-not $ExcelPackage) {
			$Stream.Close()
			$Stream.Dispose()
		}
		$Excel.Dispose()
		$Excel = $null
	} # end
} # END Get-ExcelTableItem

function Get-ExcelTableContent {
	[CmdLetBinding()]
	param (
		[string]$Path,
		$ExcelPackage,
		[Parameter(ValueFromPipelineByPropertyName, ValueFromPipeline)]
		[string[]]$TableName,
		[Parameter(ValueFromPipelineByPropertyName, ValueFromPipeline)]
		[string[]]$WorksheetName,
                [switch]$GridTable
	)

	begin {
		# TODO: error handling
		$Excel = if ($ExcelPackage) {$ExcelPackage} elseif ($Path) {
			$Path = (Resolve-Path $Path).ProviderPath
			$Stream = [System.IO.FileStream]::new($Path, 'Open', 'Read', 'ReadWrite')
			[OfficeOpenXml.ExcelPackage]::new($Stream)
		}
	}

	end {
		if (-not $Excel) {return}
		$Worksheets = if ($WorksheetName) {
			$Excel.Workbook.Worksheets[$WorksheetName]
		} else {
			$Excel.Workbook.Worksheets
		}
		foreach ($ws in $Worksheets) {
			$Tables = if ($TableName) {
				$ws.Tables[$TableName]
			} else {
				$ws.Tables
			}

			$output = @{WorksheetName = $ws.name; Tables = [ordered]@{}}
			foreach ($Table in $Tables) {
				$rowCount = $Table.Address.Rows
				$colCount = $Table.Address.Columns
				$start, $end = $Table.Address.Address.Split(':')
				$pos = $start.IndexOfAny('0123456789'.ToCharArray())
				[int]$startCol = ConvertFrom-ExcelColumnName $start.Substring(0,$pos)
				[int]$startRow = $start.Substring($pos)

				$propertyNames = for ($col=$startCol; $col -lt ($startCol+$colCount); $col++) {
					$ws.Cells[$startRow, $col].value
				}

				$startRow++
				$output['Tables'][$Table.name] = for ($row=$startRow; $row -lt ($startRow+$rowCount); $row++) {
					$nextrow = [ordered]@{}
					for (($col=$startCol),($c=0); $col -lt ($startCol+$colCount); $col++,$c++) {
						$nextrow.($propertyNames[$c]) = $ws.Cells[$row, $col].value
					}
					[PSCustomObject]$nextrow
				}
			} # tables

			# TODO: extract "visual" tables from grid
			if ($GridTable) {
				$output.Keys.clone().foreach{
					if (-not $output['Tables'].count) {return}
				}
			}
			if (-not $TableName -or ($TableName -and $output['Tables'].count)) {
				[PSCustomObject]$output
			}
		} # sheets

		if (-not $ExcelPackage) {
			$Stream.Close()
			$Stream.Dispose()
		}
                $Excel.Dispose()
		$Excel = $null
	}
} # END Get-ExcelTableContent

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

[dfinke]

@scriptingstudio Pop that puppy in a PR 🙂

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.