param(
[Parameter(Mandatory=$false)][string]$SourceRoot = 'C:\dev\TEMP\CheckPlugin',
[Parameter(Mandatory=$false)][string]$SheetName = 'Plant3D',
[Parameter(Mandatory=$false)][string]$OutputDirectory = 'C:\dev\autodesk\migration\audits\plugin-usage-plant3d-xlsx-20260731'
)
$ErrorActionPreference = 'Stop'
Add-Type -AssemblyName System.IO.Compression.FileSystem
$utf8Bom = New-Object System.Text.UTF8Encoding($true)
$utf8 = New-Object System.Text.UTF8Encoding($false)
$nsMainUri = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
$nsRelUri = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
$nsPkgRelUri = 'http://schemas.openxmlformats.org/package/2006/relationships'
$outputEvidence = Join-Path $OutputDirectory 'evidence'
New-Item -ItemType Directory -Force -Path $outputEvidence | Out-Null
function Get-XmlEntry([IO.Compression.ZipArchive]$Zip, [string]$Name) {
$entry = $Zip.GetEntry($Name.TrimStart('/'))
if ($null -eq $entry) { return $null }
$reader = [IO.StreamReader]::new($entry.Open())
try { return [xml]$reader.ReadToEnd() } finally { $reader.Dispose() }
}
function Get-ColumnNumber([string]$Reference) {
$letters = ([regex]::Match($Reference, '^[A-Z]+')).Value
$number = 0
foreach ($letter in $letters.ToCharArray()) { $number = $number * 26 + ([int][char]$letter - 64) }
return $number
}
function Normalize-Header([string]$Value) {
if ($null -eq $Value) { return '' }
return (($Value.Trim().ToLowerInvariant()) -replace '[\s_\-\.]+','')
}
function Normalize-Plugin([string]$Value) {
$v = if ($null -eq $Value) { '' } else { $Value.Trim() }
return (($v -replace '(?i)\.(dll|exe)$','').Trim().ToUpperInvariant())
}
function Normalize-Computer([string]$Value) {
if ($null -eq $Value) { return '' }
return $Value.Trim().ToUpperInvariant()
}
function Get-SharedStrings([IO.Compression.ZipArchive]$Zip) {
$xml = Get-XmlEntry $Zip 'xl/sharedStrings.xml'
$values = [System.Collections.Generic.List[string]]::new()
if ($null -eq $xml) { return $values }
$manager = New-Object Xml.XmlNamespaceManager($xml.NameTable)
$manager.AddNamespace('m',$nsMainUri)
foreach ($item in $xml.SelectNodes('//m:si',$manager)) { $values.Add((($item.SelectNodes('.//m:t',$manager) | ForEach-Object InnerText) -join '')) }
return $values
}
function Get-CellText($Cell, $Manager, $SharedStrings) {
if ($null -eq $Cell) { return '' }
$inline = $Cell.SelectSingleNode('./m:is',$Manager)
if ($null -ne $inline) { return (($inline.SelectNodes('.//m:t',$Manager) | ForEach-Object InnerText) -join '') }
$value = $Cell.SelectSingleNode('./m:v',$Manager)
if ($null -eq $value) { return '' }
$raw = $value.InnerText
if ([string]$Cell.t -eq 's') {
$index = 0
if ([int]::TryParse($raw,[ref]$index) -and $index -lt $SharedStrings.Count) { return $SharedStrings[$index] }
}
return $raw
}
function Get-DateValue([string]$Text, [bool]$Numeric, [int]$Style, $DateStyles) {
if ([string]::IsNullOrWhiteSpace($Text)) { return $null }
$dateLike = $false
if ($Numeric -and $DateStyles.Contains($Style)) { $dateLike = $true }
if ($dateLike) {
$serial = 0.0
if ([double]::TryParse($Text,[Globalization.NumberStyles]::Float,[Globalization.CultureInfo]::InvariantCulture,[ref]$serial)) {
return [pscustomobject]@{ Date = [DateTime]::FromOADate($serial); Rule = 'EXCEL_SERIAL_DATE' }
}
}
$clean = ($Text.Trim() -replace '[Tt]',' ' -replace '[\u00A0]',' ' -replace '\s+',' ')
$formats = @('yyyy MM dd HH:mm:ss','yyyy MM dd H:mm:ss','yyyy MM dd','dd.MM.yyyy HH:mm:ss','dd.MM.yyyy H:mm:ss','dd.MM.yyyy','dd.MM.yy HH:mm:ss','dd.MM.yy H:mm:ss','dd.MM.yy','yyyy-MM-dd HH:mm:ss','yyyy-MM-dd','dd/MM/yyyy HH:mm:ss','dd/MM/yyyy')
foreach ($format in $formats) {
$parsed = [DateTime]::MinValue
if ([DateTime]::TryParseExact($clean,$format,[Globalization.CultureInfo]::InvariantCulture,[Globalization.DateTimeStyles]::AllowWhiteSpaces,[ref]$parsed)) {
$rule = if ($format -match 'H|s') { 'TEXT_DATETIME' } else { 'DATE_ONLY' }
return [pscustomobject]@{ Date = $parsed; Rule = $rule }
}
}
$parsed = [DateTime]::MinValue
if ([DateTime]::TryParse($clean,[Globalization.CultureInfo]::GetCultureInfo('ru-RU'),[Globalization.DateTimeStyles]::AllowWhiteSpaces,[ref]$parsed)) {
return [pscustomobject]@{ Date = $parsed; Rule = if ($clean -match ':') { 'TEXT_DATETIME' } else { 'DATE_ONLY' } }
}
return $null
}
function Get-DateStyles([IO.Compression.ZipArchive]$Zip) {
$set = [Collections.Generic.HashSet[int]]::new()
foreach ($i in 14..22) { [void]$set.Add($i) }
$styles = Get-XmlEntry $Zip 'xl/styles.xml'
if ($null -eq $styles) { return $set }
$manager = New-Object Xml.XmlNamespaceManager($styles.NameTable); $manager.AddNamespace('m',$nsMainUri)
$dateWords = 'yy|yyyy|mm|dd|hh|ss|am/pm'
$formats = @{}
foreach ($format in $styles.SelectNodes('//m:numFmts/m:numFmt',$manager)) { $formats[[int]$format.numFmtId] = [string]$format.formatCode }
$index = 0
foreach ($xf in $styles.SelectNodes('//m:cellXfs/m:xf',$manager)) {
$id = [int]$xf.numFmtId
if (($formats.ContainsKey($id) -and $formats[$id] -match $dateWords) -or ($id -ge 14 -and $id -le 22)) { [void]$set.Add($index) }
$index++
}
return $set
}
function Get-WorkbookSheet($Zip, [string]$WantedName) {
$workbook = Get-XmlEntry $Zip 'xl/workbook.xml'
$relationships = Get-XmlEntry $Zip 'xl/_rels/workbook.xml.rels'
if ($null -eq $workbook -or $null -eq $relationships) { throw 'READ_ERROR: workbook metadata is missing' }
$manager = New-Object Xml.XmlNamespaceManager($workbook.NameTable); $manager.AddNamespace('m',$nsMainUri); $manager.AddNamespace('r',$nsRelUri)
$relManager = New-Object Xml.XmlNamespaceManager($relationships.NameTable); $relManager.AddNamespace('p',$nsPkgRelUri)
$wanted = $WantedName.Trim().ToLowerInvariant()
foreach ($sheet in $workbook.SelectNodes('//m:sheets/m:sheet',$manager)) {
if ([string]$sheet.name.Trim().ToLowerInvariant() -eq $wanted) {
$id = $sheet.GetAttribute('id',$nsRelUri)
$rel = $relationships.SelectSingleNode("//p:Relationship[@Id='$id']",$relManager)
if ($null -eq $rel) { throw 'READ_ERROR: worksheet relationship is missing' }
$target = ([string]$rel.Target).TrimStart('/')
if ($target -notmatch '^xl/') { $target = 'xl/' + $target }
return [pscustomobject]@{ Name = [string]$sheet.name; Target = $target }
}
}
return $null
}
function Get-WorksheetRows($Zip, [string]$Target, $SharedStrings, $DateStyles) {
$xml = Get-XmlEntry $Zip $Target
if ($null -eq $xml) { throw 'READ_ERROR: worksheet is missing' }
$manager = New-Object Xml.XmlNamespaceManager($xml.NameTable); $manager.AddNamespace('m',$nsMainUri)
$rows = [System.Collections.Generic.List[object]]::new()
foreach ($row in $xml.SelectNodes('//m:sheetData/m:row',$manager)) {
$cells = @{}
foreach ($cell in $row.SelectNodes('./m:c',$manager)) {
$column = Get-ColumnNumber ([string]$cell.r)
$raw = Get-CellText $cell $manager $SharedStrings
$numeric = ([string]$cell.t -eq '' -or [string]$cell.t -eq 'n')
$style = if ([string]$cell.s) { [int]$cell.s } else { 0 }
$cells[$column] = [pscustomobject]@{ Text = $raw; Date = Get-DateValue $raw $numeric $style $DateStyles }
}
$rows.Add([pscustomobject]@{ Number = [int]$row.r; Hidden = ([string]$row.hidden -eq '1'); Cells = $cells })
}
return [pscustomobject]@{ Rows = $rows; ColumnCount = (($rows | ForEach-Object { if($_.Cells.Count){($_.Cells.Keys|Measure-Object -Maximum).Maximum}else{0} } | Measure-Object -Maximum).Maximum) }
}
function Get-Cell($Row,[int]$Column) { if($Row.Cells.ContainsKey($Column)){return $Row.Cells[$Column].Text}; return '' }
function Find-Header($Rows) {
$pluginAliases = @('плагин','названиеплагина','plugin','pluginname','module','command')
$dateAliases = @('дата','время','датазапуска','датаивремя','date','time','datetime','timestamp','launchdate')
$computerAliases = @('пк','компьютер','имяпк','computer','computername','machine','machinename','host','hostname')
foreach($row in $Rows | Select-Object -First 30) {
$map = @{}
foreach($key in $row.Cells.Keys){$map[(Normalize-Header (Get-Cell $row $key))]=[int]$key}
$plugin = ($pluginAliases | ? {$map.ContainsKey($_)} | Select -First 1)
$date = ($dateAliases | ? {$map.ContainsKey($_)} | Select -First 1)
$computer = ($computerAliases | ? {$map.ContainsKey($_)} | Select -First 1)
if($plugin -and $date){return [pscustomobject]@{ Row = $row; PluginColumn = $map[$plugin]; DateColumn = $map[$date]; TimeColumn = if($map.ContainsKey('время')){$map['время']}elseif($map.ContainsKey('time')){$map['time']}else{$null}; ComputerColumn = if($computer){$map[$computer]}else{$null}; Rule='HEADER_ALIASES' }}
}
return $null
}
function Get-Sha256([string]$Path) { return (Get-FileHash -LiteralPath $Path -Algorithm SHA256).Hash }
function Get-RowHash([string]$SourceId,[int]$Row,[string]$Plugin,[string]$Date,[string]$Computer) { $text = "$SourceId|$Row|$Plugin|$Date|$Computer"; $sha = [Security.Cryptography.SHA256]::Create(); try { return (([BitConverter]::ToString($sha.ComputeHash([Text.Encoding]::UTF8.GetBytes($text)))) -replace '-','') } finally { $sha.Dispose() } }
function Csv([object[]]$Rows,[string]$Path) {
if($null -eq $Rows){$Rows=@()}
if($Rows.Count -gt 0){$Rows | Export-Csv -LiteralPath $Path -NoTypeInformation -Encoding UTF8; return}
$headers = switch ([IO.Path]::GetFileName($Path)) {
'plugin-usage-events.csv' { 'EventId,PluginName,PluginNameNormalized,LaunchDateTime,LaunchDate,LaunchMonth,ComputerName,ComputerNameNormalized,SourceFile,SourceSheet,SourceRow,ParseRule,DuplicateCount' }
'plugin-summary.csv' { 'PluginName,PluginNameNormalized,TotalLaunches,ComputerCount,FirstLaunch,LastLaunch,ActiveDays,ActiveMonths,MostActiveComputer,MostActiveComputerLaunches,ShareOfAllLaunches' }
'pc-summary.csv' { 'ComputerName,ComputerNameNormalized,TotalLaunches,UniquePlugins,FirstLaunch,LastLaunch,ActiveDays,MostUsedPlugin,MostUsedPluginLaunches' }
'plugin-pc-matrix.csv' { 'Plugin,TotalLaunches,Computers,FirstLaunch,LastLaunch' }
'plugin-month-matrix.csv' { 'Plugin' }
'rejected-records.csv' { 'SourceFile,SourceSheet,SourceRow,ReasonCode,DetectedPlugin,DetectedDateTime,DetectedComputer,RowHash' }
'source-inventory.csv' { 'SourceFile,HasPlant3DSheet,ActualSheetName,RowCount,ColumnCount,Headers,SHA256,ParseResult,RejectedRows' }
}
[IO.File]::WriteAllText($Path, $headers + "`r`n", $utf8Bom)
}
$files = @(Get-ChildItem -LiteralPath $SourceRoot -Recurse -File -Filter '*.xlsx' | Where-Object Name -notlike '~$*' | Sort-Object FullName)
$eventsRaw = [Collections.Generic.List[object]]::new(); $rejected = [Collections.Generic.List[object]]::new(); $inventory = [Collections.Generic.List[object]]::new(); $fileIndex=0; $rawRowsExamined=0; $temporaryIgnored = @(Get-ChildItem -LiteralPath $SourceRoot -Recurse -File -Filter '~$*.xlsx' -ErrorAction SilentlyContinue).Count
foreach($file in $files) {
$fileIndex++; $sourceId = ('source-{0:D4}.xlsx' -f $fileIndex); $beforeSize=$file.Length; $beforeTime=$file.LastWriteTimeUtc; $hash=Get-Sha256 $file.FullName
$plant=$false; $sheetActual=''; $rowCount=0; $columnCount=0; $result='FAILED'; $rejectedBefore=$rejected.Count
try {
$zip=[IO.Compression.ZipFile]::OpenRead($file.FullName)
try {
$sheet=Get-WorkbookSheet $zip $SheetName
if($null -eq $sheet) { $rejected.Add([pscustomobject]@{SourceFile=$sourceId;SourceSheet='';SourceRow='';ReasonCode='SHEET_NOT_FOUND';DetectedPlugin='';DetectedDateTime='';DetectedComputer='';RowHash=(Get-RowHash $sourceId 0 '' '' '')}); $inventory.Add([pscustomobject]@{SourceFile=$sourceId;HasPlant3DSheet=$false;ActualSheetName='';RowCount=0;ColumnCount=0;Headers='';SHA256=$hash;ParseResult='SHEET_NOT_FOUND';RejectedRows=1}); continue }
$plant=$true; $sheetActual=$sheet.Name; $shared=Get-SharedStrings $zip; $dates=Get-DateStyles $zip; $worksheet=Get-WorksheetRows $zip $sheet.Target $shared $dates; $rowCount=$worksheet.Rows.Count; $columnCount=$worksheet.ColumnCount; $header=Find-Header $worksheet.Rows
if($null -eq $header){$rejected.Add([pscustomobject]@{SourceFile=$sourceId;SourceSheet=$sheetActual;SourceRow='';ReasonCode='HEADER_NOT_FOUND';DetectedPlugin='';DetectedDateTime='';DetectedComputer='';RowHash=(Get-RowHash $sourceId 0 '' '' '')});continue}
$headerText=(($header.Row.Cells.Keys|Sort-Object|ForEach-Object { Get-Cell $header.Row $_ }) -join ';')
foreach($row in $worksheet.Rows | Where-Object Number -gt $header.Row.Number) {
$plugin=(Get-Cell $row $header.PluginColumn).Trim(); $dateText=(Get-Cell $row $header.DateColumn).Trim(); $timeText=if($header.TimeColumn){(Get-Cell $row $header.TimeColumn).Trim()}else{''}; $computer=if($header.ComputerColumn){(Get-Cell $row $header.ComputerColumn).Trim()}else{''}; $dateInfo=$row.Cells[$header.DateColumn].Date
if(!$plugin -and !$dateText -and !$computer){continue}
$rawRowsExamined++
$reason=''; if(!$plugin){$reason='PLUGIN_NOT_FOUND'} elseif(!$dateText){$reason='DATE_NOT_FOUND'} elseif($null -eq $dateInfo){$reason='INVALID_DATE'} elseif(!$computer){$reason='COMPUTER_NOT_FOUND'}
if($reason){$rejected.Add([pscustomobject]@{SourceFile=$sourceId;SourceSheet=$sheetActual;SourceRow=$row.Number;ReasonCode=$reason;DetectedPlugin=$plugin;DetectedDateTime=($dateText+' '+$timeText).Trim();DetectedComputer=$computer;RowHash=(Get-RowHash $sourceId $row.Number $plugin $dateText $computer)});continue}
$date=$dateInfo.Date; if($timeText){$timeInfo=Get-DateValue $timeText $false 0 $dates;if($null -ne $timeInfo){$date=$date.Date.Add($timeInfo.Date.TimeOfDay)}}
$eventsRaw.Add([pscustomobject]@{PluginName=$plugin;PluginNameNormalized=(Normalize-Plugin $plugin);LaunchDateTime=$date.ToString('yyyy-MM-ddTHH:mm:ss');LaunchDate=$date.ToString('yyyy-MM-dd');LaunchMonth=$date.ToString('yyyy-MM');ComputerName=$computer;ComputerNameNormalized=(Normalize-Computer $computer);SourceFile=$sourceId;SourceSheet=$sheetActual;SourceRow=$row.Number;ParseRule=if($dateInfo.Rule -eq 'DATE_ONLY'){'DATE_ONLY'}else{'DATE_TIME'} })
}
$result='PARSED'
} finally { $zip.Dispose() }
} catch { $rejected.Add([pscustomobject]@{SourceFile=$sourceId;SourceSheet=$sheetActual;SourceRow='';ReasonCode='READ_ERROR';DetectedPlugin='';DetectedDateTime='';DetectedComputer='';RowHash=(Get-RowHash $sourceId 0 '' '' '')}) }
$rejectedCount=$rejected.Count-$rejectedBefore; if($file.Length -ne $beforeSize -or $file.LastWriteTimeUtc -ne $beforeTime -or (Get-Sha256 $file.FullName) -ne $hash){throw "Source modified: $sourceId"}
$inventory.Add([pscustomobject]@{SourceFile=$sourceId;HasPlant3DSheet=$plant;ActualSheetName=$sheetActual;RowCount=$rowCount;ColumnCount=$columnCount;Headers=$headerText;SHA256=$hash;ParseResult=$result;RejectedRows=$rejectedCount})
}
$grouped = $eventsRaw | Group-Object { if($_.ParseRule -eq 'DATE_ONLY'){"$($_.PluginNameNormalized)|$($_.LaunchDateTime)|$($_.ComputerNameNormalized)|$($_.SourceFile)|$($_.SourceRow)"}else{"$($_.PluginNameNormalized)|$($_.LaunchDateTime)|$($_.ComputerNameNormalized)"} } | Sort-Object Name
$events=[Collections.Generic.List[object]]::new(); foreach($group in $grouped){$item=$group.Group|Sort-Object SourceFile,SourceRow|Select -First 1;$events.Add([pscustomobject]@{EventId=('EVT-{0:D6}' -f ($events.Count+1));PluginName=$item.PluginName;PluginNameNormalized=$item.PluginNameNormalized;LaunchDateTime=$item.LaunchDateTime;LaunchDate=$item.LaunchDate;LaunchMonth=$item.LaunchMonth;ComputerName=$item.ComputerName;ComputerNameNormalized=$item.ComputerNameNormalized;SourceFile=$item.SourceFile;SourceSheet=$item.SourceSheet;SourceRow=$item.SourceRow;ParseRule=$item.ParseRule;DuplicateCount=$group.Count})}
$events=@($events|Sort-Object PluginNameNormalized,LaunchDateTime,ComputerNameNormalized,SourceFile,SourceRow)
$pluginSummary=[Collections.Generic.List[object]]::new(); foreach($group in ($events|Group-Object PluginNameNormalized)){ $g=@($group.Group); $pc=$g|Group-Object ComputerNameNormalized|Sort-Object Count -Descending|Select-Object -First 1; $pluginSummary.Add([pscustomobject]@{PluginName=$g[0].PluginName;PluginNameNormalized=$group.Name;TotalLaunches=$g.Count;ComputerCount=($g.ComputerNameNormalized|Sort-Object -Unique).Count;FirstLaunch=($g.LaunchDateTime|Sort-Object|Select-Object -First 1);LastLaunch=($g.LaunchDateTime|Sort-Object|Select-Object -Last 1);ActiveDays=($g.LaunchDate|Sort-Object -Unique).Count;ActiveMonths=($g.LaunchMonth|Sort-Object -Unique).Count;MostActiveComputer=$pc.Name;MostActiveComputerLaunches=$pc.Count;ShareOfAllLaunches=if($events.Count){[math]::Round($g.Count/$events.Count,6)}else{0}}) }; $pluginSummary=@($pluginSummary|Sort-Object PluginName|Sort-Object TotalLaunches -Descending)
$pcSummary=[Collections.Generic.List[object]]::new(); foreach($group in ($events|Group-Object ComputerNameNormalized)){ $g=@($group.Group); $plugin=$g|Group-Object PluginNameNormalized|Sort-Object Count -Descending|Select-Object -First 1; $pcSummary.Add([pscustomobject]@{ComputerName=$g[0].ComputerName;ComputerNameNormalized=$group.Name;TotalLaunches=$g.Count;UniquePlugins=($g.PluginNameNormalized|Sort-Object -Unique).Count;FirstLaunch=($g.LaunchDateTime|Sort-Object|Select-Object -First 1);LastLaunch=($g.LaunchDateTime|Sort-Object|Select-Object -Last 1);ActiveDays=($g.LaunchDate|Sort-Object -Unique).Count;MostUsedPlugin=$plugin.Name;MostUsedPluginLaunches=$plugin.Count}) }; $pcSummary=@($pcSummary|Sort-Object ComputerNameNormalized)
$months=@($events.LaunchMonth|Sort -Unique); $plugins=@($events|Group PluginNameNormalized|Sort Name); $pluginPc=@(); foreach($p in $plugins){$r=[ordered]@{Plugin=$p.Group[0].PluginName;TotalLaunches=$p.Count;Computers=($p.Group.ComputerNameNormalized|Sort -Unique).Count;FirstLaunch=($p.Group.LaunchDateTime|Sort|Select -First 1);LastLaunch=($p.Group.LaunchDateTime|Sort|Select -Last 1)};foreach($pc in @($events.ComputerNameNormalized|Sort -Unique)){$r[$pc]=(@($p.Group|? ComputerNameNormalized -eq $pc)).Count};$pluginPc+=[pscustomobject]$r}
$monthRows=@();foreach($p in $plugins){$r=[ordered]@{Plugin=$p.Group[0].PluginName};foreach($m in $months){$r[$m]=(@($p.Group|? LaunchMonth -eq $m)).Count};$monthRows+=[pscustomobject]$r}
$eventView=@($events|%{[pscustomobject]@{Плагин=$_.PluginName;'Дата и время запуска'=$_.LaunchDateTime;Компьютер=$_.ComputerName;'Исходный XLSX'=$_.SourceFile;'Исходная строка'=$_.SourceRow}})
Csv $events (Join-Path $OutputDirectory 'plugin-usage-events.csv'); Csv $pluginSummary (Join-Path $OutputDirectory 'plugin-summary.csv'); Csv $pcSummary (Join-Path $OutputDirectory 'pc-summary.csv'); Csv $pluginPc (Join-Path $OutputDirectory 'plugin-pc-matrix.csv'); Csv $monthRows (Join-Path $OutputDirectory 'plugin-month-matrix.csv'); Csv $rejected (Join-Path $OutputDirectory 'rejected-records.csv'); Csv $inventory (Join-Path $OutputDirectory 'source-inventory.csv')
$summaryObj=[pscustomobject]@{XlsxFilesFound=$files.Count;TemporaryXlsxIgnored=$temporaryIgnored;FilesWithPlant3DSheet=($inventory|? HasPlant3DSheet).Count;FilesWithoutPlant3DSheet=($inventory|? {-not $_.HasPlant3DSheet}).Count;FilesParsed=($inventory|? ParseResult -eq 'PARSED').Count;FilesFailed=($inventory|? ParseResult -eq 'FAILED').Count;RawRows=$rawRowsExamined;ValidRows=$eventsRaw.Count;UniqueEvents=$events.Count;Duplicates=(($events|Measure DuplicateCount -Sum).Sum-$events.Count);RejectedRows=$rejected.Count;DateRange=if($events.Count){"$($events[0].LaunchDate) .. $($events[-1].LaunchDate)"}else{''};UniquePlugins=($events.PluginNameNormalized|Sort -Unique).Count;UniqueComputers=($events.ComputerNameNormalized|Sort -Unique).Count;MostUsedPlugin=if($pluginSummary.Count){$pluginSummary[0].PluginName}else{''};MostActiveComputer=if($pcSummary.Count){$pcSummary[0].ComputerName}else{''};SourceModified='NO'}
[IO.File]::WriteAllText((Join-Path $OutputDirectory 'summary.json'),($summaryObj|ConvertTo-Json -Depth 4),$utf8)
function XmlEscape([string]$Value){if($null -eq $Value){return ''};return [Security.SecurityElement]::Escape($Value)}
function ExcelColumn([int]$N){$s='';while($N -gt 0){$N--; $s=([char](65+($N%26)))+$s;$N=[math]::Floor($N/26)};return $s}
function SheetXml([object[]]$Rows){$sb=[Text.StringBuilder]::new();[void]$sb.Append('');$r=1;foreach($row in $Rows){[void]$sb.Append((''));$c=1;foreach($prop in $row.PSObject.Properties){$v=XmlEscape ([string]$prop.Value);$ref=(ExcelColumn $c)+$r;[void]$sb.Append((''+$v+''));$c++};[void]$sb.Append('
');$r++};[void]$sb.Append('');return $sb.ToString()}
function Build-Workbook([string]$Path,[System.Collections.IDictionary]$Sheets){
$fs=[IO.File]::Create($Path); $zip=[IO.Compression.ZipArchive]::new($fs,[IO.Compression.ZipArchiveMode]::Create)
try {
$types=''
for($i=1;$i -le $Sheets.Count;$i++){$types+=('')}; $types+=''
$root=''
$work=''
$rels=''
$i=1; foreach($name in $Sheets.Keys){$work+=('');$rels+=('');$i++}; $work+=''; $rels+=''
$entries=[ordered]@{'[Content_Types].xml'=$types;'_rels/.rels'=$root;'xl/workbook.xml'=$work;'xl/_rels/workbook.xml.rels'=$rels}; $i=1; foreach($name in $Sheets.Keys){$entries[('xl/worksheets/sheet'+$i+'.xml')]=SheetXml $Sheets[$name];$i++}
foreach($item in $entries.GetEnumerator()){$entry=$zip.CreateEntry($item.Key);$entry.LastWriteTime=[DateTimeOffset]::new(2000,1,1,0,0,0,[TimeSpan]::Zero);$writer=[IO.StreamWriter]::new($entry.Open(),$utf8);try{$writer.Write($item.Value)}finally{$writer.Dispose()}}
} finally {$zip.Dispose();$fs.Dispose()}
}
$summaryRows=@([pscustomobject]@{Плагин='Плагин';'Количество запусков'='Количество запусков';'Количество компьютеров'='Количество компьютеров';'Первый запуск'='Первый запуск';'Последний запуск'='Последний запуск';'Активных дней'='Активных дней';'Активных месяцев'='Активных месяцев';'Самый активный компьютер'='Самый активный компьютер';'Запусков на самом активном компьютере'='Запусков на самом активном компьютере';'Доля всех запусков'='Доля всех запусков'})+@($pluginSummary|%{[pscustomobject]@{Плагин=$_.PluginName;'Количество запусков'=$_.TotalLaunches;'Количество компьютеров'=$_.ComputerCount;'Первый запуск'=$_.FirstLaunch;'Последний запуск'=$_.LastLaunch;'Активных дней'=$_.ActiveDays;'Активных месяцев'=$_.ActiveMonths;'Самый активный компьютер'=$_.MostActiveComputer;'Запусков на самом активном компьютере'=$_.MostActiveComputerLaunches;'Доля всех запусков'=$_.ShareOfAllLaunches}})
$pluginPcHeader=[ordered]@{Plugin='Плагин';TotalLaunches='Всего запусков';Computers='Компьютеров';FirstLaunch='Первый запуск';LastLaunch='Последний запуск'}; foreach($pc in @($events.ComputerNameNormalized|Sort-Object -Unique)){$pluginPcHeader[$pc]=$pc}; $pluginPcRows=@([pscustomobject]$pluginPcHeader)+@($pluginPc)
$pcRows=@([pscustomobject]@{Компьютер='Компьютер';'Всего запусков'='Всего запусков';'Уникальных плагинов'='Уникальных плагинов';'Первый запуск'='Первый запуск';'Последний запуск'='Последний запуск';'Активных дней'='Активных дней';'Самый используемый плагин'='Самый используемый плагин';'Количество его запусков'='Количество его запусков'})+@($pcSummary|%{[pscustomobject]@{Компьютер=$_.ComputerName;'Всего запусков'=$_.TotalLaunches;'Уникальных плагинов'=$_.UniquePlugins;'Первый запуск'=$_.FirstLaunch;'Последний запуск'=$_.LastLaunch;'Активных дней'=$_.ActiveDays;'Самый используемый плагин'=$_.MostUsedPlugin;'Количество его запусков'=$_.MostUsedPluginLaunches}})
$monthRowsFinal=@([pscustomobject]@{Плагин='Плагин'})+@($monthRows); $eventRowsFinal=@([pscustomobject]@{Плагин='Плагин';'Дата и время запуска'='Дата и время запуска';Компьютер='Компьютер';'Исходный XLSX'='Исходный XLSX';'Исходная строка'='Исходная строка'})+@($eventView)
$finalSheets=[ordered]@{'Итог'=$summaryRows;'Плагин × ПК'=$pluginPcRows;'По компьютерам'=$pcRows;'По месяцам'=$monthRowsFinal;'События'=$eventRowsFinal}
Build-Workbook (Join-Path $OutputDirectory 'plugin-usage-final.xlsx') $finalSheets
$topPlugins=if($pluginSummary.Count){($pluginSummary|Select -First 20|%{"- $($_.PluginName): $($_.TotalLaunches)"}) -join "`n"}else{'- нет валидных событий'}
$topComputers=if($pcSummary.Count){($pcSummary|Select -First 20|%{"- $($_.ComputerName): $($_.TotalLaunches)"}) -join "`n"}else{'- нет валидных событий'}
$readme=@"
# Plant3D usage from XLSX
Source is read-only. Only Plant3D sheets are analyzed; other sheets are ignored.
XLSX files: $($summaryObj.XlsxFilesFound)
Temporary XLSX ignored: $($summaryObj.TemporaryXlsxIgnored)
Files with Plant3D: $($summaryObj.FilesWithPlant3DSheet)
Files without Plant3D: $($summaryObj.FilesWithoutPlant3DSheet)
Files parsed: $($summaryObj.FilesParsed)
Files failed: $($summaryObj.FilesFailed)
Candidate rows examined: $($summaryObj.RawRows)
Unique events: $($summaryObj.UniqueEvents)
Duplicates: $($summaryObj.Duplicates)
Rejected rows: $($summaryObj.RejectedRows)
Date range: $($summaryObj.DateRange)
Unique plugins: $($summaryObj.UniquePlugins)
Unique computers: $($summaryObj.UniqueComputers)
Most used plugin: $($summaryObj.MostUsedPlugin)
Most active computer: $($summaryObj.MostActiveComputer)
Source modified: NO
The Plant3D tables contain plugin and date fields but no computer-name column. The user field was not substituted for computer and was not published. Rows without a computer are rejected with COMPUTER_NOT_FOUND.
Top plugins:
$topPlugins
Top computers:
$topComputers
Repeat:
./tools/plugin-usage/Build-Plant3DExcelUsageMatrix.ps1 -SourceRoot C:/dev/TEMP/CheckPlugin -SheetName Plant3D -OutputDirectory ./migration/audits/plugin-usage-plant3d-xlsx-20260731
SourceFile values are anonymized as source-0001.xlsx. Project numbers, names, users and paths are not stored.
"@
$readme=[regex]::Replace($readme,'(?m)[ \t]+$','')
[IO.File]::WriteAllText((Join-Path $OutputDirectory 'README.md'),$readme,$utf8)