PSWriteExcel

0.0.15

Public/Find-ExcelDocumentText.ps1

function Find-ExcelDocumentText {
    param(
        [string] $FilePath,
        [string] $FilePathTarget,
        [string] $Find,
        [switch] $Replace,
        [string] $ReplaceWith,
        [switch] $Regex,
        [switch] $OpenWorkBook,
        [bool] $Supress
    )
    $Excel = Get-ExcelDocument -Path $FilePath
    if ($Excel) {
        $Addresses = @()
        $ExcelWorksheets = $Excel.Workbook.Worksheets
        #$i = 1
        foreach ($WorkSheet in $ExcelWorksheets) {
            #Write-Color 'Worksheet ', $i -Color White, Red
            $StartRow = $WorkSheet.Dimension.Start.Row
            $StartColumn = $WorkSheet.Dimension.Start.Column
            $EndRow = $WorkSheet.Dimension.End.Row + 1
            $EndColumn = $WorkSheet.Dimension.End.Column + 1

            for ($Row = $StartRow; $Row -le $EndRow; $Row++) {
                for ($Column = $StartColumn; $Column -le $EndColumn; $Column++) {
                    #Write-Color -Text 'Row: ', $Row, ' Column: ', $Column -Color White, Green, White, Green
                    $Value = $Worksheet.Cells[$Column, $Row].Value
                    if ($Value -like "*$Find*") {
                        if ($Replace) {
                            if ($Regex) {
                                $Worksheet.Cells[$Column, $Row].Value = $Value -Replace $Find, $ReplaceWith
                            } else {
                                $Worksheet.Cells[$Column, $Row].Value = $Value.Replace($Find, $ReplaceWith)
                            }
                        }
                        $Addresses += $WorkSheet.Cells[$Column, $Row].FullAddress
                    }
                }
            }
            #$i++
        }
        if ($Replace) {
            Save-ExcelDocument -ExcelDocument $Excel -FilePath $FilePathTarget -OpenWorkBook:$OpenWorkBook
        }
        if ($Supress) {
            return
        } else {
            return $Addresses
        }
    }
}