Microsoft powershell

How To Read Data From Excel Using PnP Powershell

In this article, we are going to see how to read data from excel files using PnP-PowerShell. To achieve this we will use COM Interface to read excel data. Before we need to be aware of each layer. The first layer will be application layer that contains one/more workbooks with each workbook containing one/more worksheets. In each worksheet, you can access data using ranges.

COM Interface

1. Application Layer:

            The application layer is the top class layer which we can open in a new instance for the excel Application on the computer.

$execelobj = New-Object -comobject Excel.Application

Application Layer

2. Workbook Layer:

In workbook layer, open the workbook inside the Excel Instance.

WorkBook Layer

You can verify whether the workbook is open using the following line


$excelObj.Workbooks | Select-Object -Property name, author, path


Then see the properties and methods you can use.

$excelObj.Workbooks | Get-Member


In the following example, I will hold the date inside the $workBook variable.

$workBook = $excelObj.Workbooks.Open(“F:Ravishankaremail_details.xlsx”)

3. Worksheet Layer:

In worksheet layer, you can list the worksheets inside the workbooks by code snippet as below.

$workBook.sheets | Select-Object -Property Name


You can select the worksheet with code snippet as below.

$workSheet = $workBook.Sheets.Item(“emails”

4. Range Layer:

In Range layer, you can get values from excel.  There are many ways to select values from the worksheet. They are,

$workSheet.cells.Item(1, 1).text
$workSheet.cells.Item(1, 1).value2

range Layer

Final code :

$filePath =”F:RavishankarDeploymentPSHELLPSHELLemail_details.xlsx”

# Create an Object Excel.Application using Com interface

$excelObj = New-Object -ComObject Excel.Application

# Disable the ‘visible’ property so the document won’t open in excel

$excelObj.Visible = $false

#open WorkBook

$workBook = $excelObj.Workbooks.Open($filePath)

#Select work sheet using Index

$workSheet = $workBook.sheets.Item(1)

#Select the range of rows should read

$range= 3

for($i=1;$i-le  $range;$i++){



Hope you have learnt how to read data from excel programmatically using PnP-PowerShell Scripting. Feel free to fill up the comment box below, should you need any assistance.

Sharing is Caring!

Leave a Reply

Your email address will not be published. Required fields are marked *