How to Open and Read Excel Sheet

Tagged: PowerShell

Introduction#

PowerShell is a scripting language that can automate a variety of tasks, including working with Excel files. In this blog post, we’ll show you how you can use PowerShell to open and read an Excel sheet.

Solution#

Open Excel Sheet#

The first step in using PowerShell to work with an Excel sheet is opening the sheet. To do this, you can try following script:

$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=$True
$workbook=$objExcel.Workbooks.Open('C:\Scripts\Test.xlsx')
$worksheet = $workbook.worksheets.item('Sheet1')
Write-Output $worksheet.name

The script will open Excel sheet named Sheet1. If the sheet does not exist, it will raise an error.

Alternatively to open the sheet, you can filter worksheets object as follows:

$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=$True
$workbook=$objExcel.Workbooks.Open('C:\Scripts\Test.xlsx')
$worksheet = $workbook.worksheets | Where-Object {$_.name -eq 'Sheet1'}
Write-Output $worksheet.name

Read Excel Sheet#

Suppose we want to read following Excel Sheet in the file named Test.xlsx on sheet Sheet1.

You can try following script to read and print the first and last name displayed on Excel file above.

$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open('C:\Scripts\Test.xlsx')
$worksheet = $workbook.worksheets.item('Sheet1')
# We loop from second row because we won't print the header
for ($i = 2; $i -le $worksheet.UsedRange.Rows.Count; $i++) {
    $firstColumnText = $workSheet.cells.Item($i, 1).Text
    $secondColumnText = $workSheet.cells.Item($i, 2).Text
    Write-Output "$firstColumnText $secondColumnText"
}

$objExcel.Quit()

Conclusion#

If you ever find yourself needing a quick and easy way to open and read an Excel spreadsheet using PowerShell, then hopefully this post has been helpful! With just a few simple commands, you can easily access data stored within an Excel spreadsheet without having to manually open it first or install any additional software onto your computer.