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.
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
2. Workbook Layer:
In workbook layer, open the workbook inside the Excel Instance.
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.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: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.
