In this article we’ll show how to read and write data from Excel worksheets directly from PowerShell scripts. You can use Excel along with PowerShell to inventory and generate various reports on computers, servers, infrastructure, Active Directory, etc.

Before showing how to access data in an Excel cell, it is worth to understand the architecture of presentation layers in the Excel file. The figure below shows 4 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 at the same time;
• WorkSheet Layer – each XLSX file can contain several sheets;
• Range Layer – here you can access data in the specific cell or cell range.

How to Read Data from an Excel Spreadsheet using PowerShell?

Let’s take a look at a simple example of how to use PowerShell to access data 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

After running the command, Excel will be launched on your computer in the background. To show the Excel window, change the Visible property of the COM object:

$ExcelObj.visible=$true

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

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

Each Excel file can contain several worksheets. Let’s display the list of worksheets in the current Excel workbook:

$ExcelWorkBook.Sheets| fl Name, index

Then you can open a sheet you want (by its name or index):

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

You can get the name of the current (active) Excel worksheet using this command:

$ExcelWorkBook.ActiveSheet | fl Name, Index

Then you can get values from cells in Excel worksheet. You can use 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 cell below:

$ExcelWorkSheet.Range("B4").Text
$ExcelWorkSheet.Range("B4:B4").Text
$ExcelWorkSheet.Range("B4","B4").Text
$ExcelWorkSheet.cells.Item(4, 2).text
$ExcelWorkSheet.cells.Item(4, 2).value2
$ExcelWorkSheet.Columns.Item(2).Rows.Item(4).Text
$ExcelWorkSheet.Rows.Item(4).Columns.Item(2).Text

Exporting Active Directory User Info to Excel Spreadsheet using PowerShell

Let’s 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 instance, their phone number (the telephoneNumber attribute), department and e-mail address .

# 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("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 2 (these cells contain the domain usernames)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
# Get the values of user attributes in AD
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail|select-object name,telephoneNumber,department,mail
# Fill in the cells with the data received from AD
$ExcelWorkSheet.Columns.Item(4).Rows.Item($i) = $ADuserProp.telephoneNumber
$ExcelWorkSheet.Columns.Item(5).Rows.Item($i) = $ADuserProp.department
$ExcelWorkSheet.Columns.Item(6).Rows.Item($i) = $ADuserProp.mail
}
# Save the XLS file and close Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)

As a result, the columns containing AD information have been added for each user in the Excel file.