How To Read Data From Excel Using PnP PowerShell

In this article, we are going to see how to read data from an Excel file using PnP-PowerShell. To achieve this, we use COM Interface. Before starting, we need to gain a little knowledge of each layer. The first layer will be an application layer that contains one or more workbooks and each workbook contains one or more worksheets. In each worksheet, you can access the data using range.

Application Layer

Application layer is the top class layer on which we can open the new instance for the Excel application on the computer.

$execelobj = New-Object -comobject Excel.Application

 WorkBook Layer

In WorkBook layer, you are going to open the workbook inside the Excel instance.

We can verify if the workbook is opened, using the following line.

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

Then, see the properties and methods that can be used.

$excelObj.Workbooks | Get-Member

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

$workBook = $excelObj.Workbooks.Open("F:\Ravishankar\email_details.xlsx")

WorkSheet Layer

In WorkSheet layer, you can list the worksheets inside the workbooks by using the below code snippet.

$workBook.sheets | Select-Object -Property Name

You can select the worksheet by using the below code.

$workSheet = $workBook.Sheets.Item("emails")

Range Layer

In Range layer, you can get the values from Excel. There are many ways to select values from a worksheet, as given below.

$workSheet.Range("A1").Text
$workSheet.Range("A1:A1").Text
$workSheet.Range("A1","A1").Text
$workSheet.cells.Item(1, 1).text
$workSheet.cells.Item(1, 1).value2
$workSheet.Columns.Item(1).Rows.Item(1).Text
$workSheet.Rows.Item(1).Columns.Item(1).Text

 Final code

$filePath ="F:\Ravishankar\Deployment\PSHELL\PSHELL\email_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 worksheet using Index
$workSheet = $workBook.sheets.Item(1)
#Select the range of rows should read
$range= 3
for($i=1;$i-le $range;$i++){
   $workSheet.Columns.Item(1).Rows.Item($i).Text
}

Hope you have learned how to read the data from Excel programmatically using PnP PowerShell scripting. Feel free to fill up the comment box below if you need any assistance.