How To Grab A Column In Excel Using Powershell

In this commodity we'll evidence how to read and write data from Excel worksheets directly from PowerShell scripts. Yous can utilise Excel along with PowerShell to inventory and generate various reports on computers, servers, infrastructure, Active Directory, etc.

Contents:

• How to Read Data from an Excel Spreadsheet using PowerShell?
• Exporting Active Directory User Info to Excel Spreadsheet using PowerShell

You tin access Excel sheets from PowerShell via a separate COM object (Component Object Model). This requires Excel to be installed on the computer.

Before showing how to access data in an Excel jail cell, it is worth to understand the architecture of presentation layers in the Excel file. The effigy beneath shows four nested presentation layers in the Excel object model:

• Application Layer – deals with the running Excel app;
• WorkBook Layer – multiple workbooks (Excel files) may exist open at the aforementioned time;
• WorkSheet Layer – each XLSX file can comprise several sheets;
• Range Layer – here you tin access data in the specific cell or cell range.

How to Read Data from an Excel Spreadsheet using PowerShell?

Let'southward take a look at a simple instance of how to use PowerShell to admission information in an Excel file containing a list of employees.

First, run the Excel app (application layer) on your computer using the COM object:
$ExcelObj = New-Object -comobject Excel.Application

Afterwards running the control, Excel will be launched on your estimator in the groundwork. To show the Excel window, alter the Visible property of the COM object:

$ExcelObj.visible=$truthful

You tin display all Excel object properties as follows:

$ExcelObj| fl

So you can open an Excel file (a workbook):

$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")

Each Excel file can incorporate several worksheets. Permit'due south display the listing of worksheets in the electric current Excel workbook:

$ExcelWorkBook.Sheets| fl Name, alphabetize

So you can open a canvass you want (past its proper noun or index):

$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_users")

Y'all can get the name of the electric current (active) Excel worksheet using this command:

$ExcelWorkBook.ActiveSheet | fl Name, Index

Then y'all can go values from cells in Excel worksheet. You can employ different methods to get the cell values on the current Excel worksheet: using a range, a cell, a column or a row. See the examples of how to get data from the same prison cell beneath:

$ExcelWorkSheet.Range("B4").Text
$ExcelWorkSheet.Range("B4:B4").Text
$ExcelWorkSheet.Range("B4","B4").Text
$ExcelWorkSheet.cells.Item(iv, ii).text
$ExcelWorkSheet.cells.Item(iv, 2).value2
$ExcelWorkSheet.Columns.Particular(two).Rows.Detail(4).Text
$ExcelWorkSheet.Rows.Particular(four).Columns.Item(two).Text

Exporting Active Directory User Info to Excel Spreadsheet using PowerShell

Allow'southward see a practical example of how to access Excel data from PowerShell. Suppose, nosotros want to get some data from Active Directory for each user in an Excel file. For instance, their phone number (the telephoneNumber attribute), section and e-mail accost .

# Importing Active Directory module into PowerShell session
import-module activedirectory
# Open an Excel workbook first:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open up("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# Become the number of filled in rows in the XLSX worksheet
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Loop through all rows in Column 1 starting from Row ii (these cells incorporate the domain usernames)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(one).Rows.Detail($i).Text
# Get the values of user attributes in AD
$ADuserProp = Go-ADUser $ADusername -properties telephoneNumber,department,post|select-object name,telephoneNumber,section,mail
# Make full in the cells with the data received from Advert
$ExcelWorkSheet.Columns.Item(4).Rows.Item($i) = $ADuserProp.telephoneNumber
$ExcelWorkSheet.Columns.Item(five).Rows.Item($i) = $ADuserProp.department
$ExcelWorkSheet.Columns.Item(6).Rows.Item($i) = $ADuserProp.post
}
# Save the XLS file and close Excel
$ExcelWorkBook.Salve()
$ExcelWorkBook.close($true)

As a effect, the columns containing AD data accept been added for each user in the Excel file.

Let's consider some other instance of making a report using PowerShell and Excel. Suppose, you want to make an Excel report about Print Spooler service state on all domain servers.

# Create an Excel object
$ExcelObj = New-Object -comobject Excel.Awarding
$ExcelObj.Visible = $true
# Add a workbook
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# Rename a worksheet
$ExcelWorkSheet.Proper name = 'Spooler Service Status'
# Fill in the head of the table
$ExcelWorkSheet.Cells.Item(1,ane) = 'Server Name'
$ExcelWorkSheet.Cells.Item(1,two) = 'Service Proper name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Condition'
# Make the table head assuming, set the font size and the column width
$ExcelWorkSheet.Rows.Detail(1).Font.Bold = $true
$ExcelWorkSheet.Rows.Item(1).Font.size=15
$ExcelWorkSheet.Columns.Item(1).ColumnWidth=28
$ExcelWorkSheet.Columns.Detail(2).ColumnWidth=28
$ExcelWorkSheet.Columns.Particular(3).ColumnWidth=28
# Get the list of all Windows Servers in the domain
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Proper name
$counter=ii
# Connect to each computer and get the service status
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer –ScriptBlock { Get-Service spooler | select Proper name, condition }
# Make full in Excel cells with the information obtained from the server
$ExcelWorkSheet.Columns.Item(1).Rows.Item($counter) = $result.PSComputerName
$ExcelWorkSheet.Columns.Item(ii).Rows.Item($counter) = $outcome.Proper name
$ExcelWorkSheet.Columns.Particular(iii).Rows.Item($counter) = $outcome.Status
$counter++
}
# Relieve the study and close Excel:
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)

Y'all can use PowerShell to access Excel in a variety of scenarios. For case, you can create handy Active Directory reports or create PowerShell scripts to update AD data from Excel.

For example, you tin can ask an employee of your HR section to keep the user register in Excel. And so using a PowerShell script and the Gear up-ADUser cmdlet, the employee tin automatically update user info in AD (only delegate the employee the permissions to alter AD user attributes and show how to run the PowerShell script). Thus you lot can proceed an up-to-date address book with the relevant phone numbers, job titles and departments.

Source: http://woshub.com/read-write-excel-files-powershell/

Posted by: moodytings1993.blogspot.com

Share this post