Microsoft 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_CDR

$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: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

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

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