Привет, Хабр! Сегодня расскажу, как я автоматизировал поиск внешних ссылок в Excel-файлах с помощью PowerShell, и какие технические сложности пришлось преодолеть по пути. История включает работу с COM-объектами, разбор ZIP-архивов, XML-парсинг, многопоточность и создание GUI на Windows Forms.

Предыстория и постановка задачи

В нашей компании Excel используется как основной инструмент для финансовой отчётности. Со временем образовалась сложная экосистема взаимосвязанных файлов, где изменение одного может повлиять на работу десятков других. Задача состояла в том, чтобы найти все файлы, которые содержат ссылки на конкретный источник данных.

Исходные требования:

• Поиск по всем типам Excel-файлов (XLSX, XLSM, XLS, XLSB)

• Рекурсивный обход папок

• Обработка ошибок и повреждённых файлов

• Понятный интерфейс для конечного пользователя

Технический анализ форматов Excel

Первым делом разберёмся с форматами файлов:

1. XLSX/XLSM - Office Open XML, по сути ZIP-архив с XML-файлами

2. XLS - бинарный формат Legacy Excel

3. XLSB - бинарный формат с улучшенной производительностью

Для современных форматов (XLSX/XLSM) можно использовать следующий подход:

# Функция для извлечения внешних ссылок из XLSX/XLSM  
function Get-ExcelLinksFromModernFormat {  
    param (  
        [string]$FilePath  
    )  
      
    try {  
        # Создаём временную директорию  
        $tempPath = [System.IO.Path]::GetTempPath() + [System.Guid]::NewGuid().ToString()  
        New-Item -ItemType Directory -Path $tempPath | Out-Null  
          
        # Копируем Excel-файл во временную ZIP  
        $zipPath = Join-Path $tempPath "temp.zip"  
        Copy-Item -Path $FilePath -Destination $zipPath  
          
        # Распаковываем  
        Expand-Archive -Path $zipPath -DestinationPath $tempPath  
          
        # Ищем ссылки в workbook.xml  
        $workbookXml = Join-Path $tempPath "xl\workbook.xml"  
        if (Test-Path $workbookXml) {  
            [xml]$xml = Get-Content $workbookXml  
            $externalLinks = $xml.workbook.externalReferences.externalReference  
              
            foreach ($link in $externalLinks) {  
                # Получаем путь к связанному файлу из rels  
                $rId = $link.id  
                $relsPath = Join-Path $tempPath "xl\_rels\workbook.xml.rels"  
                [xml]$rels = Get-Content $relsPath  
                $target = $rels.Relationships.Relationship |   
                    Where-Object { $_.Id -eq $rId } |  
                    Select-Object -ExpandProperty Target  
                  
                [PSCustomObject]@{  
                    SourceFile = $FilePath  
                    LinkedFile = $target  
                    RelationType = "External"  
                }  
            }  
        }  
    }  
    catch {  
        Write-Warning "Ошибка при обработке файла $FilePath : $_"  
    }  
    finally {  
        # Очистка временных файлов  
        if (Test-Path $tempPath) {  
            Remove-Item -Path $tempPath -Recurse -Force  
        }  
    }  
}  

Для старых форматов приходится использовать COM-объект Excel:

# Функция для работы со старыми форматами через COM  
function Get-ExcelLinksFromLegacyFormat {  
    param (  
        [string]$FilePath  
    )  
      
    $excel = $null  
    $workbook = $null  
      
    try {  
        $excel = New-Object -ComObject Excel.Application  
        $excel.Visible = $false  
        $excel.DisplayAlerts = $false  
          
        # Открываем с отключенными макросами  
        $workbook = $excel.Workbooks.Open($FilePath, 0, $true)  
          
        # Получаем список внешних ссылок  
        $links = @($workbook.LinkSources([Microsoft.Office.Interop.Excel.XlLink]::xlExcelLinks))  
          
        foreach ($link in $links) {  
            [PSCustomObject]@{  
                SourceFile = $FilePath  
                LinkedFile = $link  
                RelationType = "External"  
            }  
        }  
    }  
    catch {  
        Write-Warning "Ошибка при обработке файла $FilePath : $_"  
    }  
    finally {  
        if ($workbook) {  
            $workbook.Close($false)  
        }  
        if ($excel) {  
            $excel.Quit()  
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null  
        }  
        [System.GC]::Collect()  
        [System.GC]::WaitForPendingFinalizers()  
    }  
}  

Многопоточная обработка

Для ускорения работы реализовал параллельную обработку файлов с помощью Jobs:

Codefunction Process-ExcelFilesInParallel {  
    param (  
        [string]$FolderPath,  
        [int]$MaxJobs = 3  
    )  
      
    $files = Get-ChildItem -Path $FolderPath -Recurse -Include "*.xlsx","*.xlsm","*.xls","*.xlsb"  
    $jobs = @()  
    $results = @()  
      
    foreach ($file in $files) {  
        # Ждём, если достигнут лимит параллельных задач  
        while ((Get-Job -State Running).Count -ge $MaxJobs) {  
            Start-Sleep -Milliseconds 500  
            Get-Job | Where-Object { $_.State -eq "Completed" } | ForEach-Object {  
                $results += Receive-Job -Job $_  
                Remove-Job -Job $_  
            }  
        }  
          
        # Запускаем новую задачу  
        $jobs += Start-Job -ScriptBlock {  
            param($file)  
            if ($file.Extension -in ".xlsx",".xlsm") {  
                Get-ExcelLinksFromModernFormat -FilePath $file.FullName  
            }  
            else {  
                Get-ExcelLinksFromLegacyFormat -FilePath $file.FullName  
            }  
        } -ArgumentList $file  
    }  
      
    # Ожидаем завершения всех задач  
    Wait-Job -Job $jobs | Out-Null  
    $results += Get-Job | Receive-Job  
    Get-Job | Remove-Job  
      
    return $results  
}  

Создание GUI на Windows Forms

Для удобства пользователей реализовал графический интерфейс с помощью Windows Forms:

powershellCopy CodeAdd-Type -AssemblyName System.Windows.Forms  
Add-Type -AssemblyName System.Drawing  
  
function Create-ExcelLinksFinderGUI {  
    $form = New-Object System.Windows.Forms.Form  
    $form.Text = "Excel Links Finder"  
    $form.Size = New-Object System.Drawing.Size(800,600)  
    $form.StartPosition = "CenterScreen"  
  
    # Контролы для выбора файла-источника  
    $sourceLabel = New-Object System.Windows.Forms.Label  
    $sourceLabel.Location = New-Object System.Drawing.Point(10,20)  
    $sourceLabel.Size = New-Object System.Drawing.Size(280,20)  
    $sourceLabel.Text = "Выберите файл для поиска ссылок:"  
    $form.Controls.Add($sourceLabel)  
  
    $sourceTextBox = New-Object System.Windows.Forms.TextBox  
    $sourceTextBox.Location = New-Object System.Drawing.Point(10,40)  
    $sourceTextBox.Size = New-Object System.Drawing.Size(580,20)  
    $form.Controls.Add($sourceTextBox)  
  
    $sourceBrowseButton = New-Object System.Windows.Forms.Button  
    $sourceBrowseButton.Location = New-Object System.Drawing.Point(600,40)  
    $sourceBrowseButton.Size = New-Object System.Drawing.Size(75,20)  
    $sourceBrowseButton.Text = "Обзор..."  
    $sourceBrowseButton.Add_Click({  
        $fileDialog = New-Object System.Windows.Forms.OpenFileDialog  
        $fileDialog.Filter = "Excel Files|*.xlsx;*.xlsm;*.xls;*.xlsb"  
        if ($fileDialog.ShowDialog() -eq 'OK') {  
            $sourceTextBox.Text = $fileDialog.FileName  
        }  
    })  
    $form.Controls.Add($sourceBrowseButton)  
  
    # Прогресс-бар  
    $progressBar = New-Object System.Windows.Forms.ProgressBar  
    $progressBar.Location = New-Object System.Drawing.Point(10,80)  
    $progressBar.Size = New-Object System.Drawing.Size(665,20)  
    $form.Controls.Add($progressBar)  
  
    # Лог-бокс  
    $logBox = New-Object System.Windows.Forms.RichTextBox  
    $logBox.Location = New-Object System.Drawing.Point(10,110)  
    $logBox.Size = New-Object System.Drawing.Size(665,400)  
    $logBox.ReadOnly = $true  
    $logBox.BackColor = [System.Drawing.Color]::White  
    $form.Controls.Add($logBox)  
  
    # Кнопка запуска  
    $startButton = New-Object System.Windows.Forms.Button  
    $startButton.Location = New-Object System.Drawing.Point(10,520)  
    $startButton.Size = New-Object System.Drawing.Size(150,30)  
    $startButton.Text = "Начать поиск"  
    $startButton.Add_Click({  
        $logBox.Clear()  
        $progressBar.Value = 0  
          
        if ([string]::IsNullOrEmpty($sourceTextBox.Text)) {  
            [System.Windows.Forms.MessageBox]::Show("Выберите файл для поиска!")  
            return  
        }  
  
        $startButton.Enabled = $false  
        $sourceBrowseButton.Enabled = $false  
  
        # Запускаем поиск в отдельном потоке  
        $searchJob = [System.ComponentModel.BackgroundWorker]::new()  
        $searchJob.WorkerReportsProgress = $true  
        $searchJob.Add_DoWork({  
            param($sender, $e)  
            Search-ExcelLinks -SourceFile $sourceTextBox.Text -Progress $sender  
        })  
        $searchJob.Add_ProgressChanged({  
            param($sender, $e)  
            $progressBar.Value = $e.ProgressPercentage  
            $logBox.AppendText($e.UserState + "`n")  
            $logBox.ScrollToCaret()  
        })  
        $searchJob.Add_RunWorkerCompleted({  
            $startButton.Enabled = $true  
            $sourceBrowseButton.Enabled = $true  
            [System.Windows.Forms.MessageBox]::Show("Поиск завершен!")  
        })  
        $searchJob.RunWorkerAsync()  
    })  
    $form.Controls.Add($startButton)  
  
    return $form  
}  

Обработка ошибок и безопасность

Важной частью стала реализация корректной обработки ошибок и проверки безопасности:

# Проверка и настройка политики выполнения  
function Set-ExecutionPolicy {  
    try {  
        $currentPolicy = Get-ExecutionPolicy  
        if ($currentPolicy -eq "Restricted") {  
            # Создаём самоподписанный сертификат  
            $cert = New-SelfSignedCertificate `  
                -Subject "CN=ExcelLinksFinder" `  
                -Type CodeSigningCert `  
                -CertStoreLocation "Cert:\CurrentUser\My"  
  
            # Подписываем скрипт  
            $scriptPath = $MyInvocation.MyCommand.Path  
            Set-AuthenticodeSignature -FilePath $scriptPath -Certificate $cert  
  
            # Устанавливаем политику  
            Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser -Force  
        }  
    }  
    catch {  
        Write-Error "Ошибка настройки политики выполнения: $_"  
        return $false  
    }  
    return $true  
}  
  
# Обработка ошибок Excel  
function Handle-ExcelError {  
    param (  
        [string]$FilePath,  
        [System.Exception]$Error  
    )  
      
    switch -Regex ($Error.Exception.Message) {  
        ".*is already open.*" {  
            Write-Warning "Файл $FilePath уже открыт в Excel"  
            return $false  
        }  
        ".*cannot access.*" {  
            Write-Warning "Нет доступа к файлу $FilePath"  
            return $false  
        }  
        ".*is corrupt.*" {  
            try {  
                # Пробуем восстановить файл  
                $excel = New-Object -ComObject Excel.Application  
                $excel.Visible = $false  
                $excel.DisplayAlerts = $false  
                $workbook = $excel.Workbooks.Open($FilePath, 6) # 6 = xlRepairFile  
                return $true  
            }  
            catch {  
                Write-Warning "Не удалось восстановить файл $FilePath"  
                return $false  
            }  
            finally {  
                if ($excel) {  
                    $excel.Quit()  
                    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)  
                }  
            }  
        }  
        default {  
            Write-Warning "Неизвестная ошибка при обработке $FilePath : $Error"  
            return $false  
        }  
    }  
}  

Оптимизация производительности

Для улучшения производительности реализовал кэширование и оптимизацию памяти:

# Кэш для хранения результатов  
$script:LinkCache = @{}  
  
function Get-CachedLinks {  
    param (  
        [string]$FilePath  
    )  
      
    $fileHash = (Get-FileHash -Path $FilePath).Hash  
      
    if ($script:LinkCache.ContainsKey($fileHash)) {  
        $cachedResult = $script:LinkCache[$fileHash]  
        $lastWrite = (Get-Item $FilePath).LastWriteTime  
          
        # Проверяем актуальность кэша  
        if ($lastWrite -le $cachedResult.CacheTime) {  
            return $cachedResult.Links  
        }  
    }  
      
    return $null  
}  
  
function Set-CachedLinks {  
    param (  
        [string]$FilePath,  
        [array]$Links  
    )  
      
    $fileHash = (Get-FileHash -Path $FilePath).Hash  
    $script:LinkCache[$fileHash] = @{  
        CacheTime = Get-Date  
        Links = $Links  
    }  
}  
  
# Очистка памяти  
function Clear-ExcelProcesses {  
    Get-Process excel -ErrorAction SilentlyContinue |   
    Where-Object { $_.MainWindowTitle -eq "" } |   
    Stop-Process -Force  
      
    [System.GC]::Collect()  
    [System.GC]::WaitForPendingFinalizers()  
}  

Логирование и отчётность

Реализовал подробное логирование и формирование отчётов:

odefunction Write-Log {  
    param(  
        [string]$Message,  
        [ValidateSet('Info','Warning','Error')]  
        [string]$Level = 'Info'  
    )  
      
    $logMessage = "{0} [{1}] {2}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Level, $Message  
      
    switch ($Level) {  
        'Info'    { Write-Host $logMessage -ForegroundColor Green }  
        'Warning' { Write-Host $logMessage -ForegroundColor Yellow }  
        'Error'   { Write-Host $logMessage -ForegroundColor Red }  
    }  
      
    # Записываем в файл  
    $logMessage | Out-File -FilePath "ExcelLinksFinder.log" -Append  
}  
  
function Export-Results {  
    param(  
        [array]$Results,  
        [string]$OutputPath  
    )  
      
    $excel = New-Object -ComObject Excel.Application  
    $excel.Visible = $false  
      
    $workbook = $excel.Workbooks.Add()  
    $worksheet = $workbook.Worksheets.Item(1)  
      
    # Заголовки  
    $worksheet.Cells.Item(1,1) = "Исходный файл"  
    $worksheet.Cells.Item(1,2) = "Связанный файл"  
    $worksheet.Cells.Item(1,3) = "Тип связи"  
    $worksheet.Cells.Item(1,4) = "Дата проверки"  
      
    # Данные  
    $row = 2  
    foreach ($result in $Results) {  
        $worksheet.Cells.Item($row,1) = $result.SourceFile  
        $worksheet.Cells.Item($row,2) = $result.LinkedFile  
        $worksheet.Cells.Item($row,3) = $result.RelationType  
        $worksheet.Cells.Item($row,4) = Get-Date -Format "yyyy-MM-dd HH:mm:ss"  
        $row++  
    }  
      
    # Форматирование  
    $range = $worksheet.Range("A1:D1")  
    $range.Font.Bold = $true  
    $worksheet.Columns.AutoFit()  
      
    # Сохранение  
    $workbook.SaveAs($OutputPath)  
    $workbook.Close($true)  
    $excel.Quit()  
      
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)  
}  

Заключение

В результате получился надёжный инструмент для поиска внешних ссылок в Excel-файлах, который:

• Поддерживает все форматы Excel-файлов

• Имеет удобный графический интерфейс

• Работает в многопоточном режиме

• Корректно обрабатывает ошибки

• Ведёт подробное логирование

• Формирует понятные отчёты