Reading Excel spreadhseets using GemBox.Spreadsheet from PowerShell

reading-excel-spreadsheets-using-gembox.spreadsheet-from-powershell.ps1

#
# Example powershell script to read in contents of an Excel XLSX spreadsheet using
# the GemBox.Spreadsheet library (http://www.gemboxsoftware.com/)
#	
# NOTE: Since GemBox.Spreadsheet is dependent upon .NET 4.0, and since the powershell 2.0
#       tooling is, by default, only .NET 3.5 capable, there's an extra step you need to 
#       go through to get your environment ready.  See the stackoverflow article here
#       http://stackoverflow.com/a/5069146
#       for more details on how to configure this.
#
# SETUP: Place the GemBox.Spreadsheet.dll assembly in the current directory where you're running the script from
#        or in your GAC
#
# USAGE: scriptname.ps1 path-to-excel-file.xlsx
#

$scriptname = $MyInvocation.MyCommand.Name
if ($args.Length -ne 1) {
	Write-Host "Usage: $scriptname path-to-excel-file.xlsx"
	return
}

$filename = Get-ChildItem $args[0]
$scriptdir = $filename.Directory
[System.Reflection.Assembly]::LoadFrom("$scriptdir\GemBox.Spreadsheet.dll") | Out-Null
[GemBox.Spreadsheet.SpreadsheetInfo]::SetLicense("YOUR-KEY-GOES-HERE") # sub your license key here
Write-Host "Reading in source file: ",$filename.FullName
$aspread = New-Object GemBox.Spreadsheet.ExcelFile
$aspread.LoadXlsx($filename.FullName, [GemBox.Spreadsheet.XlsxOptions]::PreserveMakeCopy)

# Print the set of available worksheets
Write-Host "Worksheets:"
foreach($asheet in $aspread.Worksheets) {
	$sheetname = $asheet.Name
	Write-Host "`t$sheetname"
}

# Now show all columns for the first worksheet
$rownum = 0
$colnum = 0
$colnames = @()
$firstsheet = $aspread.Worksheets[0]
$colnamecell = $firstsheet.get_Cells().Item($rownum, $colnum)
while ($colnamecell.get_Value() -ne $null) {
	$theval = $colnamecell.get_Value().ToString()
	$colnames += $theval
	$colnum += 1
	$colnamecell = $firstsheet.get_Cells().Item($rownum, $colnum)
}

Write-Host "`r`nColumn names for first sheet:"
foreach ($aname in $colnames) {
	Write-Host "`t$aname"
}