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

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, т.е. в двух строках и семи столбцах, что по умолчанию выглядит не очень презентабельно, так как текст выравнивается по нижнему краю:

Excel - DiskInformation-1

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

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

Все варианты вертикального выравнивания можно посмотреть в 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

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

Excel - DiskInformation-2

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

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

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

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

# Переходим на следующую строку
$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++

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

 Excel - DiskInformation-3

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

Как видно на дисках 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 - DiskInformation-4

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

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

# Сохраняем результат и выходим
$WorkBook.SaveAs("C:\temp\DiskSpace.xlsx")
$Excel.Quit()
Реклама
Рубрики:Powershell Метки: , , ,
  1. frankk
    26/09/2013 в 15:12

    А не подскажешь вот такое по экселю. Сегодня бьюсь, но ни как не получается.
    $file = «C:\posh\test.xls»
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $true
    $workbook = $excel.Workbooks.Open($file)
    $row = 6
    $column = 4
    $sheet = $workbook.Worksheets.Item(1)
    Затем произвожу операцию по выборке и если условие не выполняется, удаляю не нужную строку командой
    $sheet.Rows.Item($row).delete()
    До этого момента проблем нет, но если я хочу строку вырезать или скопировать выходит ошибка, пишет что значение NULL и все. Скопировать или вырезать не удается. Пытаюсь это сделать вот такой командой
    $sheet.Rows.Item($row).copy()

    И еще вопрос, вот такая связка не выдает мне результат, почему? Разве это не объект?
    $sheet.Rows.Item($row) | Get- Member
    переменная row содержит номер строки

    • 26/09/2013 в 16:21

      frankk :

      … Скопировать или вырезать не удается. Пытаюсь это сделать вот такой командой
      $sheet.Rows.Item($row).copy()

      И еще вопрос, вот такая связка не выдает мне результат, почему? Разве это не объект?
      $sheet.Rows.Item($row) | Get- Member
      переменная row содержит номер строки

      Затрудняюсь сказать, у меня замечательно копирует, а вторая команда выводит кучу свойств и методов.
      А Excel и конкретно этот файл открыт во время выполнения проблемных команд?

      • frankk
        26/09/2013 в 16:55

        Да, открыт($excel.Visible = $true), у меня стало закрадываться подозрение, что это как-то связано с тем что пока я экспериментировал с командами у меня переменные перестали освобождаться и брать новые команды, так как даже строчка с удалением перестала отрабатывать и стала выдавать кучу ошибок, перезапустил powershell и «убил» службы в диспетчере, опять стала выполняться. Буду смотреть дальше. Все равно спасибо за помощь! 🙂

  2. frankk
    27/09/2013 в 16:50

    Наверно уже достал 🙂 Но моя борьба с экселем продолжается :). Может ты подскажешь, своим опытным взглядом. в чем может быть причина? Имеем такой код
    $file = «C:\posh\test.xls»
    $col = 5000
    $startTime = Get-Date -DisplayHint Time
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $true
    $workbook = $excel.Workbooks.Open($file)
    $row = 6
    $row1 = 2
    $n = 1
    $workbook.Worksheets.Add() | Out-Null
    $sheet1 = $workbook.Worksheets.Item(1)
    $sheet1.Name = ‘>250’
    $sheet = $workbook.Worksheets.Item(2)
    $sheet.Activate()
    $sheet.Rows.Item(5).Copy() | Out-Null
    $sheet1.Rows.Item(1).PasteSpecial() | Out-Null
    While ( $n -le $col){
    $zna = $sheet.Cells.Item($row,20).Value()
    $zna
    if ($zna -lt 250) {
    $row++
    $n++
    } else {
    $sheet.Rows.Item($row).Cut() | Out-Null
    $sheet1.Rows.Item($row1).PasteSpecial() | Out-Null
    $sheet.Rows.Item($row).Delete() | Out-Null
    $row1++
    $n++
    $sheet.Activate() | Out-Null
    }
    }
    $sheet1.Activate()
    Вот все что до цикла, выполняется нормально, копирует и переносит. Но как доходит до цикла, а вернее до события когда начинает отрабатывать else, получаем вот такое
    «Исключение при вызове «PasteSpecial» с «0» аргументами: «Метод PasteSpecial из
    класса Range завершен неверно»
    строка:30 знак:4
    + $sheet1.Rows.Item($row1).PasteSpecial() | Out-Null
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation»

    Вот что ему может не нравится? Может у тебя есть мысли, ну или опыт. Да, команду удаление строки ввел потому что при вырезании он не удаляет эту строчку, а просто оставляет пустой.

    • 30/09/2013 в 09:54

      Та не, всё нормально.

      А по поводу ошибки, то она вылазит после вырезания
      $sheet.Rows.Item($row).Cut() | Out-Null

      Если вместо вырезания текста сделать копирование (т.е. Copy вместо Cut), то всё работает. А там дальше по тексту всё равно удаление этой ячейки идёт
      $sheet.Rows.Item($row).Delete() | Out-Null

      • frankk
        30/09/2013 в 11:53

        Да то что заменить на копирование можно, я и сам догадался 🙂 Так собственно и сделал, тем более, как ты правильно заметил, все равно потом удалять строку. Просто хотел для себя понять, ПОЧЕМУ 🙂 И что еще интересное заметил, если убрать строку с удалением, то в кеше, вырезаная строка остается висеть и обычной комбинацией CTRL+V прекрасно вставляется, но если строку с удалением оставить, то кеш, очищается. Потому что сейчас. пока, в голове крутится только одна мысль по этому поводу : » А, Гейтс, его знает, почему» :).

        • 30/09/2013 в 12:55

          Для меня вообще загадка как Excel с буфером работает… про Гейтса улыбнуло 🙂

  3. frankk
    02/10/2013 в 16:05

    Ну что же, и в очередной раз спасибо за посильную и оперативную помощь\подсказки\наставления (нужное подчеркнуть). Всеми правда или не правдами скрипт заработал, ошибок не выявлено, теперь хоть не ручками файлы по 13К строк лопатить 🙂 два с половиной часа работы(скрипта) и все готово 🙂

    • 02/10/2013 в 17:13

      мда… не хилые такие файлы и задачи 🙂

  4. Андрей
    15/11/2014 в 15:55

    Долго искал, но ответ найти не получилось. Пытался вытащить свойство через пример описанный в оригинале, но то ли версия Excel не та, то ещё что..Команда

    [Enum]::getvalues([Microsoft.Office.Interop.Excel.XLHAlign]) |
    Select-Object @{n=»Name»;e={«$_»}},value__ результата не выдает никакого

    Нужно выровнять текст в ячейке по горизонтали. Подозреваю, что это будет -4***

  5. Андрей
    16/11/2014 в 00:14

    Решение было найдено. Если кому понадобится:
    выравнивание по левому краю -4131
    по центру -4108
    по правому краю -4152

  6. 18/12/2014 в 10:20

    Hello from France,

    Please, how can I add differnt font.color in «one» cell?

    Classique:
    $Rob.Cells.Item(36,13).Interior.ColorIndex = 4
    $Rob.Cells.Item(39,13) = $RXml

    But i have, all in one ligne:
    [Black]Week[/Black] [Red]51[/Red] [Black](day.[/Black] [Red]4[/Red] [Black])[/Black]

    My ademtion is now:
    $Rob.Cells.Item(48,2) = «Week: » + $RWeek + » (Day. » + ((get-date).DayOfWeek.value__) + «)»

    Thanks for your help!
    @r

    • 18/12/2014 в 23:41

      $Rob.Cells.Item(36,13).Interior.ColorIndex = 4 — right. What is the problem?
      What is $RXml?

  7. skat
    12/03/2015 в 14:11

    Здравствуйте подскажите, каким образом занести данные с файла 123.txt в Excel Спасибо.

    • Андрей
      12/03/2015 в 15:08

      я бы считал данные из файла и присвоил переменной. А потом уже вставил куда надо.
      Например:
      $a = Get-Content C:\123.txt
      $Excel = New-Object -ComObject Excel.Application
      $Excel.Visible = $true
      $WorkBook = $Excel.Workbooks.Add()
      $WorkSheet = $WorkBook.Worksheets.Item(1)
      $WorkSheet.Cells.Item(1, 2) = $a

      • 13/03/2015 в 09:55

        Так скорее всего не получится — Вы пытаетесь засунуть всё содержимое файла в одну ячейку.

    • 13/03/2015 в 09:53

      Для начала нужно знать, что из себя представляет файл 123.txt. Как я понимаю в нём какие-то табличные данные. Если так, то можно в самом Excel’е его открыть без использования Powershell. Нужно только определиться что выступает в качестве разделителя ячеек.

      • 13/03/2015 в 15:20

        это первый вариант…по разделителям…
        Второй, построчно считать файл из txt и заполнить таблицу построчно опять же.

    • 15/04/2015 в 12:07

      Коллеги, всем спасибо за обсуждение. Я тут всё-таки нацарапал как примерно может выглядеть такое конвертирование: http://wp.me/p1tmBU-fj. Приглашаю к обсуждению 🙂

  8. skat
    31/03/2015 в 01:27

    В файле символы по пять — девять букв, каждое слово нужно занести в отдельную ячейку, не нарушая целостность то-есть порядок должен быть как в файле 123.txt
    Есть мысли реализации ?

  9. 31/03/2015 в 06:40

    а файл можно увидеть? разделителем что является?

  10. 03/04/2015 в 16:27

    Написал скрипт по работе один, и возник по ходу дела вопрос, если сможешь, подскажи, вдруг сталкивался или есть мысли. Как говорится одна надежда — на тебя, всегда выручал когда речь шла о Powershell и Excel

    Есть данные записанные в одномерный массив, внутри массива их может быть от 1 до 50. Как их можно запихать все в одну ячейку? Есть какие то мысли?
    Просто, если прогонять через цикл и вставлять таким образом
    $WorkSheet.Cells.Item(1, 2) = $a.Value[$n] то в итоге получим только последнее значение массива.
    Заранее спасибо.

    • Андрей
      03/04/2015 в 19:23

      Если массив записан в переменную $a, то:
      $WorkSheet.Cells.Item(1, 2) = $a

      Собственно мой пост выше.

      • 04/04/2015 в 00:39

        Согласен. Т.е. другими словами нужно записать содержимое массива в ячейку? Если так, то приведенный выше вариант подходит, и не нужно никаких циклов.

  11. Dima
    23/01/2016 в 14:28

    Раскраска строк по значению из примера не заработала,вместо подсветки заполненных строк добавляется в конце ещё одна и закрашиватся.

  12. Андрей
    23/01/2016 в 16:33

    Из PoSh можно делать с Excel всё, что угодно. Если у вас что-то не получается, то это точно проблема в вашем коде. Но невозможно понять где ошибка не видя этого самого кода..

  13. Vasyl
    27/05/2016 в 10:40

    Підкажіть будь-ласка при копіюванні діапазону не вставляється в зазначене місце(наприкл. С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()

  14. sergey
    20/11/2016 в 15:17

    А как удалить столбцы? например мне нужно удалить столбцы C, D

  15. Андрей
    20/11/2016 в 18:42

    Удаление 3-го столбца:
    $WSheet.Columns.Item(3).Delete()

  1. No trackbacks yet.

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

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

Логотип WordPress.com

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

Фотография Twitter

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

Фотография Facebook

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

Google+ photo

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

Connecting to %s

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