Read a Line From Excel in Powershell
In this article we'll show how to read and write data from Excel worksheets directly from PowerShell scripts. You can utilise Excel along with PowerShell to inventory and generate various reports on computers, servers, infrastructure, Agile Directory, etc.
Contents:
- How to Read Information from an Excel Spreadsheet using PowerShell?
- Exporting Agile Directory User Info to Excel Spreadsheet using PowerShell
Y'all can access Excel sheets from PowerShell via a divide COM object (Component Object Model). This requires Excel to be installed on the computer.
Before showing how to access data in an Excel cell, it is worth to sympathize the architecture of presentation layers in the Excel file. The figure 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 be open up at the same time;
- WorkSheet Layer – each XLSX file tin contain several sheets;
- Range Layer – hither you tin admission information in the specific jail cell or prison cell range.
How to Read Data from an Excel Spreadsheet using PowerShell?
Let's take a look at a unproblematic example of how to utilise PowerShell to access data in an Excel file containing a list of employees.
First, run the Excel app (application layer) on your estimator using the COM object:
$ExcelObj = New-Object -comobject Excel.Application
After running the control, Excel will be launched on your computer in the groundwork. To show the Excel window, change the Visible property of the COM object:
$ExcelObj.visible=$truthful
You can display all Excel object backdrop equally follows:
$ExcelObj| fl
And so you can open an Excel file (a workbook):
$ExcelWorkBook = $ExcelObj.Workbooks.Open up("C:\PS\corp_ad_users.xlsx")
Each Excel file can contain several worksheets. Let's brandish the list of worksheets in the electric current Excel workbook:
$ExcelWorkBook.Sheets| fl Name, index
And then you can open a canvass you want (by its proper noun or index):
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_users")
Yous can get the proper noun of the electric current (active) Excel worksheet using this command:
$ExcelWorkBook.ActiveSheet | fl Proper name, Index
Then you can go values from cells in Excel worksheet. You lot can employ different methods to get the prison cell values on the electric current Excel worksheet: using a range, a cell, a column or a row. Run into the examples of how to go data from the same prison cell below:
$ExcelWorkSheet.Range("B4").Text
$ExcelWorkSheet.Range("B4:B4").Text
$ExcelWorkSheet.Range("B4","B4").Text
$ExcelWorkSheet.cells.Item(four, 2).text
$ExcelWorkSheet.cells.Item(4, 2).value2
$ExcelWorkSheet.Columns.Item(ii).Rows.Detail(4).Text
$ExcelWorkSheet.Rows.Item(4).Columns.Item(2).Text
Exporting Active Directory User Info to Excel Spreadsheet using PowerShell
Let'southward see a practical example of how to access Excel data from PowerShell. Suppose, we want to get some information from Active Directory for each user in an Excel file. For case, their phone number (the telephoneNumber attribute), department and eastward-mail accost .
# Importing Active Directory module into PowerShell session
import-module activedirectory
# Open an Excel workbook commencement:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open up("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# Get 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 contain the domain usernames)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Particular(i).Rows.Particular($i).Text
# Go the values of user attributes in AD
$ADuserProp = Become-ADUser $ADusername -properties telephoneNumber,section,mail|select-object name,telephoneNumber,section,mail
# Make full in the cells with the data received from AD
$ExcelWorkSheet.Columns.Item(4).Rows.Item($i) = $ADuserProp.telephoneNumber
$ExcelWorkSheet.Columns.Detail(5).Rows.Detail($i) = $ADuserProp.department
$ExcelWorkSheet.Columns.Particular(6).Rows.Item($i) = $ADuserProp.mail
}
# Salvage the XLS file and shut Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.shut($true)
Every bit a result, the columns containing AD information take been added for each user in the Excel file.
Let'southward consider another example of making a report using PowerShell and Excel. Suppose, you want to make an Excel report well-nigh Print Spooler service state on all domain servers.
# Create an Excel object
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# Add a workbook
$ExcelWorkBook = $ExcelObj.Workbooks.Add together()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(ane)
# Rename a worksheet
$ExcelWorkSheet.Name = 'Spooler Service Status'
# Fill in the head of the table
$ExcelWorkSheet.Cells.Item(1,1) = 'Server Name'
$ExcelWorkSheet.Cells.Item(ane,2) = 'Service Proper noun'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# Make the table head bold, set the font size and the cavalcade width
$ExcelWorkSheet.Rows.Item(1).Font.Bold = $true
$ExcelWorkSheet.Rows.Detail(1).Font.size=15
$ExcelWorkSheet.Columns.Item(1).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(2).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(3).ColumnWidth=28
# Get the list of all Windows Servers in the domain
$computers = (Become-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=two
# Connect to each figurer and become the service condition
foreach ($computer in $computers) {
$outcome = Invoke-Control -Computername $computer –ScriptBlock { Get-Service spooler | select Proper name, status }
# Fill up in Excel cells with the data obtained from the server
$ExcelWorkSheet.Columns.Item(1).Rows.Item($counter) = $upshot.PSComputerName
$ExcelWorkSheet.Columns.Item(ii).Rows.Item($counter) = $issue.Proper noun
$ExcelWorkSheet.Columns.Item(3).Rows.Item($counter) = $upshot.Condition
$counter++
}
# Save the report and close Excel:
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.shut($true)
Y'all can use PowerShell to access Excel in a diversity of scenarios. For example, y'all can create handy Active Directory reports or create PowerShell scripts to update AD data from Excel.
For example, you tin can enquire an employee of your HR section to keep the user annals in Excel. And then using a PowerShell script and the Set-ADUser cmdlet, the employee can automatically update user info in Advertizing (just delegate the employee the permissions to alter Advert user attributes and evidence how to run the PowerShell script). Thus you lot can go along an up-to-date accost volume with the relevant phone numbers, chore titles and departments.
Read a Line From Excel in Powershell
Source: http://woshub.com/read-write-excel-files-powershell/