Friday, April 20, 2012

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



Sunday, April 1, 2012

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