update a cell in a excel sheet using powershell

Question

I need to search for a word in a row from a spreadsheet and update another cell in the same row with a different value. For example, I have the data like this. I need to search for the person "Smith" from the below spreadsheet and update the value of the 'Status' column from 'Enabled' to 'Disabled' for that row.

"Region","Zone","Customer","Process","Status"
"TEST","East","Smith","HR","Disabled"
"TEST","East","Allen","Finance","Enabled"
"TEST","East","Jake","Payroll","Enabled"

I tried regex and few other functions before posting the question. But I can't get them to work.

Thanks.

Answer 1

It's very easy to use Excel with PowerShell:

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$excelFile = 'C:\test\testsheet.xlsx' 

$searchFor            = 'Smith'

$excel                = New-Object -ComObject Excel.Application
$excel.Visible        = $true
$excel.ScreenUpdating = $true

$workbook  = $excel.Workbooks.Open( $excelFile ,$null, $false )

$ws        = $workbook.WorkSheets.item(1) 

[void]$ws.Activate()

$searchRange  = $ws.UsedRange

$searchResult = $searchRange.Find( $searchFor, [System.Type]::Missing, [System.Type]::Missing, 
                                               [Microsoft.Office.Interop.Excel.XlLookAt]::xlWhole, 
                                               [Microsoft.Office.Interop.Excel.XlSearchOrder]::xlByColumns, 
                                               [Microsoft.Office.Interop.Excel.XlSearchDirection]::xlNext )

while( $searchResult ) {

    $row = $searchResult.Row
    $col = $searchResult.Column

    $ws.Cells( $row, $col + 2 ).Value2 = 'Disabled'
    $searchResult = $searchRange.FindNext( $searchResult )

    if( $searchResult -and $searchResult.Row -le $row ) {
        break
    }
}

[void]$workbook.Save()
[void]$workbook.Close()
[void]$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null