Edit and update excel from PowerShell-CodePudding

I have a script that download a specific xlsx file from SharePoint online to a local folder. I am searching for colors and values in the sheet in a specific column. There are 2 objects. One for values and one for colors. I need to edit the sheet: replace the colors with string and remove the color background and the same for the second. Next I need to save it and upload to SharePoint online. Anyone can help me with that? Here the object that holds the colors and values.

$excel2 = "C:\Temp\SharedFolder\test.xlsx"
    $excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Excel.DisplayAlerts = $False # Disable comfirmation prompts
    $workbook  = $excel.Workbooks.Open($excel2)
    $worksheet = $workbook.Worksheets.Item("VIP List")

    $patches = for ($row = 1; $row -le $rowMax; $row  ) {
        $val = $worksheet.Cells.Item($row, [uint16]$answer).Interior.ColorIndex # 2 is column B
        if ($val -eq $searchcolorForPatch)  {
            # output an object with both values from columns A and B
            [PsCustomObject]@{Patch = $worksheet.Cells.Item($row, 1).Value2}
        }
    }
    $cabs = for ($row = 1; $row -le $rowMax; $row  ) {
        $val2 = $worksheet.Cells.Item($row, [uint16]$answer).value2 # 2 is column B
        if ($val2 -match $searchValue)  {
            # output an object with both values from columns A and B
            [PsCustomObject]@{Patch = $worksheet.Cells.Item($row, 1).Value2}
        }
    }

I tried to add this and change to color 3

 $patches = for ($row = 1; $row -le $rowMax; $row  ) {
        $val = $worksheet.Cells.Item($row, 4).Interior.ColorIndex # 2 is column B
        if ($val -eq 14)  {
            # output an object with both values from columns A and B
            [PsCustomObject]@{Patch = $worksheet.Cells.Item($row, 1).Value2
            ($worksheet.Cells.Item($row, 4).Interior.ColorIndex) = '3'
            }
        }
    }
    $workbook.SaveAs($excel2)
    $workbook.Close($false)
    $excel.Quit()

I think this can work but I need to change the color and set a string inside Currently only the color was changed

$patches = for ($row = 1; $row -le $rowMax; $row  ) {
    $val = $worksheet.Cells.Item($row, 4).Interior.ColorIndex # 2 is column B
    if ($val -eq 14)  {
        # output an object with both values from columns A and B
        [PsCustomObject]@{Patch = $worksheet.Cells.Item($row, 4).Interior.ColorIndex = '3'
        }
    }
}

Need to change the color to -4142 and write inside a string

Case 2: Need to find all the cells with color 4 (green) and has a string 'add' inside. currently know to collect only by color code

CodePudding user response：

Ok, this is too long for a comment, so here goes:

If you have opened your Excel you can search for the cells with a certain background color, AND change the cells value like this:

# as example, we look for Green background, for more colors, see https://analysistabs.com/excel-vba/colorindex/
$colorToSearch = 4  
for ($row = 1; $row -le $rowMax; $row  ) {
    $color = $worksheet.Cells.Item($row, 2).Interior.ColorIndex # 2 is column B where to look for the background color
    $value = $worksheet.Cells.Item($row, 2).Value2
    if ($color -eq $colorToSearch -and $value -eq 'add' )  {
        # remove the background color from that cell
        $worksheet.Cells.Item($row, 2).Interior.ColorIndex = -4142   # constant: xlColorIndexNone
        # now update the same cell's content
        $worksheet.Cells.Item($row, 2) = 'New Value to add to this cell'
    }
}

# save the file with a new name ?
$workbook.SaveAs($excel2)  # remember to put a complete, absolute path and filename here
$workbook.Close($false)

# or save using the same name, so just save the changes in it
# $workbook.Close($true)

# quit Excel
$excel.Quit()

# and remove the used COM objects from memory, otherwise they keep file locks to the file
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
$null = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

When it comes to uploading the file to SharePoint, please have a look at the code here

From your comments. If you only want to find green cells with the value 'add' in them and then collect the Patch numbers from column 1 in the same row, do this:

# as example, we look for Green background, for more colors, see https://analysistabs.com/excel-vba/colorindex/
$colorToSearch = 4   # green
$valueToSearch = 'add'
$patches = for ($row = 1; $row -le $rowMax; $row  ) {
    # get the background color and value of the cell in column 4
    $color = $worksheet.Cells.Item($row, 4).Interior.ColorIndex # # 2 is column where to look for the background color
    $value = $value = $worksheet.Cells.Item($row, 4).Value2
    if ($color -eq $colorToSearch -and $value -eq $valueToSearch )  {
        # just output the patch number from column 1
        # that means that variable $patches will become an array of strings
        $worksheet.Cells.Item($row, 1).Value2

        # or if you rather have that, output an object that has both
        # the row number and the Patch number in it
        # [PsCustomObject]@{
            # Row   = $row
            # Patch = $worksheet.Cells.Item($row, 1).Value2
        # }
    }
}