Powershell и Excel. Часть 2: форматирование ячеек

Продолжаем работать в Excel через Powershell. Напомню, что в предыдущей части мы создали небольшую таблицу и заполнили её данными. Также напомню, что это не моё “изобретение”, а очень вольный перевод вот этих трёх статей.

Для начала подготовим плацдарм для работы: создадим объект Excel и заполним его начальными данными. Я не буду на этом останавливаться, так как это было подробно рассмотрено в прошлой статье.

# Создаём Excel
$Excel = New-Object -ComObject Excel.Application

# Делаем Excel видимым
$Excel.Visible = $true

# Добавляем рабочую книгу
$WorkBook = $Excel.Workbooks.Add()

# Цепляемся к первому листу
$DiskInformation = $WorkBook.Worksheets.Item(1)

# Переименовываем лист
$DiskInformation.Name = 'Информация о дисках'

# Заголовок таблицы (самая первая ячейка)
$Row = 1
$Column = 1
$DiskInformation.Cells.Item($Row, $Column) = 'Сведения о дисковом пространстве'

# Форматируем текст, чтобы он был похож на заголовок
$DiskInformation.Cells.Item($Row, $Column).Font.Size = 18
$DiskInformation.Cells.Item($Row, $Column).Font.Bold = $true
$DiskInformation.Cells.Item($Row, $Column).Font.ThemeFont = 1
$DiskInformation.Cells.Item($Row, $Column).Font.ThemeColor = 4
$DiskInformation.Cells.Item($Row, $Column).Font.ColorIndex = 55
$DiskInformation.Cells.Item($Row, $Column).Font.Color = 8210719

# Объединяем диапазон ячеек
$Range = $DiskInformation.Range('A1','G2')
$Range.Merge()

На данном этапе у нас будет одна текстовая строка, размещённая в диапазоне ячеек с A1 по G2, т.е. в двух строках и семи столбцах, что по умолчанию выглядит не очень презентабельно, так как текст выравнивается по нижнему краю:

Объединённые ячейки (неформатированные)

Чтобы текст в объединённых ячейках выглядел красивее его можно выровнять по вертикали по центру.

Все варианты вертикального выравнивания можно посмотреть в MSDN. А значения, которые нужно при этом использовать можно узнать выполнив команду:

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XLVAlign]) |
    Select-Object @{n="Name";e={"$_"}},value__

В результате мы увидим следующую таблицу:

Name	value__
xlVAlignTop	-4160
xlVAlignJustify	-4130
xlVAlignDistributed	-4117
xlVAlignCenter	-4108
xlVAlignBottom	-4107

Из таблицы видно, что для выравнивания по середине нужно использовать значение

–4108.

Выравниваем:

# Выравнивание по вертикали
$Range.VerticalAlignment = -4108

После выравнивания наш текст будет выглядеть уже красивее:

Объединённые ячейки (отформатированные)

Переходим к заполнению таблицы данными.

Для начала переходим на следующую строку. Так как в последствии вокруг таблицы мы нарисуем рамку, нам понадобится номер начальной строки, поэтому сохраним его в отдельной переменной.

Начинаем с шапки таблицы:

# Переходим на следующую строку
$Row++; $Row++

# Номер начальной строки
$InitialRow = $Row

# Заполняем шапку таблицы, устанавливая цвет фона ячейки и текст жирным
$DiskInformation.Cells.Item($Row, $Column) = 'Буква диска'
$DiskInformation.Cells.Item($Row, $Column).Interior.ColorIndex = 15
$DiskInformation.Cells.Item($Row, $Column).Font.Bold = $true
$Column++

$DiskInformation.Cells.Item($Row, $Column) = 'Метка'
$DiskInformation.Cells.Item($Row, $Column).Interior.ColorIndex = 15
$DiskInformation.Cells.Item($Row, $Column).Font.Bold = $true
$Column++

$DiskInformation.Cells.Item($Row, $Column) = 'Размер'
$DiskInformation.Cells.Item($Row, $Column).Interior.ColorIndex = 15
$DiskInformation.Cells.Item($Row, $Column).Font.Bold = $true
$Column++

$DiskInformation.Cells.Item($Row, $Column) = 'Занято'
$DiskInformation.Cells.Item($Row, $Column).Interior.ColorIndex = 15
$DiskInformation.Cells.Item($Row, $Column).Font.Bold = $true
$Column++

$DiskInformation.Cells.Item($Row, $Column) = 'Свободно'
$DiskInformation.Cells.Item($Row, $Column).Interior.ColorIndex = 15
$DiskInformation.Cells.Item($Row, $Column).Font.Bold = $true
$Column++

$DiskInformation.Cells.Item($Row, $Column) = 'Свободно, %'
$DiskInformation.Cells.Item($Row, $Column).Interior.ColorIndex = 15
$DiskInformation.Cells.Item($Row, $Column).Font.Bold = $true

# Переходим на следующую строку, возвращаемся в первый столбец
$Row++
$Column = 1

Сама таблица заполняется в цикле по логическим дискам:

Get-WmiObject Win32_LogicalDisk -Filter "DriveType = 3" | ForEach-Object {...}

Фильтрация нужна для того, чтобы исключить из рассмотрения CD/DVD диски (которые чаще всего пустые, и следовательно их размер будет равен нулю).

В цикле выводим в таблицу, интересующую нас информацию:

# Буква
$DiskInformation.Cells.Item($Row, $Column) = $_.DeviceID
$Column++

# Метка
$DiskInformation.Cells.Item($Row, $Column) = $_.VolumeName
$Column++

# Размер
$DiskInformation.Cells.Item($Row, $Column) = ([Math]::Round($_.Size/1GB, 2))
$Column++

# Занято
$DiskInformation.Cells.Item($Row, $Column) =
    [math]::Round((($_.Size - $_.FreeSpace)/1GB),2)
$Column++

# Свободно
$DiskInformation.Cells.Item($Row, $Column) =
    ([Math]::Round($_.FreeSpace/1GB, 2))
$Column++

# Сводобно, %
$DiskInformation.Cells.Item($Row, $Column) =
    ("{0:P}" -f ($_.FreeSpace / $_.Size))

А также раскрашиваем строки в зависимости от процента свободного места на диске. Для простоты я раскрашиваю строку, относящуюся к конкретному диску в жёлтый цвет, если свободного места на нём меньше 5 ГБ, и в красный цвет, если свободного места меньше 1 ГБ:

# Смотрим на заполненность дисков и раскрашиваем

# Выделяем строку таблицы
$Range = $DiskInformation.Range(("A{0}" -f $Row),("F{0}" -f $Row))
$Range.Select() | Out-Null

# Если свободного места меньше 1 ГБ
if ($_.FreeSpace -lt 1GB)
{
    # Подсвечиваем красным
    $Range.Interior.ColorIndex = 3
}

# Если свободного места меньше 5 ГБ
elseif ($_.FreeSpace -lt 5GB)
{
    # Подсвечиваем жёлтым
    $Range.Interior.ColorIndex = 6
}

# Переходим на следующую строку и возвращаемся к первой строке
$Column = 1
$Row++

Смотрим, что получилось:

 

Результаты работы (неформатированные)

Как видно на дисках C и D свободного места меньше 5 ГБ, поэтому соответствующие строки подсвечены жёлтым цветом. А на Диске F свободного места вообще меньше 1 ГБ, поэтому он подсвечен красным цветом. Кстати, для быстрого забивания диска мне пришёл на помощь скрипт, генерирующий файл заданного размера.

В целом работа выполнена. Осталось привести таблицу к боле красивому виду. Для этого мы выровняем ширину столбцов в таблице и нарисуем рамку вокруг таблицы.

Начнём с рамки.

Сейчас курсор стоит уже на следующей строке, так как в цикле мы его перевели находясь ещё в цикле. А так как нам нужны только строки таблицы возвращаемся на одну строку назад и выделяем таблицу:

# Возвращаемся на одну строку назад
$Row--

# Выделяем нашу таблицу
$DataRange = $DiskInformation.Range(("A{0}" -f $InitialRow), ("F{0}" -f $Row))

Напомню, что $InitialRow – это номер начальный строки таблицы, который мы заранее сохранили.

Переходим к “рисованию” рамки – границы диапазона ячеек.

Чтобы узнать все возможные варианты границ диапазона ячеек можно выполнить команду:

[Enum]::getvalues([Microsoft.Office.Interop.Excel.XlBordersIndex]) |
    Select-Object @{n="Name";e={"$_"}},value__

В результате получим таблицу:

Name	value__
xlDiagonalDown	5
xlDiagonalUp	6
xlEdgeLeft	7
xlEdgeTop	8
xlEdgeBottom	9
xlEdgeRight	10
xlInsideVertical	11
xlInsideHorizontal	12

Как видно из таблицы для рамки подходят значения с 7 по 12.

“Рисуем”:

7..12 | ForEach-Object `
{
    $DataRange.Borders.Item($_).LineStyle = 1
    $DataRange.Borders.Item($_).Weight = 2
}

Подгоняем ширину столбцов:

# Подгоняем ширину столбцов
$UsedRange = $DiskInformation.UsedRange
$UsedRange.EntireColumn.AutoFit() | Out-Null

В результате получается вот так:

Результаты работы (отформатированные)

Осталось сохранить полученный результат и выйти из Excel:

# Сохраняем результат и выходим
$WorkBook.SaveAs("C:\temp\DiskSpace.xlsx")
$Excel.Quit()