If city=bolton AND Hour of Registration less than or qual to <= 24hrs then delete Row

So basically, if I run the code against a xls file with the dataset above, only Row 1 (hill st) should be deleted. Basically something like current time - hour of registration.

The code I have below is able to delete a row given 1 condition but I'm not sure how to implement multiple conditions or the time

$file  = 'salehouses.xls'
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
# open file
$workbook = $excel.Workbooks.Open($file)
$sheet    = $workbook.Worksheets.Item(1)
# get max rows
$rowMax   = $sheet.UsedRange.Rows.Count

for ($row = $rowMax; $row -ge 2; $row--) {
    $cell = $sheet.Cells[$row, 2].Value2
    if ($cell -ieq 'bolton') {
        $null = $sheet.Rows($row).EntireRow.Delete() }

$Filename = 'salehouses.xls'
$workbook.SaveAs("c:\xls\salehouses.xls")
$excel.Quit()

CodePudding user response：

Here is a working solution. Tested with your data. I'll add some explanations later.

$file  = 'salehouses.xlsx'
$sourcePath = 'C:\some\path\'
$sourceFile = $sourcePath   $file

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
# open file
$workbook = $excel.Workbooks.Open($sourceFile)
$sheet    = $workbook.Worksheets.Item(1)
# get max rows
$rowMax   = $sheet.UsedRange.Rows.Count



for ($rowNumber=2; $rowNumber -le $rowMax; $rowNumber  ){
    $city = $sheet.Cells($rowNumber,2).value2
    $regTime = get-date $sheet.Cells($rowNumber,3).text
    $currentTime = Get-Date
    $timeDifference = (NEW-TIMESPAN -Start $regTime -End $currentTime).Hours
    Write-Host "time difference (hours): " $timeDifference

    if ($city -eq "bolton" -and $timeDifference -le '24') {
        Write-Host "delete"
        $null = $sheet.Rows($rowNumber).EntireRow.Delete() 

    }
}



$Filename = 'salehouses_modified.xlsx'
$path = 'c:\some\path\'
$fullPathModified =$path   $Filename
$workbook.SaveAs($fullPathModified)
$excel.Quit()

Source for getting time difference

CodePudding user response：

To calculate the time difference, you can try yourself for understanding.

[DateTime]$HourofReg = "11/16/2022 10:00" #To convert the string to DateTime
$currentTime = Get-date
$timeDiff = New-TimeSpan $HourofReg $currentTime # To find the time difference
if ( $timeDiff.TotalHours -gt 24) {
Write-Host "greater than 24 hours"
}

To answer your question: Get the hourofreg cell value in a variable and do the above steps.

if ( ($cell -ieq 'bolton') -and ($timeDiff.TotalHours -gt 24 )) {
 # Do Something 
}
Hope this helps!