Powershell и Excel. Часть 1: заполнение таблицы

Об инвентаризации не писал, наверное, только ленивый. Вот и я, чтобы не казаться ленивым, тоже решил взяться за это дело. Поводом для написания стало появление нескольких статей на эту тему. Меня даже заинтересовала не сама инвентаризация (что там инвентаризировать – дёргай нужные объекты, смотри их свойства), а работа с Excel’ем, так как всё руки не доходили попробовать. С Word’ом сталкиваться уже приходилось, а вот с Excel’ем ещё нет. Можно, конечно, не заморачиваться, и вывести всё в CSV-файл, но повторюсь – меня интересовала именно работа с Excel: заполнение и форматирование ячеек, раскраска, диаграммы и т.д. Но обо всём по порядку 🙂

Итак, прежде всего нужно создать объект Excel и сделать его видимым, чтоб видеть всю дальнейшую магию 🙂

# Созадём объект Excel
$Excel = New-Object -ComObject Excel.Application

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

Это равносильно запуску Excel. Далее необходимо создать файл (в терминологии Excel рабочую книгу):

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

В оригинале статьи автор говорит, что этой операцией добавляется три листа, и если остальные не нужны их можно/нужно удалить, и показывает как это сделать. Но у меня добавляется только один лист, не знаю с чем это связано, возможно в разных версиях офиса по разному, поэтому я на этом останавливаться  не буду.

Начинаем работать с первым листом. Для простоты  обращения к нему создаём соответствующую переменную:

$LogiclDisk = $WorkBook.Worksheets.Item(1)

Далее переименовываем лист (чтобы было не Лист1, Лист2 и т.д., а “человеческие” названия) и заполняем шапку таблицы:

# Переименовываем лист
$LogiclDisk.Name = 'Логические диски'

# Заполняем ячейки - шапку таблицы
$LogiclDisk.Cells.Item(1,1) = 'Буква диска'
$LogiclDisk.Cells.Item(1,2) = 'Метка'
$LogiclDisk.Cells.Item(1,3) = 'Размер (ГБ)'
$LogiclDisk.Cells.Item(1,4) = 'Свободно (ГБ)'

Как (наверное) понятно здесь мы пишем в каждую ячейку по очереди, первая цифра в скобках – номер строки, вторая – номер столбца.

Уже можно наслаждаться первыми результатами работы 🙂

Главное окно Excel

Пока смотрится криво из-за того, что надписи не влазят в ячейки, и хочется растянуть ячейки, но ничего страшного, мы это потом поправим.

Переходим на следующую строку, возвращаемся в первый столбец и в цикле заполняем таблицу данными по логическим дискам, после каждого диска переводим курсор (или как правильно назвать текущую ячейку?) на следующую строку и возвращаемся в первый столбец:

# Переходим на следующую строку...
$Row = 2
$Column = 1

# ... и заполняем данными в цикле по логическим разделам
Get-WmiObject Win32_LogicalDisk | ForEach-Object `
{
    # DeviceID
    $LogiclDisk.Cells.Item($Row, $Column) = $_.DeviceID
    $Column++
    
    # VolumeName
    $LogiclDisk.Cells.Item($Row, $Column) = $_.VolumeName
    $Column++
    
    # Size
    $LogiclDisk.Cells.Item($Row, $Column) = ([Math]::Round($_.Size/1GB, 2))
    $Column++
    
    # Free Space
    $LogiclDisk.Cells.Item($Row, $Column) = ([Math]::Round($_.FreeSpace/1GB, 2))
    
    # Переходим на следующую строку и возвращаемся в первую колонку
    $Row++
    $Column = 1
}

Размеры дисков переводятся в гигабайты, и чтобы много цифр не сбивали с толку, округляются до двух знаков после запятой.

Смотрим результат:

Логические диски в Excel

мдя… многовато дисков, надо-бы их немножко пообъединять, создавались когда-то временно для тестовых целей, но как известно нет ничего более постоянного чем временное 🙂

Диски с нулевыми размерами это два DVD-привода и один виртуальный.

Осталось немного приукрасить внешний вид – выделим шапку таблицы (первая строка) жирным, и отрегулируем ширину ячеек по ширине текста (до этого момента я даже не подозревал, что Excel такое умеет:)):

# Выделяем жирным шапку таблицы
$LogiclDisk.Rows.Item(1).Font.Bold = $true

# Выравниваем для того, чтобы их содержимое корректно отображалось в ячейке
$UsedRange = $LogiclDisk.UsedRange
$UsedRange.EntireColumn.AutoFit() | Out-Null

Переменная $UsedRange содержит все занятые ячейки (эквивалентно однократному нажатию Ctrl+A)

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

Готовая таблица

Красота да и только 🙂

С логическими дисками разобрались, переходим к физическим.

Создадим для них отдельный лист:

# Добавляем лист
$WorkBook.Worksheets.Add()

Тут есть один нюанс, заключающийся в том, что листы добавляются в обратном порядке, т.е. только что добавленный лист будет иметь номер 1, а предыдущий станет номером 2. Поэтому выделяем только что созданный лист, и делаем всё то же самое, только с физическими дисками:

$PhysicalDrive = $WorkBook.Worksheets.Item(1)

# Переименовываем лист
$PhysicalDrive.Name = 'Физические диски'

# Заполняем ячейки - шапку таблицы
$PhysicalDrive.Cells.Item(1,1) = 'Модель'
$PhysicalDrive.Cells.Item(1,2) = 'Размер (ГБ)'
$PhysicalDrive.Cells.Item(1,3) = 'Кол-во разделов'
$PhysicalDrive.Cells.Item(1,4) = 'Тип'

# Переходим на следующую строку...
$Row = 2
$Column = 1

# ... и заполняем данными в цикле по физическим дискам
Get-WmiObject Win32_DiskDrive | ForEach-Object `
{
    # Model
    $PhysicalDrive.Cells.Item($Row, $Column) = $_.Model
    $Column++
    
    # Size
    $PhysicalDrive.Cells.Item($Row, $Column) = ([Math]::Round($_.Size /1GB, 1))
    $Column++
    
    # Partitions
    $PhysicalDrive.Cells.Item($Row, $Column) = $_.Partitions
    $Column++
    
    # InterfaceType
    $PhysicalDrive.Cells.Item($Row, $Column) = $_.InterfaceType
    
    # Переходим на следующую строку и возвращаемся в первую колонку
    $Row++
    $Column = 1
}

# Выделяем жирным шапку
$PhysicalDrive.Rows.Item(1).Font.Bold = $true

# Выравниваем для того, чтобы их содержимое корректно отображалось в ячейке
$UsedRange = $PhysicalDrive.UsedRange
$UsedRange.EntireColumn.AutoFit() | Out-Null

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

Логические диски

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

$WorkBook.SaveAs('C:\temp\Report.xlsx')
$Excel.Quit()

На сегодня всё :). В следующих частях мы научимся объединять и раскрашивать ячейки, а также строить диаграммы.