Автоматизация создания сводного отчета в Excel с помощью PowerShell

Всем привет!

Работа тестировщика заключается не только в непосредственном тестировании, но и в приведении результатов этого тестирования в удобный для чтения и понимания вид. В случае, когда нужно сделать один отчет из множества, ручное копирование может привести к ошибкам, не говоря уже о длительности и однообразии данного занятия. Поэтому сегодня я расскажу о powershell скрипте, который написал для того, чтобы объединить два Excel отчета в один сводный.


Итак, дано:
1. Excel файл с двумя листами - Smoke test cases на одном и результаты прохождения этих кейсов на другом. Листы связаны ссылками на ячейки - на странице с кэйсами происходит подсчет количества тестов, из них прошедшие/упавшие и т.п. и заносится в таблицу результатов;
2. Второй Excel файл тоже с двумя листами - Full testing и так же связные результаты прохождения.

Необходимо объединить их в один Excel файл так, чтобы все ссылки между листами подтягивались и в сводном отчёте.

Решение:
Самым простым решением будет объединение, т.е. мы расположим листы рядом друг с другом. Так как все данные для листов подтягиваются по их названиям, проблем с нумерацией листов у нас тоже не возникнет.

Создаём файл сводного отчета. Для удобства файл скрипта расположим около отчета и начинаем писать:
$reportDir = $myinvocation.MyCommand.Definition | split-path -parent
# destination's folder, it gets the script running folder
$smokeTestDir = ($reportDir | split-path -parent) + '\Smoke_Tests' # source 1 folder
$fullDir = ($reportDir | split-path -parent) + '\Full_Testing' # source 2 folder

Как видно из кода, все файлы у меня лежат в разных папках. Пути сделаны относительными, чтобы минимально зависеть от положения файлов.
$reportPath = $reportDir + '\ConsolidatedReport.xlsx' # destination's fullpath
$smokeTests = $smokeTestDir + '\Smoke_TestCases.xlsx' # source 1 fullpath
$fullTests = $fullDir + '\Full_TestCases.xlsx' # source 2 fullpath

здесь мы подготовили переменные путей, где:
destination's fullpath - путь к сводному отчету
source 1 fullpath - путь к первому источнику
source 2 fullpath -  путь ко второму источнику

Далее, нужно создать объект с приложением и открыть нужные нам файлы:
$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user
$wb1 = $xl.workbooks.open($reportPath) # open target

Удалим то, что осталось от предыдущего отчета. Всё же останется один лист, т.к. без листов документ существовать не может, о нем мы позаботимся позже:
while ($wb1.sheets.item(2).Name -ne $null) # delete existing sheets
{
$wb1.sheets.item(2).delete()
}

Открываем источники:
$wb3 = $xl.workbooks.open($smokeTests, $null, $true) # open source 1 (Smoke), readonly
$wb2 = $xl.workbooks.open($fullTests, $null, $true) # open source 2 (Full), readonly

И копируем листы по названию в сводный:
$sh1_wb1 = $wb1.sheets.item(1) # first sheet in destination workbook
$sheetToCopy = $wb3.sheets.item('Smoke_TCs') # source sheet to copy
$sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook

$sh1_wb1 = $wb1.sheets.item(2)
$sheetToCopy = $wb2.sheets.item('Full_TCs')
$sheetToCopy.copy($sh1_wb1)

$sh1_wb1 = $wb1.sheets.item(3)
$sheetToCopy = $wb3.sheets.item('Smoke_Summary')
$sheetToCopy.copy($sh1_wb1)

$sh1_wb1 = $wb1.sheets.item(4)
$sheetToCopy = $wb2.sheets.item('Full_Summary')
$sheetToCopy.copy($sh1_wb1)

Не забыли и про лишний лист, про который говорилось в начале, он у нас будет пятым, так как вставили мы четыре листа:
$sh1_wb1 = $wb1.sheets.item(5) # delete extra file
$sh1_wb1.delete()

Осталось только сохранить, что нужно и всё закрыть:
$wb3.close($false) # close source 1 (Smoke) workbook w/o saving
$wb2.close($false) # close source 2 (VM) workbook w/o saving
$wb1.close($true) # close and save destination workbook
$xl.quit()
spps -n excel

Таким образом, мы получили один сводный отчет из двух частичных с сохранением всех ссылок между листами. В данном случае количество листов и документов минимально, однако, в случаях с большими системами это очень удобный способ создания общей картины приложения.
Сам скрипт можно найти на GitHub.

Спасибо за внимание, вопросы, по обычаю, в комментарии!