Добавить вывод Powershell в файл Excel

Я пишу сценарий powershell, который соберет некоторую информацию с внешних жестких дисков. Я архивирую на работе (подключен через док-станцию ​​USB HDD), и я хотел бы, чтобы сценарий добавлял эту информацию в файл Excel в качестве одного из последних шагов. Я немного знаком с Powershell, но не столько с работой с файлами Excel с Powershell.

В основном сценарий должен иметь возможность определять первую пустую строку в листе, а затем вставлять некоторую информацию в столбцы A, B, C и т. Д. Этой строки. Я нашел пару разных способов сделать это, но по какой-то причине переменная, которая будет содержать номер строки для добавления информации, всегда кажется пустой. Я не уверен, что я делаю что-то неправильно, поэтому любые советы будут очень оценены.

Это немного длиннее, но вот мой сценарий в текущем состоянии. Нижние 2 раздела (помеченные как «Добавить информацию в документ excel …») – это образцы сценариев, которые я нашел (и ссылки на источники), которые якобы делают то, что я пытаюсь сделать.

Заранее спасибо за помощь.

 ЦБС
 ($ null = reg.exe выгрузить HKLM \ EXTERNAL) 2> $ null
  #IF ($ AdminCred -eq $ null) {New-Variable -Name AdminCred -Value (Get-Credential -UserName Administrator -Message "Введите пароль для учетной записи администратора") -Scope Global -Force}
 $ null = reg.exe load HKLM \ EXTERNAL D: \ Windows \ System32 \ Config \ SYSTEM 

if ($ LASTEXITCODE -eq 0) {
#Find имя хоста
New-Variable -Name Hostname -Value ((Get-ItemProperty -Path HKLM: \ EXTERNAL \ ControlSet001 \ Control \ имя_компьютера \ имя_компьютера) .ComputerName) -Scope Global -Force
[GC] :: Collect ()
IF ($ LASTEXITCODE -ne 0) {Write-Host LastExitCode – $ LASTEXITCODE}
$ null = reg.exe выгрузить HKLM \ EXTERNAL

#Find the serial number Clear-Variable -Name SerialNumber -Scope Global -Force New-Variable -Name SerialNumber -Value (Read-Host "Please enter the HDD's serial number.") -Scope Global -Force # I was originally trying to programatically gather the serial number of the HDD, but that is an issue for another post. # $Disks = Get-WMIObject -class win32_PhysicalMedia # $SerialNumber = foreach($Disk in $Disks) {IF ($Disk.SerialNumber -ne ' WD-WCC2EAV91692') {Write-Host $Disk.SerialNumber}} #Find usernames $Win7 = Test-Path D:\Users $WinXP = Test-Path 'D:\Documents and Settings' IF ($Win7 -eq 'True') {$Win7Users = (Get-ItemProperty -Path D:\Users\* -Exclude ADMINI~1,Administrator,Public,TEMP,UpdatusUser,'All Users',User).name} IF ($WinXP -eq 'True') {$WinXPUsers = (Get-ItemProperty -Path 'D:\Documents and Settings\*' -Exclude ADMINI~1,Administrator,Public,TEMP,UpdatusUser,'All Users',User).name} #Display Hostname and Usernames Write-Host Hostname: -ForegroundColor Green $hostname Write-Host Write-Host Serial Number: -ForegroundColor Green $SerialNumber Write-Host Write-Host User List: -ForegroundColor Green $Win7Users $WinXPUsers #Print the output Out-File -FilePath C:\HDDinfo.txt -InputObject (New-Object -TypeName String -ArgumentList "Hostname:") Out-File -FilePath C:\HDDinfo.txt -InputObject $Hostname -Append Add-Content -Path C:\HDDinfo.txt `n Out-File -FilePath C:\HDDinfo.txt -InputObject (New-Object -TypeName String -ArgumentList "Serial Number:") -Append Out-File -FilePath C:\HDDinfo.txt -InputObject $SerialNumber -Append Add-Content -Path C:\HDDinfo.txt `n Out-File -FilePath C:\HDDinfo.txt -InputObject (New-Object -TypeName String -ArgumentList "User List:") -Append Out-File -FilePath C:\HDDinfo.txt -InputObject $Win7Users -Append Out-File -FilePath C:\HDDinfo.txt -InputObject $WinXPUsers -Append Out-Printer \\servername\printername -InputObject (Get-Content C:\HDDinfo.txt) #Append the info to an Excel document - Possible method 1 # http://stackoverflow.com/questions/8452408/using-powershell-to-append-a-table-to-the-end-of-an-excel-file-the-last-row $ExcelPath = "C:\HDDInfo.xlsx" $xldown = -4121 # see: http://msdn.microsoft.com/en-us/library/bb241212(v=office.12).aspx $xlup = -4162 $Excel = New-Object -ComObject Excel.Application $Excel.Visible = $False $ExcelWorkBook = $Excel.Workbooks.Open($ExcelPath) $ExcelWorkSheet = $Excel.WorkSheets.item("Sheet1") $ExcelWorkSheet.activate() # Find the last used cell {$lastRow = $ExcelWorkSheet.Cells.Range("A1048576").End($xlup).row $nextRow = $lastRow + 1 $range = $ExcelWorkSheet.Range("A$nextRow") $ExcelWorkSheet.Paste($range)} # Append info to the spreadsheet $ExcelWorkSheet.Cells.Item($row,1) = 'COLUMN 1 Text' $ExcelWorkSheet.Cells.Item($row,2) = 'COLUMN 2 Text' $ExcelWorkSheet.Cells.Item($row,3) = 'COLUMN 3 Text' $ExcelWorkSheet.Cells.Item($row,4) = 'COLUMN 4 Text' $ExcelWorkSheet.Cells.Item($row,5) = 'COLUMN 5 Text' # Save and close $ExcelWorkBook.Save() $ExcelWorkBook.Close() $Excel.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) Stop-Process -Name EXCEL -Force #Append the info to an Excel document - Possible method 2 # http://www.adamtheautomator.com/powershell-excel-worksheet/ $excel_file_path = 'C:\HDDInfo.xlsx' # Instantiate the COM object $Excel = New-Object -ComObject Excel.Application $ExcelWorkBook = $Excel.Workbooks.Open($excel_file_path) $ExcelWorkSheet = $Excel.WorkSheets.item("sheet1") $ExcelWorkSheet.activate() # Find the first row where the first 7 columns are empty $row = ($ExcelWorkSheet.UsedRange.Rows | Where-Object { ($_.Value2 | Where-Object {$_ -eq $null}).Count -eq 7 } | select -first 1).Row $ExcelWorkSheet.Cells.Item($row,1) = 'COLUMN 1 Text' $ExcelWorkSheet.Cells.Item($row,2) = 'COLUMN 2 Text' $ExcelWorkSheet.Cells.Item($row,3) = 'COLUMN 3 Text' $ExcelWorkSheet.Cells.Item($row,4) = 'COLUMN 4 Text' $ExcelWorkSheet.Cells.Item($row,5) = 'COLUMN 5 Text' # Save and close $ExcelWorkBook.Save() $ExcelWorkBook.Close() $Excel.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) Stop-Process -Name EXCEL -Force }

Хорошо, CSV в порядке, но при представлении данных или совместном использовании с другими это просто не очень, поэтому я полностью хочу сбросить данные в Excel. Чтобы добавить данные в лист Excel, я настоятельно рекомендую запустить массив объектов, а затем использовать командлет ConvertTo-CSV , Clip и вставить в Excel.

Вы действительно не указываете, что хотите вставить в Excel, но я собираюсь предположить HostName, Serial # и пользователей. Поскольку список пользователей представляет собой массив, я присоединяюсь к нему в строку, чтобы Excel не волновался об этом и вставлял тонну пустых строк.

Итак, давайте сделаем эту информацию в объект. Есть несколько способов сделать это, но тот, который я предпочитаю (должен работать на PS v3 и выше), должен отличать его как таковой:

 $Record = [PSCustomObject]@{ 'Hostname' = $Hostname 'Serial Number' = $Serialnumber 'Users' = $(If($XPUsers){$XPUsers}Else{$Win7Users}) -join ', ' } 

Теперь, если мы ConvertTo-Csv это к ConvertTo-Csv и сделаем его вкладкой с помощью -NoTypeInformation он выйдет примерно так:

 PS C:\Temp> $Record|ConvertTo-Csv -del "`t" -notype "Hostname" "Serial Number" "Users" "Computer001" "42K6NNZ" "TMTech, Andrew" 

Теперь, если вы не хотите, чтобы строка заголовка вставлялась в ваш файл Excel каждый раз, когда мы Select -Skip 1 в Select -Skip 1 и это решает. Но сейчас мы включим его. Вот код, который я бы использовал, чтобы получить эту информацию в Excel.

 #Launch Excel $XL = New-Object -ComObject Excel.Application #Open the workbook $WB = $XL.Workbooks.Open("C:\HDDInfo.xlsx") #Activate Sheet1, pipe to Out-Null to avoid 'True' output to screen $WB.Sheets.Item("Sheet1").Activate() | Out-Null #Find first blank row #, and activate the first cell in that row $FirstBlankRow = $($xl.ActiveSheet.UsedRange.Rows)[-1].Row + 1 $XL.ActiveSheet.Range("A$FirstBlankRow").Activate() #Create PSObject with the properties that we want, convert it to a tab delimited CSV, and copy it to the clipboard $Record = [PSCustomObject]@{ 'Hostname' = $Hostname 'Serial Number' = $Serialnumber 'Users' = $(If($XPUsers){$XPUsers}Else{$Win7Users}) -join ', ' } $Record | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Clip #Paste at the currently active cell $XL.ActiveSheet.Paste() | Out-Null # Save and close $WB.Save() | Out-Null $WB.Close() | Out-Null $XL.Quit() | Out-Null #Release ComObject [System.Runtime.Interopservices.Marshal]::ReleaseComObject($XL) 

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

Изменить: Хорошо, где пропустить заголовок и вставить имена в свои ячейки …

Как я уже сказал, вы будете трубить:

 `$Record|ConvertTo-Csv -del "`t" -notype` 

в |Select -Skip 1 . Это может сбить с толку, я думаю, потому что тогда я вложил его в | Clip | Clip , но если вы рассмотрите, как работает конвейер, и как каждый канал принимает вывод из предыдущего сегмента, то вы ConvertTo-CSV в Select -Skip 1 чтобы он пропускал первую строку (где находятся заголовки), а затем подключить к Clip чтобы скопировать результат в буфер обмена … Это имеет смысл, когда вы его сломаете. Команда будет выглядеть так:

 $Record | ConvertTo-Csv -del "`t" -notype | Select -Skip 1 | Clip 

Теперь для вставки каждого пользователя в свою ячейку вам нужно быть более конкретным. Вы хотите охватить ячейки по горизонтали или по вертикали? Если вы хотите по горизонтали, тогда все становится сложнее. В основном, что мы будем делать, это создать объект, а затем использовать Add-Member для добавления свойств к объекту, по одному для каждого пользователя.

 #Create PSObject with the properties that we want, convert it to a tab delimited CSV, and copy it to the clipboard $Record = [PSCustomObject]@{ 'Hostname' = $Hostname 'Serial Number' = $Serialnumber } #Compile users, and add a member to the object for each user [Array]$Users = If($WinXPUsers){$WinXPUsers}Else{$Win7Users} For($i = 0; $i -lt $Users.Count; $i++){ $Record | Add-Member "User$i" $Users[$i] } $Record | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | Select -Skip 1 | Clip 

Это создало бы запись вроде этого (заголовки, оставшиеся для ссылки, они не появлялись в Excel):

 Hostname Serial Number User0 User1 -------- ------------- ----- ----- Computer001 42K6NNZ TMTech Andrew 

Если вы хотите перечислить каждого пользователя по вертикали, вы можете сделать что-то вроде создания объекта для каждого пользователя, но только с Host и S / N на первом.

 [Array]$Users = If($WinXPUsers){$WinXPUsers}Else{$Win7Users} [Array]$Record = [PSCustomObject]@{ 'Hostname' = $Hostname 'Serial Number' = $Serialnumber 'Users' = $Users[0] } $Record += $Users | Select -Skip 1 | ForEach{[PSCustomObject]@{ 'Hostname' = '' 'Serial Number' = '' 'Users' = $_ }} 

Это выводит что-то вроде этого (заголовки слева для ссылки, они не появятся в Excel):

 Hostname Serial Number Users -------- ------------- ----- Computer001 42K6NNZ TMTech Andrew 
Interesting Posts

Excel 2007/2010 условное форматирование цветовой шкалы на основе формулы

Сохранение отдельного листа в Excel VBA

Десятичный разделитель Excel ведет себя по-разному на основе региональных настроек

Добавление внешних ссылок из SharePoint в книгу Excel

Переменная объекта или с переменной блока, не установленной после первоначальной работы

Как заставить мой код UserForm изменять выходную ячейку кода каждый раз, когда я запускаю UserForm в Excel

Формулы VBA медленны с большим набором данных

Excel 2016 Powerquery – загрузка модели данных, застрявшей в постоянном обновлении

Разработка пользовательских функций в Excel с помощью C #?

Можно ли экспортировать в XLS из Java без открытия Excel?

Открытие Excel с помощью c # получает ошибку на сервере

Переводчик VBA останавливается, по-видимому, без причины

Динамически изменяющиеся заголовки

Python – Как использовать Pandas для редактирования CSV на основе другого CSV

VBA: AutoFilter возвращает TRUE; Для каждого цикла попадает в каждую другую ячейку

Давайте будем гением компьютера.