Powershell і Excel

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

Про інвентаризацію не писав, напевно, лише лінивий. От і я, щоб не здаватися лінивим, теж вирішив взятися за цю справу. Приводом для написання стала поява кількох статей з цієї теми. Мене навіть зацікавила не сама інвентаризація (що там інвентаризувати – смикай потрібні об'єкти, дивись їх властивості), а робота з Excel, оскільки всі руки не доходили спробувати. З Word'ом стикатися вже доводилося, а ось з Excel ще немає. Можна, звичайно, не морочитися, і вивести все в CSV-файл, але повторюся - мене цікавила саме робота з Excel: заповнення та форматування осередків, розмальовка, діаграми і т.д. Але все по порядку 🙂

Отже, перш за все потрібно створити об'єкт Excel і зробити його видимим, щоб бачити всю подальшу магію 🙂

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

Починаємо працювати з першим листом. Для простоти звернення до нього створюємо відповідну змінну:

Далі перейменовуємо лист (щоб було не Лист1, Лист2 і т.д., а “людські” назви) і заповнюємо шапку таблиці:

Як (напевно) зрозуміло тут ми пишемо в кожен осередок по черзі, перша цифра у дужках – номер рядка, друга – номер стовпця.

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

powershell

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

Поки виглядає криво через те, що написи не влазять у комірки, і хочеться розтягнути комірки, але нічого страшного, ми це потім поправимо.

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

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

Excel

Логічні диски в Excel

мдя… забагато дисків, треба їх трошки поєднувати, створювалися колись тимчасово для тестових цілей, але як відомо немає нічого більш постійного ніж тимчасове 🙂

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

Залишилося трохи прикрасити зовнішній вигляд - виділимо шапку таблиці (перший рядок) жирним, і відрегулюємо ширину комірок по ширині тексту (до цього моменту я навіть не підозрював, що Excel таке вміє:)):

Змінна$UsedRange містить усі зайняті комірки (еквівалентно одноразовому натисканню Ctrl+A)

Дивимося, що вийшло:

Excel

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

Краса та й годі 🙂

Із логічними дисками розібралися, переходимо до фізичних.

Створимо для них окремий лист:

Тут є нюанс, полягає у цьому, що аркуші додаються у порядку, тобто. щойно доданий лист матиме номер 1, а попередній стане номером 2. Тому виділяємо щойно створений лист, і робимо все те саме, тільки з фізичними дисками:

Дивимося, що вийшло:

powershell

Логічні диски

Залишилось зберегти отриманий звіт та вийти з Excel:

На сьогодні все :). У наступних частинах ми навчимося об'єднувати та розфарбовувати осередки, а також будувати діаграми.

Ваша оцінка:

Поділитися:

Сподобалося це:

Excel

Я використовую наступний код:

$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)

foreach ($Worksheet in $Workbook.Worksheets) # Створюємо новий файл $Workbook01 = $Excel.Workbooks.Add()

# Вибираємо аркуш $Worksheet01 = $Workbook01.Worksheets.Item(1)

Я волію збирати ексель-файли через формування папок з 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 ValidWorkbooka

Як я можу звернутися з 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 «Prov > $srcCnn.Open() $firstWorksheetName = «[Лист1$]»

$srcCmd = $srcCnn.CreateCommand() $srcCmd.CommandText = «вибрати * з $firstWorksheetName» $dr = $srcCmd.ExecuteReader()

$dstCnn1 = New-Object Data.SqlClient.SqlConnection -ArgumentList «Server=$dstSrvName; Database=імя бази даних; Trusted_Connection=так; MultiSubnetFailover=так; ApplicationIntent=READWRITE;» $dstCnn1.Open()

$dstCnn1 = $dstCnn1.CreateCommand() $dstCnn1.CommandText = «якщо object_id('$dstTblName', 'U') не є нульовою таблицею $dstTblName;створити таблицю $dstTblName(дата датачас, ім'я nvarchar(20) ), lastname nvarchar(20), location_name nvarchar(20), Grazhd nvarchar(20),birth_date datetime,mobile nvarchar(20),vacansi nvarchar(20),data_s datetime)» $dstCnn1.ExecuteReader( )