Accessing old Assemblies

By "Accessing old Assemblies" I don't just mean that I am looking at a .NET 1.1 assembly.  I mean, this is the project that I wrote when we bought a VS2003 license and tried to leave VB6 behind us "OLD".

Of specific issue, I have some assemblies that are built from source by our process and loaded in VB.NET as a script.  They really do what I use PowerShell for now for other areas as they define specific logic and call into a central assembly to do anything complicated.  An instance is created and the "script" is passed a class of configuration properties and told to run.  The parent application then waits for completion and relays results back to an MSMQ where a VB6 application relays the results to a user.

This is an old and patchwork approach.   So I want to be able to remove the VB6 app and the MSMQ and call these compiled "scripts" from Powershell.  The problem is that I can't just New-Object without a namespace....  and I really have no idea what the namespace is.

Fortunately Add-Type has a PassThru argument.  What comes out of this is an array of RuntimeType.  Unfortunately it is an array and not a hash table.  Fortunately PowerShell is built for this.

001 002 003

$ScriptAssembly = add-type -Path "D:\source\MyScript.dll" -pass $MyScript = new-object $($ScriptAssembly | Where {$_.Name -eq "MyScriptBaseClass"}) $MyScript.Run()

Now I don't have to find the namespace, just the name that you see when you write-output.

Write-output $ScriptAssembly

Excel Part 2 - Import-Excel

Continuing from http://import-powershell.blogspot.com/2012/03/excel-part-1.html

Import-Excel - I have this broken into 3 basic parts.  Open/Close Excel, Populate Headers/ Member Names, and saving row data into the collection.  My original implementation pulled out an array of hash tables, but to be more effective I have started using a more true "Import" convention and output an array of PSObjects.  To maintain compatibility an argument will toggle between the two.

I am adding in another few helper functions.
Get-ExcelWorkSheet  helps directly retrieve the Sheet Object:

001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016

function Get-ExcelWorkSheet { Param([parameter(Mandatory=$true,ValueFromPipeline=$true)] $inputObject   ,$SheetName   ,[switch] $Visible   ,[switch] $readonly)   if ($inputObject -is [Microsoft.Office.Interop.Excel.Workbook]) {     $WorkBook = $inputObject   } else {     $WorkBook = Get-ExcelWorkBook $inputObject -Visible:$Visible -readonly:$readonly   }   if (($SheetName -eq $null) -or $SheetName -eq 0) {     $WorkBook.ActiveSheet   } else {     $WorkBook.WorkSheets.item($SheetName)   } }

Import-Row will read a Row and output a hash table.

001 002 003 004 005 006 007 008 009 010 011 012 013 014 015

Function Import-Row {    Param($Row,[hashtable] $Headers =@{},$ColumnStart = 1,$ColumnCount = $Row.Value2.Count)    $output = @{}    for ($index=$ColumnStart;$index -le $ColumnCount;$index ++)    {         If ($Headers.Count -eq 0)         {             $Key = $Index         } Else {             $Key = $Headers[$index]         }         $output.Add($Key,$row.Cells.Item(1,$index).Text)    }    return $output }

 I have a sample XLS like the following:

ANIMAL	ARMS	LEGS	TAIL
Dog	0	4	TRUE
Human	2	2	FALSE
Snake	0	0	TRUE

001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 039 040

Function Import-Excel { Param(   [parameter(Mandatory=$true,ValueFromPipeline=$true)] $inputObject ,[Object] $SheetName ,[switch] $Visible ,[switch] $readonly ,[int] $startOnLineNumber =1 ,[switch] $closeExcel ,[switch] $asHashTable ,[hashtable] $FieldNames =@{}) #Check what the input is. if ($inputObject -is [Microsoft.Office.Interop.Excel.range]) { $range = $inputObject } elseif ($inputObject -isnot [Microsoft.Office.Interop.Excel.Worksheet]) { $WorkSheet = Get-ExcelWorkSheet $inputObject -SheetName $SheetName -Visible:$Visible -readonly:$readonly  $range = $WorkSheet.UsedRange } else { $WorkSheet = $inputObject $range = $WorkSheet.UsedRange } # populate the Header if ($FieldNames.Count -eq 0) {        $FieldNames = Import-Row $range.Rows.Item($startOnLineNumber++)               }     for ($RowIndex=$startOnLineNumber;$RowIndex -le $range.Rows.Count;$RowIndex++) {        $output = Import-Row $range.Rows.Item($RowIndex) -Headers $FieldNames     if ($asHashtAble) {        Write-Output $output     } else {        New-Object PSObject -property $output     } }  # If we opened Excel, we should close Excel. if ($closeExcel) {     $WorkSheet.Activate() | Out-Null   Close-ExcelApplication $WorkSheet } } Import-Excel "$PWD\Animal.xlsx" -Visible -closeExcel

Yields:

TAIL                                    ARMS                                  LEGS                                  ANIMAL                                  
----                                       ----                                       ----                                      ------                                  
TRUE                                       0                                          4                                         Dog                                    
FALSE                                     2                                          2                                         Human                                  
TRUE                                       0                                          0                                         Snake