Automatos.Common

1.0.0

Functions/Data/Import-Excel.ps1

Function Import-Excel
    {
    [cmdletbinding()]
    Param
        (
        # Filesystem Path to input excel file
        [Parameter(Mandatory=$true)]
        [string]
        $Path,

        # Sheet Selection
        [Parameter(Mandatory=$false)]
        [int]
        $SheetNum = $null,

        # Number of Rows from the top to skip (for stupid header info in spreadsheets)
        [Parameter(Mandatory=$false)]
        [int]
        $SkipRows = 0
        )

    Begin
        {
        # Instantiate Excel
        $Excel = new-object -comobject Excel.Application
        $Excel.visible = $false
        $Excel.DisplayAlerts = $false
        }
    Process
        {
        # Get Workbook
        $xlsx = $Excel.Workbooks.Open($path)

        #Get Sheet/Sheets
        $Data = if (!$sheetNum)
            {
            $W=0
            foreach ($Worksheet in $xlsx.Worksheets)
                {
                Write-Progress -Id 0 -Activity "Processing Worksheets" -CurrentOperation "Worksheet: $($worksheet.name) ($W/$($xlsx.Worksheets.count))" -PercentComplete ($W/$xlsx.Worksheets.count*100)
                $Columns = foreach($val in $worksheet.UsedRange.Rows[(1+$SkipRows)].value2){$val}
                $RowCount = $worksheet.UsedRange.Rows.count
                $TrueRowCount = ($RowCount - $SkipRows - 1)
                $R=0
                $Array = foreach ($Rownum in ((2+$Skiprows)..$rowcount))
                    {
                    Write-Progress -Id 1 -ParentId 0 -Activity "Processing Rows" -CurrentOperation "Row: $R ($R/$($TrueRowCount))" -PercentComplete ($R/$TrueRowCount*100)
                    $rowdata = foreach($val in ($Worksheet.UsedRange.Rows[$rownum]).value2){$Val}
                    $RowHash =[ordered]@{}
                    $C=0
                    foreach ($column in $Columns)
                        {
                        $ColumnName = $Columns[$C]
                        Write-Progress -Id 2 -ParentId 1 -Activity "Processing Columns" -CurrentOperation "Column: $ColumnName ($C/$($Columns.Count))" -PercentComplete ($C/$columns.count*100)
                        $Exist = try{if($RowHash.$ColumnName){$true}else{$false}}catch{$false}
                        if (!$exist)
                            {
                            $RowHash.add($ColumnName,$rowdata[$C])
                            }
                        elseif($Exist)
                            {
                            $ColumnNameMod = "$ColumnName^"
                            $RowHash.add($ColumnNameMod,$rowdata[$C])
                            }
                        $C++
                        }
                    Write-Progress -Activity "Processing Columns" -Completed
                    [pscustomobject]($RowHash)
                    $R++
                    }
                Write-Progress -Activity "Processing Rows" -Completed
                $Array
                $W++
                }
            Write-Progress -Activity "Processing Worksheets" -Completed
                
            }
        else
            {
            $Worksheet = $xlsx.Worksheets[$SheetNum]
            $Columns = foreach($val in $worksheet.UsedRange.Rows[(1+$SkipRows)].value2){$val}
            $RowCount = $worksheet.UsedRange.Rows.count
            $TrueRowCount = ($RowCount - $SkipRows - 1)
            $R=0
            $Array = foreach ($Rownum in ((2+$Skiprows)..$rowcount))
                {
                Write-Progress -Id 1 -ParentId 0 -Activity "Processing Rows" -CurrentOperation "Row: $R ($R/$($TrueRowCount))" -PercentComplete ($R/$TrueRowCount*100)
                $rowdata = foreach($val in ($Worksheet.UsedRange.Rows[$rownum]).value2){$Val}
                $RowHash =[ordered]@{}
                $C=0
                foreach ($column in $Columns)
                    {
                    $ColumnName = $Columns[$C]
                    Write-Progress -Id 2 -ParentId 1 -Activity "Processing Columns" -CurrentOperation "Column: $ColumnName ($C/$($Columns.Count))" -PercentComplete ($C/$columns.count*100)
                    $Exist = try{if($RowHash.$Columnname){$true}else{$false}}catch{$false}
                    if (!$exist)
                        {
                        $RowHash.add($ColumnName,$rowdata[$C])
                        }
                    elseif($Exist)
                        {
                        $ColumnNameMod = "$ColumnName^"
                        $RowHash.add($ColumnNameMod,$rowdata[$C])
                        }
                    $C++
                    }
                 Write-Progress -Activity "Processing Columns" -Completed
                [pscustomobject]($RowHash)
                $R++
                }
            Write-Progress -Activity "Processing Rows" -Completed
            $Array
            }
        }
    End
        {
        # Return Data to Console/Object
        $Data

        # Exit Excel Com App
        $xlsx.Close()
        $Excel.quit()
        }
    }