Если город=болтон И время регистрации меньше или меньше <= 24 часов, удалите строку
Таким образом, если я запускаю код для файла xls с указанным выше набором данных, следует удалить только строку 1 (ул. холма). В основном что-то вроде текущего времени - часа регистрации.
Код, который у меня есть ниже, может удалить строку с учетом 1 условия, но я не уверен, как реализовать несколько условий или время
Счет идет снизу вверх. Сверху вниз, кажется, испортился подсчет и пропущены некоторые строки
$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()
Bigger Data set to test against as of 11/17/2022 3:50 PM where everything <24hr should be deleted.
Street City Hour Of Registeration
hill st Bolton 11/16/2022 12:28 >24hr
flow st Bolton 11/16/2022 13:39 >24hr
jane st Bolton 11/16/2022 15:00 >24hr
jack st Bolton 11/16/2022 15:00 >24hr
Gone st Bolton 11/16/2022 18:16 <24hr
top st Bolton 11/16/2022 18:27 <24hr
sale st Bolton 11/16/2022 19:18 <24hr
jack st Bolton 11/16/2022 20:14 <24hr
Gone st Bolton 11/16/2022 20:28 <24hr
top st Bolton 11/17/2022 02:51 <24hr
sale st Bolton 11/17/2022 03:02 <24hr
jack st Bolton 11/17/2022 06:21 <24hr
Gone st Bolton 11/17/2022 08:51 <24hr
Вот рабочее решение.
$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
# start at last row
for ($rowNumber=$rowMax; $rowNumber -ge '2'; $rowNumber--){
$city = $sheet.Cells($rowNumber,2).value2
if ($sheet.Cells($rowNumber,3).text -ne $null -and $sheet.Cells($rowNumber,3).text -ne ""){
# 24 hour format
$regTime = get-date -Format "MM/dd/yyyy HH:mm" $sheet.Cells($rowNumber,3).text
# 24 hour format
$currentTime = Get-Date -Format "MM/dd/yyyy HH:mm"
$timeDifference = (NEW-TIMESPAN -Start $regTime -End $currentTime)
Write-Host "time difference (hours): " $timeDifference.TotalHours
if ($city -eq "bolton" -and $timeDifference.TotalHours -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()
Источник для получения разницы во времени
В вашем исходном коде у вас есть правильное представление о том, как получить количество использованных диапазонов и пройтись по нему, а также не читать заголовок. Вы также могли сравнить одну ячейку, которая вам просто нужна, чтобы получить ячейку в столбце часов.
Если вы хотите сделать что-то на основе нескольких условий, используйте -and
в операторе if.
Одна особенность, которую вы можете заметить, заключается в том, что при получении ячейки даты мне пришлось использовать .text
вместо .value2
, как в другой ячейке. Я не совсем уверен, почему, но я думаю, что это потому, что Excel отформатировал ячейку как дату, и в ней был другой формат, сохраненный в .value2, в то время как .text содержал значение, которое мы хотели.
Я бы рекомендовал попрактиковаться в использовании различных условных операторов и команды Get-Date
, как предложил другой комментатор, чтобы лучше понять, что происходит в приведенном выше коде.
Были некоторые проблемы с моим кодом. Начинать с 0 и считать в цикле for неправильно, поскольку строки удаляются, а нумерация сбрасывается. Начиная с последней строки и отсчитывая исправляет проблему.
Вторая проблема заключалась в том, что время было в 24-часовом формате, чего я изначально не уловил. Исправлен код Get-Date -Format "MM/dd/yyyy HH:mm"
, чтобы время регистрации и текущее время считывались в 24-часовом формате.
Сравнение времени для суммирования часов также было отключено. Использование .TotalHours
вместо .hours
, как у меня, дает правильную разницу.
@NebelzCheez Я добавил несколько пояснений к своему сообщению. Одна из считываемых ячеек была пустой. Вот почему сообщение об ошибке содержит пустые кавычки "". Я добавил оператор if, чтобы проверить это, если это нулевое значение. if ($sheet.Cells($rowNumber,3).text -ne $null -and $sheet.Cells($rowNumber,3).text -ne "")
Эй, скрипт пропускает удаление некоторых строк, которые должны быть удалены. Это может быть потому, что вы считаете сверху вниз. Можно попробовать снизу вверх? Иногда он удаляет строки, которые не должны быть удалены. Не уверен, связано ли это с 24-часовым временем или отсчетом сверху вниз. Я добавил некоторый набор данных, с которым вы можете протестировать. По какой-то причине строки> 24 часа удаляются :)
@NebelzCheez, ты прав. Я не знал, что это было в 24-часовом формате. Пожалуйста, ознакомьтесь с изменениями. Также проверьте параметр Get-Date -Format
, если вам нужно внести какие-либо изменения в формат времени. То, как у меня была петля, тоже было неправильным, то, как у вас изначально было отсчет сверху, правильно.
Чтобы рассчитать разницу во времени, вы можете попробовать себя для понимания.
[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"
}
Чтобы ответить на ваш вопрос: Получите значение ячейки hourofreg в переменной и выполните описанные выше шаги.
if ( ($cell -ieq 'bolton') -and ($timeDiff.TotalHours -gt 24 )) {
# Do Something
}
Hope this helps!
Эй, спасибо за ответ, но я получаю эту ошибку: Get-Date: не удается связать параметр «Дата». Невозможно преобразовать значение "" в тип "System.DateTime". Ошибка: «Строка не была распознана как допустимый DateTime». Get-Date: невозможно связать параметр «Дата». Невозможно преобразовать значение "" в тип "System.DateTime". Ошибка: «Строка не была распознана как допустимый DateTime».