CODEWITHSUNDEEP
powershell
signalhouse
signalhouse

Reputation: 83

update a cell in a excel sheet using powershell

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.

Upvotes: 3

Views: 22395

Answers (3)

Sor4n
Sor4n

Reputation: 1

I have found solution which meet my needs... using Powershell Not the issue mentioned as in topic.. but overall module have a lot of options which might help modify Excel File using PowerShell

https://www.powershellgallery.com/packages/PSWriteExcel/0.1.15 https://github.com/EvotecIT/PSWriteExcel/blob/master/Examples/Run-Example-FindReplace.ps1

Install-Module -Name PSWriteExcel

Import-Module PSWriteExcel -Force
$FilePath = "D:\Excel_test.xlsx"
$FilePathOutput = "D:\Excel_test1.xlsx"
Find-ExcelDocumentText -FilePath $FilePath -Find 'evotec' -Replace -ReplaceWith 'somethingelse' -FilePathTarget $FilePathOutput -OpenWorkBook -Regex -Suppress $true

Upvotes: -1

signalhouse
signalhouse

Reputation: 83

I got it working using the below script. 

$TGTSERVER = "testservwc01"

$name = "ORATDLLSTR"
        $input = Invoke-Command -ComputerName "$TGTSERVER" -ScriptBlock {Import-Csv 'C:\test.csv'}
        $value = "Disabled"
        $Output = foreach ($i in $input) { 
            if ($i.Process_Instance -match "$name") {$i.Status = "$value"} $i } 

        $OutArray = $Output | Select-Object -Property * -ExcludeProperty PSComputerName, RunspaceId, PSShowComputerName
        $OutArray | Invoke-Command -ComputerName "$TGTSERVER" -ScriptBlock {Export-Csv 'C:\test.csv' -NoTypeInformation}

        if ( $LastExitCode -ge 1) 
              {
                    Write-Warning -Message "$Computer : Disable Step failed"
                    exit 1
        }

However the script fails with exit code 1 even though it updates the csv file with the right value on the remote server.

Upvotes: 0

f6a4
f6a4

Reputation: 1782

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

Upvotes: 11

Related Questions