Особенности национальной автоматизации MS Excel

Ни для кого не секрет, что существуют программы, которые прекрасно работают с английской «культурой» и начинают «глючить», когда дело касается национальных «культур». При ближайшем рассмотрении выяснилось, что офисная программа MS Excel как раз из этого числа. Как обойти некоторые «особенности» MS Excel и пойдёт речь в этой статье.

Постановка задачи

В наличии имеется операционная система Windows 7 с русскими региональными настройками и англоязычный MS Office 2010. Требуется собрать информацию о запущенных в данный момент процессах системы и создать на их основе таблицу Excel. А для пущей наглядности ещё и круговую диаграмму построить. Задачу решать будем с помощью MS PowerShell.

Решение

Итак, начнём сначала — со сбора информации о процессах ОС и создания объекта Excel.Application:

$processes = Get-WmiObject -Class Win32_Process # Список запущенных процессов
$excel = New-Object -ComObject Excel.Application
$excel.SheetsInNewWorkbook = 1 # Число листов во вновь созданной книге Excel (по умолчанию 3)

Если сейчас выполнить эти три строчки, то, из-за несовпадения региональных настроек ОС и MS Office, на экране появится «загадочное» сообщение: «Exception setting «SheetsInNewWorkbook»: «Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))»». Решается это просто — необходимо текущие региональные настройки установить в значение «en-US»:

#Сохраняем прежнее значение региональных настроек
$OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
$culture = [System.Globalization.CultureInfo]"en-US" # Устанавливаем региональные настройки en-US
# Блок, который выполнится, если где-нибудь в этом скрипте произойдёт ошибка
trap 
{
    # *******************************************************************************
    # ****  Здесь будет код необходимый для корректного завершения работы скрипта ***
    # *******************************************************************************
    # Восстанавливаем прежние региональные настройки и заканчиваем работу скрипта
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture; break;
}
[System.Threading.Thread]::CurrentThread.CurrentCulture = $culture # Применяем новые региональные настройки

После этого пишем предыдущие три команды, делаем созданный экземпляр Excel видимым, создаём новую рабочую книгу:

$excel.visible = $true
$workbook = $excel.workbooks.add()
$workbook.worksheets.item(1).Name = "Processes" # Переименуем единственную таблицу для удобства
$sheet = $workbook.worksheets.item("Processes") # Выбираем таблицу “Processes” (получаем на неё ссылку)

Нужная таблица создана, теперь займёмся её оформлением. В первой строчке таблицы будет находиться заголовок. Чтобы визуально отделить его от остальной части таблицы, выделим жирным названия свойств и нарисуем рамку заголовка. Для этой цели будем использовать перечисления xLineStyle, xlColorIndex и xlBorderWeight. Перечисление xlChartType понадобится нам для оформления круговой диаграммы. Чтобы ещё более упростить себе жизнь создадим псевдонимы для каждого из четырех используемых типов перечислений. Для этого нужно преобразовать строку, представляющую собой название типа перечисления, в тип ([type]):

$row = 2    # Первая строка таблицы занята, поэтому данные будут записаны со второй строки

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]

Отформатируем первую строку таблицы:

for($b = 1; $b -le 2; $b++)
{
    $sheet.cells.item(1,$b).font.bold = $true
    $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
    $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
    $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}
 
# Дадим осмысленные имена столбцам таблицы
$sheet.cells.item(1,1) = "Name of Process"
$sheet.cells.item(1,2) = "Working Set Size"

Теперь поместим информацию о процессах, сохранённую в переменной $processes, в соответствующие ячейки таблицы. Организуем цикл для обхода коллекции сведений о процессах. В переменной цикла $process будет храниться текущий элемент коллекции. Из него в первый столбец мы поместим название процесса, а во второй — значение свойства workingSetSize.

foreach($process in $processes)
{
    $sheet.cells.item($row, 1) = $process.name
    $sheet.cells.item($row, 2) = $process.workingSetSize
 
    $x++
}
 
# Отрегулируем ширину столбцов в соответствии с содержимым
$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | Out-Null

Настало время добавить к рабочей книге диаграмму. Функция «charts.add()» рабочей книги по умолчанию добавит столбиковую диаграмму. И для того, чтобы получить диаграмму желаемого вида, нужно задать значение перечисления, определяющего тип диаграммы. Выберем одно из возможных значений типа $chartType — xl3DPieExploded (трёхмерная разрезанная круговая диаграмма). В качестве источника данных для диаграммы установим диапазон, определённый в переменной $range.

$workbook.charts.add() | Out-Null
$workbook.charts.item(1).Name = "Working Set Size"
$workbook.ActiveChart.chartType = $chartType::xl3DPieExploded
$workbook.ActiveChart.SetSourceData($range)

Теперь, для большего эффекта, покрутим созданную диаграмму на 360 градусов с шагом в 15 градусов:

for($i = 1; $i -le 360; $i +=15)
{
    $workbook.ActiveChart.rotation = $i
}

Для того, чтобы вся эта красота не пропала, нужно сохранить созданную книгу. Для начала проверим наличие таблицы с тем же именем с помощью командлета Test-Path. Если такую таблицу найдём, то удалим старый файл с помощью Remove-Item, а потом сохраним текущую рабочую книгу туда, куда указывает переменная $strPath.

$strPath = "path\to\file\file_name.xlsx"
if(Test-Path $strPath)
{
    Remove-Item $strPath
}
$excel.ActiveWorkbook.SaveAs($strPath)

Теперь Excel нужно закрыть. Для этого сначала освободим занятые ресурсы ($sheet, $range), закроем рабочую книгу, а затем и сам Excel.

$sheet = $null
$range = $null
 
$workbook.Close($false)
$excel.Quit()

И всё бы хорошо, но если вы сейчас посмотрите в диспетчер задач, то увидите, что процесс Excel там всё ещё «болтается». Это по меньшей мере не красиво. Чтобы этого не было, принудительно вызовем сборщик мусора, предварительно обнулив переменную $excel. И, конечно же, восстановим прежние региональные настройки.

$excel = $null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
 
# Восстанавливаем предыдущие региональные настройки
[System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture

Для большего удобства можно $strPath сделать параметром, а всё приведённое (окромя лирических отступлений) скопировать в один ps1-файл. Итак, скопировали и… запускаем! И ведь работает! Да ещё и так, как ожидается! Но только до того, момента, пока где-нибудь внутри этого файла не выскочит ошибка…
Управление перейдёт в блок «trap», а там пока, кроме возврата прежних региональных настроек ничего и нет. Поэтому на экране после сбоя останется недоделанная книга Excel, а при её закрытий в списке запущенных процессов так и останется «болтаться» Excel. Не очень радужная перспектива, не так ли?
Так вот, чтобы этого избежать, добавим в блок «trap» (вместо комментария со строкой «Здесь будет…») следующий код:

if($workbook -ne $null)
{
    $sheet = $null
    $range = $null
 
    $workbook.Close($false)
}
if($excel -ne $null)
{
    $excel.Quit()
    $excel = $null
    [GC]::Collect()
    [GC]::WaitForPendingFinalizers()
}