Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 Windows OS Hub / PowerShell / Read and Write Data to Excel File with PowerShell

April 14, 2021 Active DirectoryMS OfficePowerShell

Read and Write Data to Excel File with PowerShell

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.

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

You can 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 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.

excel object model

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.

how to access a sample excel file with user info from powershell

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

You can display all Excel object properties as follows:

$ExcelObj| fl

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

open excel file from powershell console and read data

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

select active worksheet in excel with powershell

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

How to get excel data from a single cell in PowerShell

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 .

To get information about AD user attributes, we will use the Get-ADUser cmdlet from the PowerShell Active Directory module.

# 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.

Exporting Active Directory user information to the Excel worksheet using Powershell

Let’s consider another example 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.

You can use the Get-ADComputer cmdlet to get a list of servers in Active Directory, and use the WinRM Invoke-Command cmdlet to remotely check the status of a service on the servers.

# Create an Excel object
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# Add a workbook
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# 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(1,2) = 'Service Name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# Make the table head bold, set the font size and the column width
$ExcelWorkSheet.Rows.Item(1).Font.Bold = $true
$ExcelWorkSheet.Rows.Item(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 = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# Connect to each computer and get the service status
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer –ScriptBlock { Get-Service spooler | select Name, status }
# Fill in Excel cells with the data obtained from the server
$ExcelWorkSheet.Columns.Item(1).Rows.Item($counter) = $result.PSComputerName
$ExcelWorkSheet.Columns.Item(2).Rows.Item($counter) = $result.Name
$ExcelWorkSheet.Columns.Item(3).Rows.Item($counter) = $result.Status
$counter++
}
# Save the report and close Excel:
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)

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

For example, you can ask an employee of your HR department to keep the user register in Excel. Then using a PowerShell script and the Set-ADUser cmdlet, the employee can automatically update user info in AD (just delegate the employee the permissions to change AD user attributes and show how to run the PowerShell script). Thus you can keep an up-to-date address book with the relevant phone numbers, job titles and departments.

2 comments
2
Facebook Twitter Google + Pinterest
previous post
How to Disable NetBIOS and LLMNR Protocols in Windows Using GPO?
next post
How to Clean Up Large System Volume Information Folder on Windows?

Related Reading

Using Previous Command History in PowerShell Console

January 31, 2023

How to Install the PowerShell Active Directory Module...

January 31, 2023

Finding Duplicate E-mail (SMTP) Addresses in Exchange

January 27, 2023

How to Disable or Uninstall Internet Explorer (IE)...

January 26, 2023

How to Delete Old User Profiles in Windows?

January 25, 2023

2 comments

Manas Dash November 15, 2022 - 6:59 pm

It really got a lot of information access data by PowerShell. But when I increases the range by choosing multiple rows and columns by running below commands, it not showing what I expected as like tabular format.

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

XXXX YYYY ZZZZ
AAAA BBBB CCCC
MMM NNNN PPPP

Reply
Christian Andersen November 18, 2022 - 11:36 am

I experiences the same…

Reply

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • Using Previous Command History in PowerShell Console

    January 31, 2023
  • How to Install the PowerShell Active Directory Module and Manage AD?

    January 31, 2023
  • Finding Duplicate E-mail (SMTP) Addresses in Exchange

    January 27, 2023
  • How to Delete Old User Profiles in Windows?

    January 25, 2023
  • How to Install Free VMware Hypervisor (ESXi)?

    January 24, 2023
  • How to Enable TLS 1.2 on Windows?

    January 18, 2023
  • Allow or Prevent Non-Admin Users from Reboot/Shutdown Windows

    January 17, 2023
  • Fix: Can’t Extend Volume in Windows

    January 12, 2023
  • Wi-Fi (Internet) Disconnects After Sleep or Hibernation on Windows 10/11

    January 11, 2023
  • Adding Trusted Root Certificates on Linux

    January 9, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • Configure Google Chrome Settings with Group Policy
  • Get-ADUser: Find Active Directory User Info with PowerShell
  • Allow RDP Access to Domain Controller for Non-admin Users
  • How to Find the Source of Account Lockouts in Active Directory?
  • Get-ADComputer: Find Computer Properties in Active Directory with PowerShell
  • How to Disable or Enable USB Drives in Windows using Group Policy?
  • Configuring Proxy Settings on Windows Using Group Policy Preferences
Footer Logo

@2014 - 2023 - Windows OS Hub. All about operating systems for sysadmins


Back To Top