Привет, Хабр! Сегодня расскажу, как я автоматизировал поиск внешних ссылок в Excel-файлах с помощью PowerShell, и какие технические сложности пришлось преодолеть по пути. История включает работу с COM-объектами, разбор ZIP-архивов, XML-парсинг, многопоточность и создание GUI на Windows Forms.
Предыстория и постановка задачи
В нашей компании Excel используется как основной инструмент для финансовой отчётности. Со временем образовалась сложная экосистема взаимосвязанных файлов, где изменение одного может повлиять на работу десятков других. Задача состояла в том, чтобы найти все файлы, которые содержат ссылки на конкретный источник данных.
Исходные требования:
-
Поиск по всем типам Excel-файлов (XLSX, XLSM, XLS, XLSB)
-
Рекурсивный обход папок
-
Обработка ошибок и повреждённых файлов
-
Понятный интерфейс для конечного пользователя
Технический анализ форматов Excel
Первым делом разберёмся с форматами файлов:
-
XLSX/XLSM — Office Open XML, по сути ZIP-архив с XML-файлами
-
XLS — бинарный формат Legacy Excel
-
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-файлов
-
Имеет удобный графический интерфейс
-
Работает в многопоточном режиме
-
Корректно обрабатывает ошибки
-
Ведёт подробное логирование
-
Формирует понятные отчёты
ссылка на оригинал статьи https://habr.com/ru/articles/910454/
Добавить комментарий