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