Powershell
Код:
$folder = "D:\temp\visit"
$sourceTxt = "$folder\visit-data.txt"
$mark = "+" # метка визита в файлах
$csv = (gc "$sourceTxt") -match "\.bmp" -replace "\.bmp= ",";" -replace ",\d+:\d+:\d+" |ConvertFrom-Csv -Header name,dates -Delimiter ';'
$dates = $csv.dates -split ' ' |sort {$_ -as [datetime]} -Unique
$startDate = $dates[0] -as [datetime]
$day = $startDate
$finishDate = $dates[-1] -as [datetime]
$datesRange = do {$day; $day = $day.adddays(1)} until ($day -gt $finishDate)
$datesRangeString = "$($startDate.ToShortDateString())-$($finishDate.ToShortDateString())"
$reportCsv = "$folder\report $datesRangeString.csv"
"Name;" + ($datesRange.toshortdatestring() -join ';') > "$reportCsv"
$csv |% {
$_.name + ';' + ((diff $datesRange ($_.dates -split ' ' |sort -Unique |% {$_ -as [datetime]}) -IncludeEqual |sort Inputobject).sideindicator -replace '<=',';' -replace '==',"$mark;" -join '') >> "$reportCsv"
}
# Если установлен модуль ImportExcel (https://www.powershellgallery.com/packages/ImportExcel)
if (Get-Module ImportExcel -ListAvailable) {
$reportXlsx = $reportCsv -replace 'csv$','xlsx'
if (Test-Path "$reportXlsx") {rm "$reportXlsx"}
$condText = New-ConditionalText $mark green lightgreen
Import-Csv "$reportCsv" -Delimiter ';' |export-Excel -Path "$reportXlsx" -WorksheetName $datesRangeString -ConditionalText $condText -AutoSize -FreezeTopRow -AutoFilter -BoldTopRow
}
Генерирует CSV, а если установлен модуль ImportExcel, то добавляет и файл .xlsx (см. вложение).
