Экспорт Powershell в Excel

Теперь преобразовать все загруженные файлы .xls в соответствующие новые форматы несложно:

/
Настоящая статья распространяется по международной лицензии Attribution-NoDerivatives 4.0.
Все образцы кода лицензируются по международной лицензии Attribution 4.0. Используйте, делитесь и экспериментируйте свободно.

Вывод: приглашенный блогер Дуг Финке рассказывает о своем модуле PowerShell Excel.

Модуль PowerShell Excel — это совершенно новый, интересный и улучшенный способ взаимодействия с Microsoft Excel из Windows PowerShell. Кроме того, для бонусных баллов вам не нужно устанавливать Excel на целевом компьютере для создания электронной таблицы. Многие пользователи этого модуля создают электронные таблицы Excel на серверах, а затем другие сотрудники компании получают отчеты с центрального сервера. Или на последнем этапе сценария они могут отправить файл .xlsx по почте.

Вызов

До сих пор существовало несколько способов переноса данных в Excel. Один из способов — создать файл значений с разделителями-запятыми (.csv) с помощью команды Export-Csv, а затем открыть его в Excel, например:

Процесс получения | Export-Csv – NoType c:\Temp\ps.csv

Еще один способ получить данные в Excel – удаленное управление Excel. Используйте COM-интерфейс Excel, чтобы раскрутить его, создать рабочую книгу или рабочий лист, а затем прокрутить свои данные, чтобы поместить их в соответствующие ячейки. Вам нужно создать заголовки и добавить данные в правильную строку и столбец.

Вот фрагмент, который создает Excel, делает его видимым, а затем добавляет книгу:

$xl = New-Object -ComObject Excel.Application

Войдите в Office, откройте XML

Что, если бы вы могли просто сделать это?

Процесс получения | Экспорт-Excel c:\temp\ps.xlsx – Показать

В этом примере создается файл ps.xlsx, рабочая книга, рабочий лист, строка заголовка и систематизируются все данные в строках и столбцах. Параметр –Show запускает Excel и открывает файл ps.xlsx.

Это здорово (и работает с любыми данными в PowerShell). Плоские данные важны, как и визуальные эффекты.

Поднимитесь на ступеньку выше

Что, если бы вы могли создать следующий визуальный образ? Модуль PowerShell Excel позволяет создавать сводные таблицы и диаграммы Excel на основе преобразованных данных. Из сгенерированных выше данных (и сохраненных в отдельной электронной таблице в той же книге) можно легко создать сводную таблицу и диаграмму.

Вот сценарий:

Процесс получения | Где Компания |

Экспорт-Excel C:\Temp\ps.xlsx-Показать `

-IncludePivotTable -PivotRows Company -PivotData @Handles="sum">`

-IncludePivotChart -ChartType PieExploded3D

Командлеты IncludePivotTable и IncludePivotChart создают сводную таблицу и диаграмму. ChartType позволяет вам выбрать, какой тип диаграммы вы хотите (есть много вариантов).Параметры PivotRows и PivotData описывают, как сводить данные в таблицы.

Если вы запустите Get-Process, вы увидите каждый процесс, запущенный в вашей системе. Сводная таблица в Excel группирует информацию с помощью PivotRows и вычисляет измерения с помощью PivotData. Здесь вы говорите ему суммировать количество ручек. На предыдущем изображении количество дескрипторов суммируется из Get-Process и группируется по компаниям. Теперь вы можете видеть, что процессы, запущенные корпорацией Microsoft, имеют почти 50 тыс. дескрипторов.

Это краткий обзор

Я написал модуль Excel для подключения к экосистеме PowerShell, чтобы вы могли легко экспортировать любые данные в Excel точно так же, как в файл .csv. Вы можете передать результаты запроса SQL Server или REST API — список можно продолжать и продолжать.

Есть еще много интересного в этом модуле. Попробуйте функцию Import-Excel, которая позволяет читать электронную таблицу Excel и преобразовывать ее в объекты PowerShell. Он подходит для использования существующих электронных таблиц, применения вычислений (или добавления дополнительных данных из поиска) и быстрого создания новой электронной таблицы. Вот пример:

Где взять?

Существует несколько способов загрузки и установки этого модуля.

Галерея PowerShell Если вы используете Windows PowerShell 5.0, вы можете использовать новую команду Install-Module ImportExcel. Он вытащит модуль из галереи.

GitHub Вы также можете получить его с GitHub: dfinke/ImportExcel.

Несколько слов о GitHub…

Вы можете отметить проект, если он вам нравится. Вы также можете открыть вопросы, если у вас есть вопросы или вы нашли проблемы. Кроме того, вы можете установить модуль с GitHub. Вы также можете клонировать или разветвить проект. Это позволяет вам вносить необходимые изменения. Если вы хотите поделиться ими, вы можете создать запрос на извлечение, чтобы добавить его в основной проект.

Присоединяйтесь! Перейдите на GitHub и опубликуйте, что вы хотели бы увидеть и как вы с этим работаете.

Преобразует объекты в серию строк значений, разделенных запятыми (CSV), и сохраняет строки в файл.

Синтаксис

Описание

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

Не форматируйте объекты перед их отправкой командлету Export-CSV. Если Export-CSV получает отформатированные объекты, файл CSV содержит свойства формата, а не свойства объекта. Чтобы экспортировать только выбранные свойства объекта, используйте командлет Select-Object.

Примеры

Пример 1. Экспорт свойств процесса в CSV-файл

В этом примере выбираются объекты Process с определенными свойствами, объекты экспортируются в файл CSV.

Пример 2. Экспорт процессов в файл с разделителями-запятыми

В этом примере получаются объекты Process и экспортируются объекты в CSV-файл.

Пример 3. Экспорт процессов в файл, разделенный точкой с запятой

В этом примере получаются объекты Process и экспортируются объекты в файл с разделителем в виде точки с запятой.

Пример 4. Экспорт процессов с использованием разделителя списка текущего языка и региональных параметров

В этом примере получаются объекты Process и экспортируются объекты в файл. Разделитель — это разделитель списка текущего языка и региональных параметров.

Пример 5. Экспорт процессов с информацией о типе

Пример 6. Экспорт и добавление объектов в CSV-файл

В этом примере показано, как экспортировать объекты в файл CSV и использовать параметр Append для добавления объектов в существующий файл.

Командлет Get-Service получает объекты службы. Параметр DisplayName возвращает службы, содержащие слово Application. Объекты службы отправляются по конвейеру командлету Select-Object. Командлет Select-Object использует параметр Property для указания свойств DisplayName и Status. В переменной $AppService хранятся объекты.

Командлеты Get-Service и Select-Object повторяются для служб, содержащих слово Windows. В переменной $WinService хранятся объекты службы. Командлет Export-Csv использует параметр Append, чтобы указать, что объекты $WinService добавляются в существующий файл Services.csv. Командлет Get-Content повторяется, чтобы отобразить обновленный файл, содержащий добавленные данные.

Пример 7. Командлет форматирования в конвейере приводит к неожиданным результатам

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

При использовании командлета Format-Table в конвейере для выбора свойств получены неожиданные результаты. Format-Table отправляет объекты формата таблицы по конвейеру командлету Export-Csv, а не объекту DateTime. Export-Csv преобразует объекты формата таблицы в ряд строк CSV. Командлет Get-Content отображает CSV-файл, содержащий объекты формата таблицы.

Пример 8. Использование параметра Force для перезаписи файлов, доступных только для чтения

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

В командлет Export-Csv добавлен параметр Force, который принудительно записывает экспорт в файл. Командлет Get-Content использует параметр Path для отображения файла, расположенного в текущем каталоге.

Пример 9: Использование параметра Force с Append

В этом примере показано, как использовать параметры Force и Append. При объединении этих параметров несоответствующие свойства объекта могут быть записаны в файл CSV.

Другое выражение создает PSCustomObject со свойствами Name и Edition. Значения хранятся в переменной $AdditionalContent. Переменная $AdditionalContent передается по конвейеру командлету Export-Csv. Параметр Append используется для добавления данных в файл. Добавление завершается неудачно из-за несоответствия имени свойства между Version и Edition.

Параметр Force командлета Export-Csv используется для принудительной записи экспорта в файл. Свойство Edition отбрасывается. Командлет Import-Csv использует параметр Path для отображения файла, расположенного в текущем каталоге.

Пример 10. Экспорт в CSV с кавычками вокруг двух столбцов

В этом примере объект DateTime преобразуется в строку CSV.

Пример 11. Экспорт в CSV с кавычками только при необходимости

В этом примере объект DateTime преобразуется в строку CSV.

Пример 12. Преобразование хэш-таблиц в CSV

В PowerShell 7.2 и более поздних версиях при экспорте хэш-таблиц в CSV ключи первой хэш-таблицы сериализуются и используются в качестве заголовков в выходных данных CSV-файла.

Пример 13. Преобразование хеш-таблиц в CSV с дополнительными свойствами

В PowerShell 7.2 и более поздних версиях при экспорте хэш-таблицы с дополнительными свойствами, добавленными командлетом Add-Member или Select-Object, дополнительные свойства также добавляются в виде заголовка в CSV-файл.

Каждая хеш-таблица имеет свойство ExtraProp, добавленное командлетом Add-Member, а затем экспортированное в CSV. Вы можете видеть, что ExtraProp теперь является заголовком в выходном файле CSV.

Если добавленное свойство имеет то же имя, что и ключ из хеш-таблицы, ключ имеет приоритет, и только ключ экспортируется в CSV.

Параметры

Используйте этот параметр, чтобы команда Export-CSV добавляла выходные данные CSV в конец указанного файла. Без этого параметра Export-CSV заменяет содержимое файла без предупреждения.

Этот параметр появился в Windows PowerShell 3.0.

Запрашивает подтверждение перед запуском командлета.

Указывает разделитель для разделения значений свойств. По умолчанию используется запятая ( , ). Введите символ, например двоеточие ( : ). Чтобы указать точку с запятой ( ; ), заключите ее в кавычки.

Указывает кодировку экспортируемого CSV-файла. Значение по умолчанию — utf8NoBOM .

Для этого параметра допустимы следующие значения:

• ascii : использует кодировку для набора символов ASCII (7-бит).
• bigendianunicode : кодирует в формате UTF-16, используя порядок байтов с обратным порядком байтов.
• bigendianutf32 : кодирует в формате UTF-32, используя порядок байтов с обратным порядком байтов.
• oem : использует кодировку по умолчанию для MS-DOS и консольных программ.
• Unicode : кодирует в формате UTF-16, используя порядок байтов с прямым порядком байтов.
• utf7 : кодирует в формате UTF-7.
• utf8 : кодирует в формате UTF-8.
• utf8BOM: кодирует в формате UTF-8 с меткой порядка байтов (BOM).
• utf8NoBOM: кодирует в формате UTF-8 без метки порядка байтов (BOM).
• utf32 : кодирует в формате UTF-32.

UTF-7* больше не рекомендуется использовать. Начиная с PowerShell 7.1, выдается предупреждение, если вы укажете utf7 для параметра Encoding.

Этот параметр позволяет Export-Csv перезаписывать файлы с атрибутом "Только для чтения".

При объединении параметров Force и Append объекты с несовпадающими свойствами могут быть записаны в файл CSV. В файл записываются только те свойства, которые совпадают. Несоответствующие свойства отбрасываются.

Этот параметр появился в PowerShell 6.0.

Указывает объекты для экспорта в виде строк CSV. Введите переменную, содержащую объекты, или введите команду или выражение для получения объектов. Вы также можете передавать объекты в Export-CSV .

Указывает путь к выходному файлу CSV. В отличие от Path, значение параметра LiteralPath используется точно так, как оно введено. Никакие символы не интерпретируются как подстановочные знаки. Если путь включает escape-символы, используйте одинарные кавычки. Одинарные кавычки сообщают PowerShell, что никакие символы не следует интерпретировать как управляющие последовательности.

Используйте этот параметр, чтобы Export-CSV не перезаписывал существующий файл. По умолчанию, если файл существует по указанному пути, Export-CSV перезаписывает файл без предупреждения.

Обязательный параметр, указывающий место для сохранения выходного файла CSV.

Указывает имена столбцов, которые должны быть заключены в кавычки. При использовании этого параметра в кавычки заключаются только указанные столбцы. Этот параметр был добавлен в PowerShell 7.0.

Использует разделитель списка для текущего языка и региональных параметров в качестве разделителя элементов. Чтобы найти разделитель списка для языка и региональных параметров, используйте следующую команду: (Get-Culture).TextInfo.ListSeparator .

• Никогда — ничего не цитируйте
• Всегда – цитировать все (поведение по умолчанию)
• AsNeeded – заключать в кавычки только те поля, которые содержат символ-разделитель, двойную кавычку или символ новой строки.

Этот параметр был добавлен в PowerShell 7.0.

Предотвращает обработку командлета или внесение изменений. В выходных данных показано, что произойдет, если командлет будет запущен.

Входные данные

Вы можете передать любой объект с помощью адаптера системы расширенного типа (ETS) в Export-CSV .

Результаты

Список CSV отправляется в файл, указанный в параметре Path.

Примечания

Командлет Export-CSV преобразует отправленные вами объекты в набор строк CSV и сохраняет их в указанном текстовом файле. Вы можете использовать Export-CSV -IncludeTypeInformation для сохранения объектов в файле CSV, а затем использовать командлет Import-Csv для создания объектов из текста в файле CSV.

В файле CSV каждый объект представлен списком значений свойств объекта, разделенных запятыми. Значения свойств преобразуются в строки с помощью метода ToString(). Строки представлены именем значения свойства. Export-CSV -IncludeTypeInformation не экспортирует методы объекта.

Строки CSV выводятся следующим образом:

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

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

Командлеты ConvertTo-Csv и ConvertFrom-Csv преобразуют объекты в строки CSV и из строк CSV. Export-CSV аналогичен ConvertTo-CSV, за исключением того, что он сохраняет строки CSV в файле.

Каждый хочет создавать привлекательные отчеты. Хотите произвести впечатление на руководство? Используйте PowerShell и Install-Module -Name ImportExcel .

Вот Get-Process , Get-Service и каталог со списком всех в одном формате электронной таблицы Excel в виде отчета, плюс он интерактивен.

Функция Export-Excel PowerShell работает так же, как вы ожидаете от Out-GridView, за исключением того, что вы получаете рабочий лист Excel, когда закончите.

Читайте дальше, чтобы узнать, как легко это сделать, а также бонусную функцию в конце.

Код PowerShell

Чтобы собрать все воедино, вам понадобится мой модуль PowerShell из коллекции PowerShell. Если вы хотите просмотреть результаты, вам также необходимо установить Microsoft Excel.

Примечание. Этот же отчет можно создать, не устанавливая Excel. Это отличная функция, если вы хотите запустить процесс сбора данных без присмотра или на сервере. Затем вы можете отправить результат по электронной почте или сохранить его в общей папке.

Установите модуль PowerShell из галереи

Теперь вы готовы запустить скрипт и получить результаты.

Вывод Get-Process , Get-Service и Get-ChildItem передаются в Excel в одну и ту же книгу $xlfile . Если не указать -WorkSheetName , Export-Excel вставит данные на тот же лист, в данном случае Лист1 по умолчанию.

Используя параметр -StartRow, вы указываете Export-Excel, с чего начать вставку данных.

Параметр -TableName очень эффективен, он позволяет указать имя области, которую вы только что экспортировали, и настроить ее как таблицу Excel. Это дает вам полосу зебры и стрелки раскрывающегося списка, что обеспечивает интерактивную фильтрацию данных.

Переключатель -AutoSize изменяет размер столбцов электронной таблицы в соответствии с содержащимися в ней данными.

С последним сбором данных, Get-ChildItem, вы делаете что-то немного другое. Использование -PassThru возвращает объект, представляющий созданную книгу Excel. Вы сохраняете это в переменной $excel .

С помощью $excel вы можете получить доступ к свойствам книги, листам и многому другому. Здесь вы устанавливаете $ws для рабочего листа Sheet1 $ws = $excel.Workbook.Worksheets['Sheet1'] .

Теперь вы можете вставлять данные с помощью функции Set-Format, а также задавать размер шрифта и атрибут полужирного шрифта.

Не забудьте использовать Close-ExcelPackage, это сохранит ваши изменения на диске. Переключатель -Show автоматически запускает Excel с только что созданным файлом Excel.

Я рекомендую вам изучить модуль Excel PowerShell, это лишь малая часть того, что возможно. Ознакомьтесь со всеми примерами модулей.

Дополнительная функция

Мощный параметр -TableName позволяет назвать область, которую вы только что экспортировали, и настроить ее как таблицу Excel. Это дает вам полосу зебры и стрелки раскрывающегося списка, что обеспечивает интерактивную фильтрацию данных.Кроме того, функция фильтрации применяется к каждому свойству, которое было экспортировано в Excel.

Здесь вы можете увидеть элемент 1, щелкнув стрелку раскрывающегося списка рядом с FullNanme в разделе «Файлы отчетов», появится всплывающее диалоговое окно. В диалоговом окне вы можете сортировать данные или фильтровать их разными способами. Вы также получите список всех значений в этом столбце для этой таблицы, и вы можете отметить все значения, которые хотели бы видеть.

В заключение

Опять же, я призываю вас изучить модуль PowerShell для Excel, это лишь малая часть того, что возможно, и конечные результаты отлично подходят для представления сложной информации для вас, коллег или руководства. Ознакомьтесь со всеми примерами модулей.

Читайте также:

  
• Как увеличить размер интерфейса в фотошопе
  
• Небольшая утилита, предназначенная для поддержания и повышения производительности компьютера
  
• Замена батареи Dell pp29l BIOS
  
• Приложение Wombo для Android
  
• Приложение Reface для Android