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

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 - Main window

Главное окно 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 - LogicalDisks

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

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

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

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

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

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

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

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

Excel - LogicalDisks - Final

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

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

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

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

# Добавляем лист
$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 - PhysicalDisks

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

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

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

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

Реклама
Рубрики:Powershell Метки: , , ,
  1. 12/10/2013 в 09:49

    здравствуйте, подскажите пожалуйста, как правильно вызывать метод КОПИРОВАНИЯ листа в XLS. Мой способ выдает «метод copy из класса worksheet завершен неверно»

    Я использую следующий код:

    $Excel = New-Object -ComObject «Excel.Application»
    $Workbook = $Excel.Workbooks.open($filepath)

    #для каждого листа в исходном файле
    foreach($Worksheet in $Workbook.Worksheets) {

    # создаю новый файл
    $Excel01 = New-Object -ComObject Excel.Application

    #добавляю туда новый лист
    $Workbook01 = $Excel01.Workbooks.Add()
    $Worksheet01 = $Workbook01.Worksheets.Item(1)

    #пробую скопировать один лист в другой
    $Worksheet.copy($Worksheet01)

    …..
    }

    • 14/10/2013 в 10:35

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

      foreach ($Worksheet in $Workbook.Worksheets)
      {
      # Создаём новый файл
      $Workbook01 = $Excel.Workbooks.Add()

      # Выбираем лист
      $Worksheet01 = $Workbook01.Worksheets.Item(1)

      # Копируем
      $Worksheet.Copy($Worksheet01)
      }

  2. Askii
    11/11/2013 в 06:30

    Я предпочитаю собирать эксель-файлы через формирование папок с xml-данными. Ком-объекты дорогие по ресурсам, кроме того, связь с ком-объектом может упасть, если данных для передачи много.

  3. Андрей Маркин
    05/04/2015 в 21:34

    Я новичок в PS — потребовалось автоматизировать кое-что на рабочем месте (я врач).
    Не могу разобраться — как обратиться из PowerShell к ячейке листа по ее имени, а не по индексной ссылке.

    Есть шаблон Excel (template), на листе «Source_Data» есть именованные ячейки:

    B2 — UterusHeight

    Через PS открываю новую книгу из шаблона, и вношу в данные ячейки произвольные данные:

    $DocumentTemplateFile = «C:\Excel\Test_template.xltx»
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $true
    $WorkBook = $Excel.Workbooks.Open($DocumentTemplateFile)
    $SrcDataWS = $WorkBook.Worksheets.Item(‘Source_Data’)
    $ProtocolWS = $WorkBook.Worksheets.Item(‘Протокол’)
    $SrcDataWS.Cells.Item(1,2) = 86
    $SrcDataWS.Cells.Item(2,2) = 45
    $SrcDataWS.Cells.Item(3,2) = 34

    Проверяем, определено-ли имя ячейки — $SrcDataWS.Cells.Item(2,2).Name:

    Application : System.__ComObject
    Creator : 1480803660
    Parent : System.__ComObject
    _Default : =Source_Data!$B$2
    Index : 1
    Category :
    CategoryLocal :
    MacroType : -4142
    Name : UterusHeight
    RefersTo : =Source_Data!$B$2
    ShortcutKey :
    Value : =Source_Data!$B$2
    Visible : True
    NameLocal : UterusHeight
    RefersToLocal : =Source_Data!$B$2
    RefersToR1C1 : =Source_Data!R2C2
    RefersToR1C1Local : =Source_Data!R2C2
    RefersToRange : System.__ComObject
    Comment :
    WorkbookParameter : False
    ValidWorkbookParameter : True

    Как я могу обратиться из PowerShell к данной ячейки именно по ее имени «UterusHeight» без указания ее индекса как Item(2,2) ?

    Это необходимо чтобы вносить данные из txt файла не по позициям, а по именам ячеек.

    Спасибо!

    • 06/04/2015 в 10:38

      Перефразируя Зелёного из мультика «Тайна третьей планеты»: «Если врачи начнут программировать, то мне в IT делать нечего» 🙂

      По поводу вашего вопроса попробуйте так:

      $SrcDataWS.Range(‘UterusHeight’).formula = ‘Ваши данные’

  4. redFactory
    24/09/2015 в 17:20

    >> Но у меня добавляется только один лист
    File->Options->General->группа When creating new workbooks-> галка Include this many sheets — количество листов в новой книге

    • 25/09/2015 в 09:41

      «Вон оно чё, Михалыч…» Спасибо, буду знать 🙂

      • redFactory
        25/09/2015 в 15:38

        P.S. спасибо за статью. 🙂

  5. Vasyl
    27/05/2016 в 10:29

    Підкажіть будь-ласка при копіюванні діапазону не вставляється в зазначене місце(наприкл. С1) а вставляє з початку листа(з А1)? Як заставити вставити в тому місці де потрібно?
    $path = “D:\ROBOTA\PowerShell\Exp\file1.xlsx”
    $Excel = New-Object -ComObject excel.application
    $Excel.visible = $true
    $Workbook = $excel.Workbooks.open($path)
    $Worksheet = $Workbook.WorkSheets.item(1)
    $worksheet.activate()
    $range = $WorkSheet.Range(«A1:B1»).EntireColumn
    $range.Copy() | out-null
    $Worksheet = $Workbook.Worksheets.item(2)
    $Range = $Worksheet.Range(«C1»)
    $Worksheet.Paste()

    • 27/05/2016 в 12:39

      Після того як обрали зазначене місце (у Вашом прикладі це $Range = $Worksheet.Range(«C1»)) Використовуйте метод PasteSpecial():

      $Range = $Worksheet.Range(«C1»)
      $Range.PasteSpecial()

      Бо Ви вставляєте не в лист (не в $Worksheet), а в певне місце ($Range)

  6. Vasyl
    30/05/2016 в 10:47

    Дякую, я потім побачив свою помилку, дійсно все запрацювало, але з’явилась нова проблема з розгалуженням. Знайшов схожий скрипт але він не читає з вказаних комірок:
    $num = «Бар»

    $file1 = “D:\ROBOTA\PowerShell\Exp\file1.xlsx”

    $excel = New-Object -ComObject Excel.Application

    $excel.Visible = $true

    $EWB = $excel.Workbooks.Open($file1)

    $EWS = $EWB.Worksheets.Item(1)

    for ($i = 1; $i -le 4; $i++) {
    if ( $EWS.Cells.Item($i, 3) -eq $num )
    { $EWS.Cells.Item($i, 4) = ‘Plus’}
    else {$EWS.Cells.Item($i, 4) = ‘Minus’}
    }
    в усіх клітинках пише ‘Minus’?

    • 30/05/2016 в 12:59

      Ви хоча б детальніше описали, що у файлі, що має писати 🙂
      На скільки я зрозумів в залежності від значення в одному стовбці, має писатися ‘Plus’, чи ‘Minus’ у сусідньму, так?
      Якщо так, то строку

      if ( $EWS.Cells.Item($i, 3) -eq $num )

      треба змінити на

      if ( $EWS.Cells.Item($i, 3).text -eq $num )

      оскільки $EWS.Cells.Item($i, 3) — це об`єкт, що містить комірку. Цей об`єкт має купу властивостей та методів. В даному випадку нас цікавить лише вміст комірки, тобто нам потрібна властивість text.

  7. Алексей
    03/05/2017 в 09:04

    Спасибо большое за решение, мне очень пригодилось, и все понятно. Мне было важно заполнять данные по столбцам сверху в низ, а через экспорт csv не получалось.

  1. 26/03/2013 в 12:06

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

%d такие блоггеры, как: