Powershell создать файл excel
Доброго времени суток друзья. Я уже рассказывал, как можно работать с Microsoft Excel средствами PowerShell. В данной статье мы рассмотрим работу PowerShell с Microsoft Word. Как и для Excel, чтобы получить доступ к MS Word нужно использовать COM объект. Для этого используем командлет New-Object с параметром –ComObject далее сам объект к которому хотим получить доступ, в нашем случае это Word.Application.
После создания объекта обращаемся к свойству Visible и переключаем его в TRUE чтобы видеть работу скрипта непосредственно в самом MS Word а не в фоновом режиме.
Далее необходимо создать сам документ для этого обращаемся к свойству Documents и его методу Add().
Обратимся к свойству Selection для работы с текущим документом, далее мы будем использовать его для форматирования нашего документа.
Задаем начальные настройки для документа допустим отступы со всех сторон.
Установим строчный интервал сверху и снизу.
Подготовительные работы закончены, теперь можем заполнять наш документ. Укажем что наш первый абзац будет по центру для этого воспользуемся свойством ParagraphFormat и его свойством Aligment.
Далее создаем таблицу с одной строкой и одной ячейкой которую зальем черным цветом и разместим пользовательскую картинку.
Далее переходим на новую строчку документа воспользовавшись методом TypeParagraph().
Далее установим нужные настройки для нашего заголовка.
Как видно из примера мы установили шрифт Time New Roman и размер шрифта 18, далее вставляем текст.
Для следующего текста поменяем настройки шрифта и размер и вид, а также выровняем текст по ширине документа.
Далее добавляем новый текст в наш документ.
Так как для следующего текста нам не нужен курсив, то отключаем его.
Далее создаем настройки для следующего текста, выправным его по центру и изменим цвет на темно-зеленый, а также выделим жирным.
Далее вставляем картинку в документ по центру, для этого используем свойство InlineShapes и его метод AddPicture() передав путь до картинки.
Далее отменяем центрирование и меняем цвет текста на черный.
Устанавливаем новые настройки для следующего текста.
Далее создадим таблицу с тремя столбцами в которую заполним с помощью PowerShell.
Здесь нужно отметить, что ячейки таблицы начинаются с 1. Для генерации новой строки нужно использовать свойство Rows и его метод Add(). Для удобства форматирования таблицы используем метод AutoFormat() передав число (это идентификатор шаблона форматирования таблицы). Для перехода от таблицы к обычному документу используем метод EndKey(6, 0).
Далее создаем настройки для нового текста, выровняем все по левому краю и добавим гиперссылок в наш документ.
Как вы заметили мы использовали свойство Hyperlinks и его метод Add() в который передали URL для гиперссылки.
Далее сохраняем документ (СохранитьКак) указываем путь куда будем сохранять наш файл, закрываем сам документ, закрываем само приложение MS Word.
Я постарался собрать на мой взгляд основные действия для заполнения документа – это форматирование текста, изменение размера текста, шрифта, цвета. Создание таблиц, заполнение таблицы графически и текстовым материалом, а также заливка ячейки цветом.
Вот таким способом можно создавать документы MS Word с помощью PowerShell.
Продолжаем работать в Excel через Powershell. Напомню, что в предыдущей части мы создали небольшую таблицу и заполнили её данными. Также напомню, что это не моё “изобретение”, а очень вольный перевод вот этих трёх статей.
Для начала подготовим плацдарм для работы: создадим объект Excel и заполним его начальными данными. Я не буду на этом останавливаться, так как это было подробно рассмотрено в прошлой статье.
На данном этапе у нас будет одна текстовая строка, размещённая в диапазоне ячеек с A1 по G2, т.е. в двух строках и семи столбцах, что по умолчанию выглядит не очень презентабельно, так как текст выравнивается по нижнему краю:
Объединённые ячейки (неформатированные)
Чтобы текст в объединённых ячейках выглядел красивее его можно выровнять по вертикали по центру.
Все варианты вертикального выравнивания можно посмотреть в MSDN. А значения, которые нужно при этом использовать можно узнать выполнив команду:
В результате мы увидим следующую таблицу:
Name | value__ |
xlVAlignTop | -4160 |
xlVAlignJustify | -4130 |
xlVAlignDistributed | -4117 |
xlVAlignCenter | -4108 |
xlVAlignBottom | -4107 |
Из таблицы видно, что для выравнивания по середине нужно использовать значение
После выравнивания наш текст будет выглядеть уже красивее:
Объединённые ячейки (отформатированные)
Переходим к заполнению таблицы данными.
Для начала переходим на следующую строку. Так как в последствии вокруг таблицы мы нарисуем рамку, нам понадобится номер начальной строки, поэтому сохраним его в отдельной переменной.
Начинаем с шапки таблицы:
Сама таблица заполняется в цикле по логическим дискам:
Фильтрация нужна для того, чтобы исключить из рассмотрения CD/DVD диски (которые чаще всего пустые, и следовательно их размер будет равен нулю).
В цикле выводим в таблицу, интересующую нас информацию:
А также раскрашиваем строки в зависимости от процента свободного места на диске. Для простоты я раскрашиваю строку, относящуюся к конкретному диску в жёлтый цвет, если свободного места на нём меньше 5 ГБ, и в красный цвет, если свободного места меньше 1 ГБ:
Смотрим, что получилось:
Результаты работы (неформатированные)
Как видно на дисках C и D свободного места меньше 5 ГБ, поэтому соответствующие строки подсвечены жёлтым цветом. А на Диске F свободного места вообще меньше 1 ГБ, поэтому он подсвечен красным цветом. Кстати, для быстрого забивания диска мне пришёл на помощь скрипт, генерирующий файл заданного размера.
В целом работа выполнена. Осталось привести таблицу к боле красивому виду. Для этого мы выровняем ширину столбцов в таблице и нарисуем рамку вокруг таблицы.
Сейчас курсор стоит уже на следующей строке, так как в цикле мы его перевели находясь ещё в цикле. А так как нам нужны только строки таблицы возвращаемся на одну строку назад и выделяем таблицу:
Напомню, что $InitialRow – это номер начальный строки таблицы, который мы заранее сохранили.
Переходим к “рисованию” рамки – границы диапазона ячеек.
Чтобы узнать все возможные варианты границ диапазона ячеек можно выполнить команду:
В результате получим таблицу:
Name | value__ |
xlDiagonalDown | 5 |
xlDiagonalUp | 6 |
xlEdgeLeft | 7 |
xlEdgeTop | 8 |
xlEdgeBottom | 9 |
xlEdgeRight | 10 |
xlInsideVertical | 11 |
xlInsideHorizontal | 12 |
Как видно из таблицы для рамки подходят значения с 7 по 12.
Подгоняем ширину столбцов:
В результате получается вот так:
Результаты работы (отформатированные)
Осталось сохранить полученный результат и выйти из Excel:
Ваша оценка:
Понравилось это:
Похожее
А не подскажешь вот такое по экселю. Сегодня бьюсь, но ни как не получается.
$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 содержит номер строки
frankk :
… Скопировать или вырезать не удается. Пытаюсь это сделать вот такой командой
$sheet.Rows.Item($row).copy()И еще вопрос, вот такая связка не выдает мне результат, почему? Разве это не объект?
$sheet.Rows.Item($row) | Get- Member
переменная row содержит номер строки
Затрудняюсь сказать, у меня замечательно копирует, а вторая команда выводит кучу свойств и методов.
А Excel и конкретно этот файл открыт во время выполнения проблемных команд?
Да, открыт($excel.Visible = $true), у меня стало закрадываться подозрение, что это как-то связано с тем что пока я экспериментировал с командами у меня переменные перестали освобождаться и брать новые команды, так как даже строчка с удалением перестала отрабатывать и стала выдавать кучу ошибок, перезапустил powershell и «убил» службы в диспетчере, опять стала выполняться. Буду смотреть дальше. Все равно спасибо за помощь! 🙂
Наверно уже достал 🙂 Но моя борьба с экселем продолжается :). Может ты подскажешь, своим опытным взглядом. в чем может быть причина? Имеем такой код
$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»
Вот что ему может не нравится? Может у тебя есть мысли, ну или опыт. Да, команду удаление строки ввел потому что при вырезании он не удаляет эту строчку, а просто оставляет пустой.
Об инвентаризации не писал, наверное, только ленивый. Вот и я, чтобы не казаться ленивым, тоже решил взяться за это дело. Поводом для написания стало появление нескольких статей на эту тему. Меня даже заинтересовала не сама инвентаризация (что там инвентаризировать – дёргай нужные объекты, смотри их свойства), а работа с Excel’ем, так как всё руки не доходили попробовать. С Word’ом сталкиваться уже приходилось, а вот с Excel’ем ещё нет. Можно, конечно, не заморачиваться, и вывести всё в CSV-файл, но повторюсь – меня интересовала именно работа с Excel: заполнение и форматирование ячеек, раскраска, диаграммы и т.д. Но обо всём по порядку 🙂
Итак, прежде всего нужно создать объект Excel и сделать его видимым, чтоб видеть всю дальнейшую магию 🙂
Это равносильно запуску Excel. Далее необходимо создать файл (в терминологии Excel рабочую книгу):
В оригинале статьи автор говорит, что этой операцией добавляется три листа, и если остальные не нужны их можно/нужно удалить, и показывает как это сделать. Но у меня добавляется только один лист, не знаю с чем это связано, возможно в разных версиях офиса по разному, поэтому я на этом останавливаться не буду.
Начинаем работать с первым листом. Для простоты обращения к нему создаём соответствующую переменную:
Далее переименовываем лист (чтобы было не Лист1, Лист2 и т.д., а “человеческие” названия) и заполняем шапку таблицы:
Как (наверное) понятно здесь мы пишем в каждую ячейку по очереди, первая цифра в скобках – номер строки, вторая – номер столбца.
Уже можно наслаждаться первыми результатами работы 🙂
Главное окно Excel
Пока смотрится криво из-за того, что надписи не влазят в ячейки, и хочется растянуть ячейки, но ничего страшного, мы это потом поправим.
Переходим на следующую строку, возвращаемся в первый столбец и в цикле заполняем таблицу данными по логическим дискам, после каждого диска переводим курсор (или как правильно назвать текущую ячейку?) на следующую строку и возвращаемся в первый столбец:
Размеры дисков переводятся в гигабайты, и чтобы много цифр не сбивали с толку, округляются до двух знаков после запятой.
Логические диски в Excel
мдя… многовато дисков, надо-бы их немножко пообъединять, создавались когда-то временно для тестовых целей, но как известно нет ничего более постоянного чем временное 🙂
Диски с нулевыми размерами это два DVD-привода и один виртуальный.
Осталось немного приукрасить внешний вид – выделим шапку таблицы (первая строка) жирным, и отрегулируем ширину ячеек по ширине текста (до этого момента я даже не подозревал, что Excel такое умеет:)):
Переменная $UsedRange содержит все занятые ячейки (эквивалентно однократному нажатию Ctrl+A)
Смотрим, что получилось:
Готовая таблица
Красота да и только 🙂
С логическими дисками разобрались, переходим к физическим.
Создадим для них отдельный лист:
Тут есть один нюанс, заключающийся в том, что листы добавляются в обратном порядке, т.е. только что добавленный лист будет иметь номер 1, а предыдущий станет номером 2. Поэтому выделяем только что созданный лист, и делаем всё то же самое, только с физическими дисками:
Смотрим, что получилось:
Логические диски
Осталось сохранить полученный отчёт и выйти из Excel:
На сегодня всё :). В следующих частях мы научимся объединять и раскрашивать ячейки, а также строить диаграммы.
Ваша оценка:
Понравилось это:
Похожее
здравствуйте, подскажите пожалуйста, как правильно вызывать метод КОПИРОВАНИЯ листа в XLS. Мой способ выдает «метод copy из класса worksheet завершен неверно»
Я использую следующий код:
$Excel = New-Object -ComObject «Excel.Application»
$Workbook = $Excel.Workbooks.open($filepath)
Вы в цикле каждый раз запускаете новый процесс Excel. Я бы так не делал, и судя по всему в этом и проблема, так как это копирование не через буфер и Ваши Excel’и просто не знают куда копировать.
Достаточно просто создать новый файл:
Я предпочитаю собирать эксель-файлы через формирование папок с xml-данными. Ком-объекты дорогие по ресурсам, кроме того, связь с ком-объектом может упасть, если данных для передачи много.
Я новичок в PS — потребовалось автоматизировать кое-что на рабочем месте (я врач).
Не могу разобраться — как обратиться из PowerShell к ячейке листа по ее имени, а не по индексной ссылке.
Есть шаблон Excel (template), на листе «Source_Data» есть именованные ячейки:
Через 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 файла не по позициям, а по именам ячеек.
Перефразируя Зелёного из мультика «Тайна третьей планеты»: «Если врачи начнут программировать, то мне в IT делать нечего» 🙂
По поводу вашего вопроса попробуйте так:
$SrcDataWS.Range(‘UterusHeight’).formula = ‘Ваши данные’
>> Но у меня добавляется только один лист
File->Options->General->группа When creating new workbooks-> галка Include this many sheets — количество листов в новой книге
«Вон оно чё, Михалыч…» Спасибо, буду знать 🙂
P.S. спасибо за статью. 🙂
Підкажіть будь-ласка при копіюванні діапазону не вставляється в зазначене місце(наприкл. С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()
Після того як обрали зазначене місце (у Вашом прикладі це $Range = $Worksheet.Range(«C1»)) Використовуйте метод PasteSpecial():
$Range = $Worksheet.Range(«C1»)
$Range.PasteSpecial()
Бо Ви вставляєте не в лист (не в $Worksheet), а в певне місце ($Range)
Дякую, я потім побачив свою помилку, дійсно все запрацювало, але з’явилась нова проблема з розгалуженням. Знайшов схожий скрипт але він не читає з вказаних комірок:
$num = «Бар»
$excel = New-Object -ComObject Excel.Application
Ви хоча б детальніше описали, що у файлі, що має писати 🙂
На скільки я зрозумів в залежності від значення в одному стовбці, має писатися ‘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.
Спасибо большое за решение, мне очень пригодилось, и все понятно. Мне было важно заполнять данные по столбцам сверху в низ, а через экспорт csv не получалось.
Всем привет. Написал из разных частей скрипт на создание базы данных и передачи информации в неё из файла эксель. Может кому пригодится.
Осталось пара нюансов. Может кто подскажет как в файле Excel удалить первую строку? То есть у меня шапка файла начинается со второй строки.
$dstSrvName = «srv-sqlinf-lsn»; $dstTblName = «dbo.candi_test»
$dstCnn = New-Object Data.SqlClient.SqlConnection -ArgumentList «Server=$dstSrvName; Database=имя базы данных; Trusted_Connection=yes; MultiSubnetFailover=yes; ApplicationIntent=READWRITE;»
$dstCnn.Open()
$srcCnn = New-Object Data.OleDb.OleDbConnection -ArgumentList «Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test\test_scr_bet1.xlsx;Extended Properties=’Excel 12.0 Xml;HDR=YES'»
$srcCnn.Open()
$firstWorksheetName = «[Лист1$]»
$dstCnn1 = New-Object Data.SqlClient.SqlConnection -ArgumentList «Server=$dstSrvName; Database=имя базы данных; Trusted_Connection=yes; MultiSubnetFailover=yes; ApplicationIntent=READWRITE;»
$dstCnn1.Open()
Конвертирование txt-файла в xls с помощью Powershell
подскажите, каким образом занести данные с файла 123.txt в Excel?
Там же в комментариях развелась целая дискуссия на эту тему. А недавно сам столкнулся с похожей проблемой. В моём случае файлов было несколько, и мало того, что нужно было перенести данные из текстового файла в Excel, так их ещё нужно было проанализировать. Поэтому решил поделиться своим способом конвертирования текстового файла в файл Excel.
В моём скрипте два обязательных параметра: путь к исходному текстовому файлу, и знак разделитель (по умолчанию — пробел):
Считываем наш файл и запускаем Excel:
Чтобы было веселее Для наглядности я всегда в своих скриптах добавляю поддержку стандартных параметров, в частности –Verbose для того, чтобы было видно, что в данный момент делает скрипт:
Стандартные подготовительные действия для последующей работы с Excel:
Далее начинается непосредственно работа. Каждая строка из файла разбивается на подстроки (блоки, разделённые указанным символом разделителем) и каждый блок записывается в свою собственную ячейку:
По сути это всё. Далее при необходимости можно форматировать файл, как будет угодно, например выровнять ширину ячеек, чтобы текст помещался:
Осталось сохранить результат и выйти. В моём случае xls-файл сохраняется в том же каталоге, что и исходный текстовый файл, с тем же именем (за исключением расширения):
В результате память может и освобождается, но процесс Excel остаётся запущенным и невидимым.
Ну и напоследок пару скриншотов, как это работает.
Если исходный текстовый файл имеет вид:
То готовый xls-файл будет выглядеть так:
Конечно, это не комплексное решение: здесь нет проверки на то является-ли указанный файл действительно текстовым, присутствуют-ли в нём разделители, и много чего ещё. Я только показал алгоритм работы.
Для работы с Excel в среде powershell нужно использовать COM объект.
При выполнении данного командлета произойдет запуск приложения Excel в скрытом состоянии, т.е. у нас будет висеть процесс Excel, но при этом самого приложения как такового мы не увидем. Для того чтобы увидеть работу приложения нужно обратиться к его свойству Visible и установить его в TRUE т.к. по умолчанию стоит FALSE
Далее после того как приложение открылось нужно создать книгу воспользовавшись свойством Workbooks и методом Add().
После создания книги нужно выбрать лист с которым будем работать в данной книге. Для этого воспользуемся свойством Worksheet и методом Item()передав ему номер листа с которым будем работать.
Зададим имя нашему листу для этого используем свойство Name и присвоим ему значение нового имени листа.
Так как Excel это таблица и мы работаем с ее ячейками то используем свойство Cells и метод Item() для указания с какими ячейками данного листа мы будем работать. Отсчет начинается с 1.
В данном примере мы устанавливаем названия наших колонок которые в дальнейшем будем заполнять. Первый аргумент это номер строки с которой работаем, второй аргумент это ячейка в таблице. Перед тем как перебирать массив с данными и записывать все в таблицу нам нужна вспомогательная переменная которая будет ссылаться на начальную строку в таблице с дальнейшим увеличением в цикле для перехода на новую строку.
После того как книга заполнена нужными данными ее необходимо сохранить. Для этого используем метод Saveas() и передаем ему путь и имя файла для сохранения.
После этого закрываем саму книгу воспользовавшить методом Close().
После того как закрыли книгу можно завершать работу приложения Excel использую метод Quit(). Тем самым освободив память в системе.
У данного способа есть существенный недостаток это очень медленная работа экспорта данных в таблицу Excel, но за то можно создавать красивые отформатированные таблицы для конечного пользователя. Здесь представлен полный скрипт, который получает список всех служб на вашем компьютере и записывает их в ячейки таблицы Excel.
Читайте также: