Written by

Ravishankar

Share

  

Category

see all categories
Request Demo

How To Read Datas 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

leaderboard

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

workbook

Then see the properties and methods you can use.

$excelObj.Workbooks | Get-Member

wbook

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

$workBook = $excelObj.Workbooks.Open("F:\Ravishankar\email_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

worksheet

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

range Layer

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 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++){

     $workSheet.Columns.Item(1).Rows.Item($i).Text

  }

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.