SLPSLib

1.0

HelperFunctions.ps1

Function Backup-SLDocument{
    
    [CmdletBinding(Defaultparametersetname='Default')]
    Param(
            [parameter(Mandatory=$true,Position=0)]
            [SpreadsheetLight.SLDocument]$WorkBookInstance,

            [parameter(Mandatory=$true,Position=1,Parametersetname='Path')]
            [String]$Path
    )
         
          $DateTimeString = get-date -f  dd-MM-yyyy_hhmmss
          $DuplicateName = $WorkBookInstance.workbookname + '_' + $DateTimeString + '.xlsx'


         if($PSCmdlet.ParameterSetName -eq 'Default')
         {
              If($WorkBookInstance.path)
             {
                if(-not (Test-Path $env:temp\SLPSLib))
                {
                    Try
                    {
                        New-Item -Path $env:TEMP -Name SLPSLib -ItemType Directory -ErrorAction Stop | Out-Null   
                    }
                    Catch
                    {
                        Write-Warning ("Backup-SLDocument :`tAn error occured while creating the Backup folder 'SLPSLIB' at '{0}'...{1}" -f $env:temp, $Error[0].Exception.Message)
                    }            
                }
                Try
                {
                    Copy-Item $WorkBookInstance.path "$env:TEMP\SLPSLib\$DuplicateName" -ErrorAction Stop
                    Write-Verbose ("Backup-SLDocument :`tWorkbook - '{0}' is now backed up to '{1}'" -f $WorkBookInstance.workbookname,"$env:TEMP\SLPSLib\$DuplicateName")
                }
                catch
                {
                    Write-Warning ("Backup-SLDocument :`tAn error occured while copying the file...{0}" -f $Error[0].Exception.Message)
                }
             }
         }
         

         if($PSCmdlet.ParameterSetName -eq 'Path')
         {
            
             If($WorkBookInstance.path)
             {
                if(Test-Path $Path)
                {
                    $backuppath = Join-Path $Path -ChildPath $DuplicateName         
                    Try
                    {
                        Copy-Item $WorkBookInstance.path $backuppath -ErrorAction Stop
                        Write-Verbose ("Backup-SLDocument :`tWorkbook - '{0}' is now backed up to '{1}'" -f $WorkBookInstance.workbookname,$backuppath)
                    }
                    catch
                    {
                        Write-Warning ("Backup-SLDocument :`tAn error occured while copying the file...{0}" -f $Error[0].Exception.Message)
                    }
                }
                Else
                {
                    Write-Warning ("Backup-SLDocument :`tCould not find Path...{0}.. Make sure the target directory is created" -f $Path)
                }
            }
         }#parameterset path
}

# convert excel column number to name
Function Convert-ToExcelColumnName {
    [CmdletBinding(Defaultparametersetname='index')]
    Param(
        [parameter(Mandatory=$true,Position=0,Parametersetname='index')] 
        [int]$Index,
        [parameter(Mandatory=$true,Position=0,Parametersetname='CellReference')] 
        [String]$CellReference         
    )

    if($PSCmdlet.ParameterSetName -eq 'index')
    {
        [SpreadsheetLight.SLDocument]::WhatIsColumnName($Index)
    }

    if($PSCmdlet.ParameterSetName -eq 'CellReference')
    {
        [regex]::Match($CellReference,'[a-zA-Z]+') | select -ExpandProperty value
    }
}

Function Convert-ToExcelColumnIndex {
    Param(
        [parameter(Mandatory=$true,Position=0)] 
        [String]$ColumnName  
    )

    [SpreadsheetLight.SLDocument]::WhatIsColumnIndex($ColumnName)
}

Function Convert-ToExcelCellReference {
    Param(
        [parameter(Mandatory=$true,Position=0)] 
        [Int]$Row,
        [parameter(Mandatory=$true,Position=1)] 
        [Int]$Column
    )

    $cReference = [SpreadsheetLight.SLDocument]::WhatIsCellReference($Row,$Column)
    Write-Output ($cReference + ":" + $cReference) 
}

Function Convert-ToExcelRowColumnIndex {
    Param(
        [validatepattern('[a-z]+\d+')]
        [parameter(Mandatory=$true,Position=0)] 
        [String]$CellReference

    )
    $refrow = 0
    $refcolumn = 0
    [SpreadsheetLight.SLDocument]::WhatIsRowColumnIndex($CellReference,[ref]$refRow,[ref]$refColumn) | Out-Null
    New-Object PSObject -Property @{Row = $refRow;Column = $refColumn} 
}

Function Convert-ToExcelRange {
    Param(
        [parameter(Mandatory=$true,Position=0)] 
        [Int]$StartRowIndex,
        [parameter(Mandatory=$true,Position=1)]
        [Int]$StartColumnIndex,
        [parameter(Mandatory=$true,Position=2)]
        [Int]$EndRowIndex,
        [parameter(Mandatory=$true,Position=3)]
        [Int]$EndColumnIndex,
        [parameter(Mandatory=$false,Position=4)]
        [string]$WorkSheetName

    )

    if($WorkSheetName)
    {
        [SpreadsheetLight.SLConvert]::ToCellRange($WorkSheetName,$StartRowIndex,$StartColumnIndex,$ENDRowIndex,$ENDColumnIndex)
    }
    Else
    {
        [SpreadsheetLight.SLConvert]::ToCellRange($StartRowIndex,$StartColumnIndex,$ENDRowIndex,$ENDColumnIndex)
    }

}

Function Convert-ToExcelAbsoluteRange {
    Param(
        [parameter(Mandatory=$true,Position=0)] 
        [String]$Range,
        [parameter(Mandatory=$false,Position=1)]
        [string]$WorkSheetName

    )
    $r1,$r2 = $Range -split ":"
    $RC1 = Convert-ToExcelRowColumnIndex -CellReference $r1
    $RC2 = Convert-ToExcelRowColumnIndex -CellReference $r2
    if($WorkSheetName)
    {
        [SpreadsheetLight.SLConvert]::ToCellRange($WorkSheetName,$RC1.Row,$RC1.Column,$RC2.Row,$RC2.Column,$true)
    }
    Else
    {
        [SpreadsheetLight.SLConvert]::ToCellRange($RC1.Row,$RC1.Column,$RC2.Row,$RC2.Column,$true)
    }

}

Function Convert-ToExcelRowColumnStats {
    Param(
        [parameter(Mandatory=$true,Position=0)] 
        [String]$Range


    )
        $StartCellReference, $ENDCellReference = $Range -split ":"

        $refrow = $refrow1 = 0
        $refcolumn = $refcolumn1 = 0
        [SpreadsheetLight.SLDocument]::WhatIsRowColumnIndex($StartCellReference,[ref]$refRow,[ref]$refColumn) | Out-Null
        [SpreadsheetLight.SLDocument]::WhatIsRowColumnIndex($ENDCellReference,[ref]$refRow1,[ref]$refColumn1) | Out-Null


        $props = [Ordered]@{
            StartColumnName = [SpreadsheetLight.SLConvert]::ToColumnName($refColumn)
            StartColumnIndex = $refColumn
            StartRowIndex = $refRow
            EndColumnName = [SpreadsheetLight.SLConvert]::ToColumnName($refColumn1)
            EndColumnIndex = $refColumn1
            EndRowIndex = $refRow1 }
        
        New-Object PSobject -Property $props
    
}


Function Out-DataTable {
    [CmdletBinding()]
    [OutputType([SpreadsheetLight.SLDocument])]
    param (
        
        [parameter(Mandatory=$true,Position=0,valuefrompipeline=$true)]
         $inputobject,

        [Switch]$ParseStringData


    )
    BEGIN
    {
        $Data = @()
        $dt = New-Object System.Data.DataTable
    }
    PROCESS
    {    
        $Data += $InputObject  
    }
    END
    {
       
        Write-Verbose "Out-DataTable :`tCreating Datatable..."
        #region Create DataTable
        $dt = New-Object System.Data.DataTable
        $DataHeaders = @()
        $DateHeaders = @()
        
        #$DataHeaders += $Data[0] | Get-Member -MemberType Properties | select -ExpandProperty name
        $DataHeaders  += $Data[0].psobject.Properties | select -ExpandProperty name
        
        Write-Verbose "Out-DataTable :`tAdding column Headers to Datatable..."
        ## Add datatable Columns
        ForEach($d in $DataHeaders )
        { 

             $DataColumn = $d
             try
             {
                $ErrorActionPreference = 'stop'
                if([string]::IsNullOrEmpty($($data[0].$DataColumn))) 
                {
                    $dt.columns.add($DataColumn, [String]) | Out-Null
                }
                else
                {
                    $Dtype= ($data[0].$DataColumn).gettype().name              
                     Switch -regex  ( $Dtype )
                     {

                        'string' 
                        {
                            if( $parseStringData )
                            {
                                 $ConvertedIntValue = ""
                                 $ConvertedDoubleValue = ""
                                 $Int    = [Int]::TryParse($data[0].$DataColumn,[ref]$ConvertedIntValue)
                                 $Double = [Double]::TryParse($data[0].$DataColumn,[ref]$ConvertedDoubleValue)
                                 try
                                 {

                                    $ConvertedDateValue = Get-Date -Date $data[0].$DataColumn -ErrorAction Stop 
                                    $IsDateTime = $true
                                    $DateHeaders += $DataColumn 
                                 }
                                 catch
                                 {
                                    $IsDateTime = $false
                                 }
             
                                 if($ConvertedIntValue -ne 0 -and $ConvertedDoubleValue -ne 0 )
                                 {
                                    $dt.columns.add($DataColumn, [Int]) | Out-Null
                                 }
                                 elseif($ConvertedIntValue -eq 0 -and $ConvertedDoubleValue -ne 0)
                                 {
                                    $dt.columns.add($DataColumn, [Double]) | Out-Null
                                 }
                                 elseif($IsDateTime)
                                 {
                                    $dt.columns.add($DataColumn, [DateTime]) | Out-Null
                                 }
                 
                                 else{
                                    $dt.columns.add($DataColumn) | Out-Null
                                 }
                                 break;

                            }#Ifparsestringdatatype
                            Else
                            {
                                $dt.columns.add($DataColumn, [String]) | Out-Null
                                break;

                            }

                            
                        }
         
                       'Double'
                       {
                            $dt.columns.add($DataColumn, [Double]) | Out-Null
                            break;
                       } 
                       'Datetime'
                       {
                            $dt.columns.add($DataColumn, [DateTime]) | Out-Null
                            $DateHeaders += $DataColumn
                            break;
                       }

                      'Boolean'
                       {
                            $dt.columns.add($DataColumn, [System.Boolean]) | Out-Null
                            Break
                       } 
           
                       'Byte\[\]'
                       {
                            $dt.columns.add($DataColumn, [System.Byte[]]) | Out-Null
                            $dt.Columns[$DataColumn].DataType = [System.String]
                            break;
                       } 
                       'Byte'
                       {
                            $dt.columns.add($DataColumn, [System.Byte]) | Out-Null
                            Break
                       } 
           
                       'char'
                       {
                            $dt.columns.add($DataColumn, [System.Char]) | Out-Null
                            break;
                       } 
                       'Decimal'
                       {
                            $dt.columns.add($DataColumn, [System.Decimal]) | Out-Null
                            Break
                       } 
           
                       'Guid'
                       {
                            $dt.columns.add($DataColumn, [System.Guid]) | Out-Null
                            break;
                       } 
                       'Int16'
                       {
                            $dt.columns.add($DataColumn, [System.Int16]) | Out-Null
                            Break
                       } 
           
                       'Int32'
                       {
                            $dt.columns.add($DataColumn, [System.Int32]) | Out-Null
                            break;
                       } 
                       'Int64|long'
                       {
                            $dt.columns.add($DataColumn, [System.Int64]) | Out-Null
                            break;
                       } 
                       'UInt16'
                       {
                            $dt.columns.add($DataColumn, [System.UInt16]) | Out-Null
                            Break
                       } 
           
                       'UInt32'
                       {
                            $dt.columns.add($DataColumn, [System.UInt32]) | Out-Null
                            break;
                       } 
                       'UInt64|long'
                       {
                            $dt.columns.add($DataColumn, [System.UInt64]) | Out-Null
                            Break
                       } 
           
                       'Single'
                       {
                            $dt.columns.add($DataColumn, [System.Single]) | Out-Null
                            break;
                       }    
                       'IntPtr'
                       {
                            $dt.columns.add($DataColumn, [System.IntPtr]) | Out-Null
                            $dt.Columns[$DataColumn].DataType = [System.Int64]
                            break;
                       }                  

                       Default
                       {
                            $dt.columns.add($DataColumn) | Out-Null
                
                       }                  
                }#switch
            }#else

        }
        catch
        {
            $ErrorActionPreference = 'continue'
            if($Dtype -eq $null)
            {
                $dt.columns.add($DataColumn, [String]) | Out-Null
            }
            #Write-Warning $Error[0].Exception.Message
            #continue
        }

        }# END foreach dataheaders

        Write-Verbose "Out-DataTable :`tAdding Rows to Datatable..."
        ## Add datatable Rows
        for($i = 0;$i -lt $data.count; $i++)
        {
            $row = $dt.NewRow()
            foreach($dhead in $DataHeaders)
            {
                If([string]::IsNullOrEmpty($Data[$i].$dhead))
                {
                    $row.Item($dhead) = [DBNull]::Value
                }
                Else
                {
                    Try
                    {
                        $ErrorActionPreference = 'Stop'
                        if($Data[$i].$dhead.Gettype().name -match 'Intptr' )
                        {
                            $row.Item($dhead) = $Data[$i].$dhead.ToInt64()
                        }
                        Elseif($Data[$i].$dhead.Gettype().basetype.name -eq 'array')
                        {
                          $row.Item($dhead) = $Data[$i].$dhead -join ','
                        }
                        Elseif($Data[$i].$dhead.Gettype().name -match 'byte\[\]')
                        {
                          $row.Item($dhead) = $Data[$i].$dhead -join ','
                        }
                        Else
                        {
                            $row.Item($dhead) = $Data[$i].$dhead
                        }


                    }
                    Catch
                    {
                        Write-Warning ("Out-DataTable : An Error Occured...{0}" -f $Error[0].Exception.Message)
                        $ErrorActionPreference = 'Continue'
                    }
                }

            }

            $dt.Rows.Add($row)
        }

     #ENDregion Create DataTable

     #Write the Datatable Object
     Write-Output $dt
            
    }
}